Help my Doctrine migrations are broken!

Recently we ran into a problem with doctrine migrations. The schema was manually edited, and wrong migrations were committed to the repository. For a dev (and even staging) env you could just drop the database, fix the migrations, and run them again. But we didn’t want to lose our production data. So how do we fix this?

We really had 2 problems. Our migrations were wrong, and the (production) database was not in the correct state.

Fixing the migrations

The migrations looked (something like) this:

• Migration 1: Create users table
• Migration 2: Create products table. Create category table
• Migration 3: Create category table. Alter users table

So migration 2 and 3 had some of the same content. The fix here was to just remove the creation of the category table from migration 3. Normally you shouldn’t edit migrations, but this was the only way to keep them working. We tested the migrations in a local environment, and were able to run them starting from an empty DB. The down migrations were also working.

This was step one, as now anyone can work on the project and get their DB in the correct state, without manual extra work. However there was another issue, the production database.

Fixing the production database

So now we were ready to take a look at the production database. It turned out the database was in the ‘correct’ state. However the migrations themselves were only ran up until migration 2. Migration 3 was never ran, but the altering of the users table had been done. Thankfully, doctrine has a command for this. The version command will add (or delete) a migration from your migration table, without executing it. In symfony this is exposed as bin/console doctrine:migrations:version.

So to manually add the migration, we ran the following command:

$ bin/console doctrine:migrations:version DoctrineMigrations\\Version20210125124213 --add

DoctrineMigrations\\Version20210125124213 Is the FQN of the migration. The --add is to add the migration. If we needed to remove one we could’ve used --delete. Using this command is safer than manually editing the migrations table, because if you make a typo in the name, doctrine wont add it.

Lessons learned

There are a few lessons to learn here. The first one is that if you mess up your migrations and your database, there is a way to fix it. However, its even better to prevent these issues from happening. There are a few precautions you can take.

Test your migrations

Make it a step in your CI to run your migrations, from an empty DB to the latest version, and from the last version all the way back. This will stop you from committing faulty migrations, and can be as simple as the following steps:

script:
  - bin/console doctrine:migrations:migrate --no-interaction # migrations up
  - bin/console doctrine:schema:validate # check that the entities and the schema match
  - bin/console doctrine:migrations:migrate first --no-interaction # migrations down

Don’t manually change the DB

We wouldn’t have gotten into this trouble if we had only used the migrations to update the DB. A developer had ran php bin/console doctrine:schema:update --force to get the DB into the latest state. This (and manual ALTER ) queries shouldn’t be used if you want your DB to be managed by migrations.

Closing words

In reality this was a small and new project. If we had wiped the DB and recreated it, the data would’ve been added back within 5 minutes. But now we have the experience and knowledge on how to deal with these issues. When life gives you a safe training opportunity, take it!