Doctrine Postgres Types Laravel Package

Getting Started

The package is a small collection of Doctrine DBAL custom types for Postgres-native column types that Doctrine doesn't ship with: citext, hstore, ltree, inet, tsvector, and similar. It is a type registration library — there is no service provider, no Symfony bundle, no config tree. You wire it up yourself.

1. Install (with a Postgres-backed Doctrine 2.x project):

composer require opensoft/doctrine-postgres-types

2. Look at the source before anything else. The repo is tiny. Confirm the PSR-4 namespace in composer.json (e.g. Opensoft\...\Types\) and list the shipped types:

find vendor/opensoft/doctrine-postgres-types -name '*Type.php'

The class names map roughly to the Postgres type names. Adjust the use lines in the examples below to match what you find.

3. Enable the Postgres extensions you actually need — the types won't work without them:

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS ltree;

4. Register the types before the connection is built. This must happen in every entry point: web kernel, CLI (cli-config.php), migrations, test bootstrap, and any standalone scripts.

use Doctrine\DBAL\Types\Type;

Type::addType('citext', \Opensoft\DoctrinePostgresTypes\Types\CitextType::class);
Type::addType('hstore', \Opensoft\DoctrinePostgresTypes\Types\HstoreType::class);
Type::addType('ltree',  \Opensoft\DoctrinePostgresTypes\Types\LtreeType::class);

In Symfony, prefer the config so it is applied consistently:

# config/packages/doctrine.yaml
doctrine:
    dbal:
        types:
            citext: Opensoft\DoctrinePostgresTypes\Types\CitextType
            hstore: Opensoft\DoctrinePostgresTypes\Types\HstoreType
            ltree:  Opensoft\DoctrinePostgresTypes\Types\LtreeType

5. First use case — a case-insensitive slug. This is the canonical reason to reach for the package: citext gives you case-insensitive uniqueness at the database level without LOWER() indexes scattered everywhere.

/**
 * @ORM\Column(type="citext", unique=true)
 */
private string $slug;

You keep writing ordinary string comparisons and findOneBy(['slug' => $slug]); Postgres does the case folding.

Implementation Patterns

Centralize registration in a bootstrap file. Because Type::addType() is global static state, most teams end up with a single config/doctrine_types.php that is required from the kernel, cli-config.php, and tests/bootstrap.php. Do this on day one — drift between web and CLI registration is the number one source of "works in the browser, fails in doctrine:migrations:diff".

Annotation-driven mapping is the norm. The types are plain DBAL types, so ORM mapping is just type="hstore" etc.:

/** @ORM\Column(type="hstore", nullable=true) */
private ?array $attributes = null;

/** @ORM\Column(type="ltree") */
private string $path; // e.g. "root.electronics.tv"

HstoreType converts to/from a PHP associative array, so $entity->getAttributes()['color'] just works. LtreeType is a string (a.b.c); traversal is done in SQL, not PHP.

Migrations need two extra steps. In a migration, addColumn('slug', 'citext') works only after registration, and the schema manager needs to know how to map the Postgres type name back to your Doctrine type when diffing:

public function up(Schema $schema): void
{
    $this->addSql('CREATE EXTENSION IF NOT EXISTS citext');
    $this->addSql('CREATE TABLE product (id SERIAL, slug CITEXT NOT NULL)');
    $this->addSql('CREATE UNIQUE INDEX uniq_product_slug ON product (slug)');
}

And register the reverse mapping once per connection, ideally in a postConnect listener or your bootstrap:

$platform = $connection->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('citext', 'citext');
$platform->registerDoctrineTypeMapping('hstore', 'hstore');
$platform->registerDoctrineTypeMapping('ltree', 'ltree');

Without this, doctrine:migrations:diff will see an existing citext column as string and generate spurious ALTER statements forever.

Use the types as a bridge, not as a query language. Doctrine's DQL has no idea what ltree operators (@>, <@, ~) or hstore operators (?, ->) are. Two workable patterns:

• Repository native SQL for reads, hydrating entities with $em->getRepository(X::class)->findBy(...) only for simple access:

$sql = 'SELECT id FROM category WHERE path <@ :root ORDER BY path';
$ids = $conn->fetchFirstColumn($sql, ['root' => 'root.electronics']);

• Custom DQL functions (subclass Doctrine\ORM\Query\AST\Functions\FunctionNode) that emit the operator, registered in orm.dql.postgresql string/string functions. This is more work but keeps queries in DQL, which matters if you later swap backends or use query builders heavily.

hstore for sparse, schema-free attributes; not for anything you filter on heavily. The pattern that ages well: hstore (or jsonb) for user-defined metadata, with a GIN index:

CREATE INDEX idx_product_attrs ON product USING gin (attributes);

Queries then use attributes @> 'color=>red' via native SQL. If you find yourself building an entire query layer plus a validation layer over hstore, that's a signal to promote the keys to real columns.

tsvector belongs in a generated column. The TsvectorType is mostly useful for reading; writing vectors from PHP is almost always wrong. Prefer:

ALTER TABLE article ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_article_search ON article USING gin (search);

and drop search from the ORM mapping entirely (keep it out of the entity, or map it as readonly/insertable=false if you need to select it for diagnostics).

Testing needs a real Postgres. Doctrine's SQLite fallback cannot create citext, hstore, or ltree. Run the test suite against Postgres (Docker Compose or Testcontainers) and make the type bootstrap part of tests/bootstrap.php, otherwise fixtures fail before your first assertion.

Gotchas and Tips

The package is from 2016 — check DBAL compatibility first. It was written for DBAL 2.x. Type::addType() was deprecated in DBAL 3 and removed in DBAL 4, and the protected getName()/getSQLDeclaration() signatures changed. If you're on a modern Doctrine, you will likely need to either pin DBAL 2.x, or copy the eight small type classes into src/Doctrine/Types/ and upgrade them — the classes are short enough that vendoring is often cheaper than fighting the constraint.

Check which types you actually need before installing. DBAL 2.6+ ships json_array/jsonb-ish handling, uuid arrived in 2.7, array types in 2.6. If your only need is jsonb or an integer array, you may not need this package at all — use the native type and reduce your dependency surface.

citext changes index and LIKE semantics. Uniqueness and equality are case-insensitive, but sorting is still byte order unless you add a collation, and LIKE 'Foo%' is case-insensitive too. If an existing unique index on varchar had case-sensitive behavior that code relied on, migrating the column to citext silently widens what collides. Also validate that your column defaults ('' vs NULL) still behave the way your forms expect.

ltree labels are restrictive. Labels must match [A-Za-z0-9_]+ joined by dots — hyphens, spaces, and dots inside a single label are invalid. If users provide the path segments (categories, org units), slugify them on the way in, or you'll get opaque syntax error in ltree errors at flush time.

hstore string escaping bites when you build values in SQL. Prefer binding:

$conn->executeStatement(
    'UPDATE product SET attributes = attributes || hstore(:k, :v) WHERE id = :id',
    ['k' => 'color', 'v' => 'red', 'id' => $id]
);

String-concatenating '"color"=>"red"' will break on quotes, commas, and backslashes. On the PHP side, HstoreType gives you an array, but nulls are ambiguous — Postgres hstore has no NULL value, only absent keys.

inet/cidr come back as strings, not objects. Don't expect IpUtils-style value objects. If you need typed IPs on the domain side, wrap the string in an embeddable/value object and let the DBAL type be the storage detail.

Custom types need a SQL comment hint for schema introspection. DBAL only reliably recognizes a custom type during reverse-engineering if the type returns true from requiresSQLCommentHint(AbstractPlatform $platform). If the bundled class doesn't, the schema manager may treat it as string and generate phantom diffs. The workaround — subclass the type and override the method:

class Citext extends \Opensoft\DoctrinePostgresTypes\Types\CitextType
{
    public function requiresSQLCommentHint(AbstractPlatform $platform): bool
    {
        return true;
    }
}

Then register citext to your subclass, and let migrations:diff emit the (DC2Type:citext) comment.

Registration order matters, and mistakes are silent. If you call Type::addType() after the entity manager has loaded metadata, or you forget the CLI bootstrap, Doctrine falls back to string. That often "works" for reads and fails on writes (e.g., array-to-string on hstore), or worse, passes tests and breaks migrations:diff. Verification loop:

bin/console doctrine:schema:validate
bin/console doctrine:migrations:diff   # should produce no changes on a clean DB

If diff proposes altering every citext/hstore column, your platform mapping or comment hint is wrong — that's the tell.

Extensions must be created outside migrations diff. doctrine:migrations:diff will not emit CREATE EXTENSION. Add it manually at the top of the migration that introduces the first citext column, and add it to your test/CI database provisioning. On managed Postgres (RDS, Cloud SQL) verify the extension is on the allowed list before the team builds on it.

Debugging: \d+ table and SELECT typname FROM pg_type WHERE typname IN ('citext','hstore','ltree') confirm the database side; DBAL's SQLLogger / middleware confirms what Doctrine is actually sending. When a value round-trips wrong, reproduce with the raw connection ($conn->fetchOne('SELECT hstore(...)')) to isolate whether it's the DBAL type or the query.

Extension point is subclassing. All the types expose the standard convertToPHPValue / convertToDatabaseValue / getSQLDeclaration hooks, so wrapping them (adding comment hints, converting hstore into a value object, adding validation on ltree paths) is a few lines per type and keeps you off a fork.