SQLDelight: Typed SQL Shared Across Platforms

Local persistence has the same duplication problem networking does: Room on Android, Core Data or GRDB on iOS, two schemas and two query layers describing the same tables. SQLDelight replaces both with .sq files — plain SQL that generates a typed Kotlin API, run against a platform-specific SQLite driver.

#The old way

The orders table existed twice: once as a Room @Entity with DAO methods, once as a Core Data model with NSFetchRequest boilerplate. A new indexed column meant writing (and testing) two migrations that were supposed to produce the same schema.

#The new way

An Order.sq file in commonMain/sqldelight declares CREATE TABLE order (...) and named queries like selectAll and insertOrder; SQLDelight's Gradle plugin generates an OrderDatabase class with a typed orderQueries property from it.

expect class DriverFactory {
    fun createDriver(): SqlDriver
}

class OrderRepository(driverFactory: DriverFactory) {
    private val queries = OrderDatabase(driverFactory.createDriver()).orderQueries

    fun observeOrders(): Flow<List<Order>> =
        queries.selectAll().asFlow().mapToList(Dispatchers.Default)

    fun insertOrder(id: String, status: String, totalCents: Long) {
        queries.insertOrder(id, status, totalCents)
    }
}

actual class DriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver =
        AndroidSqliteDriver(OrderDatabase.Schema, context, "orders.db")
}

actual class DriverFactory {
    actual fun createDriver(): SqlDriver =
        NativeSqliteDriver(OrderDatabase.Schema, "orders.db")
}

OrderRepository, the query names, and the generated Order row type are identical on both platforms — only driver construction differs.

#Why it matters

• Schema, migrations, and queries are written once and compile-checked against each other — a typo in a column name fails the build, not a runtime query
• Flow-backed queries via the coroutines extension plug directly into the same reactive patterns used for networking
• Migrations are just numbered .sqm files run through the same generated Schema, so both platforms migrate identically

#Gotcha

Generated query classes only regenerate when SQLDelight's Gradle task runs. Add a new query to a .sq file and the IDE will show "unresolved reference: orderQueries" or a missing method on queries until you trigger a Gradle sync or build — it's not a Kotlin error in your code, it's stale generated sources, and re-typing the query differently won't fix it.