Nelson's log

Home-rolled database migrations

Confession time: I’ve never personally managed a whole project that involved a complex, changing database. I’ve certainly worked on projects with them, like Google AdWords, but someone else was in charge of the database management. With this LoL Slackbot I’m working on I’m the guy. Which means I’m the one who has to handle migrating a database when the code changes the schema.

Here’s a sample migration script I’ll discuss below.

Turns out changing database schemas on deploys is complicated. Who knew? It was a blessing in disguise that sqlite’s alter table is so limited, it forced me to take migrations seriously. And since I’m not using a tool like SQLAlchemy I’m writing migration scripts by hand. Here’s what I do:

One observation I’ve had from this is “schema changes” are more than just changes to the shape of tables. They’re also changes to global values stored in the database, and sometimes changes to the meaning of the data in some column. This single incrementing version number captures all of that just fine, at least with a single developer.

Having now done this work I’m less excited than I used to be about autogenerated schema migrations. I mean, I should try one, but there’s something to be said for hand-crafting a migration when you have to change column values, rerun programs to fill empty data, etc. The automation around testing and version numbers is really valuable though.

I’m terrified with how ad hoc this is. There’s this temptation doing ops work to half-ass stuff, to just type some code in by hand and throw it out, or test it by visual inspection, or… Part of the devops mystique is treating the ops part as real development too, with real testing and repeatability. I’m about 3/4 of the way there with the migration stuff but not fully.