sqldelight-patterns

SQLDelight Patterns for KMP

Gradle Plugin Configuration

// build.gradle.kts (shared module)
plugins {
    id("app.cash.sqldelight") version "2.3.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.3.2")
            implementation("app.cash.sqldelight:primitive-adapters:2.3.2")
        }
        androidMain.dependencies {
            implementation("app.cash.sqldelight:android-driver:2.3.2")
        }
        iosMain.dependencies {
            implementation("app.cash.sqldelight:native-driver:2.3.2")
        }
    }
}

.sq File Syntax

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

Table Definitions

-- User.sq
import com.example.db.UserStatus;
import kotlin.time.Instant;

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 AS UserStatus NOT NULL DEFAULT 'active',
    created_at INTEGER AS Instant 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 = ?;