gem-hunterExtends Doctrine with native PostgreSQL support for arrays, JSONB, ranges, PostGIS geometries, text search, and 100+ PostgreSQL-specific functions.
Enhances Doctrine with PostgreSQL-specific features and functions. Supports PostgreSQL 9.4+ and PHP 8.2+.
Documentation at postgresql-for-doctrine.dev
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 ');
int[], smallint[], bigint[])real[], double precision[])text[])bool[])jsonb[])bytea, bytea[])bit, bit[])bit varying, bit varying[])inet, inet[])cidr, cidr[])macaddr, macaddr[], macaddr8, macaddr8[])box, box[])circle, circle[])line, line[])lseg, lseg[])path, path[])point, point[])polygon, polygon[])geometry, geometry[])geography, geography[])daterange, daterange[], tsrange, tsrange[], tstzrange, tstzrange[])numrange, numrange[], int4range, int4range[], int8range, int8range[])datemultirange, datemultirange[], int4multirange, int4multirange[], int8multirange, int8multirange[], nummultirange, nummultirange[], tsmultirange, tsmultirange[], tstzmultirange, tstzmultirange[])date[], timestamp[], timestamptz[])DateInterval support (interval, interval[])timetz, timetz[])tsvector, tsvector[])tsquery, tsquery[])citext, citext[])ulid, ulid[])hstore, hstore[])money, money[])xml, xml[])ltree, ltree[])vector)halfvec)sparsevec)Enum base classComposite base classCOMPOSITE_FIELD() function@>)<@)&&)->, ->>)#>, #>>)@> and <@)&&)<<, >>, &<, &>, |&>, &<|, <<|, |>>)@, ~)<->, <#>, <<->>, |=|)&&&)to_tsvector, to_tsquery)ilike, similar to)ascii, btrim, char_length, chr, decode, encode, initcap, lpad, ltrim, octet_length, quote_ident, quote_literal, quote_nullable, rpad, rtrim, strpos, translate)similarity, word_similarity, strict_word_similarity) (requires pg_trgm extension)md5, sha224, sha256, sha384, sha512, crc32, crc32c, reverse for bytea)array_agg, array_append, array_prepend, array_remove, array_replace, array_shuffle)any_value)json_build_object, jsonb_build_object)row_to_json, row)clock_timestamp, statement_timestamp, transaction_timestamp)justify_days, justify_hours, justify_interval)array_agg, json_agg, jsonb_agg)FILTER (WHERE ...) on any aggregatebool_and, bool_or, every, bit_and, bit_or, bit_xor, stddev, stddev_pop, var_pop, variance, corr, covar_pop, covar_samp)WITHIN GROUP (ORDER BY ...) (percentile_cont, percentile_disc, mode)any_value, xmlagg)OVER, including PARTITION BY, ORDER BY and ROWS, RANGE and GROUPS frame clausesrow_number, rank, dense_rank, percent_rank, cume_dist, ntile)lag, lead, first_value, last_value, nth_value)akeys, avals, skeys, svals)defined)delete)hstore_to_json, hstore_to_json_loose)xmlagg, xmlcomment, xmlconcat, xml_is_well_formed)xpath, xpath_exists)sin, cos, tan, asin, acos, atan, degree variants)sinh, cosh, tanh, asinh, acosh, atanh)gcd, lcm, factorial, div)erf, erfc, random_normal)daterange, int4range, int8range, numrange, tsrange, tstzrange)range_agg, range_intersect_agg)cast)to_char, to_number)uuidv4, uuidv7, uuid_extract_timestamp, uuid_extract_version)l2_distance, cosine_distance, inner_product)Full documentation, also published at postgresql-for-doctrine.dev:
composer require martin-georgiev/postgresql-for-doctrine
Then follow Getting started to register a type and a function and run a first query.
See our Examples for detailed code samples.
composer run-unit-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.
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:
This package is licensed under the MIT License. See the LICENSE file for details.