sqldelight-patterns

SQLDelight Patterns for KMP

Gradle Plugin Configuration

// build.gradle.kts (shared module)
plugins {
    id("app.cash.sqldelight") version "2.0.2"
}

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

// Dependencies
kotlin {
    sourceSets {
        commonMain.dependencies {
            implementation("app.cash.sqldelight:coroutines-extensions:2.0.2")
            implementation("app.cash.sqldelight:primitive-adapters:2.0.2")
        }
        androidMain.dependencies {
            implementation("app.cash.sqldelight:android-driver:2.0.2")
        }
        iosMain.dependencies {
            implementation("app.cash.sqldelight:native-driver:2.0.2")
        }
    }
}

.sq File Syntax

Place .sq files in src/commonMain/sqldelight/com/example/db/.

Table Definitions

-- User.sq

CREATE TABLE User (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    display_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    avatar_url TEXT,
    status TEXT NOT NULL DEFAULT 'active',
    created_at INTEGER NOT NULL
);

CREATE INDEX idx_user_email ON User(email);
CREATE INDEX idx_user_created_at ON User(created_at);

Named Queries

-- User.sq (continued)

selectAll:
SELECT * FROM User ORDER BY created_at DESC;

selectById:
SELECT * FROM User WHERE id = ?;

selectByEmail:
SELECT * FROM User WHERE email = ?;

selectByStatus:
SELECT * FROM User WHERE status = :status;

insert:
INSERT OR REPLACE INTO User(display_name, email, avatar_url, status, created_at)
VALUES (?, ?, ?, ?, ?);

updateDisplayName:
UPDATE User SET display_name = ? WHERE id = ?;

deleteById:
DELETE FROM User WHERE id = ?;

deleteAll:
DELETE FROM User;

countByStatus:
SELECT COUNT(*) FROM User WHERE status = ?;

Parameterized Queries with Joins

-- Post.sq

CREATE TABLE Post (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER NOT NULL REFERENCES User(id),
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    published_at INTEGER
);

selectWithAuthor:
SELECT Post.*, User.display_name AS author_name
FROM Post
INNER JOIN User ON Post.author_id = User.id
WHERE Post.id = ?;

selectByAuthorId:
SELECT * FROM Post WHERE author_id = ? ORDER BY published_at DESC;