Implement SQLDelight into your first Kotlin Multiplatform Project

Tech evolution is happened in super fast pace now days. After just get a grab on Jetpack Compose, now we have Kotlin Multiplatform (KMM). KMM is an interesting tech where we can have app in Android and IOS using same bussiness logic. However the UI will still using native UI.

This article will be served as my online notes in implementing DB library for KMM using SQLDelight

1. Add plugin and dependency

Inside project build.graddle.kts

buildscript {
    extra.apply {
        set("sqlDelightVersion", "1.5.3")
    }
    repositories {
        google()
        mavenCentral()
    }
    dependencies {
        classpath("org.jetbrains.kotlin:kotlin-gradle-plugin:1.6.21")
        classpath("com.android.tools.build:gradle:7.1.3")
        classpath("com.squareup.sqldelight:gradle-plugin:${rootProject.extra["sqlDelightVersion"]}")
    }
}

inside build.graddle.kts(:shared)

plugins {
    kotlin("multiplatform")
    id("com.android.library")
    id("com.squareup.sqldelight")
}
...
sourceSets {
    val commonMain by getting {
        dependencies {
            implementation("com.squareup.sqldelight:runtime:1.5.3")
        }
    }
        val androidMain by getting {
            dependencies {
                implementation ("com.squareup.sqldelight:android-driver:1.5.3")
            }
        }
val iosMain by creating {
    dependsOn(commonMain)
    iosX64Main.dependsOn(this)
    iosArm64Main.dependsOn(this)
    iosSimulatorArm64Main.dependsOn(this)
    dependencies {
        implementation("com.squareup.sqldelight:native-driver:1.5.3")
    }
}
....

Note :
Groovy DSL script files use the .gradle file name extension.
Kotlin DSL script files use the .gradle.kts file name extension.

2. Create DB inside build.graddle.kts(:shared)

sqldelight {
    database("KmmNotesDB" //database name) {
        packageName = "com.andreasgift.kmmnotes.shared.cache"
    }
}

3. Create schema
Create file Table.sq file under src/commonMain/sqldelight/package-name/shared/cache. From this SQLDelight will generate a Database Kotlin class with an associated Schema object that can be used to create your database and run your statements on it

CREATE TABLE Note (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL UNIQUE,
body TEXT NOT NULL UNIQUE,
timestamp TEXT NOT NULL UNIQUE DEFAULT '0'
);

selectAllNotes:
SELECT * FROM Note;

selectByTitle:
SELECT * FROM Note WHERE title = ?;

insertNote:
INSERT OR IGNORE INTO Note(title, body, timestamp)
VALUES (?, ?, ?);

deleteAll:
DELETE FROM Note;

deleteByTitle:
DELETE FROM Note WHERE title= ?;

4. Create driver
To actually execute the SQL queries in Kotlin common code, we need an object named SqlDriver. But this SqlDriver is created using different APIs for Android and iOS platforms.

// inside src/commonMain/kotlin/package-name/
expect class DatabaseDriverFactory {
    fun createDriver(): SqlDriver
}
// inside src/androidMain/kotlin/package-name/
actual class DatabaseDriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(com.andreasgift.kmmnotes.shared.cache.KmmNotesDB.Companion.Schema, context, "KmmNotesDB.db")
    }
}
// inside src/IosMain/kotlin/package-name/
actual class DatabaseDriverFactory {
    actual fun createDriver(): SqlDriver {
        return NativeSqliteDriver(com.andreasgift.kmmnotes.shared.cache.KmmNotesDB.Companion.Schema, "KmmNotesDB.db")
    }
}

5. Create DB repository
Now, we’ll create a wrapper class called Database in the same package that internally creates a SqlDriver object using DatabaseDriverFactory and exposes functions to do database operations.

class Database(databaseDriverFactory: DatabaseDriverFactory) {
    private val database = KmmNotesDB(databaseDriverFactory.createDriver())
    private val dbQuery = database.tableQueries

    internal fun getAllNotes(): List<Note> {
        return dbQuery.selectAllNotes().executeAsList()
    }

    internal fun getNoteByTitle(title: String): Note {
        return dbQuery.selectByTitle(title).executeAsOne()
    }

    internal fun insertNote(title: String, body: String?) {
        return dbQuery.insertNote(title, body ?: " ", "0")
    }

    internal fun deleteAll() {
        return dbQuery.deleteAll()
    }

    internal fun deleteByTitle(title: String){
        return dbQuery.deleteByTitle(title)
    }
}

6. Create Database class
Next, let’s create another SDK root class. This creates the Database object and exposes all the operations. This will become our point of entry into the shared library for Android/iOS apps.

class KmmSdk(dbDriverFactory: DatabaseDriverFactory) {
    private val database: Database = Database(dbDriverFactory)

    fun getAllNotes(): List<Note> {
        return database.getAllNotes()
    }

    fun getNoteByTitle(title: String): Note {
        return database.getNoteByTitle(title)
    }

    fun insertNote(title: String, body: String?) {
        database.insertNote(title, body)
    }

    fun deleteAll() {
        database.deleteAll()
    }

    fun deleteByTitle(title: String) {
        database.deleteByTitle(title)
    }
}

The next step will be creating UI for each dedicated Android and iOS using KMMSdk class for its data logic.

Tutorial source logRocket