Recently I was writing a web app for a friend to track some employees and their trainings. They were going to use a spreadsheet to track everything and as we know every spreadsheet is an app waiting to be developed.
The app uses an embedded sqlite db to store all the information. Sqlite allows for a more nimble and flexible application that can be run and forgotten. To take this flexibility to the next level I wanted the app to run the migrations required if not applied. I didn’t want to introduce a heavy migration framework or library as that starts degrading the app’s nimbleness.
It is not too bad to do with
create tables since it is possible to tack on a
if not exists but
alter tables are a bit more tricky because they don’t support
if not exists directive.
The thought around the migration is simple.
- Have migrations with an increasing sequence number - total is M
- Grab last applied sequence number from DB - N
- Apply all migrations between 2 and 3 between M and N and update the sequence number as an atomic transaction
Focusing on 2 a bit - most DB frameworks will have a schemas or migrations table in the DB stored alongside the app’s tables where it keeps tracks of the last applied migrations.
We can avoid creating this table by taking advantage of the
user_version integer that is specifically for the app to add an integer to the database header that can be interpreted anyway it wants - sqlite itself makes no use of this number.
I am using ktorm as my ORM library and will use it to run raw sql commands to do the migrations. Initially I was using jdbi - but then removed it as I could do it with just ktorm so it was nice to only have one dependency.
const val dbPath = "jdbc:sqlite:default.db"
that’s it. All migrations are now safely applied directly on app startup.
I had the mvp of the app deployed very quickly and working with Javalin, Kotlin with a Sqlite DB using ktorm was an absolute delight. I might do more short posts detailing how certain parts of the app, such as auth, were done.