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. Those are plain SQL, and they generate a typed Kotlin API that runs 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 rather than 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. That is stale generated sources rather than a Kotlin error in your code, so re-typing the query differently won't fix it.