Getting started

This page takes you from an empty project to a first query that uses a PostgreSQL feature: a text[] column, saved from a PHP array and filtered with PostgreSQL’s @> operator.

See also: Available types · Available functions and operators · Troubleshooting

Before you start

You need PHP 8.2 or newer, Doctrine DBAL 3 or 4, Doctrine ORM 2.14 or newer, and a PostgreSQL database. The library’s integration tests run on PostgreSQL 16, 17, 18 and 19.

1. Install the package 

composer require martin-georgiev/postgresql-for-doctrine

2. Register the type and the functions

Nothing is registered for you: you add the types and functions you use. This page uses one type, text[], and three functions:

  • CONTAINS, PostgreSQL’s @> operator;
  • ARRAY, which builds an array inside a DQL query;
  • ARRAY_APPEND, PostgreSQL’s array_append().

With Doctrine on its own 

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

Type::addType('text[]', TextArray::class);

// $configuration and $connection are what you pass to new EntityManager($connection, $configuration)
$configuration->addCustomStringFunction('CONTAINS', Contains::class);
$configuration->addCustomStringFunction('ARRAY', Arr::class);
$configuration->addCustomStringFunction('ARRAY_APPEND', ArrayAppend::class);

$platform = $connection->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('text[]', 'text[]');
$platform->registerDoctrineTypeMapping('_text', 'text[]');

The two platform mappings are for the schema tools. The first lets Doctrine write TEXT[] into a CREATE TABLE; the second lets it read a column back, because PostgreSQL reports a text[] column as _text. Without them, doctrine:schema:update and migration diffs fail with Unknown database type "text[]" requested or Unknown database type "_text" requested.

With Symfony 

# config/packages/doctrine.yaml
doctrine:
    dbal:
        types:
            'text[]': MartinGeorgiev\Doctrine\DBAL\Types\TextArray
        mapping_types:
            'text[]': 'text[]'
            _text: 'text[]'
    orm:
        dql:
            string_functions:
                CONTAINS: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Contains
                ARRAY: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Arr
                ARRAY_APPEND: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAppend

These keys go next to the ones the Symfony recipe already puts under dbal and orm. If your configuration lists connections or entity_managers, put mapping_types under the connection and dql under the entity manager instead, as Symfony setup shows.

With Laravel 

// config/doctrine.php
return [
    'managers' => [
        'default' => [
            // ... the rest of the published configuration

            'mapping_types' => [
                'text[]' => 'text[]',
                '_text' => 'text[]',
            ],
        ],
    ],

    'custom_types' => [
        'text[]' => MartinGeorgiev\Doctrine\DBAL\Types\TextArray::class,
    ],

    'custom_string_functions' => [
        'CONTAINS' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Contains::class,
        'ARRAY' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Arr::class,
        'ARRAY_APPEND' => MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAppend::class,
    ],
];

This needs the Laravel Doctrine package; Laravel setup shows how to install it.

3. Map a column 

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Product
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    public ?int $id = null;

    /** @param list<string> $tags */
    public function __construct(
        #[ORM\Column]
        public string $name,

        #[ORM\Column(type: 'text[]')]
        public array $tags = [],
    ) {}
}

4. Create the table

Let Doctrine generate it, with doctrine:schema:update --dump-sql or a migration diff. Measured with ORM 3.7 and DBAL 4.5, this entity gives:

CREATE TABLE Product (id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR(255) NOT NULL, tags TEXT[] NOT NULL, PRIMARY KEY (id))

5. Save and query 

$entityManager->persist(new Product('Dune', ['books', 'sci-fi']));
$entityManager->persist(new Product('Doctrine in Action', ['books', 'php']));
$entityManager->flush();

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

[Product {name: 'Doctrine in Action', tags: ['books', 'php']}]

CONTAINS(p.tags, ARRAY(:tag)) = TRUE becomes (p0_.tags @> ARRAY[?]) = true in SQL. DQL only accepts a function in WHERE as part of a comparison, which is why it ends in = TRUE; Writing DQL explains the rule.

A value the query computes comes back differently from a mapped field:

$rows = $entityManager
    ->createQuery("SELECT p.name, ARRAY_APPEND(p.tags, 'new') AS extended FROM App\Entity\Product p ORDER BY p.id")
    ->getResult();

[
    ['name' => 'Dune', 'extended' => '{books,sci-fi,new}'],
    ['name' => 'Doctrine in Action', 'extended' => '{books,php,new}'],
]

p.tags would arrive as a PHP array, because Doctrine converts a mapped field with its type. extended is computed by the query, so Doctrine hands it over as PostgreSQL’s text. Query results shows how to convert it.

Where to go next

Table of contents