Notes

One Database on Four Platforms: Room in Kotlin Multiplatform

18 min read

The previous post gave the reading list a memory: every change wrote the whole list, as one JSON string, into each platform’s key-value store. That works for three books. It does not scale, and it leaves the app’s rules entirely in Kotlin, with nothing underneath to enforce them and nothing to tell the screen that the data changed.

This post replaces it with a real database, Room, the same library most Android apps already use, now running on Android, iOS, the desktop and the web from one commonMain definition. Room 3, stable since July 2026, added the browser as a target, and the browser turned out to be where most of the work was: a web worker, a file system the user never sees, a lock between tabs, and a migration bug found by testing it there. The post also measures what the database costs to ship and how fast the same queries run on each platform.

1. What Moves into commonMain

Everything that describes the data is common code: the table, the queries and the database.

// commonMain
@Serializable
@Entity(indices = [Index(value = ["title"], unique = true)])
data class Book(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    @ColumnInfo(collate = ColumnInfo.NOCASE) val title: String,
    val author: String,
    val read: Boolean = false,
)
 
@Dao
interface BookDao {
    @Query("SELECT * FROM Book ORDER BY id")
    fun observeAll(): Flow<List<Book>>
 
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(book: Book): Long          // -1 when the title already exists
 
    @Query("UPDATE Book SET read = NOT read WHERE id = :id")
    suspend fun toggle(id: Int)
 
    @Query("DELETE FROM Book WHERE id = :id")
    suspend fun delete(id: Int)
    // … insertAll, count, and two queries for a small Setting table (Section 5)
}
 
@Database(entities = [Book::class, Setting::class], version = 1)
@ConstructedBy(BookDatabaseConstructor::class)
abstract class BookDatabase : RoomDatabase() {
    abstract fun books(): BookDao
}
 
@Suppress("KotlinNoActualForExpect")
expect object BookDatabaseConstructor : RoomDatabaseConstructor<BookDatabase> {
    override fun initialize(): BookDatabase
}

Three details are worth a closer look.

  • One rule moved into the schema. In version 2, add() scanned the list for a title that already existed, ignoring case. Now the table has a unique index on title with COLLATE NOCASE, so the database refuses the duplicate itself, and insert reports it by returning −1. Room writes the schema it generated to schemas/…/1.json, which belongs in version control:

    CREATE TABLE `Book` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT NOT NULL COLLATE NOCASE,
                     `author` TEXT NOT NULL, `read` INTEGER NOT NULL)
CREATE UNIQUE INDEX `index_Book_title` ON `Book` (`title`)

  • Every query is suspend or a Flow. Room 3 no longer offers blocking queries. On Android that was already good practice; in the browser it is the only option, because there the database lives in another thread and every answer arrives asynchronously.

  • One expect without an actual. BookDatabaseConstructor is the generated declaration from the first post’s list of Now in Android’s 14 expects: Room’s KSP processor writes the actual for each target at build time. It is also an expect object, which the previous post found is Beta and warns on every target. A Room project therefore needs the flag the warning names:

    kotlin {
    compilerOptions { freeCompilerArgs.add("-Xexpect-actual-classes") }
}

The Gradle side adds two plugins (KSP and Room’s own) and runs Room’s processor once per target:

dependencies {
    for (target in listOf("Android", "Jvm", "WasmJs", "IosArm64", "IosSimulatorArm64")) add("ksp$target", libs.room3.compiler)
}
room3 { schemaDirectory("$projectDir/schemas") }

2. One API, Four Drivers

Room describes the database; a driver runs it. The only platform-specific code is choosing that driver and a file name, in each entry point, the same pattern as the key-value store in the previous post:

commonMain: Room@Entity Book · @Dao BookDao · @Database BookDatabaseobserveAll(): Flow<List<Book>> · suspend insert(book)KSP generates the implementation for each targetAndroidAndroidSQLiteDriverAndroid's own SQLite3.9 (API 24) to 3.44 (API 35)file: app's database folderdesktop JVMBundledSQLiteDriverSQLite built into the app3.50.1file: ~/.reading-listiOSBundledSQLiteDriverSQLite built into the appsame build as desktopfile: the app's Documentsweb (Wasm)WebWorkerSQLiteDriverSQLite's WebAssembly build,3.53.4, in a web workerfile: the browser's OPFSThe entity, the queries and the rules are written once. Each entry point only chooses a driver and a file name; the SQLite underneath differs.
// androidMain
fun openDatabase(context: Context): BookDatabase {
    val app = context.applicationContext
    return Room.databaseBuilder<BookDatabase>(app, app.getDatabasePath("reading-list.db").absolutePath)
        .setDriver(AndroidSQLiteDriver())
        .setQueryCoroutineContext(Dispatchers.IO)
        .build()
}
 
// jvmMain (iosMain is the same with the app's Documents folder)
fun openDatabase(file: File): BookDatabase =
    Room.databaseBuilder<BookDatabase>(file.absolutePath)
        .setDriver(BundledSQLiteDriver())
        .setQueryCoroutineContext(Dispatchers.IO)
        .build()

The interesting choice is which SQLite each platform runs. BundledSQLiteDriver compiles SQLite into the app, so every platform runs the same version: here 3.50.1 on the desktop. On Android there is a second option, AndroidSQLiteDriver, which uses the SQLite that every Android device already contains. The catch is that Android’s copy depends on the Android version: SQLite 3.9 on API 24, this app’s minimum, up to 3.44 on API 35. SQLite 3.9 is from 2015 and lacks, for example, RETURNING (3.35), window functions (3.25) and UPSERT (3.24).

What the bundled copy costs on Android was measured (Section 6): it adds SQLite compiled for each of four CPU types, about 1.1–1.3 MB each, and turns a 1.29 MB APK into a 6.19 MB one. This app’s schema needs nothing newer than SQLite 3.9, so it uses Android’s own SQLite on Android and the bundled one on the desktop and iOS, where there is no such trade-off to make (iOS would offer NativeSQLiteDriver for the system SQLite; it was not measured here). An app that relies on newer SQL, or on full-text search with FTS5, should bundle.

3. A List That Updates Itself

In version 2 the screen held the list in memory and the store was written after each change. Now the database is the only copy, and the screen observes it:

// commonMain: the rules, on top of the DAO
class BookRepository(private val db: BookDatabase, private val store: KeyValueStore) {
    private val dao = db.books()
    val books: Flow<List<Book>> = dao.observeAll()
 
    suspend fun add(title: String, author: String): Boolean {
        val t = title.trim()
        if (t.isEmpty()) return false
        return dao.insert(Book(title = t, author = author.trim().ifEmpty { "Unknown" })) != -1L
    }
    suspend fun toggle(id: Int) = dao.toggle(id)
    suspend fun remove(id: Int) = dao.delete(id)
}
 
// commonMain, App(): the UI shows whatever the database says, whenever it changes
val books by remember(repo) { repo.books.catch { failure = it.message; emit(emptyList()) } }
    .collectAsState(initial = null)                       // null until the database has answered
Checkbox(checked = book.read, onCheckedChange = { scope.launch { repo.toggle(book.id) } })

observeAll() re-runs its query whenever the Book table changes, so a tap on a checkbox writes one row and the list on screen updates from the new query result. No code copies data into the UI by hand. The catch matters too: if the database cannot be opened, the screen shows a message instead of an uncaught exception.

The same six repository tests from commonTest ran on the desktop JVM against BundledSQLiteDriver, and in headless Chrome as Kotlin/Wasm against the web worker described next, each with an in-memory database. They did not run on Android. Host-side Android tests run on the Mac’s JVM, where AndroidSQLiteDriver has no Android to call and the Android build of the bundled SQLite cannot load; Room’s Android tests need a device or an emulator. (Update: the next post set up an Android 16 emulator, and the same tests, run there as instrumented tests with AndroidSQLiteDriver, pass. Android’s SQLite on that image reports version 3.44.3.)

One test failed on its first run, and it was the test that was wrong. It expected the book added after a rejected duplicate to get id 4, and it got 5: with AUTOINCREMENT, the insert that OnConflictStrategy.IGNORE silently dropped had already used up an id. Version 2’s in-memory counter never skipped a number. Nothing in the app depends on ids being consecutive, so the test now checks only that the new id is larger.

4. The Browser: a Worker, a Hidden File System, and One Tab at a Time

On Android, iOS and the desktop, SQLite reads and writes an ordinary file. A web page has no ordinary files. Room’s web driver, WebWorkerSQLiteDriver, runs SQLite compiled to WebAssembly inside a web worker, and the worker keeps the database in the Origin Private File System (OPFS), a file system each site gets in the browser, invisible to the user and to other sites.

tab 1 (the page)Compose UI + RoomWebWorkerSQLiteDriverholds the Web Lock "reading-list-database"web worker (worker.js)SQLite, compiled to Wasmopfs-sahpool VFSno SharedArrayBuffer, no COOP/COEPOPFSprivate to this site,not visible to the user/.reading-list/.opaque/…a pool of files, openedexclusively by one workerprepare, steprowstab 2asks for the same lock with ifAvailable: refused, so it opens an in-memory database and says so on screen:"The saved list is open in another tab. Changes made in this tab will not be kept."Every statement is a message to the worker and back, so the browser is slower per query than a JVM; batch writes into one transaction.

The driver does not ship the worker: it defines a small message protocol (open, prepare, step, close) and expects the app to provide a worker that speaks it. The androidx repository contains a reference worker, and it does not work on this blog. It opens databases with SQLite’s "opfs" storage, which needs JavaScript’s SharedArrayBuffer, which browsers only enable on pages sent with two HTTP headers (COOP and COEP). A static host such as GitHub Pages cannot send them.

So the app has its own worker, 66 lines of JavaScript that speak the same protocol but use SQLite’s "opfs-sahpool" storage, which needs neither header and is also SQLite’s fastest OPFS option. It ships as a local npm package that webpack bundles, together with SQLite’s WebAssembly build, from one line of Kotlin:

// wasmJsMain: webpack sees the literal new Worker(new URL(…)) and bundles the worker as its own file
private fun sqliteWorker(): Worker =
    js("""new Worker(new URL("reading-list-sqlite-worker/worker.js", import.meta.url))""")

opfs-sahpool has one restriction: its files can be open in one tab at a time. A test that opened the app in two tabs showed what that means without care. The second tab could not get the files, quietly fell back to an empty in-memory database, and displayed the sample list. Nothing crashed, which was worse: the user’s books seemed to be gone, and anything changed in that tab would be lost on close.

The app now settles it before opening anything, with the browser’s Web Locks API. The first tab takes a lock and holds it until the page closes. A later tab asks with ifAvailable, is refused, opens an in-memory database and says so:

suspend fun openDatabase(): Pair<BookDatabase, String?> {
    val driver = WebWorkerSQLiteDriver(sqliteWorker())
    return when (claimDatabase().await<JsString>().toString()) {      // a Web Lock, held until the page closes
        "owner" -> Room.databaseBuilder<BookDatabase>("reading-list.db").setDriver(driver).build() to null
        "busy" -> Room.inMemoryDatabaseBuilder<BookDatabase>().setDriver(driver).build() to
            "The saved list is open in another tab. Changes made in this tab will not be kept."
        else -> Room.inMemoryDatabaseBuilder<BookDatabase>().setDriver(driver).build() to
            "This browser cannot store the list. Changes will be lost when the page closes."
    }
}

This is the previous post’s rule applied to storage: a capability the platform does not have right now (here, the saved file in this tab) is modelled and shown, not hidden behind a fallback that pretends.

One build problem is worth knowing about if you write your own worker. Gradle has yarn copy a local npm package into build/wasm/node_modules once, and afterwards neither yarn nor webpack notices edits to it: for a while, the app shipped an old worker while the source said otherwise. The project’s build files now make both tasks depend on the worker’s folder and force a fresh copy.

5. Moving the Data, Once

People who used version 2 have their list in the key-value store. Version 3 moves it on first start, or starts with the sample if there is nothing to move. The first version of this code kept an “imported” flag in the key-value store, next to the old data. The browser test above found the problem with that: with the key-value store cleared and the database kept (which a browser can do, and which a test did), the next start imported a second time, on top of the existing rows.

The flag and the data must not be able to disagree, so the flag now lives in the database, in a one-row Setting table, written in the same transaction as the books:

suspend fun initialize() = db.withWriteTransaction {
    if (dao.setting(INITIALIZED) != null) return@withWriteTransaction
    val saved = store.get(LEGACY_KEY)?.let { runCatching { Json.decodeFromString<List<Book>>(it) }.getOrNull() }
    dao.insertAll(saved ?: SAMPLE)
    dao.putSetting(Setting(INITIALIZED, "1"))
}

Either the books and the flag are both there, or neither is: an interrupted first start simply runs again. A regression test, losingTheKeyValueStoreDoesNotImportAgain, reproduces the scenario and passes on both test targets. In the browser, the full path was checked end to end: a list stored by version 2 in localStorage was imported on first start, a removal and a tick were made, localStorage was then cleared entirely, and after a reload the list came back unchanged from SQLite in OPFS.

6. What It Costs, and How Fast It Runs

All sizes are release builds of the same app, before (version 2) and after Room:

BuildVersion 2Version 3 (Room)
Android APK, AndroidSQLiteDriver (the app)1.24 MB1.29 MB
Android APK, BundledSQLiteDriver, universal6.19 MB
Android APK, BundledSQLiteDriver, arm64 only2.53 MB
Web, downloaded (gzip)4.55 MB5.06 MB
Desktop uber JAR45.6 MB50.1 MB

On Android, Room itself is small: the app’s code (classes.dex) grew from 1.99 MB to 2.08 MB, or from 1.02 MB to 1.07 MB as compressed inside the APK. What is large is SQLite, when the app brings its own. On the web, the browser now downloads SQLite’s WebAssembly build (0.40 MB compressed) and the worker (0.06 MB); Skia, the largest file, is unchanged. The desktop JAR carries SQLite for four operating systems and CPUs and loads one.

Speed was measured with a timing test in commonTest: the same work on a fresh in-memory database, five rounds after one warm-up, the median reported, the whole test run three times.

WorkDesktop JVM (bundled SQLite 3.50.1)Browser (SQLite Wasm 3.53.4 in a worker)
200 inserts, one transaction each39 ms (0.20 ms each)229 ms (1.1 ms each)
1,000 inserts in one transaction2.2 ms35 ms
50 reads of 1,200 rows54 ms208 ms

The browser is 4 to 16 times slower, because every statement is a message to the worker and back, and SQLite runs as WebAssembly. For this app it does not matter: one insert takes about a millisecond, far below what a tap can notice. It matters for bulk work. A thousand separate inserts take about a second in the browser, against 35 ms in one transaction, so imports and syncs should write in transactions, as initialize() does. These are in-memory numbers; with OPFS, writes also go to storage, so they bound the cost from below. The JVM’s timings also varied more between runs than the browser’s: across six runs on this machine, the 200 inserts took 20 to 41 ms on the JVM and 218 to 257 ms in the browser.

7. The Reading List, Version 3

The app below is the chapter’s latest version (since the next post it also has a screen per book; the code and sizes here are those of version 3, in the download below). Your list from the earlier posts’ embedded app, if you edited it, was imported on first start. Changes survive a reload; open this page in a second tab to see the warning.

TargetLines it compilesOf which in commonMain
Android (with androidApp)17378.0%
iOS15786.0%
desktop16283.3%
web178 (+ 66 lines of JavaScript in the worker)75.8%

commonMain grew from 93 to 135 lines: the entity, the DAO, the database and the repository. Each platform added one function to open the database; the web added more, the lock and the worker, because that is where the platform differs most.

To build it, download the project (reading-list-3.zip, 73 KB), unzip it, and with JDK 21 and the Android SDK:

cd reading-list-3
./gradlew :shared:jvmTest                                  # the repository tests, desktop JVM (bundled SQLite)
CHROME_BIN="/path/to/chrome" ./gradlew :shared:wasmJsBrowserTest   # … and in Chrome, through the web worker
./gradlew :androidApp:assembleRelease                      # Android APK (Android's SQLite)
./gradlew :shared:run                                      # desktop window; the database is ~/.reading-list/reading-list.db
./gradlew :shared:wasmJsBrowserDistribution                # the browser build in shared/build/dist/wasmJs
./gradlew -Pkmp.ios=true :shared:compileKotlinIosSimulatorArm64    # type-check iOS, including Room's generated code

8. Common Claims, Checked

ClaimVerdict
”Room is Android-only.”Room 2.7 added Android, iOS and JVM; Room 3, stable since July 2026, adds JavaScript and Wasm. The whole database definition here is commonMain.
”Room in KMP means writing expect/actual.”One expect object, and Room’s KSP processor generates every actual. It does need -Xexpect-actual-classes, because expect/actual objects are Beta.
”Bundle SQLite everywhere for consistency.”Consistent, yes, and it cost 4.9 MB in the universal APK (1.2 MB per device with an app bundle). With a schema that needs only SQLite 3.9, Android’s own SQLite costs nothing.
”SQLite in the browser needs special server headers.”Only SQLite’s "opfs" storage (SharedArrayBuffer, COOP/COEP). "opfs-sahpool" needs none, and works on GitHub Pages; it allows one tab at a time.
”If the web build compiles, the web version works.”The second tab quietly lost the user’s list, and a stale worker was shipped from Gradle’s npm cache. Both were found only by running the app in a browser.
”A migration flag is a detail.”Kept in a different store from the data, it re-imported the list when that store was cleared. In the same database and transaction, the two cannot disagree.
”The browser is too slow for a real database.”1.1 ms per insert and 4 ms per read of 1,200 rows, measured: fine for an app, if bulk writes use transactions (35 ms for 1,000 rows instead of about a second).
”Autogenerated ids are consecutive.”With AUTOINCREMENT, an insert that IGNORE dropped still used up an id.

Summary

IdeaIn one line
Room in KMPEntity, DAO and database in commonMain; KSP generates the code for each target
DriversAndroid’s own SQLite on Android (3.9 to 3.44), bundled SQLite on desktop and iOS (3.50.1), SQLite Wasm in a worker on the web (3.53.4)
Reactive dataA Flow query; the UI collects it, and every write shows up without copying
The browserOPFS through opfs-sahpool, no special headers, one tab at a time, decided by a Web Lock and shown to the user
MigrationThe “done” marker in the same database and transaction as the data
Cost1.29 MB APK (6.19 MB if SQLite is bundled), 5.06 MB web download; the browser 4–16× slower, still about 1 ms per insert

Next: State That Survives: ViewModels and Navigation 3 in Kotlin Multiplatform: a second screen, a shared back stack and ViewModels, the browser’s history, and five ways to hold an unsaved note, each tested against a rotation, process death and a reload.

The app: reading-list-3 (zip) (Kotlin 2.4.21, Compose Multiplatform 1.12.2, Room 3.0.3, androidx.sqlite 2.7.1, KSP 2.3.12, SQLite Wasm 3.53.4, AGP 9.4.1, Gradle 9.8.1). The measurements: measure_room.py, which builds both versions and the two bundled-SQLite variants and runs the timing test three times. Everything ran on this machine (macOS, Apple silicon, JDK 21, Chrome 155, no Xcode, no Android emulator): the iOS code was compiled but not linked or run, and the Android app was built but its database was not run on a device.

References

Room and SQLite drivers

SQLite in the browser

Tooling

Graph View

Backlinks