Skip to content
Velloreth
Android Development 8 min read

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

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 WHERE clauses 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.

Frequently asked questions

Should I use Room with a repository, or can the ViewModel call the DAO directly?
A repository is worth it when you have more than one data source (network, cache, preferences) or need to map database entities to domain models. For a local-only app, a DAO behind a thin repository interface is enough — the important part is that UI code never imports the DAO.
How do I handle schema migrations without losing user data?
Write an explicit Migration for every schema version bump, with the SQL for the ALTER or CREATE statements. Test each migration with MigrationTestHelper. Use fallbackToDestructiveMigration only during development.
Why does my Flow query not update when I insert data?
Flow invalidation is tied to the tables referenced in the query. If you observe one table but write to another through a raw query or trigger, Room cannot see the change. Also make sure you are collecting the Flow in a lifecycle-aware scope.
When should I use the Paging library with Room?
When the result set can be large — message threads, product catalogs, search results. A DAO method returning PagingSource<Int, Entity> plugs directly into Pager and LazyColumn. For small, bounded lists, a plain Flow<List<T>> is simpler.