Kotlin Multiplatform Persistence with SQLDelight

Posted on Feb 18, 2020

SQLDelight is a powerful multiplatform persistence framework built by the team over at Square. You can find the project on their GitHub. What can it do? Let’s take a look together.

SQLDelight generates typesafe kotlin APIs from your SQL statements. It verifies your schema, statements, and migrations at compile-time and provides IDE features like autocomplete and refactoring which make writing and maintaining SQL simple.

See the project website for documentation and APIs.

Setup

Getting Started

To get started, simply add the plugin and respective source sets to your kotlin multiplatform build script. For the examples in this article, we’ll be using Android and iOS source sets as follows:

plugins {
    id("kotlin-multiplatform)
    id("com.squareup.sqldelight")
}
kotlin {
    sourceSets {
        val commonMain by getting {
            dependencies {
                implementation("com.squareup.sqldelight:coroutines-extensions:1.4.3")
                implementation("com.squareup.sqldelight:runtime:1.4.3")
            }
        }
        val androidMain by getting {
            dependencies {
                implementation("com.squareup.sqldelight:android-driver:1.4.3")
            }
        }
        val iosMain by getting {
            dependencies {
                implementation("com.squareup.sqldelight:native-driver:1.4.3")
            }
        }
    }
}
sqldelight {
    database("MyDatabase") { // this will be the class name for your database
        packageName = "com.example"
        sourceFolders = listOf("sqldelight")
    }
}

SQL Driver Setup

Before we can use the database, we first have to create it—that requires setting up our SQL drivers. Let’s do this by defining an expect function in our common source set, and then providing the concrete implementations in the platform specific source sets.

commonMain

import com.squareup.sqldelight.db.SqlDriver

expect fun createSqlDriver(schema: SqlDriver.Schema, name: String): SqlDriver

androidMain

import android.content.Context
import com.squareup.sqldelight.android.AndroidSqliteDriver
import com.squareup.sqldelight.db.SqlDriver

lateinit var appContext: Context // set this inside onCreate of your android application class

actual fun createDatabaseDriver(schema: SqlDriver.Schema, name: String): SqlDriver =
    AndroidSqliteDriver(schema, appContext, name)

iosMain

import com.squareup.sqldelight.db.SqlDriver
import com.squareup.sqldelight.drivers.native.NativeSqliteDriver

actual fun createDatabaseDriver(schema: SqlDriver.Schema, name: String): SqlDriver =
    NativeSqliteDriver(schema, name)

Database Setup

Now that we’ve setup our SQL drivers, we can get onto creating the database. I like to do this by defining a function in our common source set as follows:

import com.example.MyDatabase

fun createDatabase() = MyDatabase(
    driver = createSqlDriver(
        schema = MyDatabase.Schema,
        name = "my.db"
    )
    // wire in any custom adapters here (we'll cover these later)
)

Types

Below are the supported types (taken from the official documentation) you can use in your SQL scripts;

some_long INTEGER,           -- Stored as INTEGER in db, retrieved as Long
some_double REAL,            -- Stored as REAL in db, retrieved as Double
some_string TEXT,            -- Stored as TEXT in db, retrieved as String
some_blob BLOB,              -- Stored as BLOB in db, retrieved as ByteArray
some_int INTEGER AS Int,     -- Stored as INTEGER in db, retrieved as Int
some_short INTEGER AS Short, -- Stored as INTEGER in db, retrieved as Short
some_float REAL AS Float     -- Stored as REAL in db, retrieved as Float

Schema

Add your database schema by adding .sq scripts to your sqldelight source folder. For the example in this article, we’ll be adding a table to store our Episodes from the TVMaze API.

CREATE TABLE EpisodeEntity(
    id INTEGER AS Int NOT NULL PRIMARY KEY,
    airdate TEXT NOT NULL, -- YYYY-MM-DD
    name TEXT NOT NULL,
    showId INTEGER AS Int NOT NULL,
    number INTEGER AS Int,
    season INTEGER AS Int,
    summary TEXT,
);

CREATE INDEX EpisodeEntityAirdate ON EpisodeEntity(airdate);

Functions

Add function definitions ot your .sq scripts:

count:
SELECT COUNT(*)
FROM EpisodeEntity;

insertOrReplace:
INSERT
OR
REPLACE INTO EpisodeEntity(id, airdate, name, showId, number, season, summary)
VALUES ?;

After building, we can see the generated interfaces for our query functions:

interface EpisodeQueries: Transacter {
    fun count(): Query<Long>
    fun insertOrReplace(EpisodeEntity: EpisodeEntity)
}

Views

We’ve got a table for storing our episodes, but to really show off the power of views we need another related entity. Lets add one for storing the corresponding shows too. Back in our .sq script for our EpisodeEntity:

CREATE TABLE EpisodeEntity(
    ...
    FOREIGN KEY (showId) REFERENCES ShowEntity(id)
);

CREATE TABLE ShowEntity(
    id INTEGER AS Int NOT NULL PRIMARY KEY,
    imageUrlMedium TEXT,
    imageUrlOriginal TEXT,
    name TEXT NOT NULL,
);

Now we can leverage that foreign key and create a view for collating information from both tables, allowing us to get additional information about the corresponding show for an episode.

CREATE VIEW ScheduleEpisodeEntity AS
SELECT
  EpisodeEntity.id,
  EpisodeEntity.airdate,
  EpisodeEntity.name,
  EpisodeEntity.showId,
  EpisodeEntity.number,
  EpisodeEntity.season,
  EpisodeEntity.summary,
  ShowEntity.imageUrlMedium,
  ShowEntity.imageUrlOriginal,
  ShowEntity.name AS showName,
FROM EpisodeEntity
JOIN ShowEntity ON EpisodeEntity.showId = ShowEntity.id;

selectByAirdate:
SELECT *
FROM ScheduleEpisodeEntity
WHERE airdate = :airdate;

After building, again we can see the newly generated interfaces.

Adapters

Sometimes we may want to retrieve a column as a custom type, and that’s exactly what Adapters are for. Let’s define a genres property on our ShowEntity and see how we can use an Adapter to keep things clean.

CREATE TABLE ShowEntity(
    ...
    genres TEXT AS List<String>
);

And again after we build, we can see what has been generated.

interface ShowEntity {
    ...
    val genres: List<String>

    class Adapter(
        val genresAdapter: ColumnAdapter<List<String>, String>
    )
}

Next we need to define a column adapter which specifies how to transform the values.

import com.example.MyDatabase
import com.squareup.sqldelight.ColumnAdapter
import schema.ShowEntity

val listOfStringsAdapter = object : ColumnAdapter<List<String>, String> {
    override fun decode(databaseValue: String) = databaseValue.split(",")
    override fun encode(value: List<String>) = value.joinToString(separator = ",")
}

fun createDatabase() = MyDatabase(
    driver = createSqlDriver(
        schema = MyDatabase.Schema,
        name = "my.db"
    )
    ShowEntityAdapter = ShowEntity.Adapter(
        genresAdapter = listOfStringsAdapter
    )
)

Enum Adapters

Enums as TEXT are already provided, so lets define one by adding a status for our ShowEntity.

enum class ShowEntityStatus { 
    ENDED,
    RUNNING,
}

Import this newly created enum into your .sq files, in conjunction with the TEXT type.

import com.example.ShowEntityStatus;

CREATE TABLE ShowEntity(
    ...
    status TEXT AS ShowEntityStatus
);

The generated code should look familiar.

interface ShowEntity {
    ...
    val status: ShowEntityStatus

    class Adapter(
        val statusAdapter: ColumnAdapter<ShowEntityStatus, String>
    )
}

Then all that’s left to do is wire in the provided EnumColumnAdapter class that comes with SQLDelight. Take note of the import location.

import com.example.MyDatabase
import com.squareup.sqldelight.EnumColumnAdapter
import schema.ShowEntity

fun createDatabase() = MyDatabase(
    driver = createSqlDriver(
        schema = MyDatabase.Schema,
        name = "my.db"
    )
    ShowEntityAdapter = ShowEntity.Adapter(
        statusAdapter = EnumColumnAdapter()
    )
)

Testing

We can use the JDBC driver when running our tests against the JVM.

build.gradle.kts

kotlin {
    ...
    sourceSets {
        getByName("androidTest") {
            dependencies {
                implementation("com.squareup.sqldelight:sqlite-driver:1.4.3)
            }
        }
    }
}

commonTest

import com.example.MyDatabase

expect fun createTestDatabase(): MyDatabase

androidTest

import com.example.MyDatabase
import com.squareup.sqldelight.sqlite.driver.JdbcSqliteDriver.*

actual fun createTestDatabase() = MyDatabase(
    driver = JdbcSqliteDriver(IN_MEMORY).apply {
        MyDatabase.Schema.create(this)
    }
    // wire in any custom adapters here, as usual
)

iosTest

Hopefully in the future, we can remedy this and get better test coverage.

actual fun createTestDatabase(): MyDatabase {
    throw NotImplementedError()
}

Migrations

The .sq files always describe how to create the latest schema. If your database is currently on an earlier version, migration files bring those databases up-to-date.

Lets run through a basic migration, adding the runtime property to our ShowEntity. Add the schemaOutputDirectory property to the plugin config, and sync the gradle project.

sqldelight {
    database("MyDatabase") {
        ...
        schemaOutputDirectory = file("src/commonMain/sqldelight/databases")
    }
}

Additional tasks will be added to the module, for generating a database with the current schema, and for verifying migrations.

generateMetadataMainMyDatabaseSchema
verifyMetadataMainMyDatabaseMigration

Add your migrations to .sqm files, they will be verified against the .db files.

ALTER TABLE ShowEntity ADD COLUMN runtime INTEGER;

DROP VIEW ScheduleEpisodeEntity;
CREATE VIEW ScheduleEpisodeEntity AS
SELECT
  EpisodeEntity.id,
  EpisodeEntity.airdate,
  EpisodeEntity.name,
  EpisodeEntity.showId,
  EpisodeEntity.number,
  EpisodeEntity.season,
  EpisodeEntity.summary,
  ShowEntity.imageUrlMedium,
  ShowEntity.imageUrlOriginal,
  ShowEntity.name AS showName,
  ShowEntity.runtime AS showRuntime
FROM EpisodeEntity
JOIN ShowEntity ON EpisodeEntity.showId = ShowEntity.id;

Verify the migration;

./gradlew clean verifyMetadataMainMyDatabaseMigration

> TASK :shared:verifyMetadataMainMyDatabaseMigration FAILED

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':shared:verifyMetadataMainMyDatabaseMigration'.
> Error migration from 1.db, fresh database looks different from migration database:
  /tables[ScheduleEpisodeEntity]/columns[ScheduleEpisodeEntity.showRuntime] - ADDED

As you can see, the current schema must match after the migration, let’s touch up the .sq file.

CREATE TABLE ShowEntity(
    ...
    runtime INTEGER AS Int
);

insertOrReplace:
INSERT
OR
REPLACE INTO ShowEntity(..., runtime)
VALUES ?;

And try again…

./gradlew clean verifyMetadataMainMyDatabaseMigration

> TASK :shared:verifyMetadataMainMyDatabaseMigration

BUILD SUCCESSFUL in 13s
3 actionable tasks: 2 executed, 1 up-to-date

Complex Migrations

It is possible to do migrations in code, if complex transforms are required. Use caution; migration scripts are safer.

import com.example.MyDatabase
import com.squareup.sqldelight.db.SqlDriver

expect fun createDatabase(): MyDatabase

object Schema: SqlDriver.Schema by MyDatabase.Schema { // and then use this schema when creating the sql drivers
    override fun migrate(driver: SqlDriver, oldVersion: Int, newVersion: Int) {
        ...
    }
}