Shared Persistence with SQLDelight

Why SQLDelight?

SQLDelight generates type-safe Kotlin from SQL statements. You write real SQL in .sq files, and it produces functions you call from commonMain. The same queries run on Android and iOS.

Version: SQLDelight 2.1.x.

Step 1: Add the Plugin and Dependencies

In the root build.gradle.kts (or settings.gradle.kts pluginManagement), add the plugin. Then configure the shared module.

// shared/build.gradle.kts (plugins)
plugins {
    kotlin("multiplatform")
    id("app.cash.sqldelight") version "2.1.0"
}

kotlin {
    androidTarget()
    iosArm64(); iosX64(); iosSimulatorArm64()

    sourceSets {
        commonMain.dependencies {
            implementation("app.cash.sqldelight:coroutines-extensions:2.1.0")
            implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.10.2")
        }
        androidMain.dependencies {
            implementation("app.cash.sqldelight:android-driver:2.1.0")
        }
        iosMain.dependencies {
            implementation("app.cash.sqldelight:native-driver:2.1.0")
        }
    }
}

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

Step 2: Write the Schema

Create the file shared/src/commonMain/sqldelight/com/example/db/User.sq:

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

insertUser:
INSERT INTO User(id, name, email) VALUES (?, ?, ?);

selectByName:
SELECT * FROM User WHERE name = ?;

selectAll:
SELECT * FROM User;

Each labeled query (ending with :) becomes a Kotlin function.

Step 3: Provide a Driver Per Platform

The database needs a platform SqlDriver.

// commonMain
import app.cash.sqldelight.db.SqlDriver

expect class DatabaseDriverFactory {
    fun createDriver(): SqlDriver
}

// androidMain
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 =
        AndroidSqliteDriver(AppDatabase.Schema, context, "app.db")
}

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

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

Step 4: Use the Database

// commonMain
import com.example.db.AppDatabase
import kotlinx.coroutines.flow.Flow
import app.cash.sqldelight.coroutines.asFlowToList

class UserRepository(factory: DatabaseDriverFactory) {
    private val db = AppDatabase(factory.createDriver())
    private val queries = db.userQueries

    fun insert(id: Long, name: String, email: String) =
        queries.insertUser(id, name, email)

    fun all(): List<User> = queries.selectAll().executeAsList()

    fun observe(): Flow<List<User>> =
        queries.selectAll().asFlowToList()
}

Diagram: commonMain defines the schema and repository; each platform supplies the driver.

Loading diagram...

Expected Output

User(id=1, name=Alice, email=alice@example.com)

Migrations

Add .sq files like 1.sqm for changes. Increase the schema version and write migration statements. SQLDelight verifies them against the new schema.

Practice Exercise

1. Add a Task table with id, title, and done columns.
2. Add queries insertTask and selectAllTasks.
3. Create TaskRepository with add(title) and all() functions.
4. Insert two tasks on Android and list them to confirm persistence.