Articles

Room Interview Questions: Migrations, Transactions, and Observable Queries

Prepare for Android Room interviews with safe migrations, transaction invariants, observable Flow behavior, relation queries, and focused follow-ups.

Room interview questions become difficult when an annotation that looks correct still loses data, exposes a half-finished update, or wakes a collector for a result that has not changed. The strongest answer states the invariant first, identifies the Room version, and then proves what happens during failure.

What a Strong Room Interview Answer Must Prove

A useful Room answer follows one invariant through three kinds of time. Schema time asks whether an installed database can move from an old version to the latest one without losing meaning. Write time asks whether several statements commit as one unit. Observation time asks when a reader reruns and what a new emission actually means.

The underlying guarantees come from Android's documentation: Room supports automatic and manual migrations, transactions roll back on failure, and observable queries rerun when referenced tables are invalidated.

Name your API generation before writing code. Current Room 3 uses the androidx.room3 package, SQLiteConnection, and coroutine-first APIs. Room 2 examples commonly use androidx.room, SupportSQLiteDatabase, and DAO @Transaction. An interviewer may accept either when you state the version and preserve the same invariant.

Question 1: How Would You Design a Safe Room Migration?

Suppose version 1 stores Note(id, title). Version 2 adds folder_id, a Folder table, a foreign key, and an index. Do not begin with fallbackToDestructiveMigration(). Begin with the product rule: every existing note must remain readable, uncategorized notes must have a defined representation, and the new foreign key must be valid.

Official fact: Room can generate an automatic migration when it can infer the schema change from exported schemas. An ambiguous rename or deletion requires an AutoMigrationSpec. A complex data transformation needs a manual Migration. If an automatic and manual migration cover the same version path, Room uses the manual one.

A strong answer then chooses a path:

Export versioned schemas in the build so the old and new structures are available to Room and to tests.
Add the new table and nullable folder_id, or introduce a real default folder if the product contract requires every note to belong to one.
Backfill deterministically. Do not invent a sentinel value that violates the foreign key.
Add the index and constraints after the data can satisfy them.
Test direct upgrades from every supported starting version to the latest version.

For Room 3, a manual migration has the shape below. The exact SQL depends on the intended schema, so explain each statement rather than memorizing this skeleton.

val MIGRATION_1_2 = object : Migration(1, 2) {
    override suspend fun migrate(connection: SQLiteConnection) {
        connection.execSQL("CREATE TABLE Folder (...)")
        connection.execSQL("ALTER TABLE Note ADD COLUMN folder_id INTEGER")
        connection.execSQL("CREATE INDEX index_Note_folder_id ON Note(folder_id)")
    }
}

In a Room 2 discussion, say that the same plan is implemented with SupportSQLiteDatabase. The interview signal is the transition contract: supported starting versions, legal intermediate states, preservation rules, and a testable final schema.

Question 2: How Do You Test More Than Schema Validity?

MigrationTestHelper can create a database at an older version, let the test insert representative rows, run the migration, and validate the resulting schema. That is necessary, but a schema match alone does not prove that user data still means the same thing.

Build a fixture that exposes the risky cases: a note with no folder, a folder containing several notes, non-ASCII text, a deleted parent if the old schema permitted it, and values near any new constraint boundary. After migration, query the rows and assert their meaning. Check indexes and foreign keys when they affect behavior. Then test the full path from each supported version, not only N-1 to N.

Android's database-testing guidance recommends running database tests on an Android device because host machines can use a different SQLite version. Treat that as an environment boundary, not as a substitute for assertions.

Migration test	Failure it catches	Evidence to show
`1 → latest` with sentinel rows	skipped transformation or missing path	old IDs, text, and relationships survive
`N-1 → latest`	newest incremental error	latest columns, constraints, and values
fresh install at latest	migration-only schema drift	fresh and migrated schemas behave alike
invalid legacy edge case	backfill or constraint conflict	deliberate error or documented repair
process reopened after upgrade	assumptions tied to one connection	data remains valid after close and reopen

The last two rows are editorial test recommendations. They extend the official tooling into failure cases an interviewer can evaluate.

Question 3: Where Should the Transaction Boundary Be?

Imagine moveNote(noteId, newFolderId) must update the note and append an audit record. The invariant is not “both DAO calls usually succeed.” It is “a reader can never observe the moved note without its matching audit entry.” Put the smallest complete unit that preserves that invariant in one write transaction.

Official fact: Room 3's withReadTransaction and withWriteTransaction methods commit when the block completes normally and roll back when the block throws or requests rollback. Nested transaction blocks inherit the parent transaction type. In Room 2, a DAO method annotated with @Transaction runs its implementation in a database transaction; an exception causes rollback. Individual insert, update, and delete DAO methods are already transactional, but two separate methods are still two separate units unless a wrapper transaction joins them.

database.withWriteTransaction {
    noteDao.move(noteId, newFolderId)
    auditDao.insert(MoveAudit(noteId, newFolderId))
}

Now invite the failure: the audit insert violates a constraint. The correct result is that the note move rolls back too. If a network call sits inside this block, move it out. A database rollback cannot undo a remote side effect, and a long network wait holds database resources while providing no atomicity across systems. Persist an intent or outbox record in the transaction, then let a separate worker perform the remote action idempotently. That outbox design is an architectural inference, not a Room guarantee.

Question 4: What Does an Observable Query Actually Observe?

Room supports one-shot reads and observable reads. A Kotlin suspend query produces one result. A Flow<T> observable query reruns when Room detects changes to a table referenced by the query.

The trap is assuming that invalidation means the result changed. Official fact: Room can rerun an observable query when any row in a referenced table changes, even when that row is outside the query result. Android's guidance recommends distinctUntilChanged() when downstream work should occur only for a distinct result.

Consider:

@Query("SELECT * FROM Note WHERE folder_id = :folderId ORDER BY title")
fun observeNotes(folderId: Long): Flow<List<Note>>

Updating a note in another folder can invalidate Note, rerun this query, and produce the same list. The safe explanation is:

table invalidation is the trigger;
the SQL query recomputes the snapshot;
equality or domain-specific comparison determines whether the consumer treats it as new;
collection lifetime belongs to the caller and its lifecycle, not to SQL itself.

Do not promise exactly one emission per logical write. Multiple writes inside one transaction become visible after commit, while unrelated invalidations may cause extra query work. If recomputation is expensive, narrow the query, reduce downstream transformations, and measure before adding a custom cache that creates another consistency problem.

Question 5: Why Put a Relationship Query in a Transaction?

Room does not model direct object references between entities. Relationship results are usually represented with an intermediate data class using @Embedded and @Relation, or with a multimap return type. Android's documentation prefers the multimap approach when it fits because it usually requires less supporting structure.

Some relationship methods execute multiple SQL queries: one obtains parents and another obtains children. Annotating the read with @Transaction gives those related queries one transaction boundary, so a concurrent writer cannot leave the object graph assembled from incompatible moments. This matters for nested relationships, which Android also warns can become expensive.

An interviewer may ask whether a transaction is always the best answer. It is not a performance shortcut. If one explicit JOIN can return the projection cleanly, it may make the snapshot and cost easier to see. If the object graph requires several Room-generated queries, a read transaction protects consistency but can extend read duration. State the cardinality, expected result size, indexing, and paging behavior before choosing.

A Worked Follow-Up: Upgrade While a Collector Is Active

Suppose an app upgrades from version 4 to 6. Version 5 splits display_name into two columns; version 6 adds an index. A screen collects a Flow<UserProfile> after the database opens.

A disciplined answer proceeds in order:

Room must find a complete migration path from 4 to 6 before normal DAO access begins.
The 4-to-5 migration must define how ambiguous names split and preserve the original when the split is uncertain.
The 5-to-6 step creates the index and the migration test proves both the final schema and representative row meaning.
Only after successful open does the query produce its current snapshot.
Later writes to referenced tables may invalidate and rerun the query; distinctUntilChanged() can suppress equal results for the consumer.
If migration fails, do not describe destructive fallback as recovery unless product policy explicitly permits permanent local-data deletion.

Steps 1, 4, 5, and the destructive-fallback risk follow documented Room behavior. The name-splitting policy and assertion set are scenario-specific inferences that must come from product requirements.

Practice Questions That Transfer to Room

Question	Room skill to rehearse	Room-specific follow-up
Design Android MVVM API Architecture	ownership and data flow	Where does database state end and UI state begin?
Explain database transactions and ACID	atomicity and isolation	Which Room calls must share one transaction?
Plan a major database version upgrade	staged schema change	Which installed versions remain supported?
Design an In-Memory Transactional Key-Value Store	invariants and rollback	What visible state is forbidden after failure?
Build a Compose Rating Card	observable UI state	How should equal database results affect recomposition?

A Compact Answer Framework

For any Android Room interview question, use six moves:

Version: Say Room 2 or Room 3 before naming APIs.
Invariant: Define the data meaning or visible state that must survive.
Boundary: Mark the migration path, transaction block, or observed tables.
Failure: Introduce an exception, old version, concurrent write, or irrelevant invalidation.
Evidence: Show a fixture, assertion, query result, or rollback check.
Limit: State what Room does not guarantee, such as remote atomicity or distinct logical emissions.

Avoid three weak shortcuts. Do not use destructive migration as a default answer for user-owned data. Do not wrap unrelated work in a large transaction without naming the invariant. Do not describe Flow as if it watches the returned rows individually. Each shortcut hides the failure mode the interviewer is trying to surface.

The best final sentence is concrete: “I would preserve these rows across every supported version, commit these two writes as one invariant, and treat table invalidation as a reason to recompute rather than proof that the result changed.” Then test the claim.