martin-georgiev/postgresql-for-doctrine

Quick Start

use Doctrine\DBAL\Types\Type as DoctrineType;
use Doctrine\ORM\Mapping as ORM;
use MartinGeorgiev\Doctrine\DBAL\Type;
use MartinGeorgiev\Doctrine\DBAL\Types\ValueObject\NumericRange;

// Register types with Doctrine
// DBAL 4.3+ ships its own jsonb type, which, unlike this one, reads integers beyond PHP_INT_MAX as floats and writes 1.0 instead of 1
if (DoctrineType::hasType('jsonb')) {
    DoctrineType::overrideType('jsonb', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\Jsonb");
} else {
    DoctrineType::addType('jsonb', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\Jsonb");
}
DoctrineType::addType('text[]', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\TextArray");
DoctrineType::addType('numrange', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\NumRange");

// Use in your Doctrine entities
#[ORM\Column(type: Type::JSONB)]
private array $data;

#[ORM\Column(type: Type::TEXT_ARRAY)]
private array $tags;

#[ORM\Column(type: Type::NUMRANGE)]
private NumericRange $priceRange;

// Use in DQL
$query = $em->createQuery('
    SELECT e
    FROM App\Entity\Post e
    WHERE CONTAINS(e.tags, ARRAY(:tags)) = TRUE
    AND JSON_GET_FIELD(e.data, :field) = :value
');

🚀 Features Highlight

Data Types

• Array Types
  • Integer arrays (int[], smallint[], bigint[])
  • Float arrays (real[], double precision[])
  • Text arrays (text[])
  • Boolean arrays (bool[])
  • JSONB arrays (jsonb[])
• Binary Types
  • Raw binary data (bytea, bytea[])
• Bit String Types
  • Fixed-length bit strings (bit, bit[])
  • Variable-length bit strings (bit varying, bit varying[])
• JSON Types
  • Native JSONB support
  • JSON field operations
  • JSON construction and manipulation
• Network Types
  • IP addresses (inet, inet[])
  • Network CIDR notation (cidr, cidr[])
  • MAC addresses (macaddr, macaddr[], macaddr8, macaddr8[])
• Geometric Types
  • Box (box, box[])
  • Circle (circle, circle[])
  • Line (line, line[])
  • Line segment (lseg, lseg[])
  • Path (path, path[])
  • Point (point, point[])
  • Polygon (polygon, polygon[])
  • PostGIS Geometry (geometry, geometry[])
  • PostGIS Geography (geography, geography[])
• Range Types
  • Date and time ranges (daterange, daterange[], tsrange, tsrange[], tstzrange, tstzrange[])
  • Numeric ranges (numrange, numrange[], int4range, int4range[], int8range, int8range[])
  • Multiranges (datemultirange, datemultirange[], int4multirange, int4multirange[], int8multirange, int8multirange[], nummultirange, nummultirange[], tsmultirange, tsmultirange[], tstzmultirange, tstzmultirange[])
• Date and Time Types
  • Arrays (date[], timestamp[], timestamptz[])
  • Time durations with DateInterval support (interval, interval[])
  • String-represented time with timezone (timetz, timetz[])
• Text Search Types
  • Full-text search document (tsvector, tsvector[])
  • Full-text search query (tsquery, tsquery[])
• Case-Insensitive Text Types (requires citext extension)
  • Case-insensitive text (citext, citext[])
• ULID Types (requires pgx_ulid extension)
  • Sortable, timestamp-prefixed identifiers (ulid, ulid[])
• Key-Value Types (requires hstore extension)
  • Key-value store (hstore, hstore[])
• Monetary Types
  • Currency amounts (money, money[])
• XML Types
  • Native XML document storage (xml, xml[])
• Hierarchical Types
  • Label-tree data (ltree, ltree[])
• Vector Types (requires pgvector extension)
  • Fixed-dimension float vector (vector)
  • Half-precision float vector (halfvec)
  • Sparse vector (sparsevec)
• Enum Types
  • User-defined PostgreSQL enum types via Enum base class
• Composite Types
  • User-defined PostgreSQL composite (row) types via Composite base class
  • Access fields from user-defined composite types via COMPOSITE_FIELD() function

PostgreSQL Operators

• Array Operations
  • Contains (@>)
  • Is contained by (<@)
  • Overlaps (&&)
  • Array aggregation with ordering
• JSON Operations
  • Field access (->, ->>)
  • Path operations (#>, #>>)
  • JSON containment and existence operators
• Range Operations
  • Containment checks (in PHP value objects and for DQL queries with @> and <@)
  • Overlaps (&&)
• PostGIS Spatial Operations
  • Bounding box relationships (<<, >>, &<, &>, |&>, &<|, <<|, |>>)
  • Spatial containment (@, ~)
  • Distance calculations (<->, <#>, <<->>, |=|)
  • N-dimensional operations (&&&)

Functions

• Text Search
  • Full text search (to_tsvector, to_tsquery)
  • Pattern matching (ilike, similar to)
  • Regular expressions
  • String manipulation (ascii, btrim, char_length, chr, decode, encode, initcap, lpad, ltrim, octet_length, quote_ident, quote_literal, quote_nullable, rpad, rtrim, strpos, translate)
  • Trigram similarity (similarity, word_similarity, strict_word_similarity) (requires pg_trgm extension)
  • Hashing & Checksum (md5, sha224, sha256, sha384, sha512, crc32, crc32c, reverse for bytea)
• Array Functions
  • Generic array aggregation and manipulation (array_agg, array_append, array_prepend, array_remove, array_replace, array_shuffle)
  • Array dimensions and length
  • Special aggregates (any_value)
• JSON Functions
  • JSON construction (json_build_object, jsonb_build_object)
  • JSON manipulation and transformation
  • Row to JSON (row_to_json, row)
• Date Functions
  • Current timestamp functions (clock_timestamp, statement_timestamp, transaction_timestamp)
  • Interval adjustment (justify_days, justify_hours, justify_interval)
• Aggregate Functions
  • Aggregation with ordering and distinct (array_agg, json_agg, jsonb_agg)
  • Conditional aggregation with FILTER (WHERE ...) on any aggregate
  • Statistical aggregates (bool_and, bool_or, every, bit_and, bit_or, bit_xor, stddev, stddev_pop, var_pop, variance, corr, covar_pop, covar_samp)
  • Ordered-set aggregates with WITHIN GROUP (ORDER BY ...) (percentile_cont, percentile_disc, mode)
  • Special aggregates (any_value, xmlagg)
• Window Functions
  • Any aggregate over a window with OVER, including PARTITION BY, ORDER BY and ROWS, RANGE and GROUPS frame clauses
  • Ranking (row_number, rank, dense_rank, percent_rank, cume_dist, ntile)
  • Value (lag, lead, first_value, last_value, nth_value)
• Hstore Functions (requires hstore extension)
  • Key and value extraction (akeys, avals, skeys, svals)
  • Key inspection (defined)
  • Key deletion (delete)
  • JSON conversion (hstore_to_json, hstore_to_json_loose)
• XML Functions
  • XML aggregation, construction, manipulation, validation (xmlagg, xmlcomment, xmlconcat, xml_is_well_formed)
  • XPath querying (xpath, xpath_exists)
• Mathematical/Arithmetic Functions
  • Trigonometric functions (sin, cos, tan, asin, acos, atan, degree variants)
  • Hyperbolic functions (sinh, cosh, tanh, asinh, acosh, atanh)
  • Number theory functions (gcd, lcm, factorial, div)
  • Statistical functions (erf, erfc, random_normal)
• Range Functions
  • Range construction (daterange, int4range, int8range, numrange, tsrange, tstzrange)
  • Range aggregation (range_agg, range_intersect_agg)
• Utility Functions
  • Type casting (cast)
  • Data formatting (to_char, to_number)
  • UUID generation and inspection (uuidv4, uuidv7, uuid_extract_timestamp, uuid_extract_version)
• Vector Distance Functions (requires pgvector extension)
  • Distance and similarity (l2_distance, cosine_distance, inner_product)

Full documentation, also published at postgresql-for-doctrine.dev:

• Getting started - From install to a first query
• Available Types
• Value Objects for Range Types
• PostgreSQL ltree Types
• Infinity Values
• Writing DQL - How PostgreSQL syntax is written in DQL
• Available Functions and Operators - Overview and cross-references
  • Array and JSON Functions
  • PostGIS Spatial Functions
  • Text and Pattern Functions
  • Date and Range Functions
  • Mathematical and statistical functions
  • Utility Functions
  • XML Functions
  • Window Functions
  • Network Address Functions
• Examples
• PostGIS geometry and geography
• Geometry Arrays
• Troubleshooting - Common errors, by the message you see

📦 Installation

composer require martin-georgiev/postgresql-for-doctrine

Then follow Getting started to register a type and a function and run a first query.

🔧 Integration Guides

• Symfony setup
• Laravel setup
• Doctrine setup

💡 Usage Examples

See our Examples for detailed code samples.

🧪 Testing

Unit Tests

composer run-unit-tests

PostgreSQL Integration Tests

We also provide integration tests that run against a real PostgreSQL database with PostGIS:

# Start PostgreSQL with PostGIS using Docker Compose
docker compose up -d

# Run integration tests
composer run-integration-tests

# Stop PostgreSQL
docker compose down -v

See tests/Integration/README.md for more details.

⭐ Support the Project

💖 GitHub Sponsors

If you find this package useful for your projects, please consider sponsoring the development via GitHub Sponsors. Your support helps maintain this package, create new features, and improve documentation.

Benefits of sponsoring:

• Priority support for issues and feature requests
• Direct access to the maintainer
• Help sustain open-source development

Other Ways to Help

• Star the repository
• Report issues
• Contribute with code or documentation
• Share the project with others

📝 License

This package is licensed under the MIT License. See the LICENSE file for details.

⚡️ Powered by