KMP Tutorial #7: SQLDelight — Database in Kotlin Multiplatform

In the Ktor tutorial, we loaded data from the internet. But what happens when the user has no internet? The app shows nothing.

You need a local database — and in KMP, that means SQLDelight.

SQLDelight is the opposite of Room. Room generates SQL from Kotlin. SQLDelight generates Kotlin from SQL. You write real SQL queries, and SQLDelight generates type-safe Kotlin code. It works on Android, iOS, Desktop, and Web — all from the same .sq files.

SQLDelight vs Room — Which for KMP?

	SQLDelight	Room
Approach	SQL first → generates Kotlin	Kotlin first → generates SQL
KMP support	Full (designed for it)	Partial (added later)
Query language	You write real SQL	Annotations + DAO methods
Type safety	Generated from SQL	Generated from annotations
Migration	`.sqm` files with SQL	Java/Kotlin migration classes
Best for	KMP projects, SQL lovers	Android-only, Kotlin lovers

If you are building KMP, SQLDelight is the standard choice. Room’s KMP support is newer and less mature.

Setup

Dependencies

# gradle/libs.versions.toml
[versions]
sqldelight = "2.0.2"

[libraries]
sqldelight-coroutines = { module = "app.cash.sqldelight:coroutines-extensions", version.ref = "sqldelight" }
sqldelight-android-driver = { module = "app.cash.sqldelight:android-driver", version.ref = "sqldelight" }
sqldelight-native-driver = { module = "app.cash.sqldelight:native-driver", version.ref = "sqldelight" }

[plugins]
sqldelight = { id = "app.cash.sqldelight", version.ref = "sqldelight" }

Gradle Configuration

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

// Configure the database
sqldelight {
    databases {
        create("AppDatabase") {
            packageName.set("com.kemalcodes.db")
        }
    }
}

kotlin {
    sourceSets {
        commonMain.dependencies {
            implementation(libs.sqldelight.coroutines)
        }
        androidMain.dependencies {
            implementation(libs.sqldelight.android.driver)
        }
        iosMain.dependencies {
            implementation(libs.sqldelight.native.driver)
        }
    }
}

The sqldelight block tells the plugin to generate a database class called AppDatabase in the package com.kemalcodes.db.

Create the Driver (expect/actual)

Each platform needs a different SQLite driver:

// shared/src/commonMain/kotlin/database/DriverFactory.kt
// Note: expect/actual classes are in beta — constructor signatures
// can differ between expect and actual declarations
expect class DriverFactory {
    fun createDriver(): SqlDriver
}

// shared/src/androidMain/kotlin/database/DriverFactory.android.kt
import android.content.Context
import app.cash.sqldelight.driver.android.AndroidSqliteDriver

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

// shared/src/iosMain/kotlin/database/DriverFactory.ios.kt
import app.cash.sqldelight.driver.native.NativeSqliteDriver

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

Step 1: Define Your Tables with .sq Files

This is where SQLDelight is fundamentally different from Room. Instead of Kotlin classes, you write SQL files.

Create shared/src/commonMain/sqldelight/com/kemalcodes/db/Note.sq:

-- Note.sq
-- This file defines the 'notes' table and all queries for it.
-- SQLDelight generates Kotlin code from these SQL statements.

-- TABLE DEFINITION
CREATE TABLE notes (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL DEFAULT '',
    is_completed INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);

-- CREATE INDEX for faster searches
CREATE INDEX notes_title ON notes(title);

-- QUERIES
-- Each named query becomes a Kotlin function

-- Get all notes, newest first
selectAll:
SELECT * FROM notes ORDER BY created_at DESC;

-- Get one note by ID
selectById:
SELECT * FROM notes WHERE id = ?;

-- Search notes by title (case-insensitive)
searchByTitle:
SELECT * FROM notes
WHERE title LIKE '%' || ? || '%'
ORDER BY created_at DESC;

-- Get only incomplete notes
selectActive:
SELECT * FROM notes
WHERE is_completed = 0
ORDER BY created_at DESC;

-- Get only completed notes
selectCompleted:
SELECT * FROM notes
WHERE is_completed = 1
ORDER BY created_at DESC;

-- Count all notes
countAll:
SELECT COUNT(*) FROM notes;

-- Count completed notes
countCompleted:
SELECT COUNT(*) FROM notes WHERE is_completed = 1;

-- INSERT a new note
insert:
INSERT INTO notes (title, content, is_completed, created_at, updated_at)
VALUES (?, ?, 0, ?, ?);

-- UPDATE a note's title and content
updateNote:
UPDATE notes
SET title = ?, content = ?, updated_at = ?
WHERE id = ?;

-- Toggle completion status
toggleCompleted:
UPDATE notes
SET is_completed = CASE WHEN is_completed = 0 THEN 1 ELSE 0 END,
    updated_at = ?
WHERE id = ?;

-- DELETE a note
deleteById:
DELETE FROM notes WHERE id = ?;

-- DELETE all completed notes
deleteCompleted:
DELETE FROM notes WHERE is_completed = 1;

This is real SQL. Every named block (like selectAll:) becomes a Kotlin function in the generated code. The ? placeholders become function parameters.

What SQLDelight Generates

From the .sq file above, SQLDelight generates:

// Auto-generated — you never write this
interface NoteQueries : Transacter {
    fun selectAll(): Query<Notes>
    fun selectById(id: Long): Query<Notes>
    fun searchByTitle(title: String): Query<Notes>
    fun selectActive(): Query<Notes>
    fun selectCompleted(): Query<Notes>
    fun countAll(): Query<Long>
    fun countCompleted(): Query<Long>
    fun insert(title: String, content: String, created_at: Long, updated_at: Long)
    fun updateNote(title: String, content: String, updated_at: Long, id: Long)
    fun toggleCompleted(updated_at: Long, id: Long)
    fun deleteById(id: Long)
    fun deleteCompleted()
}

Type-safe. Compile-time checked. If your SQL has a typo, the project won’t compile — not a runtime crash.

Step 2: Create the Repository

Wrap the generated queries in a repository that returns Flow:

// shared/src/commonMain/kotlin/repository/NoteRepository.kt

import app.cash.sqldelight.coroutines.asFlow
import app.cash.sqldelight.coroutines.mapToList
import app.cash.sqldelight.coroutines.mapToOneOrNull
import com.kemalcodes.db.AppDatabase
import com.kemalcodes.db.Notes
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.flow.Flow

class NoteRepository(database: AppDatabase) {

    private val queries = database.noteQueries

    // Get all notes as a Flow — UI updates automatically when data changes
    fun getAllNotes(): Flow<List<Notes>> {
        return queries.selectAll()
            .asFlow()
            .mapToList(Dispatchers.IO)
    }

    // Get one note by ID
    fun getNoteById(id: Long): Flow<Notes?> {
        return queries.selectById(id)
            .asFlow()
            .mapToOneOrNull(Dispatchers.IO)
    }

    // Search notes
    fun searchNotes(query: String): Flow<List<Notes>> {
        return queries.searchByTitle(query)
            .asFlow()
            .mapToList(Dispatchers.IO)
    }

    // Get active (incomplete) notes
    fun getActiveNotes(): Flow<List<Notes>> {
        return queries.selectActive()
            .asFlow()
            .mapToList(Dispatchers.IO)
    }

    // Get completed notes
    fun getCompletedNotes(): Flow<List<Notes>> {
        return queries.selectCompleted()
            .asFlow()
            .mapToList(Dispatchers.IO)
    }

    // INSERT
    fun addNote(title: String, content: String = "") {
        val now = currentTimeMillis()
        queries.insert(
            title = title,
            content = content,
            created_at = now,
            updated_at = now
        )
    }

    // UPDATE
    fun updateNote(id: Long, title: String, content: String) {
        queries.updateNote(
            title = title,
            content = content,
            updated_at = currentTimeMillis(),
            id = id
        )
    }

    // TOGGLE completion
    fun toggleNote(id: Long) {
        queries.toggleCompleted(
            updated_at = currentTimeMillis(),
            id = id
        )
    }

    // DELETE one
    fun deleteNote(id: Long) {
        queries.deleteById(id)
    }

    // DELETE all completed
    fun deleteCompletedNotes() {
        queries.deleteCompleted()
    }

    // Transactions — multiple operations as one atomic unit
    fun addMultipleNotes(titles: List<String>) {
        queries.transaction {
            val now = currentTimeMillis()
            titles.forEach { title ->
                queries.insert(
                    title = title,
                    content = "",
                    created_at = now,
                    updated_at = now
                )
            }
        }
    }
}

// Platform-specific time function
expect fun currentTimeMillis(): Long

How Flow Works with SQLDelight

Database changes (insert/update/delete)
        ↓
SQLDelight detects the change
        ↓
.asFlow() emits new query result
        ↓
.mapToList() converts to List<Notes>
        ↓
ViewModel collects it
        ↓
UI updates automatically

This is the same reactive pattern as Room + Flow. No manual refresh needed.

Step 3: Use in ViewModel

// shared/src/commonMain/kotlin/viewmodel/NotesViewModel.kt

class NotesViewModel(private val repository: NoteRepository) {

    private val _state = MutableStateFlow(NotesScreenState())
    val state: StateFlow<NotesScreenState> = _state.asStateFlow()

    private var searchJob: Job? = null

    init {
        observeNotes("")
    }

    fun addNote(title: String, content: String = "") {
        repository.addNote(title, content)
        // No manual refresh — Flow handles it
    }

    fun toggleNote(id: Long) {
        repository.toggleNote(id)
    }

    fun deleteNote(id: Long) {
        repository.deleteNote(id)
    }

    fun onSearchQueryChange(query: String) {
        _state.update { it.copy(searchQuery = query) }
        observeNotes(query)
    }

    fun deleteCompleted() {
        repository.deleteCompletedNotes()
    }

    private fun observeNotes(query: String) {
        searchJob?.cancel()
        searchJob = viewModelScope.launch {
            val flow = if (query.isEmpty()) {
                repository.getAllNotes()
            } else {
                repository.searchNotes(query)
            }
            flow.collect { notes ->
                _state.update { it.copy(notes = notes) }
            }
        }
    }
}

data class NotesScreenState(
    val notes: List<Notes> = emptyList(),
    val searchQuery: String = ""
)

This ViewModel works on both Android and iOS. The repository, database, and queries are all shared code.

Migrations — Evolving Your Schema

Your app is in production. Users have data. Now you need to add a new column. You can’t just change the CREATE TABLE statement — that would destroy existing data.

SQLDelight uses migration files (.sqm) to upgrade existing databases.

How Versioning Works

Version 1: Initial schema (from Note.sq)
Version 2: Add 'priority' column (from 1.sqm)
Version 3: Add 'category' column (from 2.sqm)

The .sq file always describes the latest schema. Migration files describe how to get from one version to the next.

Creating a Migration

Step 1: Update your .sq file with the new column:

-- Note.sq (updated — version 3)
CREATE TABLE notes (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL DEFAULT '',
    is_completed INTEGER NOT NULL DEFAULT 0,
    priority INTEGER NOT NULL DEFAULT 0,    -- NEW in version 2
    category TEXT NOT NULL DEFAULT '',       -- NEW in version 3
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);

Step 2: Create migration files:

shared/src/commonMain/sqldelight/migrations/
├── 1.sqm    ← Migrates from version 1 → 2
└── 2.sqm    ← Migrates from version 2 → 3

-- 1.sqm (version 1 → 2)
-- Adds the priority column
ALTER TABLE notes ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;

-- 2.sqm (version 2 → 3)
-- Adds the category column
ALTER TABLE notes ADD COLUMN category TEXT NOT NULL DEFAULT '';

Step 3: Update the database version in your driver:

// The schema version is automatically tracked by SQLDelight
// AppDatabase.Schema.version returns the current version
// Migrations run automatically when the database opens

Migration with Data Transformation

Sometimes you need to transform data during migration. Use AfterVersion:

AppDatabase.Schema.migrate(
    driver = driver,
    oldVersion = 1,
    newVersion = AppDatabase.Schema.version,
    AfterVersion(2) { driver ->
        // After migrating to version 3, set default category
        // for all existing notes based on title keywords
        driver.execute(null, """
            UPDATE notes SET category = 'work'
            WHERE title LIKE '%meeting%' OR title LIKE '%project%'
        """.trimIndent(), 0)
        driver.execute(null, """
            UPDATE notes SET category = 'personal'
            WHERE category = ''
        """.trimIndent(), 0)
    }
)

Verifying Migrations

SQLDelight can verify your migrations produce the correct schema:

Place a database file from version 1 at shared/src/commonMain/sqldelight/1.db
Run ./gradlew verifySqlDelightMigration
SQLDelight applies all migrations and checks the result matches your .sq file

This catches migration bugs before they reach production.

Migration Best Practices

Practice	Why
Always use `DEFAULT` for new columns	Existing rows need a value
Never rename columns	SQLite doesn’t support `ALTER TABLE RENAME COLUMN` in all versions
Test migrations on real data	Use the `.db` verification method
Keep migrations small	One change per `.sqm` file
Never delete migration files	Users might upgrade from any old version
Don’t wrap in transactions	SQLDelight handles transactions automatically

Transactions

For multiple operations that must succeed or fail together:

// All or nothing — if any insert fails, none are saved
queries.transaction {
    queries.insert("Buy groceries", "", now, now)
    queries.insert("Clean house", "", now, now)
    queries.insert("Call dentist", "", now, now)
}

// With rollback handling
queries.transaction {
    queries.insert("Important task", "", now, now)

    // Check a condition
    val count = queries.countAll().executeAsOne()
    if (count > 100) {
        rollback()  // Cancel the transaction — nothing is saved
    }
}

Full CRUD Example

Here is every operation in one place:

class NotesCrudExample(private val db: AppDatabase) {

    private val queries = db.noteQueries
    private val now get() = Clock.System.now().toEpochMilliseconds()

    // CREATE
    fun create(title: String, content: String = "") {
        queries.insert(title, content, now, now)
    }

    // READ — single item
    fun getById(id: Long): Notes? {
        return queries.selectById(id).executeAsOneOrNull()
    }

    // READ — all items as Flow
    fun getAll(): Flow<List<Notes>> {
        return queries.selectAll().asFlow().mapToList(Dispatchers.IO)
    }

    // UPDATE
    fun update(id: Long, title: String, content: String) {
        queries.updateNote(title, content, now, id)
    }

    // DELETE — single
    fun delete(id: Long) {
        queries.deleteById(id)
    }

    // DELETE — batch
    fun deleteCompleted() {
        queries.deleteCompleted()
    }

    // TOGGLE
    fun toggle(id: Long) {
        queries.toggleCompleted(now, id)
    }

    // BATCH INSERT (transaction)
    fun createMultiple(titles: List<String>) {
        queries.transaction {
            titles.forEach { title ->
                queries.insert(title, "", now, now)
            }
        }
    }

    // COUNT
    fun totalCount(): Long {
        return queries.countAll().executeAsOne()
    }

    // SEARCH
    fun search(query: String): Flow<List<Notes>> {
        return queries.searchByTitle(query).asFlow().mapToList(Dispatchers.IO)
    }
}

Common Mistakes

Mistake 1: Forgetting the .sq File Location

# WRONG — won't be found by SQLDelight
shared/src/commonMain/kotlin/Note.sq

# RIGHT — must be in the sqldelight folder
shared/src/commonMain/sqldelight/com/kemalcodes/db/Note.sq

The path after sqldelight/ must match the packageName in your Gradle config.

Mistake 2: Not Using Flow for UI

// BAD — only gets data once, doesn't update
val notes = queries.selectAll().executeAsList()

// GOOD — updates automatically when data changes
val notes = queries.selectAll().asFlow().mapToList(Dispatchers.IO)

Mistake 3: Forgetting Dispatchers.IO

// BAD — runs on main thread, might cause ANR
queries.selectAll().asFlow().mapToList()

// GOOD — runs database queries on IO thread
queries.selectAll().asFlow().mapToList(Dispatchers.IO)

Mistake 4: Wrong Migration File Names

# WRONG — migration won't be found
migrations/v1_to_v2.sqm
migrations/migration_1.sqm

# RIGHT — must be just the version number
migrations/1.sqm    ← Migrates FROM version 1
migrations/2.sqm    ← Migrates FROM version 2

Mistake 5: Changing .sq Without Migration

-- If you add a column to the .sq file:
CREATE TABLE notes (
    ...
    new_column TEXT NOT NULL  -- Added this
);

-- You MUST also create a migration file:
-- migrations/N.sqm
ALTER TABLE notes ADD COLUMN new_column TEXT NOT NULL DEFAULT '';

Without the migration file, existing users lose all their data.

Quick Reference

Concept	SQLDelight	Room Equivalent
Table definition	`.sq` file with `CREATE TABLE`	`@Entity` data class
Queries	Named queries in `.sq`	`@Query` in `@Dao` interface
Auto-generated code	Kotlin from SQL	SQL from Kotlin
Reactive queries	`.asFlow().mapToList()`	`Flow<List<T>>` return type
Migrations	`.sqm` files with SQL	`Migration` classes
Transactions	`queries.transaction { }`	`@Transaction` annotation
Platform driver	`expect/actual DriverFactory`	Built-in (Android only)

Source Code

The KMP tutorial project is on GitHub:

View source code on GitHub →

KMP Tutorial #6: Ktor Networking — combine Ktor (remote) + SQLDelight (local) for offline-first
KMP Tutorial #3: Project Structure — commonMain where SQLDelight code lives
Compose Tutorial #13: Room — Android-only alternative to SQLDelight

What’s Next?

In the next tutorial, we will learn about DataStore — multiplatform key-value storage for settings and preferences. Unlike SQLDelight (which stores structured data), DataStore is for simple settings like “dark mode on/off” and “last selected tab.”

See you there.

SQLDelight vs Room — Which for KMP?#

Setup#

Dependencies#

Gradle Configuration#

Create the Driver (expect/actual)#

Step 1: Define Your Tables with .sq Files#

What SQLDelight Generates#

Step 2: Create the Repository#

How Flow Works with SQLDelight#

Step 3: Use in ViewModel#

Migrations — Evolving Your Schema#

How Versioning Works#

Creating a Migration#

Migration with Data Transformation#

Verifying Migrations#

Migration Best Practices#

Transactions#

Full CRUD Example#

Common Mistakes#

Mistake 1: Forgetting the .sq File Location#

Mistake 2: Not Using Flow for UI#

Mistake 3: Forgetting Dispatchers.IO#

Mistake 4: Wrong Migration File Names#

Mistake 5: Changing .sq Without Migration#

Quick Reference#

Source Code#

Related Tutorials#

What’s Next?#