martin-georgiev/postgresql-for-doctrine
Enhances Doctrine ORM/DBAL with native PostgreSQL data types and DQL functions: JSONB, arrays, ranges, network, geometric and bit strings, plus operators such as CONTAINS and JSON_GET_FIELD for flexible querying. Supports PostgreSQL 9.4+ and PHP 8.2+.
Install the package (v4.10.0):
composer require martin-georgiev/postgresql-for-doctrine:^4.10
Register DBAL types — this is what makes columns map to PHP values:
use Doctrine\DBAL\Types\Type;
use MartinGeorgiev\Doctrine\DBAL\Type as PgType;
public function boot(): void
{
Type::addType('jsonb', PgType\Jsonb::class);
Type::addType('text[]', PgType\TextArray::class);
Type::addType('geometry', PgType\Geometry::class);
Type::addType('geography', PgType\Geography::class);
Type::addType('geometry[]', PgType\GeometryArray::class);
Type::addType('daterange', PgType\DateRange::class);
Type::addType('ltree', PgType\Ltree::class);
Type::addType('vector', PgType\Vector::class);
}
Register DQL functions — types alone only give you reading/writing. To query with PostgreSQL functions (JSON_GET_FIELD, ST_DWithin, RANK() OVER …, TO_HEX, MAKE_INTERVAL, …) the function classes must be registered with the ORM (e.g. in config/doctrine.php for laravel-doctrine/orm):
// config/doctrine.php
'custom_string_functions' => [
MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetField::class,
MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\StDWithin::class,
MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ToHex::class,
MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\RegexpSplitToArray::class,
// …
],
'custom_numeric_functions' => [
// PERCENTILE_CONT, PERCENTILE_DISC, MODE, BIT_COUNT, RANDOM, …
],
'custom_datetime_functions' => [
// TO_TIMESTAMP, MAKE_INTERVAL, …
],
The DQL name is whatever the function class declares — grep the registered class for its name before using it in DQL. 4.10.0 is purely additive: new function classes slot into the existing custom_*_functions blocks, no config shape changes.
First use case — JSONB column on a Doctrine entity:
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity]
class Product
{
#[ORM\Column(type: 'jsonb')]
public array $metadata = [];
#[ORM\Column(type: 'timestamp', nullable: true)]
public ?\DateTimeImmutable $publishedAt = null;
}
$product->metadata = ['price' => 19.99, 'tags' => ['sale', 'premium']];
$em->persist($product);
$em->flush();
$rows = $em->createQueryBuilder()
->select('p')
->from(Product::class, 'p')
->where("JSON_GET_FIELD(p.metadata, '$.price') > :min")
->setParameter('min', 10)
->getQuery()
->getResult();
BIT_COUNT, JSON, MAKE_INTERVAL, PG_INPUT_IS_VALID, ERROR_ON_NULL, TO_HEX/TO_BIN/TO_OCT, TRIM_ARRAY, REGEXP_SPLIT_TO_ARRAY, XMLSERIALIZE, RANDOM with bounds, range/multirange MINUS_MULTI).laravel-doctrine/orm.IGNORE NULLS/RESPECT NULLS, and flag arguments for ARRAY_SORT/RANDOM. No breaking changes; the 4.9.0 precision notes remain the last behaviour-changing release.Pattern: Treat type registration and function registration as two separate, ordered concerns.
| Concern | Registers | Needed for |
|---|---|---|
Type::addType() |
DBAL type | persisting/reading columns, schema tool |
custom_*_functions |
DQL node | using the function inside createQueryBuilder() |
Tip: Register types in a DoctrineServiceProvider that boots before the Doctrine manager is resolved, otherwise Schema:: introspection and doctrine:schema:validate will report unknown column types.
Tip (4.10.0): When a release adds functions, only the class list changes — diff the CHANGELOG against your config block and append; no need to re-register types.
#[ORM\Column(type: 'jsonb')]
public array $metadata = [];
// read a nested field server-side
->where("JSON_GET_FIELD(p.metadata, '$.specs.weight') > :w")
Tip: Use PostgreSQL operators for existence/containment instead of pulling JSON into PHP — it keeps the query indexable (GIN on the jsonb column).
Tip (4.10.0): JSON() builds json from a text expression in-query — handy for shaping ad-hoc payloads (JSON('{"id":' || p.id || '}')), but the result is json, not jsonb; cast explicitly if you need containment/operators or jsonb column mapping.
#[ORM\Column(type: 'text[]')]
public array $tags = [];
Tip (4.9.0): numeric arrays now accept every real/double-precision form PostgreSQL accepts, and signed numbers (-1, -1.5) are accepted wherever a DQL function takes a numeric argument.
New in 4.10.0 — array manipulation in the query:
// sort with direction + null ordering in a single call
->select("ARRAY_SORT(p.scores, 'DESC', 'NULLS FIRST') AS ranked")
// deterministic truncation from the tail
->select('TRIM_ARRAY(p.tags, 2) AS trimmed')
// split a delimited string straight into text[]
->where(":needle = ANY(REGEXP_SPLIT_TO_ARRAY(p.csvLine, ','))")
->setParameter('needle', 'urgent')
Tip: ARRAY_SORT flags are arguments of the function itself, not DQL keywords — pass them as string literals and keep the order the docs specify (direction, then nulls placement). TRIM_ARRAY removes a count of trailing elements, so a count larger than the array yields {}.
#[ORM\Column(type: 'daterange')]
public DateRange $duration;
Tip (4.9.0): range round-tripping was hardened — bound parsing now preserves infinity bound sign ((,5] vs [5,) no longer swapped), five-digit years and BC dates, and all significant digits of floats in numeric ranges. Workarounds in PHP can go.
New in 4.10.0 — set difference: RANGE_MINUS_MULTI and MULTIRANGE_MINUS_MULTI let you subtract a multirange from a range (or from another multirange) without decomposing bounds in PHP:
->select('RANGE_MINUS_MULTI(b.blackout, b.holidays) AS effectiveWindow')
// "is the booking fully covered by the excluded set?" is now a comparison, not a loop
->where('RANGE_MINUS_MULTI(b.window, b.excluded) = :emptyRange')
Tip: Result cardinality can change (a range minus a multirange is itself a multirange) — map the projection to a multirange-aware type or read it as a string.
->where('ST_DWithin(p.location, :point, 1000)')
use MartinGeorgiev\Doctrine\DBAL\Type\ValueObject\WktSpatialData;
$point = WktSpatialData::fromWkt('POINT(-122.4194 37.7749)'); // still supported
$point = WktSpatialData::fromString('POINT(-122.4194 37.7749)'); // 4.9.0: uniform factory
Tip: fromString() is the consistent entry point across all value objects. Prefer it in new code; keep fromWkt() only for readability.
The two functions you will reach for daily, e.g. in an import/ingest job:
// find rows that will break downstream consumers
$broken = $em->createQueryBuilder()
->select('g.id, ST_IsValidReason(g.geom) AS reason')
->from(GeoFeature::class, 'g')
->where('ST_IsValid(g.geom) = FALSE')
->getQuery()
->getResult();
// repair on the way in
$em->createQuery(
'UPDATE GeoFeature g SET g.geom = ST_MakeValid(g.geom) WHERE g.id = :id'
)->setParameter('id', $id)->execute();
Pattern: run ST_IsValidReason in a nightly command and log the reason string — it names the exact OGC defect (self-intersection, ring orientation, etc.), which is far more actionable than a boolean.
Extract component geometries / rings / points without hydration, and convert text formats in-query:
->select('ST_AsEWKT(ST_GeometryN(a.geom, 1)) AS firstPart')
->where('ST_NumGeometries(a.geom) > 1')
MULTIPOLYGON into parts server-side.ST_CollectionExtract with a single geometry, ST_Subdivide with a single geometry, and ST_Scale with a geometry factor now accept the shortened signatures PostgreSQL allows.geometry[] and geography[] accept the same geometry_type and srid column options as scalar columns:
#[ORM\Column(type: 'geometry[]', options: ['geometry_type' => 'POINT', 'srid' => 4326])]
public array $samples = [];
Tip: Always set srid. Mixed-SRID arrays are the single most common cause of "operation on mixed SRID geometries" at query time, and the option lets the schema tool emit the correct typmod.
OVER is supported, along with the ranking and value families.
// rank products within each category
->select('p.id', 'p.price', 'RANK() OVER (PARTITION BY p.categoryId ORDER BY p.price DESC) AS priceRank')
// delta between consecutive measurements
->select('m.value', 'LAG(m.value, 1) OVER (ORDER BY m.recordedAt) AS previousValue')
Available: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE.
New in 4.10.0 — null handling on value functions: IGNORE NULLS / RESPECT NULLS are now parsed after the window value functions. This is the difference between "previous measurement" and "previous non-null measurement":
->select("LAST_VALUE(m.value) IGNORE NULLS OVER (PARTITION BY m.sensorId ORDER BY m.recordedAt) AS lastKnown")
Tip: Window functions are evaluated after WHERE but before ORDER BY / LIMIT. To filter on a rank you must wrap the query — use a native query or a subselect; WHERE priceRank = 1 will not parse.
FILTER and Ordered-Set AggregatesReplace conditional SUM(CASE WHEN …) boilerplate:
->select(
'COUNT(*) AS total',
'COUNT(*) FILTER (WHERE o.refunded = true) AS refundedCount',
'PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.total) AS medianOrder'
)
MODE() — most frequent value (great for "most common shipping country").PERCENTILE_DISC / PERCENTILE_CONT — discrete vs interpolated medians; document which one your report means.TO_TIMESTAMP accepts a raw Unix epoch integer, so epoch-in-bigint legacy tables become queryable directly.Tip: FILTER is PostgreSQL-only and not portable back to MySQL — keep these queries in dedicated repository methods so a future multi-database build doesn't have to untangle them.
->where('p.categoryPath <@ :path')
->setParameter('path', 'Sports.Football')
Tip: LCA() accepts an array of paths (4.9.0), so nearest-common-ancestor lookups across N branches are a single function call. Labels in lquery / ltxtquery are strictly validated to digits and letters — Sports.Football_2 is rejected client-side rather than producing a confusing SQL error.
#[ORM\Column(type: 'vector')]
public array $embedding = [];
->where('i.embedding <=> :q < 0.5')->setParameter('q', $embedding) // cosine
->where('i.embedding <-> :q < 10') // L2
Tip: Create an HNSW/IVFFlat index with matching vector_cosine_ops / vector_l2_ops; the operator in your WHERE and the index opclass must agree or the planner silently seq-scans.
Two functions that move validation into the database, where it can run over rows you never hydrate:
// which rows will blow up a later cast?
->select('r.id, r.rawPayload')
->where("PG_INPUT_IS_VALID('jsonb', r.rawPayload) = FALSE")
// fail loudly instead of silently writing NULL into a NOT NULL-ish domain column
->select("ERROR_ON_NULL(r.mandatoryField) AS guarded")
PG_INPUT_IS_VALID(type, value) is the ingest workhorse: sweep staging tables for text that won't cast to jsonb/numeric/date before you run the migration that casts them.ERROR_ON_NULL(...) turns a would-be silent NULL into a server-side error — good for assertion-style reports, not for user-facing write paths.Tip: Prefer PG_INPUT_IS_VALID in a nightly/queue command; a single bad row aborts the statement, so batch with LIMIT or a WHERE id BETWEEN … window. Do the actual cleanup with TO_JSONB/casts after the sweep.
->select(
'TO_HEX(i.flags) AS flagsHex',
'TO_OCT(i.mask) AS maskOct',
'TO_BIN(i.mask) AS maskBin',
"JSON_BUILD_OBJECT('id', p.id, 'price', p.price) AS payload",
'XMLSERIALIZE(CONTENT x.doc AS TEXT) AS xmlString'
)
TO_HEX / TO_BIN / TO_OCT — render integers in non-decimal bases for checksums, permission masks, or export formats without post-processing in PHP.JSON(...) — construct json from a text expression.XMLSERIALIZE(...) — turn xml/xmltype columns into text for APIs and logs.Tip: Keep these as projections, not as WHERE predicates — you lose index usage as soon as you wrap a column in a function. If you filter on a hex/base representation often, add an expression index that matches the exact call.
MAKE_INTERVAL (new in 4.10.0)Building intervals from stored parts (e.g. a trial_length, trial_unit pair) is now a single expression:
->select('MAKE_INTERVAL(0, 0, 0, s.trialDays, s.trialHours) AS trialInterval')
// date maths on the result
->where('s.createdAt + MAKE_INTERVAL(0, 0, 0, s.graceDays) > :now')
->setParameter('now', new \DateTimeImmutable())
Tip: Argument order is positional (years, months, weeks, days, hours, minutes, seconds). To skip a leading component, pass 0, not NULL — NULL in any position makes the whole interval NULL and will quietly break comparisons.
// how many permission bits are set?
->select('BIT_COUNT(u.permissions) AS grantedCount')
->where('BIT_COUNT(u.permissions) = 0') // find unconfigured accounts
// bounded randomness, no PHP-side range maths
->orderBy('RANDOM(1, 1000000)', 'ASC')
Tip: BIT_COUNT takes a bit or bytea value, so cast integer columns explicitly (BIT_COUNT(u.permissions::bit(64)) style) rather than relying on implicit conversion. RANDOM(min, max) is non-deterministic per execution — never put it in a cached/queued query or a test assertion, and keep it out of ORDER BY on large tables where it forces a full sort.
The most common first-day error is [Syntax Error] Error: Expected … got 'JSON_GET_FIELD'. It is not a type problem — the DQL function class was never registered. Keep a single config block listing all function classes and add to it whenever a query fails at parse time.
Several of the new functions only exist on recent PostgreSQL. RANDOM(min, max) and PG_INPUT_IS_VALID need a modern server; on an older one the DQL parses, then the statement fails at execution with "function does not exist". Check your target server before adopting a new function in production queries, and pin the image tag in CI.
WktSpatialData, DateRange, Ltree, etc. now throw package-specific exceptions instead of generic \InvalidArgumentException/\RuntimeException. Action: audit catch blocks that swallow base SPL exceptions around value-object construction — they may silently miss a package exception. Catch MartinGeorgiev\Doctrine\DBAL\Type\Exception\… explicitly, or keep catching the interface.
4.9.0 deliberately changed how data is read back:
Any test asserting on serialised geometry, json_encoded rows, or assertEquals on floats will diff. Switch to assertEqualsWithDelta for coordinates and regenerate fixtures — this is a correctness improvement, not a regression.
ST_MakeValid Can Change Geometry TypeST_MakeValid on a self-intersecting POLYGON can return a MULTIPOLYGON or GEOMETRYCOLLECTION. If the column is declared geometry(Polygon, 4326), the repaired value will fail the typmod check on write. Either widen the column to GEOMETRY, or wrap with ST_CollectionExtract(…, 3) to force polygons.
ORDER BY (fixed in 4.9.0)An ORDER BY belonging to a fetch-joined collection could leak into an aggregate's ORDER BY when the two were combined. If you added manual workarounds (separate queries, DISTINCT), revisit them — a single query now works.
Negative numeric literals are accepted everywhere a function takes a number (since 4.9.0). Older code passed :param bindings purely to dodge the parser; those can be inlined now, though bound parameters are still preferable for plan caching.
An empty ltree element inside ltree[] is preserved rather than dropped or promoted to NULL. If you branch on array length, verify the semantics — [] vs [''] are different and the latter is correctly round-tripped.
geometry_type / srid Options Must Match RealityThe array options generate a typmod. Setting srid => 4326 on a geometry[] column means rows loaded from another SRID will be rejected at insert. When importing legacy data, validate SRIDs (ST_SRID) before enabling the constraint, and run ST_Transform in the migration/backfill instead of after.
Custom selection aliases like AS priceRank are not mapped to entity fields. Either add them to a DTO via NEW, or read them as scalar arrays — hydrating an entity and expecting $product->priceRank to be populated will not work.
IGNORE NULLS Scope (4.10.0)IGNORE NULLS / RESPECT NULLS are accepted only after window value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE) — not after ranking functions and not after plain aggregates such as SUM/AVG. RESPECT NULLS is the default, so its only value is documentation. Note that LAST_VALUE still respects the default frame (RANGE UNBOUNDED PRECEDING AND CURRENT ROW) — add an explicit frame clause, or you'll get the current row rather than the partition's last row.
ARRAY_SORT's descending / nulls-first flags are positional string literals, not DQL modifiers. Passing 'NULLS FIRST' before 'DESC', or passing bare keywords, produces a parse error rather than an ignored argument. When in doubt, bind the flags as parameters and print the SQL.
ERROR_ON_NULL Aborts the StatementIt raises a server-side error, which in PostgreSQL means the whole transaction is rolled back — a background report using it inside an open transaction will take unrelated work down with it. Use it in read-only/autocommit contexts, or isolate it in a dedicated connection.
$query->getSQL() shows the generated SQL with functions expanded — the fastest way to confirm a function registered correctly, to check argument/frame/flag ordering, and to see whether arguments were treated as literals or parameters. Pair with DB::listen() to catch the final parameterised statement.
For window-frame clauses (ROWS BETWEEN …), unusual OVER combinations, and any function the DQL parser still rejects, drop to $em->getConnection()->executeQuery() or DB::select() with UNNEST/jsonb_to_recordset. You keep the type mapping on write and only lose it on the ad-hoc read — usually the right trade-off.
Custom DBAL types extend the package's base types; custom DQL functions typically extend the package's abstract function node and simply declare a new name. Both survive minor upgrades, but pin the major version — 4.9.0 shipped behaviour-changing precision fixes in a patch release, and 4.10.0 only guarantees its new signatures going forward.
How can I help you explore Laravel packages today?