Sqldelight

SQLDelight

Instructions

SQLDelight turns .sq files into generated Kotlin APIs. Unlike Room (Android-only), it runs on every KMP target via platform drivers.

1. Plugin & dependencies

// shared/build.gradle.kts
plugins {
    alias(libs.plugins.kotlinMultiplatform)
    alias(libs.plugins.sqldelight)
}

sqldelight {
    databases {
        create("AppDatabase") {
            packageName.set("com.example.db")
            schemaOutputDirectory.set(file("src/commonMain/sqldelight/databases"))
            verifyMigrations.set(true)
        }
    }
}

kotlin.sourceSets {
    commonMain.dependencies {
        implementation(libs.sqldelight.runtime)
        implementation(libs.sqldelight.coroutines.extensions)
    }
    androidMain.dependencies { implementation(libs.sqldelight.android.driver) }
    iosMain.dependencies    { implementation(libs.sqldelight.native.driver) }
    jvmMain.dependencies    { implementation(libs.sqldelight.sqlite.driver) }
    jsMain.dependencies     { implementation(libs.sqldelight.web.driver) }
}

2. Schema & queries in src/commonMain/sqldelight/com/example/db/Article.sq

CREATE TABLE Article (
    id           INTEGER PRIMARY KEY NOT NULL,
    title        TEXT NOT NULL,
    body         TEXT NOT NULL,
    authorId     INTEGER NOT NULL,
    publishedAt  INTEGER NOT NULL,
    FOREIGN KEY(authorId) REFERENCES Author(id)
);

CREATE INDEX article_published_idx ON Article(publishedAt DESC);

selectAll:
SELECT * FROM Article ORDER BY publishedAt DESC;

selectById:
SELECT * FROM Article WHERE id = :id;

selectByAuthor:
SELECT * FROM Article WHERE authorId = :authorId ORDER BY publishedAt DESC LIMIT :limit OFFSET :offset;

upsert:
INSERT OR REPLACE INTO Article(id, title, body, authorId, publishedAt)
VALUES (:id, :title, :body, :authorId, :publishedAt);

deleteOlderThan:
DELETE FROM Article WHERE publishedAt < :cutoff;

SQLDelight generates AppDatabase, ArticleQueries, and a data class Article.

3. Driver factory

// commonMain
expect class DatabaseDriverFactory { fun create(): SqlDriver }

// androidMain
actual class DatabaseDriverFactory(private val context: Context) {
    actual fun create(): SqlDriver =
        AndroidSqliteDriver(AppDatabase.Schema.synchronous(), context, "app.db")
}
// iosMain
actual class DatabaseDriverFactory {
    actual fun create(): SqlDriver =
        NativeSqliteDriver(AppDatabase.Schema.synchronous(), "app.db")
}
// jvmMain
actual class DatabaseDriverFactory(private val dir: File) {
    actual fun create(): SqlDriver {
        val file = File(dir, "app.db")
        val driver = JdbcSqliteDriver("jdbc:sqlite:${file.absolutePath}")
        if (!file.exists()) AppDatabase.Schema.synchronous().create(driver)
        return driver
    }
}

4. Using queries with coroutines

class ArticleDao(db: AppDatabase) {
    private val queries = db.articleQueries

    fun observeAll(): Flow<List<Article>> =
        queries.selectAll().asFlow().mapToList(Dispatchers.IO)

    suspend fun upsertAll(items: List<Article>) = withContext(Dispatchers.IO) {
        queries.transaction {
            items.forEach { queries.upsert(it.id, it.title, it.body, it.authorId, it.publishedAt) }
        }
    }

    suspend fun byId(id: Long): Article? = withContext(Dispatchers.IO) {
        queries.selectById(id).executeAsOneOrNull()
    }
}

• asFlow().mapToList(Dispatchers.IO) emits a new list whenever any write invalidates the query.
• Wrap batch writes in transaction { } — without it each upsert is its own transaction (slow on iOS WAL).

5. Migrations

Bump the schema by adding a .sqm file:

-- 1.sqm — from v1 to v2
ALTER TABLE Article ADD COLUMN excerpt TEXT;

• Each .sqm file is named after the source version (1.sqm ⇒ v1 → v2).
• verifyMigrations.set(true) asserts that applying migrations produces the same schema as the current .sq files.
• Generate verification DB snapshots with ./gradlew generateSqlDelightSchema.

6. Type adapters

For non-primitive columns (enums, Instant, JSON):

CREATE TABLE Article (
    id INTEGER PRIMARY KEY NOT NULL,
    status TEXT AS com.example.domain.ArticleStatus NOT NULL,
    publishedAt INTEGER AS kotlinx.datetime.Instant NOT NULL
);

val db = AppDatabase(
    driver = driver,
    ArticleAdapter = Article.Adapter(
        statusAdapter = EnumColumnAdapter(),
        publishedAtAdapter = InstantColumnAdapter,
    ),
)

object InstantColumnAdapter : ColumnAdapter<Instant, Long> {
    override fun decode(databaseValue: Long) = Instant.fromEpochMilliseconds(databaseValue)
    override fun encode(value: Instant) = value.toEpochMilliseconds()
}

7. Testing

@Test
fun insertsAndObservesArticle() = runTest {
    val driver = inMemoryDriver(AppDatabase.Schema)
    val db = AppDatabase(driver)
    val dao = ArticleDao(db)
    dao.upsertAll(listOf(sampleArticle))
    dao.observeAll().test {
        assertEquals(listOf(sampleArticle), awaitItem())
        cancel()
    }
}

expect fun inMemoryDriver(schema: SqlSchema<QueryResult.Value<Unit>>): SqlDriver

Implement inMemoryDriver per platform (AndroidSqliteDriver(..., name = null), NativeSqliteDriver(..., name = ":memory:"), JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)).

Checklist

• .sq files live in src/commonMain/sqldelight/<package>/… and build without warnings.
• Every table with WHERE-clauses has an index.
• Batch writes use transaction { }.
• Query flows go through mapToList(Dispatchers.IO) — never raw asFlow().
• Migrations tested via verifyMigrations.set(true).
• Type adapters exist for enums and Instant columns.
• In-memory driver available to commonTest.