Using SQLDelight in Kotlin Multiplatform Project

May 14, 2020

SQLDelight is quickly becoming the defacto library for persisting data locally in Kotlin Multiplatform (KMP) projects. It generates type-safe APIs from SQL statements. Generally, the installation steps in the documentation are not bad but I managed to get stuck at some point. I was putting the .sq file in a wrong folder so my database schema was not generated—no warnings, just nothing gets generated.

Hence, the idea for this blog post: add more detail to the installation steps as well as providing a link to the complete source code so you don’t end up wasting time like I did.

Let’s assume we have a KMP/KMM project that defines two targets, Android and iOS, with the following structure:

And here is a shared expanded:

Define Gradle dependency

Let’s define SQLDelight dependencies to be used in <project-root>/shared/build.gradle.kts like so:

object SqlDelight {
  const val runtime = "com.squareup.sqldelight:runtime:$"
  const val android = "com.squareup.sqldelight:android-driver:$"
  const val native = "com.squareup.sqldelight:native-driver:$

Edit shared/build.gradle.kts

Modify <project-root>/shared/build.gradle.kts

Add sqldelight plugin:

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

Configure the plugin at the bottom of the file:

sqldelight {
  database("KmpGithubDatabase") {
    packageName = "com.jshvarts.kmp.db"
    sourceFolders = listOf("sqldelight")
  }
}

Use the dependency in your targets:

val commonMain by getting {
  dependencies {
    ...
    implementation(SqlDelight.runtime)
  }
}

val androidMain by getting {
  dependencies {
    ...
    implementation(SqlDelight.android)
  }
}

val iOSMain by getting {
  dependencies {
    ...
    implementation(SqlDelight.native)
  }
}

Set up SQL schema and queries

Create <project-root>/shared/src/commonMain/sqldelight/ folder

Notice that the sqldelight plugin declared the package com.jshvarts.kmp.db above. Create this package structure to match it:

<project-root>/shared/src/commonMain/sqldelight/com/jshvarts/kmp/db/

Finally, create an .sq file that contains your SQL schema and queries (the file name is not important; the file extension .sq is important):

<project-root>/shared/src/commonMain/sqldelight/com/jshvarts/kmp/db/KmpGithub.sq

Here are the contents of the file in my case:

CREATE TABLE member (
  key INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  id INTEGER NOT NULL,
  login TEXT NOT NULL,
  avatarUrl TEXT NOT NULL
);

insertItem:
INSERT OR REPLACE INTO member (id, login, avatarUrl) VALUES (?, ?, ?);

selectAll:
SELECT * FROM member ORDER BY key;

deleteAll:
DELETE FROM member;

Although not necessary, I recommend installing SqlDelight Android Studio plugin at this point. This will help with syntax validation and highlighting.

This definitely helps editing your SQL files:

Generate SQL Queries

You can now build the project which will generate Database and queries, everything that you need to use SQLDelight in your app. Execute the build task from the root of your project:

./gradlew build

If no Database files got generated in ./shared/build/, something went wrong. Check that the location where you placed your .sq file matches with your plugin definition.

Define Database

Define target-specific Database driver using expect/actual KMP mechanism.

expect

package com.jshvarts.kmp.repository

import com.jshvarts.kmp.db.KmpGithubDatabase

internal expect fun cache(): KmpGithubDatabase

android actual

package com.jshvarts.kmp.repository

import android.content.Context
import com.jshvarts.kmp.db.KmpGithubDatabase
import com.squareup.sqldelight.android.AndroidSqliteDriver

lateinit var appContext: Context

internal actual fun cache(): KmpGithubDatabase {
  val driver = AndroidSqliteDriver(KmpGithubDatabase.Schema, appContext, "members.db")
  return KmpGithubDatabase(driver)
}

ios actual

package com.jshvarts.kmp.repository

import com.jshvarts.kmp.db.KmpGithubDatabase
import com.squareup.sqldelight.drivers.native.NativeSqliteDriver

internal actual fun cache(): KmpGithubDatabase {
  val driver = NativeSqliteDriver(KmpGithubDatabase.Schema, "members.db")
  return KmpGithubDatabase(driver)
}

Using SQL Queries

Finally, use the queries generated by the plugin:

In my case, these two interfaces will be used by my repository: KmpGithubDatabase and KmpGithubQueries.

I import them in my repository:

import com.jshvarts.kmp.db.KmpGithubDatabase
import com.jshvarts.kmp.db.KmpGithubQueries

And use them like so:

queries.deleteAll()
...
queries.insertItem(
          member.id,
          member.login,
          member.avatarUrl
      )
...
queries.selectAll()
        .executeAsList()
        .map { Member(id = it.id, login = it.login, avatarUrl = it.avatarUrl) }

And that’s all! I hope you were able to get this set up in your KMP project without any issues. You can see the entire source code for this project here: KmpGithubMVVM