Doctrine migrations: create code for mysql and postgresql

Question

I am using Doctrine ORM 2.6.1 in a Symfony 3.4.4 project. Some of my instances work on a MySQL database, some on Postgresql, and a few installations even access a MicosoftSQL server. This works fine without any special changes to my project or entities, I only have to configure the corresponding connection parameters.

But: if I create migrations, only statements compatible with the current database connection are created in the migration file.

I develop with a postgres-conncection, so I only produce postgresql-statements, like:

class Version20180430083616 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('DELETE FROM document_category');
        $this->addSql('DROP SEQUENCE document_category_id_seq CASCADE');
        $this->addSql('DROP TABLE document_category');
    }

    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');
        //...
    }
}

My Question: How can I tell the migrations bundle to create statements for each platform, like:

class Version20180430083616 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        if($this->connection->getDatabasePlatform()->getName() == 'postgresql'){

            $this->addSql('DELETE FROM document');
            $this->addSql('DELETE FROM document_category');
            $this->addSql('DROP SEQUENCE document_category_id_seq CASCADE');
            $this->addSql('DROP TABLE document_category');
        } else if($this->connection->getDatabasePlatform()->getName() == 'mysql'){
            ...
        } else if ($this->connection->getDatabasePlatform()->getName() == 'mssql') { // MicrosoftSQL ?
            ...
        }
    }
}

Edit:

So, I think the only solution to my problem is to define multiple database connections and entity managers, and to always create a distinct migration for each connection type. According to this article, I can define several connections as:

No can do, sorry. Doctrine's migration tool doesn't work this way. — emix
Thanks for your answer. I think I would have to define several concurrent database connections in my dev environment and then create migrations for each. Is there a way to perform entity writes to multiple databases automatically? — olidem

olidem olidem · Accepted Answer · 2018-06-26T08:02:07

I found a doable solution:

inf config.yml I define one connection and one EntityManager per database type:

doctrine:
    dbal:
        default_connection: pgdb
        connections:
            pgdb:
                driver: pdo_pgsql
                host: db
                port: 5432
                name: pgdb
                user: postgres
                password: example
                charset: utf8
                mapping_types:
                    enum: string
            mysql:
                driver: pdo_mysql
                host: mysqlhost
                port: 3306
                name: mydb
                dbname: mydb
                user: root
                password: xxx
                charset: utf8mb4
                default_table_options:
                    collate: utf8mb4_unicode_ci
                mapping_types:
                    enum: string
            mssql:
                driver: pdo_sqlsrv
                host: mssqlhost
                port: 1433
                name: msdb
                dbname: testdb
                user: sa
                password: xxx
                charset: utf8
                mapping_types:
                    enum: string

    orm:
        auto_generate_proxy_classes:  false
        proxy_dir:            '%kernel.cache_dir%/doctrine/orm/Proxies'
        proxy_namespace:      Proxies
        entity_managers:
            default:
                connection: pgdb
                naming_strategy: doctrine.orm.naming_strategy.underscore
                mappings:
                    AppBundle: ~ 
            my:
                connection: mydb
                naming_strategy: doctrine.orm.naming_strategy.underscore
                mappings:
                    AppBundle: ~
            ms:
                connection: msdb
                naming_strategy: doctrine.orm.naming_strategy.underscore
                mappings:
                    AppBundle: ~

Then, I can issue the diff-command 3 times instead of only once

$ bin/console doctrine:migrations:diff --em=default
$ bin/console doctrine:migrations:diff --em=my
$ bin/console doctrine:migrations:diff --em=ms

This creates three migrations each starting with a fence line:

$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mssql', 'Migration can only be executed safely on \'mssql\'.');

in which I exchange abortIf by skipIf, such that the migration process is not aborted if the current migration if for a different database type, but just skipped:

$this->skipIf($this->connection->getDatabasePlatform()->getName() !== 'mssql', 'Migration can only be executed safely on \'mssql\'.');

I hope this helps somebody.

Doctrine migrations: create code for mysql and postgresql

1 Answers