How is it possible to generate doctrine migrations for both mysql, mssql and sqlite?

Question

I would like to generate database migration scripts using doctrine migrations.

One of my customers have mysql database, the other one has mssql, and we use sqlite in our dev environment.

If I generate the scripts using php artisan doctrine:migrations:diff, then it will start with this line:

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

How is it possible to generate migration script from the changes in our entity classes in a way, that it can be used on mysql, sqlsrv and sqlite as well?

Answer 1

Instead of using only sqllite in dev, you can create a dev/doctrine.yml file that declares three entity managers (one per database engine) as described in documentation. Of course, you only do it in development file. Don't alter your production configuration file.

Then, you create an env variable to specify the default orm to use in dev:

# config/packages/dev/doctrine.yaml
doctrine:
    orm:
        default_entity_manager: '%env(my_current_orm)%'
# ...

At this step, you are now able to change database server/engine only by changing an environment variable.

Based on this answer, you can create one doctrine_migration.yaml file per database engine. In these files, you specify different subdirectories to store your migrations files.

#example for mssql engine
doctrine_migrations:
    dir_name: '%kernel.project_dir%/migrations/mssql'
    # namespace is arbitrary but should be different from App\Migrations
    # as migrations classes should NOT be autoloaded
    namespace: DoctrineMigrations/MsSql/

At this step, you are now able to launch migration commands for each database engine.

symfony console doctrine:migrations:status --em=mssql \
         --configuration=src/config/packages/dev/migrations/mssql.yml

As you see, for migration, I did not use the environment variable. I manually specify the default orm --em=xxxx and I specify the configuration file. I used this in an old project, but I think that it's now possible to use the environment variable in the doctrine_migration.yaml file to optimize configuration and have only one file.

edit:(I remember that I have updated the module loader to avoid to load multiple times the migrations config files, you perhaps have to move the non-sqlite doctrine_migration configuration files in another directory than the usual one)