Room Database Patterns for Real Apps
Practical patterns for Android Room — reactive Flow queries, relations, transactions, migrations, and testing — that hold up when the app grows past a toy project.
By Velloreth
Room is not an ORM in the Hibernate sense. It is a thin, compile-checked layer over SQLite: you write SQL (or use the convenience annotations), Room generates the implementation, and Kotlin coroutines or Flow handle the threading. The patterns below are the ones that keep working after the app has more than one screen and more than one table.
The single source of truth rule
The most important pattern is architectural, not syntactic: the database is the source of truth for UI state. The UI observes the database. The network layer writes into the database. Nothing reads from the network and renders directly.
Network ──writes──▶ Room ◀──observes── Repository ◀──collects── ViewModel ◀──State── Compose
When a sync finishes, it inserts or upserts rows. The Flow emitting from the DAO fires automatically, the ViewModel maps it, and Compose recomposes. There is no separate “cache invalidation” step because there is no separate cache — the Room table is the cache, and it happens to be persistent.
This collapses a whole class of bugs: stale lists after returning from a detail screen, double-fetches on rotation, and “why is my UI showing old data” threads on Stack Overflow. If the database is wrong, fix the write path; the read path takes care of itself.
Reactive queries with Flow
DAO methods that return Flow<T> are re-executed whenever the underlying tables change. Room tracks invalidation at the table level — any insert, update, or delete on a referenced table triggers a fresh query.
@Dao
interface NoteDao {
@Query("SELECT * FROM notes WHERE folderId = :folderId ORDER BY updatedAt DESC")
fun observeNotesInFolder(folderId: Long): Flow<List<NoteEntity>>
@Query("SELECT * FROM notes WHERE id = :id")
fun observeNote(id: Long): Flow<NoteEntity?>
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun upsert(note: NoteEntity)
@Query("DELETE FROM notes WHERE id = :id")
suspend fun delete(id: Long)
}
A few habits that make this predictable:
- Suspend for writes, Flow for reads. One-shot reads (
suspend fun get(id): NoteEntity?) are fine for one-off fetches, but anything the screen displays should be a Flow so updates propagate. - Observe exactly what you render. If the screen shows notes and their folder name, the query should join both tables. Room invalidates on either table, so the join stays fresh.
- Map in the ViewModel, not the DAO. Let the DAO return entities; map to UI models where you need to. This keeps the schema decoupled from the UI and makes both sides easier to change.
In Compose, collect with collectAsStateWithLifecycle() (from androidx.lifecycle:lifecycle-runtime-compose) so the collection stops when the app is backgrounded.
Relations: from flat tables to nested objects
Real data is relational. Room models this with @Embedded (flatten columns into the parent), @Relation (one-to-many or many-to-many), and @Transaction (required whenever you use @Relation, so the nested read is atomic).
data class FolderWithNotes(
@Embedded val folder: FolderEntity,
@Relation(parentColumn = "id", entityColumn = "folderId")
val notes: List<NoteEntity>
)
@Transaction
@Query("SELECT * FROM folders WHERE id = :id")
suspend fun getFolderWithNotes(id: Long): FolderWithNotes
For many-to-many, introduce a junction entity and use the entity parameter of @Relation:
@Entity(primaryKeys = ["noteId", "tagId"])
data class NoteTagCrossRef(val noteId: Long, val tagId: Long)
data class NoteWithTags(
@Embedded val note: NoteEntity,
@Relation(
parentColumn = "id",
entityColumn = "id",
associateBy = Junction(
NoteTagCrossRef::class,
parentColumn = "noteId",
entityColumn = "tagId"
)
)
val tags: List<TagEntity>
)
@Transaction on the query is not optional decoration. Without it, Room reads the parent and children as separate queries; a concurrent write between the two reads can produce an inconsistent snapshot. The annotation wraps them in a single SQLite transaction.
One caution: @Relation runs a second query per parent row. For a list of 100 folders each with notes, you get 101 queries. For bounded lists that is fine. For unbounded lists, use a JOIN query that returns a flat row and group in memory, or switch to Paging.
Transactions for multi-step writes
@Insert, @Update, and @Delete each run in their own transaction. When several writes must succeed or fail together, annotate a DAO function with @Transaction:
@Transaction
suspend fun archiveFolder(folderId: Long, noteIds: List<Long>) {
noteIds.forEach { id ->
updateNote(id, archived = true, folderId = null)
}
updateFolder(folderId, archived = true)
}
If any statement throws, Room rolls the whole thing back. You can also manage transactions manually on RoomDatabase (beginTransaction / setTransactionSuccessful / endTransaction) when the logic needs to live outside the DAO, at the cost of more boilerplate.
A subtle failure mode: suspending inside a transaction while doing network I/O. The transaction stays open, holding a SQLite write lock, and other writers stall. Do network calls before the transaction, then write the results in one atomic block.
Type converters and value objects
SQLite has a fixed set of types. When your Kotlin model uses richer types — Instant, LocalDate, enums, sealed values — map them with a TypeConverter:
class Converters {
@TypeConverter
fun fromInstant(value: Instant?): Long? = value?.toEpochMilli()
@TypeConverter
fun toInstant(value: Long?): Instant? = value?.let(Instant::ofEpochMilli)
@TypeConverter
fun fromStatus(value: String): NoteStatus = NoteStatus.valueOf(value)
@TypeConverter
fun toStatus(value: NoteStatus): String = value.name
}
@Database(entities = [NoteEntity::class, FolderEntity::class], version = 4)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
abstract fun noteDao(): NoteDao
}
Two rules of thumb. Store enums by name, not ordinal — reordering the enum silently corrupts every row if you store ordinals. And prefer ISO-8601 strings or epoch millis over formatted human strings; parsing a custom date format is a classic source of crashes after a locale change.
Migrations without data loss
Every schema change is a version bump and a Migration. The SQL is yours to write; Room validates the result at compile time in tests and at open time on device.
val MIGRATION_3_4 = object : Migration(3, 4) {
override fun migrate(db: SupportSQLiteDatabase) {
db.execSQL("ALTER TABLE notes ADD COLUMN archived INTEGER NOT NULL DEFAULT 0")
db.execSQL("ALTER TABLE notes ADD COLUMN folderId INTEGER")
db.execSQL(
"CREATE INDEX IF NOT EXISTS index_notes_folderId ON notes(folderId)"
)
}
}
Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
.addMigrations(MIGRATION_3_4)
.build()
fallbackToDestructiveMigration() is acceptable in development, and in a demo or sample app. In production it means a user who updates the app loses every local row. If you ship an app people rely on, write the migration.
Test migrations with MigrationTestHelper in an androidTest source set:
@RunWith(AndroidJUnit4::class)
class MigrationTests {
private val helper = MigrationTestHelper(
InstrumentationRegistry.getInstrumentation(),
AppDatabase::class.java
)
@Test
fun migrate3To4() {
helper.createDatabase("test", 3).apply {
execSQL("INSERT INTO notes (id, title, content, updatedAt) VALUES (1, 'a', 'b', 0)")
close()
}
helper.runMigrationsAndValidate("test", 4, true, MIGRATION_3_4)
}
}
This catches the two common migration bugs: wrong column types and forgotten NOT NULL constraints that make the new schema reject old rows.
Paging for large result sets
When the list can be long — chat messages, search results, a catalog — a Flow<List<T>> loads everything into memory. Room integrates with Paging 3 directly:
@Dao
interface SearchDao {
@Query("SELECT * FROM notes WHERE title LIKE '%' || :query || '%' ORDER BY updatedAt DESC")
fun search(query: String): PagingSource<Int, NoteEntity>
}
class NotesViewModel(private val dao: SearchDao) : ViewModel() {
val results = Pager(PagingConfig(pageSize = 30)) {
dao.search("")
}.flow.cachedIn(viewModelScope)
.collectAsLazyPagingItems()
}
cachedIn(viewModelScope) keeps pages alive across configuration changes. In Compose, LazyColumn renders LazyPagingItems directly. If the network is also a source, pair the PagingSource with a RemoteMediator that fetches pages into Room when the local cache runs dry — the database stays the source of truth, and paging works offline.
Testing: in-memory database, real DAO
The testing mistake that matters most: mocking the DAO. A mocked DAO tests that your fake behaves like your fake. The SQL — the thing most likely to be wrong — never runs.
Room’s own guidance is an in-memory database with the real generated DAO:
@RunWith(AndroidJUnit4::class)
class NoteDaoTest {
private lateinit var db: AppDatabase
private lateinit var dao: NoteDao
@Before
fun setup() {
db = Room.inMemoryDatabaseBuilder(
InstrumentationRegistry.getInstrumentation().context,
AppDatabase::class.java
).allowMainThreadQueries().build()
dao = db.noteDao()
}
@After
fun teardown() = db.close()
@Test
fun upsert_thenObserve_emitsNote() = runTest {
val note = NoteEntity(id = 1, title = "Test", content = "", updatedAt = 0)
dao.upsert(note)
val emitted = dao.observeNotesInFolder(note.folderId ?: -1).first()
assertEquals(listOf(note), emitted)
}
}
The in-memory database runs the real schema and real SQL, so a typo in a @Query string fails here instead of in production. Use runTest from kotlinx-coroutines-test so suspend DAO calls work on the test scheduler.
Common mistakes worth a checklist
- Returning entities straight to the UI. Entities carry foreign keys and internal fields. Map to UI models in the repository or ViewModel; you will thank yourself when the schema changes.
- Ignoring indices. Foreign keys and columns used in
WHEREclauses need@Entity(indices = [...]). Without them, list screens get slower as the table grows. - Observing everything on the main thread by accident. Room’s Flow query runs on a background dispatcher by default, but
allowMainThreadQueries()in production code is a lint warning waiting to become an ANR. - Over-normalizing. Room does not force you into third normal form. A single denormalized table with a few redundant columns can be the right answer for read-heavy screens; JOINs are not a virtue on their own.
- Forgetting indexes on junction tables. The many-to-many cross-ref entity needs
primaryKeys(or an index) on both foreign key columns, or every junction lookup is a table scan.
A short word on FTS and triggers
For full-text search, Room supports SQLite FTS tables directly — declare an @Fts4 entity that mirrors the searchable columns and query it with MATCH. For reactive updates when a row changes outside Room (a sync service writing via ContentProvider, say), SQLite triggers can call back into your invalidation logic; Room’s enableMultiInstanceInvalidation() covers the multi-process case. These are sharp tools for specific problems, not defaults.
The through-line across all of these patterns: keep the database honest as the single source of truth, let Flow do the invalidation, write migrations like they matter, and test with the real generated code. Apps that follow that shape stay boring as they grow — which is exactly what you want from a persistence layer.