Database migrations with SQLDelight and Flyway

• by Alejandro Serrano
• •
• November 07, 2022
• •
• kotlin• database• persistence
• |
• 9 minutes to read.

Application domains are never fully set in stone. New requirements – new functionality, more complex scenario, or even new laws – often require that we change how we model our data. There’s increased awareness of this problem of evolving schemas, and good software architectures prepare upfront for the inevitable change. Kafka, for example, works alongside a registry to support different schema versions. Database servers have caught up in recent years, slowly but steadily, and migrations have become an essential part of the vocabulary.

SQLDelight and Flyway are two of our best tools for dealing with databases in Kotlin. The former allows us to generate typesafe Kotlin methods from SQL schemas and operations, and the latter helps with the tedious work of applying migrations to different databases. In this post, we discuss how to make them work together in order to have a full workflow from start to end; something that is not entirely obvious looking only at the documentation of both projects. If you’re interested in SQLDelight, we’ve talked about the different options for database persistence available in Kotlin previously in this blog.

SQLDelight basic layout

SQLDelight is packaged as a Gradle plug-in. The most basic configuration specifies a name for the class that represents a connection to the database, the package where this class and all the rest should live, and optionally the SQL dialect used in the rest of the files. In the code block below, we show how to declare the dependency on the plug-in in the build.gradle.kts file, alongside a single database named Database living in com.fortyseven.sqldelight.example and using the latest SQLite dialect. Note that we’re assuming here that you’re using an SQLDelight version on the 2.x series, which is still in development at the moment of writing, but works fine for most applications.

plugins {
  kotlin("jvm") version "$kotlinVersion" // or multiplatform
  // maybe others like serialization
  id("app.cash.sqldelight") version "$sqlDelightVersion"
}

sqldelight {
  database("Database") {
    packageName = "com.fortyseven.sqldelight.example"
    dialect("app.cash.sqldelight:sqlite-3-38-dialect:$sqlDelightVersion")
  }
}

Once configured, SQLDelight looks for .sq files in the sqldelight folder under your sources path. With the options defined above, and assuming a Kotlin/JVM layout, those files should live in src/main/sqdelight/com/fortyseven/sqldelight/example. Here’s an example of a very simple Person.sq file that declares a person table, and a newPersonInTown operation to insert new people in the table. Apart from a couple of SQLDelight-isms, this file is plain SQL.

CREATE TABLE person (
  id INTEGER AS PersonId PRIMARY KEY,
  age INTEGER,
  name TEXT NOT NULL
);

newPersonInTown:
INSERT INTO person (age, name)
VALUES (:newAge, :newName)
RETURNING id;

More concretely, there are two elements that prevent this file from being directly consumed by a SQL database server. The first one is the use of AS PersonId in the declaration of the id column, which instructs SQLDelight to (de)serialize the values of that field to and from the PersonId type, instead of the default Long. The second one is giving a name newPersonInTown: to the parametrized insertion below. From this file, SQLDelight generates a Database class to access the database, a Person data class representing values of the table, and a newPersonInTown method that performs the insertion. Since the goal of this post is strictly migrations, we redirect the interested reader to our post on persistence in Kotlin, and the SQLDelight docs.

SQLDelight migration-ready layout

The main idea after migrations is that, every time that the schema changes, instead of modifying the single .sq file, you write a new migration file that explains the changes to be done to the schema to bring it up-to-date. It’s a bit like “control versioning” for database schemas: you start with an initial schema, and then write “diffs”; if you want to get the final schema, you simply execute the migration files in sequence.

SQLDelight supports migrations natively, but it requires a bit of preparation. First of all, we should separate the files describing the schema – the CREATE TABLE above – from the operations and queries – like newPersonInTown. The current schema is going to become our initial schema, which we save as the V1.sqm file (note that the extension has an additional m); the newPersonInTown operation stays in Person.sq.

CREATE TABLE person (
  id INTEGER AS PersonId PRIMARY KEY,
  age INTEGER,
  name TEXT NOT NULL
);

Any change to this schema is described in new migration files with consecutive numbering. For example, we may want to add a new column to this table to record the birth year of each person, so we create a new file V2.sqm with the following content.

ALTER TABLE person ADD COLUMN year INTEGER;

We need to instruct SQLDelight to obtain the complete schema from the combination of all those migration files. For this, we need to slightly tweak the Gradle file, enabling the deriveSchemaFromMigrations option.

sqldelight {
  database("Database") {
    ...
    deriveSchemaFromMigrations = true
  }
}

If you build the project first with only the V1.sqm file in the sqldelight folder, and then with both V1.sqm and V2.sqm, you’ll notice that the Person class gets an additional year: Long? field. This corresponds to the additional field in the ALTER TABLE command.

Flyway

Flyway comes in different packagings, but since we’re already using Gradle for our Kotlin + SQLDelight project, we can also add the Flyway plug-in there.

plugins {
  kotlin("jvm") version "$kotlinVersion" // or multiplatform
  // maybe others like serialization
  id("app.cash.sqldelight") version "$sqlDelightVersion"
  id("org.flywaydb.flyway") version "$flywayVersion"
}

Usually, you only need to point Flyway to the migration files and it will do its magic. Alas, the migration files V1.sqm and V2.sqm cannot be read by Flyway because of the SQLDelight-isms we’ve mentioned above. Fortunately, the SQLDelight developers have thought of this scenario, and provide a Gradle command to generate base SQL files from .sqm migrations. We’re going to configure the output directory, though, to appear as part of the generated code.

sqldelight {
  database("Database") {
    ...
    deriveSchemaFromMigrations = true
    migrationOutputDirectory = file("$buildDir/generated/migrations")
  }
}

If you now run ./gradlew generateMainDatabaseMigrations, the migration files are verified, and then stripped down to bare SQL which Flyway can handle. The next step is telling Flyway that the given folder is the one to look for migrations, once again in the Gradle build file.

flyway {
  locations = arrayOf("filesystem:$buildDir/generated/migrations")
}

We’re ready to execute some migrations! For the sake of conciseness, we’re going to apply the changes using SQLite, which only requires a file path where the information is persisted, but almost every database on Earth is actually supported. In this simple case, we kindly ask Gradle to execute the flywayMigrate task on the given URL, and ask for additional logs with the -i option.

gradle flywayMigrate -Dflyway.url=jdbc:sqlite:file.db -i

The outcome explains that a new schema is created and brought to version 2.

Creating Schema History table "main"."flyway_schema_history" ...
Current version of schema "main": << Empty Schema >>
Migrating schema "main" to version "1"
Migrating schema "main" to version "2"
Successfully applied 2 migrations to schema "main", now at version v2 (execution time 00:00.005s)

You can try to execute the same command again, but then no changes are required, so no migration is performed.

Current version of schema "main": 2
Schema "main" is up to date. No migration necessary.

Your project is now set up for evolving schemas. Any additional changes go into subsequent V3.sqm, V4.sqm, and so on. In most cases, the queries and operations in the .sq file don’t need changes; if they do, this is a good alert of possible breaking changes in your schema. For example, if we’ve marked the year column as NOT NULL, the newPersonInTown operation would no longer be correct, because our definition specifies no value for that column.

Xebia Functional 💙 Kotlin

We’re great fans of Kotlin at Xebia Functional, formerly 47 Degrees, exploring the many possibilities it brings to the back-end scene. We’re proud maintainers of Arrow, a set of companion libraries to Kotlin’s standard library, coroutines, and compiler; and provide Kotlin training to become an expert Kotliner. If you’re interested in talking to us, you can use our contact form, or join us on the Kotlin Slack.