Managing Database version controlled migrations using a list/table

Question

I'm not much of an expert in the area but I am aware that solutions already exist to managing database migrations in version control, e.g. sqitch and liquibase. However I'm currently planning a small hobby project and even sqitch looks like an annoying amount of cognitive overhead.

Idea

A migration is a (name, up action, down action). I was thinking of keeping a single column table (the migrations table) in the SQL database containing unique names of all of the migrations that had been run. Then in version controlled source code there would be a list (the migrations list) with the order of the migrations to run.

Pros:

• (key) conflicts in the migrations list would be caught by the version control software, e.g. git, and then manually resolved;
• a database can easily bring itself up to date by running the migrations in the migrations list that aren't in the migrations table in the order that they appear in the list;
• a development database can rollback to previous schemas by running the down action for all of the migrations in the migrations table that occur before the target migration in the migrations list (might need to add a timestamp so that they can be removed in the right order);
• it's simple enough that I'd be okay with writing an ad-hoc implementation for a project.

Cons:

• the obvious xkcd critique;
• maybe someone has done this before and it didn't work;
• maybe no one has done this before because it wont work.

Question

So my question is, given that I want to handle schema migrations in version control but I don't want to make things complicated, is the above solution a good idea? I'm open to other solutions provided they automatically detect or resolve migration conflicts and are simple.

Answer 1

The way you are planning to do migrations is essentially the same way that Entity Framework does it for .NET applications. It has a DatabaseMigrations table with a key that is the date/time with a unique string appended to it.

The code in a particular release is designed to work with a particular version of the database. It looks for the most recent migration in the migrations table, and applies up migrations until it has the database schema it needs. It can also do downward migrations, but these aren't automatic.

Here's where it differs from your solution: instead of the migration scripts being encoded as SQL as being run as scripts by the version control (if I understand what I read correctly), Entity Framework encodes the migrations as C# classes within the program, so upon database initialisation it will search for subclasses of "Migration" and instantiate them as appropriate. You can get an idea of what this looks like here.

I think the solution you've come up with is a good one. If you're comfortable implementing it, go for it.