Down First Migrations
Write the down part of the migration before the up part. That’s it, that’s all I want to say here.
All good ideas in software development seem to come from the thought “why don’t I do this the other way around?”. Test Driven or Test First Development is the biggest of these. Another big one that you might come across is Design First development which is quite big if you’ve worked on an Open API project (I think this would be better named Document First development to catch the real meaning).
People often take these ideas too literally and think you have to test everything before touching a line of code for the feature for example. The most important part of these ideas is that you change the way you think about problems. Taking it too seriously will make you think you need to either adopt TDD (or whatever other practice) or you drop it entirely. This kind of thing is about changing the way you think and approach problems. They are part of a broader set of programming practices, they don’t need to be rules or rituals that need to be followed.
So why do we write the “up” migration first when making database migrations? At least for me, it’s because when I write a database migration I’m trying to change the database in some way. What I want to achieve now is achieved in the “up” stage not the “down” stage. I only want “down” if something goes wrong and I want to reverse the change, so why not write the “down” statement at the end?
Writing the “down” is easier, firstly because you already know the state of the application you can just copy and paste names from the table itself. If you want to return the database to its original state there is not a better time to prepare that, than while you have the original state in front of you.
Secondly, for the most part your “down” queries will fail if run before the “up” query
ALTER TABLE some_table DROP COLUMN new_column;
The above will fail if not run before
ALTER TABLE some_table ADD COLUMN new_column VARCHAR(75);
But the reverse will not fail. This can matter because if we add the column the state of the database has changed and we may not know how to get back to the original state. If we run the drop, the query will fail and the database will still have its original state.
At the end of the day, you should run both “up” and “down” migrations multiple times locally before deployment, subtle bugs in migrations can be very frustrating to resolve. But if something goes wrong you’ll be far more interested that your “down” migration, your undo button, works.
I also find that when I write the “down” query first, I end up with a better understanding of what I want to change. It also makes me want to make smaller migrations so that the “down” migration isn’t too complicated. Smaller, more atomic migrations are easier to manage anyway.