The fun of migrations, and why they suck

A lot of web developers know them, migrations. Migrations are the type of things that look very robust and easy to use when you read about them. The idea is that you got a set of revisions of your database, so you can maintain a history of your database structure. The thought behind it, is that if one of your fellow developers on your project makes a very stupid database edit, you can just revert it with the blink of an eye.

And now we’re going back to the real world. In the real world, we use SCM (Source Code Management) tools. And this means in a lot of situations, that you are doing stuff that is already there! And not only you’re duplicating behavior that already exists, but you also make the process more error-prone.

Like for instance, you’re using the SCM Git. And you are writing a migration, and you commit it. And then you pull from one of the other developers in the project. And then it seems that he also has made a new migration! Which means you got merge conflicts. So, what would you do then? You can rename one of the files, but which one gets which migration number? This isn’t really a big problem when you are working with just the two of you. But for bigger projects, it could be a huge problem when the maintainer pulls from a few developers, who all created a new migration. Then you need to do a lot of work to figure out in which order they should run.

But there is still some light on the end of the tunnel. Some projects like Ruby on Rails or Doctrine use timestamps as migration numbers. Because the chance that two developers commit exactly the same migration number is very thin, you would not have version control conflicts. Yes, they are right with that. But it only makes the problem worse. Because now the order of the migrations is purely determined by the time that each programmer creates the migration, not in a logical order.

Now the project maintainer pulls from those developers, and sees that there are no merge conflicts. Then he asks the migration tool to migrate the database. And it seems that all those migrations except for one fail! Why did this happen? The first migration simply dropped a table that the other migrations builded further on!

So, I’ve done my words about the pain of migrations, so I should also give a better solution. Well, this is actually quite easy, use one database schema instead of a revision system inside of a revision system. Let your SCM handle the differences between them, because that is exactly where an SCM is meant for. And of course there can still be conflicts. But instead of thinking in which order they should run, you only have to think about the database schema, which is much easier.