DB migrations with sqlite and ktorm

Context

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.

Thought

The thought around the migration is simple.

1. Have migrations with an increasing sequence number - total is M
2. Grab last applied sequence number from DB - N
3. 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.

Code

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"
val database = Database.connect(dbPath)

// call this from main()
fun dbMigration(database: Database) {
  // add migrations here - this could be read from a file as well
  val firstMigration = "create table if not exists trainings (id integer primary key, name text, priority integer);"
  // other migrations go here..

  val migrationsToApply = listOf(firstMigration)

  // get sequence number
  var migrationVersion = database.useConnection {
    it.prepareStatement("pragma user_version;").executeQuery().getInt("user_version")
  }

  // apply each migration and update the user_version as an atomic transaction
  while (migrationVersion < migrationsToApply.size) {
    database.useTransaction {
      val connection = it.connection
      connection.prepareStatement(migrationsToApply[migrationVersion]).execute()
      migrationVersion += 1
      connection.prepareStatement("PRAGMA user_version = ${migrationVersion};").execute()
      connection.commit()
    }
  }
}

that’s it. All migrations are now safely applied directly on app startup.

Conclusion

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.