Learn more about Israeli genocide in Gaza, funded by the USA, Germany, the UK and others.

The migration SQL should go in the table

So you run migrate in your dev environment, but it fails: some unknown migration add-foobar has already been applied from some other experimental branch, and the migration tool doesn’t know how to down-migrate it. To fix it, you have to hunt for the branch that created that migration, to find its down-migration SQL. Hopefully you still have that migration!

I want migrate to always get me to my desired database schema, no matter what the current migration list looks like. But no migration tools today have this property: they always need a developer in the loop to find the down-migration SQL.

Say the database has applied migrations a → b → c, but we want a → b → d. Then migrate needs to run the down-migration for c, then run the up-migration for d. This means migrate may need the down-migration SQL for any migration that has been applied. We can guarantee this by storing the down-migration in the migrations table:

id  name         down_sql
==  ===========  ====================================
0   users-table  drop table users;
1   email-col    alter table users drop column email;

All other migration tools I know of have a migrations table, but they only store a list of migration names. They assume you have the migration code checked out for the current database schema. This is a bad assumption, even for your production database! Say a bad migration gets applied to your production database. You should be able to roll back in the normal way: revert the commit and redeploy. But with standard migration tools, this can’t work: the down-migration SQL is lost in the revert!

Tagged #programming.

Similar posts

More by Jim

Want to build a fantastic product using LLMs? I work at Granola where we're building the future IDE for knowledge work. Come and work with us! Read more or get in touch!

This page copyright James Fisher 2025. Content is not associated with my employer. Found an error? Edit this page.