Ignore custom indexes on Doctrine DBAL 4

Indra Gunawan — Sat, 27 Jul 2024 12:28:20 +0000

You can create database indexes using #[ORM\Index(fields: ['fieldName'] attribute on the entity file and Doctrine will do the rest to manage the index for you, but I will not talk about that.

As your project grows or requires specific requirements, you may need to use custom index types like GIST, GIN, BRIN, etc (https://www.postgresql.org/docs/current/indexes-types.html). Doctrine does not support creating database-vendor-specific index types out-of-the-box (ref).

To tackle this issue you can either execute the CREATE INDEX DDL directly to the database or write the DDL on doctrine-migrations file. The latter option gives you the benefit of easier to deploy or rollback the changes.

No matter which method you use to create the custom index, Doctrine will always mark those custom indexes as unmapped indexes hence if you execute doctrine:schema:validate you will get an error that state if your database is not in sync, the same while executing doctrine:schema:update --dump-sql or doctrine:migrations:diff it will show you DROP INDEX ... statement to remove the custom indexes.

Solution

I'm using these package versions. (I believe the solution will work on the same major version of the packages):

doctrine/dbal 4.0.4
doctrine/doctrine-bundle 2.12.0

I found several tutorials to handle this but it is not satisfying:

https://www.liip.ch/en/blog/doctrine-and-generated-columns This was not working anymore as Doctrine DBAL removes the event manager on DBAL 4. (ref)
https://medium.com/yousign-engineering-product/ignore-custom-indexes-on-doctrine-dbal-b5131dd22071 This was showing a deprecation message The "platform_service" configuration key is deprecated since doctrine-bundle 2.9. DBAL 4 will not support setting a custom platform via connection params anymore. (ref)

I found a GitHub issue about platform_service config replacement here https://github.com/doctrine/DoctrineBundle/issues/1656.

Reading these 2 pages gave me all the pieces of information on how to use a custom DBAL platform through Doctrine middleware:

Finally, after digging into the source code, I found the solution. You need to create 4 files, 2 files are about Doctrine middleware and 2 other files are about Doctrine DBAL platform and schema.

Modify the namespace and class name as your needs.

<?php

declare(strict_types=1);

namespace App\Doctrine\DBAL\Middleware;

use Doctrine\DBAL\Driver;
use Doctrine\DBAL\Driver\Middleware;

final class PostgreSQLPlatformMiddleware implements Middleware
{
    #[\Override]
    public function wrap(Driver $driver): Driver
    {
        return new PostgreSQLPlatformDriver($driver);
    }
}

<?php

declare(strict_types=1);

namespace App\Doctrine\DBAL\Middleware;

use App\Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Driver\Middleware\AbstractDriverMiddleware;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\ServerVersionProvider;

final class PostgreSQLPlatformDriver extends AbstractDriverMiddleware
{
    #[\Override]
    public function getDatabasePlatform(ServerVersionProvider $versionProvider): AbstractPlatform
    {
        return new PostgreSQLPlatform();
    }
}

<?php

declare(strict_types=1);

namespace App\Doctrine\DBAL\Platforms;

use App\Doctrine\DBAL\Schema\PostgreSQLSchemaManager;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform as BasePostgreSQLPlatform;
use Doctrine\DBAL\Schema\PostgreSQLSchemaManager as BasePostgreSQLSchemaManager;
use Doctrine\DBAL\Schema\SchemaManagerFactory;

final class PostgreSQLPlatform extends BasePostgreSQLPlatform implements SchemaManagerFactory
{
    #[\Override]
    public function createSchemaManager(Connection $connection): BasePostgreSQLSchemaManager
    {
        return new PostgreSQLSchemaManager($connection, $this);
    }
}

<?php

declare(strict_types=1);

namespace App\Doctrine\DBAL\Schema;

use Doctrine\DBAL\Schema\PostgreSQLSchemaManager as BasePostgreSQLSchemaManager;

final class PostgreSQLSchemaManager extends BasePostgreSQLSchemaManager
{
    private const array IGNORED_INDEXES = [
        'index_name_1' => true,
        'index_name_2' => true,
    ];

    #[\Override]
    protected function _getPortableTableIndexesList(array $tableIndexes, string $tableName): array
    {
        $indexes = parent::_getPortableTableIndexesList($tableIndexes, $tableName);

        foreach (array_keys($indexes) as $indexName) {
            if (isset(self::IGNORED_INDEXES[$indexName])) {
                unset($indexes[$indexName]);
            }
        }

        return $indexes;
    }
}

Forem: Indra Gunawan

Ignore custom indexes on Doctrine DBAL 4

Solution