Whid
← Blog

Whid — The Origin

Chapter 2: SQLDelight for Kotlin/Native

1 April 2022

Arthur is still on his way to the desk, basically the same as before he was a snail. Well, then I guess it’s up to me to set up our data storage. Typical.

In Kotlin Native, oh boy, that’s gonna be fun. 
This Organizational Change Specialist Manager lady better have a good reason for this.

TLDR; Issues addressed

Finding a suitable Data Storage Solution

For our time tracking app we’ll have to store data. There is a number of different approaches we could take here. The most basic would be to just read and write files on the disk manually. While this is very easy to get started with, it isn’t the most versatile or safe approach.

Instead, we decided to go with a classic SQL Database. We don’t have tons of data so there is no need for some big data solution for our task. Through our choice of Kotlin/Native, we are somewhat limited in the database libraries we can use. We decided to go with SQLDelight which is built on SQLite. It’s a fairly lightweight yet powerful solution that should be suitable for our use case.

On our search for a fitting database, we happened upon a very useful resource for Kotlin/Native Libraries. This GitHub Repo has a list of libraries that are written for or support Kotlin/Native. Not all of them support all platforms that it can be run on though.

Technical Setup for SQLDelight with Kotlin/Native

The technical setup will involve adding a few dependencies and a plugin to our gradle file and creating a file for some initial database queries. After that, we set up a small unit test to try out our first queries.

Gradle Setup

For the gradle part, we need to add the plugin

plugins {
    kotlin("multiplatform") version "1.6.10"
        id("com.squareup.sqldelight") version "1.5.3"
}

and put the dependency for the SQLDelight native driver in our kotlin block.

sourceSets {
    val nativeMain by getting {
        dependencies {
            implementation("com.squareup.sqldelight:native-driver:1.5.3")
        }
    }
    val nativeTest by getting {
        dependencies {
            implementation("com.squareup.sqldelight:native-driver:1.5.3")
            implementation(kotlin("test-common"))
            implementation(kotlin("test-annotations-common"))
        }
    }
}

After that, we can add an SQLDelight block that defines the package and database name.

sqldelight {
    database("WhidDatabase") { // The name of the generated database class
        packageName = "com.zr.db"
    }
}

You can find additional gradle configuration options on the SQLDelight website.
For some additional SQLDelight integration in IntelliJ, you can install their plugin.

Creating some queries

When creating your first query file, be careful to put it in the right folder. Unless configured otherwise, your query files need to be in src/commonMain/sqldelight to be detected. SQLDelight uses these files to generate query files that you can call from your Kotlin code.

Let’s look at a small example. First, we create a file in src/commonMain/sqldelight/com/zr/db called tasks.sq and put the following SQL statements into it:

CREATE TABLE task
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    description TEXT NOT NULL
);

createTask:
INSERT INTO task (description)
VALUES (:desc); -- :desc is a parameter we can use in the method call

getTasks:
SELECT * FROM task;

clearTasks:
DELETE FROM task;

This will set up a table called task and three queries for inserting into the table, getting its content and emptying it completely.

We then need to write some code to create our database and add functions to call our queries. To keep it simple, we will keep everything in one file for now. There will be a Chapter on Dependency Injection with Kodein for a more elegant solution.

private val driver: SqlDriver = NativeSqliteDriver(WhidDatabase.Schema, "whid.db", 1)
private val database = WhidDatabase(driver)

fun createTaskEntry(taskName: String) {
    database.taskQueries.createTask(taskName)
}

fun getTasks(): List<Task> {
    return database.taskQueries.getTasks().executeAsList()
}

fun clearTasks() {
    database.taskQueries.clearTasks()
}

Trying them out

Finally, to make sure everything works, let’s set up some simple unit tests for database insertion, selection and deletion.

If you have set up the project in IntelliJ as a Kotlin/Native project, as described in a previous blogpost, you can add a Kotlin file with a small test case at src/nativeTest/kotlin:

class TaskQueriesTest {

    /**
     * Clear the database before every test so we don't have any
     * conflicts with previous tests.
     */
    @BeforeTest
    fun prepareDb() {
        clearTasks()
    }

    @Test
    fun testInsertTask() {
        createTaskEntry("Build that damn time tracking app")
        print(getTasks())
    }
}

Note that for actual regression tests, printing doesn’t make a whole lot of sense and you should have assertions in your tests that automatically check that the output is as expected. On the other hand, for seeing whether our database implementation works, this is sufficient and actually more easy to grasp IMO.

Issues we encountered

During our initial setup, we ran into some problems that took quite a while to figure out but are easy to avoid if you know about them.

How to switch to an in-memory DB

During development, having a database that is persistent from the start can cause issues. Since we might make a lot of changes to our database during development, having it in memory might be more beneficial. To achieve this, you will have to change the configuration during database creation. Here is an example for what this can look like: 

private val dbConfig = DatabaseConfiguration(
    name = "whid.db",
    inMemory = true,
    version = WhidDatabase.Schema.version,
    create = { connection ->
        wrapConnection(connection) { WhidDatabase.Schema.create(it) }
    },
    upgrade = { connection, oldVersion, newVersion ->
        wrapConnection(connection) { WhidDatabase.Schema.migrate(it, oldVersion, newVersion) }
    }
)

private val driver: SqlDriver = NativeSqliteDriver(dbConfig, 1)

Note that even though you are using an in-memory database - so no file will be created - you must not leave the name empty, otherwise you will get a runtime error.

Problems with Schema Changes

If you have made some changes to your .sq files and the code doesn’t run anymore, it might just be that your database schema has changed. This results in some fairly unhelpful runtime errors about not being able to compile the queries . Even completely emptying the database won’t reset the schema.

co.touchlab.sqliter.interop.SQLiteExceptionErrorCode: error while compiling: DELETE FROM timeEntries
    at kfun:kotlin.Throwable#(kotlin.String?;kotlin.Throwable?){} (00007ff60d4cad90)
    at kfun:kotlin.Throwable#(kotlin.String?){} (00007ff60d4cb0c0)
    at kfun:kotlin.Exception#(kotlin.String?){} (00007ff60d4c4630)
    at kfun:co.touchlab.sqliter.interop.SQLiteException#(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig){} (00007ff60d6136b0)
    at kfun:co.touchlab.sqliter.interop.SQLiteExceptionErrorCode#(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig;kotlin.Int){} (00007ff60d613780)
    at kfun:co.touchlab.sqliter.interop.SqliteDatabase#prepareStatement(kotlin.String){}co.touchlab.sqliter.interop.SqliteStatement (00007ff60d616a30)

The easiest solution we found for the problem was to delete the database from the disk - or move the database to in-memory as described in the previous section.

By default on MacOS you can find the database file in ~/Library/Application Support/databases or in  ~/Library/Containers/com.zr.whid-mac/Data/Library/Application Support/databases if you are using it from within your Xcode project.