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.
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.
- (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.
- 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.
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.