Working with PostgreSQL for many years, makes you forget that not all databases support transactional DDL. While working on a Laravel/MySQL setup project, I stumbled upon this issue as my migrations failed mid-way and left the database in inconsistent state since an incomplete migration could not be rolled back.
The migration in focus, was a two command script that created a table and then placed constraints on it. While the table creation succeeded, the constraints failed due to an incompatibility between Larvel’s DB layer and MySQL encoding. The migration did not rollback though, nor completed. The migrations tracking table was not updated, but the database was partially updated as a table without all the necessary constraints was created.
Executing the migrations again fails with a different reason (table exists) and the only way to make migrations working again, is either by manually editing the database to bring it to a consistent state or drop and re-run all migrations. The first option is cumbersome, the second is not always possible, especially in production environments.
There’s this notion that migrations can’t fail easily. I believe that’s not so true. A simple environment change ( database update, configuration changes, framework update/configuration changes) can lead to such an issue. I had to focus on a similar issue like this a year ago when working with flywaydb and postgresql and a migration that was targeting a specific version of the database server could not execute on a newer (minor) version of the database server. At least PostgreSQL supported transactional DDL and I was able to avoid database schema-migrations inconsistencies. But if not all database support transactional DDL (which is a large pool of databases including enterprise vendors) then how can we be sure that our migrations won’t cause a mess. We can’t. What we can do though, is take the necessary steps to recover without any hussle when that happens. I follow two different paths based on the circumstances:
Migration’s first approach
If possible, include all the database seeding in the migrations. That way, when a problem arises, drop the database completelly and re-run the migrations. That will guarnatee that all the testing data will be restored as the migrations run.
If seeding occurs after the migrations are run (e.g. a database being populated by a crawler) make sure to backup the testing database prior to running migrations. If something goes wrong, you can restore the previous database version and the testing data won’t be lost.