Data Persistence in Kotlin Multiplatform with SQLDelight

Building offline-first mobile applications is standard practice for providing smooth, interruption-free user experiences. However, managing local relational databases across both Android and iOS typically means writing separate SQLite wrappers or using platform-locked libraries.

SQLDelight solves this in the Kotlin Multiplatform (KMP) ecosystem. It generates type-safe Kotlin APIs directly from your SQL statements, letting you share database logic across platforms while using native SQLite drivers under the hood.

---

Why SQLDelight?

Unlike standard ORMs that map code models to tables, SQLDelight is SQL-first. You write standard SQL schemas and queries, and the SQLDelight compiler generates the corresponding Kotlin code. This provides:

• 100% type-safe query parameters and results.
• Compile-time validation of SQL queries.
• Integration with Kotlin Coroutines Flow.

---

1. Adding Dependencies and the Plugin

First, apply the SQLDelight plugin in your project's root build.gradle.kts and define the database schema configuration in the shared module's build.gradle.kts:

// Root build.gradle.kts
plugins {
    id("app.cash.sqldelight") version "2.0.2" apply false
}

Then, in your shared module's build.gradle.kts:

plugins {
    kotlin("multiplatform")
    id("app.cash.sqldelight")
}

sqldelight {
    databases {
        create("AppDatabase") {
            packageName.set("com.example.db")
        }
    }
}

kotlin {
    sourceSets {
        commonMain.dependencies {
            implementation("app.cash.sqldelight:runtime:2.0.2")
            implementation("app.cash.sqldelight:coroutines-extensions:2.0.2") // For Flow support
        }
        androidMain.dependencies {
            implementation("app.cash.sqldelight:android-driver:2.0.2")
        }
        iosMain.dependencies {
            implementation("app.cash.sqldelight:native-driver:2.0.2")
        }
    }
}

---

2. Defining the Schema and Queries

SQLDelight queries are written in .sq files located in the src/commonMain/sqldelight/com/example/db/ directory.

Create a file named AppDatabase.sq:

CREATE TABLE UserEntity (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

-- Queries:
insertUser:
INSERT OR REPLACE INTO UserEntity(id, name, email)
VALUES (?, ?, ?);

selectAllUsers:
SELECT * FROM UserEntity;

deleteUserById:
DELETE FROM UserEntity WHERE id = ?;

When you build the project, SQLDelight compiles this file and generates the AppDatabase class and query helper functions in Kotlin.

---

3. Platform-Specific Database Drivers

SQLite runs differently on Android and iOS. Android requires a Context to initialize the SQLite open helper, whereas iOS does not. We resolve this by using Kotlin's expect/actual mechanism.

In src/commonMain/kotlin/com/example/db/DatabaseDriverFactory.kt:

import app.cash.sqldelight.db.SqlDriver

expect class DatabaseDriverFactory {
    fun createDriver(): SqlDriver
}

In src/androidMain/kotlin/com/example/db/DatabaseDriverFactory.kt:

import android.content.Context
import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.android.AndroidSqliteDriver

actual class DatabaseDriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(AppDatabase.Schema, context, "app.db")
    }
}

In src/iosMain/kotlin/com/example/db/DatabaseDriverFactory.kt:

import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.native.NativeSqliteDriver

actual class DatabaseDriverFactory {
    actual fun createDriver(): SqlDriver {
        return NativeSqliteDriver(AppDatabase.Schema, "app.db")
    }
}

---

4. Creating the Repository Layer

Now we can initialize our database and query users inside the common repository in commonMain:

import app.cash.sqldelight.coroutines.asFlow
import app.cash.sqldelight.coroutines.mapToList
import kotlinx.coroutines.CoroutineDispatcher
import kotlinx.coroutines.flow.Flow
import kotlinx.coroutines.withContext

class UserRepository(
    driverFactory: DatabaseDriverFactory,
    private val ioDispatcher: CoroutineDispatcher
) {
    private val driver = driverFactory.createDriver()
    private val database = AppDatabase(driver)
    private val dbQueries = database.appDatabaseQueries

    // Get live flow updates from database
    fun getUsersFlow(): Flow<List<UserEntity>> {
        return dbQueries.selectAllUsers()
            .asFlow()
            .mapToList(ioDispatcher)
    }

    // Write operation on background thread
    suspend fun saveUser(id: Long, name: String, email: String) {
        withContext(ioDispatcher) {
            dbQueries.insertUser(id, name, email)
        }
    }
}

---

Conclusion

With SQLDelight, local persistence is completely shared between Android and iOS without writing any platform-specific database handling wrappers. The database schema remains the single source of truth, queries are validated at compile time, and reactive data streams are easily exposed using Kotlin Flows. This makes building high-quality, offline-capable applications in Kotlin Multiplatform an absolute breeze.

To learn how to pair local database caching with a robust remote networking pipeline, check out my guide on Setting Up Ktor for Networking in Kotlin Multiplatform.

---

Hire a Kotlin Multiplatform Specialist

Need help implementing offline-first architecture, database migrations, or scaling your mobile codebase using KMP? I offer remote consulting and contract development for engineering teams in Europe, the UK, and Canada, matching your local working hours (supporting CEST, GMT, and EST).

Let's Connect & Discuss Your Project