Ignore custom indexes on Doctrine ORM

Sometimes, we need to create complex indexes on a database, like creating a GIN index using trgm extension for Postgres, etc. Currently, we can’t generate this kind of index definition using doctrine index mapping configuration.

Picture credit : Franco Antonio Giovanella

For this purpose, the best way to create such indexes is to add them manually inside a doctrine migration. But by doing this, the table mapping is no more synchronized with the database, so command like this one (using Symfony console):

bin/console doctrine:schema:validate

Will fail, and creating new migrations will be bloated with the DROP INDEX SQL statement.

To make it properly work again with doctrine, a pertinent solution will be to ignore such indexes from doctrine dbal schema parser.

How to

I’m using these packages versions which are the most recent when I wrote this article :

• doctrine/dbal: 3.6.5
• The Symfony doctrine/doctrine-bundle: 2.10.2

On latest doctrine dbal versions, listening to schema related events is deprecated, and will be removed in doctrine dbal v4.

The proper way is to override the SQLSchemaManager. Depending on your Database, you need to extend the good one. On my side, using Postgres, have to create a class that extend : Doctrine\DBAL\Schema\PostgreSQLSchemaManager

Example (don’t forget to add your namespace / rename if needed) :

<?php

class CustomPostgreSQLSchemaManager extends \Doctrine\DBAL\Schema\PostgreSQLSchemaManager
{
    private const INDEXES_TO_FILTER = [
        'idx_custom1',
        'idx_custom2',
    ];

    protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
    {
        $indexes = parent::_getPortableTableIndexesList($tableIndexes, $tableName);

        foreach (self::INDEXES_TO_FILTER as $index) {
            if (isset($indexes[$index])) {
                unset($indexes[$index]);
            }
        }

        return $indexes;
    }
}

Here, indexes named “idx_custom1” and “idx_custom2” will be ignored. To configure ignored indexes we just need to update the INDEXES_TO_FILTER const array.

Now that we set up the index ignore/filter system, we still need to tell doctrine to use our CustomPostgreSQLSchemaManager. Looking at doctrine dbal documentation, it tells us that we need to set a custom SchemaManagerFactory.

You can follow the doctrine documentation, but if you are using the Symfony doctrine-bundle, you can do that from the config/doctrine.yaml file.

Simply add the “schema_manager_factory” config key in your connection configuration with your own factory service, or if you already override the doctrine platform service with your own, we just need to override a method and don’t need a custom SchemaManagerFactory. We can simply use the doctrine DefaultSchemaManagerFactory instead of creating our own.

Note : if you don’t configure the SchemaManagerFactory, with Symfony doctrine-bundle, the SchemaManagerFactory used is the LegacySchemaManagerFactory. This one get the SchemaManager through the doctrine driver, it’s deprecated and will be removed in doctrine/dbal v4. So better use the DefaultSchemaManagerFactory right now 👍

If we go back to our config/doctrine.yaml:

platform_service: Namespace\To\My\CustomPostgreSQLPlatformService
schema_manager_factory: 'doctrine.dbal.default_schema_manager_factory'

Here we use a custom platform service, and use the doctrine default SchemaManagerFactory. Now we just need to add this method in our CustomPostgreSQLPlatformService:

class CustomPostgreSQLPlatformService extends \Doctrine\DBAL\Platforms\PostgreSQLPlatform
{

    public function createSchemaManager(Connection $connection): CustomPostgreSQLSchemaManager
    {
        return new CustomPostgreSQLSchemaManager($connection, $this);
    }

}

Again, extend the platform matching your database type (PostgreSQLPlatform here).

If you want to know how it works, I let you look at the Doctrine\DBAL\Schema\DefaultSchemaManagerFactory class.

Spoiler : it calls the overridden method above from our platform service👆

And that’s it, generating new migrations no longer delete custom indexes.