Rolling up database migrations with Doctrine

Berlin, Germany

Introduction

As a user of doctrine/orm and doctrine/migrations, it is likely that you - like me - have encountered one of the following scenarios:

  • You have started using doctrine/migrations late in a project. Unfortunately, you can not set up a database from scratch using database migrations.
  • You use Doctrine entities or other services in the database migrations. You have realized that this was a mistake, regret it, and you would like to get rid of these database migrations.
  • You populate the database with data using database migrations. Again, you have realized that this was a mistake, regret it, and you would like to get rid of these database migrations.
  • You have accumulated a lot of database migrations in a mature project. Since you run these database migrations frequently to set up databases in development, test, and staging environments, and because you get that extra kick out of things running fast, you would like to collapse them into a single database migration.

If that does not describe you, for example, because you do not work on these kinds of projects or never make any mistakes, please share this article with someone who does.

Are you still here? Great!

doctrine/migrations:2.0.0 introduced a DumpSchemaCommand and a RollupCommand. The DumpSchemaCommand reads the schema from our entity mapping and dumps corresponding SQL statements into a single database migration. The RollupCommand verifies that only a single database migration exists, removes information about previously run database migrations from the configured table, and inserts information about this single database migration.

Using these commands, we can easily roll up database migrations. In the following, I will show you how.

The process consists of four steps:

  • validate entity mapping and database
  • remove existing migrations
  • dump the schema into a single database migration
  • validate the dumped database migration
  • roll up migrations

Commands

In a moment, we will run Doctrine console commands. These commands operate with options that have default values, but if our application has a setup that differs from the defaults, we need to specify values for these options.

Options

  • –connection: The name of the Doctrine DBAL database connection; could be default.
  • –env: The name of the environment; could be dev, develop, prod, or production.
  • –em: The name of the Doctrine ORM entity manager to use; could be default.

Environments

We will run these commands in development and production environments. To achieve the best results, our development environment should reflect the production environment. How to create such a development environment is out of the scope of this article.

❗ Make sure to use the appropriate parameters when executing the commands.

Validate entity mapping and database

As mentioned before, the DumpSchemaCommand reads the schema from our entity mapping and dumps corresponding SQL statements into a single database migration. When our entity mapping and the database are not in sync, this will result in a database migration that does not reflect the actual database structure.

To avoid that, we need to validate the schema and fix all errors in a development environment.

We run

$ bin/console doctrine:migrations:status

to show the database migration status in our development environment.

When the command reports that there are new database migrations, we run

$ bin/console doctrine:migrations:execute

to run these database migrations in our development environment.

When all database migrations have been run, we run

$ bin/console doctrine:schema:validate

to validate that the entity mapping is correct and in sync with the database in our development environment.

When this command reports errors, we need to fix them. Fixing these errors might involve adjusting our entity mapping and creating database migrations. These fixes are useful - even when we do not want to roll up database migrations. Therefore we can apply them to the main line of the project. When we have fixed these errors, we can return to rolling up the database migrations.

When this command does not report any errors, we can continue.

💡 To avoid validation errors, we can validate that the entity mapping is correct and in sync with the database in a continuous-integration environment.

When we cannot set up a database from database migrations, we validate the mapping only. Once we have rolled up the database migrations, we can set up a database from database migrations. Then we can also verify that the entity mapping is in sync with the database.

Remove existing database migrations

Since our entity mapping is valid and in sync with the database, we can now remove the existing database migrations from the configured migrations directory, and commit the changes.

Dump the schema

After removing the existing database migrations, we can now dump the schema from our entity mapping into a single database migration.

We run

$ bin/console doctrine:migrations:dump-schema

to dump the schema into a single migration in our development environment, and commit the changes.

Validate the dumped migration

We have dumped the schema into a single migration, but before we continue, let us double-check that running this migration really creates a database structure that reflects our entity mapping. We can do that by dropping and recreating the database, running the migration, and validating the schema.

We run

$ bin/console doctrine:database:drop

to drop the database in our development environment. Then we run

$ bin/console doctrine:database:create

to create the database in our development environment.

We now have an empty database in our development environment, and we run

$ bin/console doctrine:migrations:status

to show the database migration status. This command will show a single new database migration: the database migration we just dumped.

We run

$ bin/console doctrine:migrations:execute

to run this database migration in our development environment.

We now run

$ bin/console doctrine:schema:validate

to validate that the entity mapping is correct and in sync with the database in our development environment.

At this point, this command should not report any errors. When it does, we need to fix them, but we should not adjust our entity mapping. However, we can modify the dumped database migration. Remember, the RollupCommand will not run when more than one database migration exists.

We run

$ bin/console doctrine:migrations:diff

to create a database migration from the differences between our entity mapping and the database. Instead of committing the migration created from running this command, we manually merge it into the previously dumped migration and commit the resulting changes.

We repeat this process until the single migration creates a database in sync with our entity mapping.

Roll up

We have rolled up the database migrations in our development environment, and can finally roll up the database migrations in our production environment.

After deploying the changes, we run

$ bin/console doctrine:migrations:rollup

to roll up the migrations in our production environment.

We run

$ bin/console doctrine:migrations:status

to verify that there is only a single available migration and that there are more migrations that need to be executed.

Conclusion

We have now collapsed all existing database migrations into a single database migration.

We can set up a database from scratch in development, testing, or continuous-integration environments now, and run commands and tests that require the presence of a database. We have also rid ourselves of database migrations that use entities or services or populate the database with data. By reducing the number of migrations, we have sped up the process of running them.