Room Database and Paging

Room is a persistence layer on top of SQLite that removes the two biggest sources of database bugs: hand-written SQL that only fails at run time, and boilerplate that maps Cursor rows to objects by hand. Room verifies every @Query against the schema at compile time — a typo in a column name or a query that does not match its return type fails the build, not a user’s device. It generates the mapping code itself, and it integrates directly with coroutines and Flow so a query can be observed reactively instead of polled.

In TrailMate, Room is the layer OfflineFirstTrailRepository treats as the single source of truth: the UI never reads the network response directly, it reads Room, and a background refresh (a periodic sync worker, a pull-to-refresh, or a RemoteMediator, covered later on this page) is the only thing allowed to write into it.

Room’s annotation processor runs through KSP (Kotlin Symbol Processing), not kapt; KSP analyzes Kotlin symbols directly instead of going through a Java stub, which builds noticeably faster. The Room Gradle plugin adds one more piece: it copies each version of the generated JSON schema into a folder in the module (schemaDirectory), which both documents every schema change over the app’s lifetime and is required for testing migrations.

See Android Developers — Save data in a local database using Room.

See Android Developers — Save data in a local database using Room and Android Developers — Migrate to KSP from kapt.

KSP 2.3.12 is a KSP2 release, versioned independently of Kotlin (the old <kotlin>-<ksp> scheme such as 2.2.20-2.0.4 is gone), and because AGP 9 compiles Kotlin itself the module applies no org.jetbrains.kotlin.android plugin — the same baseline as Project Structure, Gradle and Build Variants.

The old alternative — adding room-compiler to kapt { } and passing room.schemaLocation as an annotation-processor argument — still works but is legacy; new modules should use the Room Gradle plugin and KSP as shown above.

An @Entity class maps one Kotlin class to one SQLite table. TrailMate stores two entities: TrailEntity for the trail catalog and PhotoEntity for the user’s photos of each trail.

See Android Developers — Define data using Room entities.

See Android Developers — Define data using Room entities.

@ColumnInfo(name = …​) decouples the Kotlin property name from the SQL column name (snake_case in the table, camelCase in Kotlin, a common convention). defaultValue on a column matters for auto-migrations: it lets Room add the column to existing rows without a manual migration, covered below.

An index speeds up lookups and joins on a column at the cost of extra storage and slightly slower writes. Add one with indices on @Entity (as above) whenever a column is filtered or joined on often: region and difficulty because TrailListScreen filters by both, and trail_id on photos because every relation query below joins on it and because it backs the foreign key’s cascading delete. A column that is only ever read as part of a full table scan, such as name in a screen with no search field, does not need one — indices are not free, so add them where a query plan shows they help rather than on every column defensively.

SQLite has no native enum or date column, so TrailEntity.difficulty (Difficulty, a Kotlin enum) needs a TypeConverter that tells Room how to store and read it back.

See Android Developers — Referencing complex data using Room.

The converter class is registered once, on the database (@TypeConverters(Converters::class), shown below), and applies to every entity and DAO query in it. Keep converters pure and cheap — they run on every row of every query that touches that column, on whatever thread the query runs on.

A trail and its photos are stored in two tables but are almost always read together. @Relation tells Room to run a second query and assemble the result into a single Kotlin object, so the repository never has to join manually.

See Android Developers — Choose relationship types between objects.

@Embedded flattens TrailEntity’s own columns directly into the result (no `trail_ prefix needed here because the outer holder class is not itself a table); @Relation tells Room which parent and child column join the two tables. Room issues one query for the parent rows and one follow-up query per relation, not a SQL JOIN, which avoids duplicating trail columns once per photo. The same @Embedded annotation is also used the other way, to group a handful of columns of one entity into a nested value class (a Coordinates(lat: Double, lon: Double) field embedded inside TrailEntity, for example) without a separate table.

A @Dao interface declares the queries Room generates an implementation for. Returning Flow<T> from a query makes it reactive: Room re-runs the query and re-emits automatically whenever a write touches a table the query reads from, so TrailListViewModel never needs to refresh anything manually after an insert.

See Android Developers — Accessing data using Room DAOs.

Three things worth calling out:

See Android Developers — Accessing data using Room DAOs and Android Developers — @Upsert.

@Database ties the entities, the DAOs and the type converters together and is the class the rest of the app depends on.

See Android Developers — Save data in a local database using Room.

abstract fun trailDao(): TrailDao is all a DAO accessor needs — Room implements it. exportSchema = true (the default) is what makes the Room Gradle plugin write a JSON snapshot of the schema into schemaDirectory on every build; that file is what both auto-migrations and MigrationTestHelper validate against, so it must be checked into version control alongside the entity change that produced it.

Every schema change bumps version on @Database and needs a migration path, or Room throws IllegalStateException the first time the app opens an updated database it does not know how to read.

An app that ships with a starter dataset — TrailMate could ship a handful of well-known trails so the list is not empty on first launch, offline, before the first sync — can prepopulate the database from a bundled asset instead of inserting rows in code on first run.

See Android Developers — Prepopulate your Room database.

The asset is a real SQLite file matching version 1’s schema exactly (built once, offline, from the same entities); Room copies it in on first open and then runs the usual migrations up to the current version. createFromFile and createFromInputStream cover the same idea for a file outside assets or a stream produced at run time (for example downloaded once and cached).

Exactly one RoomDatabase instance per file should exist for the life of the process — creating a second one against the same file wastes memory and risks lock contention. Hilt enforces this by scoping the provider to SingletonComponent.

See Android Developers — Dependency injection with Hilt.

provideTrailDao is intentionally un-scoped: it is cheap to call and Hilt already guarantees the TrailMateDatabase it reads from is a singleton, so every caller ends up sharing the same DAO backed by the same connection pool regardless.

Every Room query that touches disk must run off the main thread. Suspend DAO functions and Flow-returning queries are main-safe automatically — Room dispatches them to its own internal executor — which is why every DAO method above is either suspend or returns Flow. Room additionally throws at run time if a non-suspend, non-Flow query is called from the main thread, unless allowMainThreadQueries() is explicitly opted into on the builder. The check is not limited to debug builds — a release build throws the same IllegalStateException — and allowMainThreadQueries() is a test-only escape hatch that should never ship. This mirrors the dispatcher-injection discipline covered in ViewModel, Lifecycle-Aware Components, Coroutines and Flow — a repository built on Room-only DAOs needs no explicit withContext(Dispatchers.IO) around a query, because Room already guarantees it never blocks the caller’s thread.

For a column that needs substring or token search rather than an exact match — searching trail names and descriptions by keyword, for example — Room can back a table with SQLite’s FTS4 virtual table module instead of an ordinary table.

See Android Developers — Define data using Room entities.

contentEntity keeps the FTS index in sync with TrailEntity automatically instead of duplicating the data by hand. FTS is worth reaching for only once a plain LIKE '%query%' query becomes a measured bottleneck or the search needs token-aware matching (prefix search, ranking); for a short list like TrailMate’s own catalog a plain indexed LIKE query is usually enough.

Room 3.0 (stable since mid-2026) ships as a new Maven group and package, androidx.room3, side by side with the 2.x androidx.room artifacts rather than as an in-place upgrade — so migrating is a deliberate, versioned step, not a patch update. The changes that matter when planning that migration:

Because the two live in different Maven groups, a large codebase can migrate module by module rather than in one sweep; TrailMate’s :core:database module is a natural first candidate precisely because Paging’s RemoteMediator and DAOs already isolate every other module from Room-specific types behind the repository interface. See Android Developers — Room 3.0 release notes.

See also Local Storage: SQLDelight, Room, SQLite Drivers, DataStore and Settings in the Kotlin Multiplatform section for Room 3 in shared code.

A DAO test does not need a real file on disk or an emulator’s SQLite build — an in-memory database is faster to create and tear down, and disappears automatically at the end of the test.

See Android Developers — Test your Room database.

inMemoryDatabaseBuilder (rather than databaseBuilder) is the only difference from production setup; allowMainThreadQueries() is acceptable here because the test itself controls the thread the query runs on and values immediate, synchronous failures over the small realism gap. This test needs an Android runtime (a device, emulator, or Robolectric) because Room delegates to the platform’s SQLite implementation; a pure JVM unit test without one of those cannot open a Room database at all. See Android Developers — Test your Room database.

Loading TrailMate’s entire trail catalog into memory at once does not scale once the catalog grows past a few hundred trails, and re-querying "give me the next 20" by hand means hand-rolling offset tracking, duplicate-row edge cases and loading-state bookkeeping. Paging 3 does that work once, correctly, and integrates with both Compose and RecyclerView.

The figure below shows how the pieces on this page fit into OfflineFirstTrailRepository: Room’s entities and DAOs (including the RemoteKeyEntity/RemoteKeyDao pair a RemoteMediator needs), the Pager built on top of them, and the two ways PagingData is consumed depending on whether the screen is built with Compose or Views.