martin-georgiev/postgresql-for-doctrine
Adds PostgreSQL-specific power to Doctrine DBAL/ORM: rich native types (jsonb, arrays, ranges, network, geometric, etc.) plus DQL functions/operators for JSON and array querying. Supports PostgreSQL 9.4+ and PHP 8.2+.
This guide covers integration with Laravel 11.x+ using the community-supported Laravel Doctrine package (laravel-doctrine/orm v3.x).
First, install Laravel Doctrine ORM:
composer require laravel-doctrine/orm
Then publish the configuration:
php artisan vendor:publish --tag="config" --provider="LaravelDoctrine\ORM\DoctrineServiceProvider"
Register the DBAL types you plan to use. The full set of available types can be found in AVAILABLE-TYPES.md.
<?php
// config/doctrine.php
return [
// ... other configuration
'managers' => [
'default' => [
// ... other configuration
'type_mappings' => [
// Binary type mappings
'bytea' => 'bytea',
'bytea[]' => 'bytea[]',
'_bytea' => 'bytea[]',
// Bit type mappings
'bit' => 'bit',
'bit[]' => 'bit[]',
'_bit' => 'bit[]',
'bit varying' => 'bit varying',
'varbit' => 'bit varying',
'bit varying[]' => 'bit varying[]',
'_varbit' => 'bit varying[]',
// Array type mappings
'_bool' => 'bool[]',
'bool[]' => 'bool[]',
'smallint[]' => 'smallint[]',
'_int2' => 'smallint[]',
'integer[]' => 'integer[]',
'_int4' => 'integer[]',
'bigint[]' => 'bigint[]',
'_int8' => 'bigint[]',
'double precision[]' => 'double precision[]',
'_float8' => 'double precision[]',
'real[]' => 'real[]',
'_float4' => 'real[]',
'_text' => 'text[]',
'text[]' => 'text[]',
'uuid[]' => 'uuid[]',
'_uuid' => 'uuid[]',
// Case-insensitive text type mappings
'citext' => 'citext',
'citext[]' => 'citext[]',
'_citext' => 'citext[]',
// Datetime array type mappings
'date[]' => 'date[]',
'_date' => 'date[]',
'interval' => 'interval',
'interval[]' => 'interval[]',
'_interval' => 'interval[]',
'timestamp[]' => 'timestamp[]',
'_timestamp' => 'timestamp[]',
'timestamptz[]' => 'timestamptz[]',
'_timestamptz' => 'timestamptz[]',
'timetz' => 'timetz',
'timetz[]' => 'timetz[]',
'_timetz' => 'timetz[]',
// JSON type mappings
'jsonb' => 'jsonb',
'_jsonb' => 'jsonb[]',
'jsonb[]' => 'jsonb[]',
// Network type mappings
'cidr' => 'cidr',
'cidr[]' => 'cidr[]',
'_cidr' => 'cidr[]',
'inet' => 'inet',
'inet[]' => 'inet[]',
'_inet' => 'inet[]',
'macaddr' => 'macaddr',
'macaddr[]' => 'macaddr[]',
'_macaddr' => 'macaddr[]',
'macaddr8' => 'macaddr8',
'macaddr8[]' => 'macaddr8[]',
'_macaddr8' => 'macaddr8[]',
// Geometric type mappings
'box' => 'box',
'box[]' => 'box[]',
'_box' => 'box[]',
'circle' => 'circle',
'circle[]' => 'circle[]',
'_circle' => 'circle[]',
'line' => 'line',
'line[]' => 'line[]',
'_line' => 'line[]',
'lseg' => 'lseg',
'lseg[]' => 'lseg[]',
'_lseg' => 'lseg[]',
'path' => 'path',
'path[]' => 'path[]',
'_path' => 'path[]',
'point' => 'point',
'point[]' => 'point[]',
'_point' => 'point[]',
'polygon' => 'polygon',
'polygon[]' => 'polygon[]',
'_polygon' => 'polygon[]',
// PostGIS spatial type mappings
'geometry' => 'geometry',
'geometry[]' => 'geometry[]',
'_geometry' => 'geometry[]',
'geography' => 'geography',
'geography[]' => 'geography[]',
'_geography' => 'geography[]',
// Range type mappings
'daterange' => 'daterange',
'daterange[]' => 'daterange[]',
'_daterange' => 'daterange[]',
'int4range' => 'int4range',
'int4range[]' => 'int4range[]',
'_int4range' => 'int4range[]',
'int8range' => 'int8range',
'int8range[]' => 'int8range[]',
'_int8range' => 'int8range[]',
'numrange' => 'numrange',
'numrange[]' => 'numrange[]',
'_numrange' => 'numrange[]',
'tsrange' => 'tsrange',
'tsrange[]' => 'tsrange[]',
'_tsrange' => 'tsrange[]',
'tstzrange' => 'tstzrange',
'tstzrange[]' => 'tstzrange[]',
'_tstzrange' => 'tstzrange[]',
// Multirange type mappings
'datemultirange' => 'datemultirange',
'datemultirange[]' => 'datemultirange[]',
'_datemultirange' => 'datemultirange[]',
'int4multirange' => 'int4multirange',
'int4multirange[]' => 'int4multirange[]',
'_int4multirange' => 'int4multirange[]',
'int8multirange' => 'int8multirange',
'int8multirange[]' => 'int8multirange[]',
'_int8multirange' => 'int8multirange[]',
'nummultirange' => 'nummultirange',
'nummultirange[]' => 'nummultirange[]',
'_nummultirange' => 'nummultirange[]',
'tsmultirange' => 'tsmultirange',
'tsmultirange[]' => 'tsmultirange[]',
'_tsmultirange' => 'tsmultirange[]',
'tstzmultirange' => 'tstzmultirange',
'tstzmultirange[]' => 'tstzmultirange[]',
'_tstzmultirange' => 'tstzmultirange[]',
// Text search type mappings
'tsquery' => 'tsquery',
'tsquery[]' => 'tsquery[]',
'_tsquery' => 'tsquery[]',
'tsvector' => 'tsvector',
'tsvector[]' => 'tsvector[]',
'_tsvector' => 'tsvector[]',
// Monetary type mappings
'money' => 'money',
'money[]' => 'money[]',
'_money' => 'money[]',
// Key-value type mappings
'hstore' => 'hstore',
'hstore[]' => 'hstore[]',
'_hstore' => 'hstore[]',
// Hierarchical type mappings
'ltree' => 'ltree',
'ltree[]' => 'ltree[]',
'_ltree' => 'ltree[]',
// XML type mappings
'xml' => 'xml',
'xml[]' => 'xml[]',
'_xml' => 'xml[]',
// Vector type mappings
'halfvec' => 'halfvec',
'sparsevec' => 'sparsevec',
'vector' => 'vector'
],
],
],
'custom_types' => [
// Binary types
'bytea' => MartinGeorgiev\Doctrine\DBAL\Types\Bytea::class,
'bytea[]' => MartinGeorgiev\Doctrine\DBAL\Types\ByteaArray::class,
// Bit types
'bit' => MartinGeorgiev\Doctrine\DBAL\Types\Bit::class,
'bit[]' => MartinGeorgiev\Doctrine\DBAL\Types\BitArray::class,
'bit varying' => MartinGeorgiev\Doctrine\DBAL\Types\BitVarying::class,
'bit varying[]' => MartinGeorgiev\Doctrine\DBAL\Types\BitVaryingArray::class,
// Array types
'bool[]' => MartinGeorgiev\Doctrine\DBAL\Types\BooleanArray::class,
'bigint[]' => MartinGeorgiev\Doctrine\DBAL\Types\BigIntArray::class,
'integer[]' => MartinGeorgiev\Doctrine\DBAL\Types\IntegerArray::class,
'smallint[]' => MartinGeorgiev\Doctrine\DBAL\Types\SmallIntArray::class,
'double precision[]' => MartinGeorgiev\Doctrine\DBAL\Types\DoublePrecisionArray::class,
'real[]' => MartinGeorgiev\Doctrine\DBAL\Types\RealArray::class,
'text[]' => MartinGeorgiev\Doctrine\DBAL\Types\TextArray::class,
'uuid[]' => MartinGeorgiev\Doctrine\DBAL\Types\UuidArray::class,
// Case-insensitive text types
'citext' => MartinGeorgiev\Doctrine\DBAL\Types\Citext::class,
'citext[]' => MartinGeorgiev\Doctrine\DBAL\Types\CitextArray::class,
// Date and time types
'date[]' => MartinGeorgiev\Doctrine\DBAL\Types\DateArray::class,
'interval' => MartinGeorgiev\Doctrine\DBAL\Types\Interval::class,
'interval[]' => MartinGeorgiev\Doctrine\DBAL\Types\IntervalArray::class,
'timestamp[]' => MartinGeorgiev\Doctrine\DBAL\Types\TimestampArray::class,
'timestamptz[]' => MartinGeorgiev\Doctrine\DBAL\Types\TimestampTzArray::class,
'timetz' => MartinGeorgiev\Doctrine\DBAL\Types\Timetz::class,
'timetz[]' => MartinGeorgiev\Doctrine\DBAL\Types\TimetzArray::class,
// JSON types
'jsonb' => MartinGeorgiev\Doctrine\DBAL\Types\Jsonb::class,
'jsonb[]' => MartinGeorgiev\Doctrine\DBAL\Types\JsonbArray::class,
// Network types
'cidr' => MartinGeorgiev\Doctrine\DBAL\Types\Cidr::class,
'cidr[]' => MartinGeorgiev\Doctrine\DBAL\Types\CidrArray::class,
'inet' => MartinGeorgiev\Doctrine\DBAL\Types\Inet::class,
'inet[]' => MartinGeorgiev\Doctrine\DBAL\Types\InetArray::class,
'macaddr' => MartinGeorgiev\Doctrine\DBAL\Types\Macaddr::class,
'macaddr[]' => MartinGeorgiev\Doctrine\DBAL\Types\MacaddrArray::class,
'macaddr8' => MartinGeorgiev\Doctrine\DBAL\Types\Macaddr8::class,
'macaddr8[]' => MartinGeorgiev\Doctrine\DBAL\Types\Macaddr8Array::class,
// Geometric types
'box' => MartinGeorgiev\Doctrine\DBAL\Types\Box::class,
'box[]' => MartinGeorgiev\Doctrine\DBAL\Types\BoxArray::class,
'circle' => MartinGeorgiev\Doctrine\DBAL\Types\Circle::class,
'circle[]' => MartinGeorgiev\Doctrine\DBAL\Types\CircleArray::class,
'line' => MartinGeorgiev\Doctrine\DBAL\Types\Line::class,
'line[]' => MartinGeorgiev\Doctrine\DBAL\Types\LineArray::class,
'lseg' => MartinGeorgiev\Doctrine\DBAL\Types\Lseg::class,
'lseg[]' => MartinGeorgiev\Doctrine\DBAL\Types\LsegArray::class,
'path' => MartinGeorgiev\Doctrine\DBAL\Types\Path::class,
'path[]' => MartinGeorgiev\Doctrine\DBAL\Types\PathArray::class,
'point' => MartinGeorgiev\Doctrine\DBAL\Types\Point::class,
'point[]' => MartinGeorgiev\Doctrine\DBAL\Types\PointArray::class,
'polygon' => MartinGeorgiev\Doctrine\DBAL\Types\Polygon::class,
'polygon[]' => MartinGeorgiev\Doctrine\DBAL\Types\PolygonArray::class,
// PostGIS spatial types
'geometry' => MartinGeorgiev\Doctrine\DBAL\Types\Geometry::class,
'geometry[]' => MartinGeorgiev\Doctrine\DBAL\Types\GeometryArray::class,
'geography' => MartinGeorgiev\Doctrine\DBAL\Types\Geography::class,
'geography[]' => MartinGeorgiev\Doctrine\DBAL\Types\GeographyArray::class,
// Range types
'daterange' => MartinGeorgiev\Doctrine\DBAL\Types\DateRange::class,
'int4range' => MartinGeorgiev\Doctrine\DBAL\Types\Int4Range::class,
'int8range' => MartinGeorgiev\Doctrine\DBAL\Types\Int8Range::class,
'numrange' => MartinGeorgiev\Doctrine\DBAL\Types\NumRange::class,
'tsrange' => MartinGeorgiev\Doctrine\DBAL\Types\TsRange::class,
'tstzrange' => MartinGeorgiev\Doctrine\DBAL\Types\TstzRange::class,
// Range array types
'daterange[]' => MartinGeorgiev\Doctrine\DBAL\Types\DateRangeArray::class,
'int4range[]' => MartinGeorgiev\Doctrine\DBAL\Types\Int4RangeArray::class,
'int8range[]' => MartinGeorgiev\Doctrine\DBAL\Types\Int8RangeArray::class,
'numrange[]' => MartinGeorgiev\Doctrine\DBAL\Types\NumRangeArray::class,
'tsrange[]' => MartinGeorgiev\Doctrine\DBAL\Types\TsRangeArray::class,
'tstzrange[]' => MartinGeorgiev\Doctrine\DBAL\Types\TstzRangeArray::class,
// Multirange types
'datemultirange' => MartinGeorgiev\Doctrine\DBAL\Types\DateMultirange::class,
'datemultirange[]' => MartinGeorgiev\Doctrine\DBAL\Types\DateMultirangeArray::class,
'int4multirange' => MartinGeorgiev\Doctrine\DBAL\Types\Int4Multirange::class,
'int4multirange[]' => MartinGeorgiev\Doctrine\DBAL\Types\Int4MultirangeArray::class,
'int8multirange' => MartinGeorgiev\Doctrine\DBAL\Types\Int8Multirange::class,
'int8multirange[]' => MartinGeorgiev\Doctrine\DBAL\Types\Int8MultirangeArray::class,
'nummultirange' => MartinGeorgiev\Doctrine\DBAL\Types\NumMultirange::class,
'nummultirange[]' => MartinGeorgiev\Doctrine\DBAL\Types\NumMultirangeArray::class,
'tsmultirange' => MartinGeorgiev\Doctrine\DBAL\Types\TsMultirange::class,
'tsmultirange[]' => MartinGeorgiev\Doctrine\DBAL\Types\TsMultirangeArray::class,
'tstzmultirange' => MartinGeorgiev\Doctrine\DBAL\Types\TstzMultirange::class,
'tstzmultirange[]' => MartinGeorgiev\Doctrine\DBAL\Types\TstzMultirangeArray::class,
// Text search types
'tsquery' => MartinGeorgiev\Doctrine\DBAL\Types\Tsquery::class,
'tsquery[]' => MartinGeorgiev\Doctrine\DBAL\Types\TsqueryArray::class,
'tsvector' => MartinGeorgiev\Doctrine\DBAL\Types\Tsvector::class,
'tsvector[]' => MartinGeorgiev\Doctrine\DBAL\Types\TsvectorArray::class,
// Monetary types
'money' => MartinGeorgiev\Doctrine\DBAL\Types\Money::class,
'money[]' => MartinGeorgiev\Doctrine\DBAL\Types\MoneyArray::class,
// Key-value types
'hstore' => MartinGeorgiev\Doctrine\DBAL\Types\Hstore::class,
'hstore[]' => MartinGeorgiev\Doctrine\DBAL\Types\HstoreArray::class,
// Hierarchical types
'ltree' => MartinGeorgiev\Doctrine\DBAL\Types\Ltree::class,
'ltree[]' => MartinGeorgiev\Doctrine\DBAL\Types\LtreeArray::class,
// XML types
'xml' => MartinGeorgiev\Doctrine\DBAL\Types\Xml::class,
'xml[]' => MartinGeorgiev\Doctrine\DBAL\Types\XmlArray::class,
// Vector types
'halfvec' => MartinGeorgiev\Doctrine\DBAL\Types\Halfvec::class,
'sparsevec' => MartinGeorgiev\Doctrine\DBAL\Types\Sparsevec::class,
'vector' => MartinGeorgiev\Doctrine\DBAL\Types\Vector::class,
],
// ... other configuration
];
User-defined enum types: For each PostgreSQL native
ENUMtype, create a concrete class extendingMartinGeorgiev\Doctrine\DBAL\Types\Enumand register it like the examples above. See ENUM-TYPE.md for a full example.
Register the functions you'll use in your DQL queries. The full set of available functions and operators can be found in the Available Functions and Operators documentation and its specialized sub-pages:
Add the function configuration to your config/doctrine.php:
<?php
// config/doctrine.php
return [
// ... other configuration
'custom_string_functions' => [
# alternative implementation of ALL() and ANY() where subquery is not required, useful for arrays
'ALL_OF' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\All::class,
'ANY_OF' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Any::class,
# operators for working with array and json(b) data
'GREATEST' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Greatest::class,
'LEAST' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Least::class,
'CONTAINS' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Contains::class, // @>
'IS_CONTAINED_BY' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\IsContainedBy::class, // <@
'OVERLAPS' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Overlaps::class, // &&
'RIGHT_EXISTS_ON_LEFT' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\TheRightExistsOnTheLeft::class, // ?
'ALL_ON_RIGHT_EXIST_ON_LEFT' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AllOnTheRightExistOnTheLeft::class, // ?&
'ANY_ON_RIGHT_EXISTS_ON_LEFT' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AnyOnTheRightExistsOnTheLeft::class, // ?|
'RETURNS_VALUE_FOR_JSON_VALUE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ReturnsValueForJsonValue::class, // @?
'DELETE_AT_PATH' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\DeleteAtPath::class, // #-
# array and string specific functions
'IN_ARRAY' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\InArray::class,
'ANY_VALUE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AnyValue::class,
'ARRAY' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Arr::class,
'ARRAY_APPEND' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAppend::class,
'ARRAY_CARDINALITY' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayCardinality::class,
'ARRAY_CAT' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayCat::class,
'ARRAY_DIMENSIONS' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayDimensions::class,
'ARRAY_FILL' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayFill::class,
'ARRAY_LENGTH' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayLength::class,
'ARRAY_LOWER' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayLower::class,
'ARRAY_NUMBER_OF_DIMENSIONS' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayNumberOfDimensions::class,
'ARRAY_POSITION' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayPosition::class,
'ARRAY_POSITIONS' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayPositions::class,
'ARRAY_PREPEND' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayPrepend::class,
'ARRAY_REMOVE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayRemove::class,
'ARRAY_REPLACE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayReplace::class,
'ARRAY_SAMPLE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArraySample::class,
'ARRAY_SHUFFLE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayShuffle::class,
'ARRAY_TO_JSON' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayToJson::class,
'ARRAY_TO_STRING' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayToString::class,
'ARRAY_UPPER' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayUpper::class,
'ASCII' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Ascii::class,
'BTRIM' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Btrim::class,
'CHAR_LENGTH' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\CharLength::class,
'CHR' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Chr::class,
'DECODE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Decode::class,
'ENCODE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Encode::class,
'INITCAP' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Initcap::class,
'LPAD' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Lpad::class,
'LTRIM' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Ltrim::class,
'OCTET_LENGTH' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\OctetLength::class,
'QUOTE_IDENT' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\QuoteIdent::class,
'QUOTE_LITERAL' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\QuoteLiteral::class,
'QUOTE_NULLABLE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\QuoteNullable::class,
'RPAD' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Rpad::class,
'RTRIM' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Rtrim::class,
'SPLIT_PART' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\SplitPart::class,
'STARTS_WITH' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\StartsWith::class,
'ROW' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Row::class,
'STRPOS' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Strpos::class,
'STRING_TO_ARRAY' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\StringToArray::class,
'TRANSLATE' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Translate::class,
'UNNEST' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Unnest::class,
# json specific functions
'JSON_ARRAY_LENGTH' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonArrayLength::class,
'JSON_BUILD_ARRAY' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonBuildArray::class,
'JSON_BUILD_OBJECT' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonBuildObject::class,
'JSON_EACH' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonEach::class,
'JSON_EACH_TEXT' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonEachText::class,
'JSON_EXISTS' => Mar...
How can I help you explore Laravel packages today?