Sqldelight Example for Kotlin Multiplatform

A useful Sqldelight example should show more than a single SELECT statement. The real workflow connects a schema file, labeled operations, generated Kotlin APIs, a platform driver and a repository that keeps database details away from the user interface. This guide uses a small notes feature to explain those boundaries without adding framework-specific code.

Define a small SQL schema

Start with a table that has a stable primary key, required text and a simple stored flag. Keep names explicit because generated Kotlin properties are based on the SQL result shape.

Note.sq

CREATE TABLE note (
  id INTEGER NOT NULL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  pinned INTEGER NOT NULL DEFAULT 0
);

selectAll:
SELECT id, title, body, pinned
FROM note
ORDER BY pinned DESC, title ASC;

Label every application operation

A label becomes the generated function name, so use names that describe intent rather than SQL syntax. selectAll, selectById, insertNote and deleteById are easy to understand in repository code.

Keep result projections deliberate

Select only the columns needed by the caller. This makes generated result types easier to understand and protects the UI from accidental schema coupling. A dedicated summary query can return fewer columns than a detail query.

Create the driver outside shared business logic

In a Kotlin Multiplatform project, shared code can own the generated database interface and repository behavior, while Android, iOS, desktop or web code creates the actual driver. This prevents file paths and platform lifecycle rules from leaking into common code.

Build a repository around generated calls

Illustrative Kotlin repository

class NoteRepository(
  private val database: AppDatabase
) {
  fun allNotes() = database.noteQueries
    .selectAll()
    .executeAsList()

  fun save(id: Long, title: String, body: String) {
    database.noteQueries.insertNote(id, title, body)
  }
}

The generated API already carries parameter and result types. The repository should add application rules, transaction boundaries and conversions that are meaningful to the product rather than duplicating generated mapping.

Test the complete flow

Create a temporary database with the target driver.
Insert several rows and verify ordering and null behavior.
Exercise updates and deletes inside transactions.
Open an older schema and apply migrations in sequence.
Run at least one test on each platform-specific driver.

What this example demonstrates

Sqldelight keeps the SQL reviewable while generated Kotlin APIs reduce manual parsing. The strongest design keeps schema, query intent and migration history in SQL, then places platform driver creation and product-specific repository rules in separate layers.