For zsql, I needed a way to set up and migrate the SQLite database that stores history. An ORM would be overkill at the size of my project, so I rolled my own migration system.
Typical ORMs keep migration state in a table. As it turns out, SQLite has a user_version pragma for, well, user versioning. We’ll use it to track the current schema version. It even conveniently defaults to zero!
This post uses Python and its import sqlite3, but language doesn’t matter. It’s all SQLite!
We define an array-of-arrays of migrations and use its length as the application version:
And helpers to wrap the version-handling so it doesn’t complicate migrating:
user_version is treated as an array index into MIGRATIONS that can be saved and loaded from the database. We only need to run the migrations at and above the user version we load.
I’ve found this pattern to be helpful for smaller applications using SQLite. It’s generalizable and doesn’t require much code, so it’s easy to adapt to what’s needed.
Migrations are ordered and keyed by array index. Developers who want to merge migrations must keep the migration array ordered by merge date (or another system) to avoid a release which skips migrations or runs them multiple times.
SQLite has limited ability to alter the schema, particularly with alter table. You’ll have to write migrations so they’re compatible with SQLite.