Use PostgreSQL's own types and functions in Doctrine

Map arrays, JSONB, ranges, PostGIS geometries, pgvector embeddings and over 100 column types to your entities, and call over 450 PostgreSQL functions and operators straight from DQL.

composer require martin-georgiev/postgresql-for-doctrine

Set it up with Doctrine, Symfony or Laravel.

The query you would write in PostgreSQL, and the Doctrine code that produces it.

PostgreSQL

SELECT id FROM products
WHERE attributes ->> 'color' = 'red'

DQL

SELECT p.id FROM App\Entity\Product p
WHERE JSON_GET_FIELD_AS_TEXT(p.attributes, 'color') = 'red'

PostgreSQL

SELECT id FROM products
WHERE tags && ARRAY['php', 'postgres']

DQL

SELECT p.id FROM App\Entity\Product p
WHERE OVERLAPS(p.tags, ARRAY('php', 'postgres')) = TRUE

PostgreSQL

SELECT id FROM bookings
WHERE slot && tstzrange('2026-07-01', '2026-07-02')

DQL

SELECT b.id FROM App\Entity\Booking b
WHERE OVERLAPS(b.slot, TSTZRANGE('2026-07-01', '2026-07-02')) = TRUE

PostgreSQL

SELECT id FROM stores
WHERE ST_DWithin(location, 'POINT(4.9 52.37)', 500)

DQL

SELECT st.id FROM App\Entity\Store st
WHERE ST_DWITHIN(st.location, 'POINT(4.9 52.37)', 500) = TRUE

location is a geography column, so the distance is in metres.

PostgreSQL

SELECT id FROM articles
ORDER BY embedding <=> :query

DQL

SELECT a.id, COSINE_DISTANCE(a.embedding, :query) AS HIDDEN distance
FROM App\Entity\Article a
ORDER BY distance

PostgreSQL

SELECT id FROM articles
WHERE search @@ plainto_tsquery('english', :terms)

DQL

SELECT a.id FROM App\Entity\Article a
WHERE TSMATCH(a.search, PLAINTO_TSQUERY('english', :terms)) = TRUE

PostgreSQL

SELECT id,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY placed_at)
FROM sales

DQL

SELECT s.id,
  OVER(SUM(s.amount), PARTITION BY s.customer ORDER BY s.placedAt)
FROM App\Entity\Sale s

PostgreSQL

SELECT customer_id,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales GROUP BY customer_id

DQL

SELECT IDENTITY(s.customer),
  FILTER(COUNT(s.id), WHERE s.status = 'paid') AS paid,
  PERCENTILE_CONT(0.5 WITHIN GROUP ORDER BY s.amount) AS median
FROM App\Entity\Sale s GROUP BY s.customer

PostgreSQL

SELECT id FROM products
WHERE category ~ 'Books.Databases.*'

DQL

SELECT p.id FROM App\Entity\Product p
WHERE MATCHES_LQUERY(p.category, 'Books.Databases.*') = TRUE

PostgreSQL

CREATE TYPE sale_status AS ENUM ('pending', 'paid');

SELECT id FROM sales WHERE status = 'paid'

PHP

enum SaleStatus: string
{
    case Pending = 'pending';
    case Paid = 'paid';
}

#[ORM\Column(type: 'sale_status')]
public SaleStatus $status;

$em->createQuery('
    SELECT s.id FROM App\Entity\Sale s
    WHERE s.status = :status
')->setParameter('status', SaleStatus::Paid);

sale_status is registered by a small Enum subclass; see Enums.

PostgreSQL

SELECT id FROM stores
WHERE (address).city = 'Sofia'

DQL

SELECT st.id FROM App\Entity\Store st
WHERE COMPOSITE_FIELD(st.address, 'city') = 'Sofia'

PostgreSQL

SELECT id FROM products
WHERE name % :query
ORDER BY similarity(name, :query) DESC

DQL

SELECT p.id, SIMILARITY(p.name, :query) AS HIDDEN score
FROM App\Entity\Product p
WHERE ARE_SIMILAR(p.name, :query) = TRUE
ORDER BY score DESC

PostgreSQL

SELECT id FROM products
WHERE attributes @? '$.sizes[*] ? (@ > 40)'

DQL

SELECT p.id FROM App\Entity\Product p
WHERE JSONB_PATH_EXISTS(p.attributes, '$.sizes[*] ? (@ > 40)') = TRUE

PostgreSQL

SELECT date_trunc('day', placed_at) AS day, COUNT(*)
FROM sales GROUP BY day

DQL

SELECT DATE_TRUNC('day', s.placedAt) AS day, COUNT(s.id)
FROM App\Entity\Sale s GROUP BY day

What you can map and query

Column types

Arrays: Of nearly every core type, from integer[] and text[] to jsonb[], uuid[] and inet[]
JSON and JSONB: Read back as PHP arrays, scalars and nested structures included
Ranges: Date, timestamp and numeric ranges and multiranges as value objects, infinite bounds included
PostGIS: Geometry and geography columns, with their geometry type and SRID
pgvector: vector, halfvec and sparsevec embeddings for similarity search
Hierarchies: ltree paths, and lquery and ltxtquery patterns to match them
Your own types: PostgreSQL enums backed by PHP enums, and composite types
And more: Network addresses, geometric shapes, bit strings, money, XML, hstore, citext, cube and ULID

Functions and operators in DQL

Arrays and JSON: Containment and overlap, JSON paths, and aggregation with ordering and FILTER
Text search: Full-text search, regular expressions, trigram similarity and fuzzy matching
Dates and ranges: Range operators, date arithmetic and time series
PostGIS: Spatial relationships, measurements, constructors and validation
Window functions: Rankings, running totals and moving averages with OVER
Statistics: Medians, percentiles and other statistical aggregates, plus trigonometry and rounding
And more: Network addresses, hstore, XML and XPath, UUIDs and formatting

From install to your first query

Install the package

composer require martin-georgiev/postgresql-for-doctrine

Register what you use

use Doctrine\DBAL\Types\Type;
use MartinGeorgiev\Doctrine\DBAL\Types\TextArray;
use MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Contains;

Type::addType('text[]', TextArray::class);
$configuration->addCustomStringFunction('CONTAINS', Contains::class);

Map the column and query it

#[ORM\Column(type: 'text[]')]
private array $tags;

$em->createQuery('
    SELECT p FROM App\Entity\Product p
    WHERE CONTAINS(p.tags, ARRAY(:tag)) = TRUE
')->setParameter('tag', 'postgres')->getResult();

Tested in CI against

PHP	8.2 to 8.5
PostgreSQL	16 to 18
PostGIS	3.4 to 3.6
Doctrine ORM	2.14, 2.18 and 3
Doctrine Lexer	1.2, 2.1 and 3