Testing Room Database — In-Memory Setup, DAO Tests, Migrations, Foreign Keys, and the Tests That Save Production

Last week I shipped a Room migration to production. It worked on my emulator. It worked on QA’s device. It crashed for 12% of users on launch. The bug? A column rename that I’d tested manually but never with the actual upgrade path from version 4 to version 7. That’s why we test databases. Manual testing covers maybe three scenarios. A 60-line test file covers thirty — foreign keys, transactions, schema migrations, conflict strategies, type converters, reactive Flow updates — and finishes in under a second. This post is everything I wish I’d known about testing Room before I learned the hard way.

Where Room Tests Actually Live

Most Room tests should live in src/androidTest/ — not src/test/. Room generates SQLite-backed code that needs the real Android SQLite implementation. You can run it via Robolectric on the JVM, but you’ll hit subtle differences between the emulated SQLite and the real one. For DAO tests, migrations, and anything touching real queries, instrumented tests are the safer default. Mappers and pure Kotlin logic that happens around Room? Those go in src/test/.

// build.gradle.kts
dependencies {
    // Room itself + its testing helpers
    androidTestImplementation("androidx.room:room-testing:2.6.1")
    // room-testing is a LIBRARY — gives you MigrationTestHelper

    // AndroidX test runner + JUnit
    androidTestImplementation("androidx.test.ext:junit:1.2.1")
    androidTestImplementation("androidx.test:runner:1.6.2")

    // Coroutines + Turbine (for Flow-returning DAOs)
    androidTestImplementation("org.jetbrains.kotlinx:kotlinx-coroutines-test:1.9.0")
    androidTestImplementation("app.cash.turbine:turbine:1.1.0")

    // Schema export — required for migration tests
    // Add to your defaultConfig:
    // ksp { arg("room.schemaLocation", "$projectDir/schemas") }
    // OR for kapt:
    // javaCompileOptions {
    //   annotationProcessorOptions { arguments["room.schemaLocation"] = "$projectDir/schemas" }
    // }
}

// And tell Gradle to ship the schemas as test assets:
android {
    sourceSets {
        getByName("androidTest").assets.srcDir("$projectDir/schemas")
    }
}

DAO Tests — The 90% Case

Most of your Room tests will be DAO tests. The pattern is dead simple: spin up an in-memory database, get the DAO, exercise it, assert. The trick is using inMemoryDatabaseBuilder instead of databaseBuilder. In-memory databases don’t touch disk, vanish when the test ends, and let every test start from a clean slate.

@RunWith(AndroidJUnit4::class)
class ArticleDaoTest {

    private lateinit var db: AppDatabase
    private lateinit var dao: ArticleDao

    @Before
    fun setUp() {
        val context = ApplicationProvider.getApplicationContext<Context>()
        db = Room.inMemoryDatabaseBuilder(context, AppDatabase::class.java)
            // inMemoryDatabaseBuilder is a STATIC FUNCTION on Room
            // Database lives in RAM only — perfect for tests
            .allowMainThreadQueries()
            // allowMainThreadQueries() — lets the test block on queries
            // NEVER use this in production; for tests it's fine
            .build()
        dao = db.articleDao()
    }

    @After
    fun tearDown() {
        db.close()
        // Always close — otherwise subsequent tests can see stale state
    }

    @Test
    fun insertedArticle_canBeQueriedById() = runTest {
        val article = ArticleEntity(id = "1", title = "Kotlin Basics", body = "...")

        dao.insert(article)

        val fetched = dao.getById("1")
        assertEquals("Kotlin Basics", fetched?.title)
    }

    @Test
    fun insertReplace_updatesExistingRow() = runTest {
        dao.insert(ArticleEntity(id = "1", title = "v1", body = "..."))
        dao.insert(ArticleEntity(id = "1", title = "v2", body = "..."))
        // Assumes @Insert(onConflict = OnConflictStrategy.REPLACE)

        assertEquals("v2", dao.getById("1")?.title)
        assertEquals(1, dao.getAll().size)   // only one row, not two
    }

    @Test
    fun deleteById_removesOnlyMatchingRow() = runTest {
        dao.insert(ArticleEntity("1", "A", "..."))
        dao.insert(ArticleEntity("2", "B", "..."))

        dao.deleteById("1")

        assertNull(dao.getById("1"))
        assertNotNull(dao.getById("2"))
    }
}

Two things from this snippet are worth pausing on. First, allowMainThreadQueries() is fine in tests because there is no UI thread to block — you just want simple, blocking semantics. Second, every test gets a fresh database. No @BeforeAll singletons, no carry-over state. If test A inserts and test B happens to pass because of A’s data, you have a bug waiting to happen.

Testing DAOs That Return Flow

Room can return Flow<T> from queries. The Flow re-emits whenever the underlying data changes. Testing it means subscribing to the Flow, mutating the data, and asserting that you see the new emission. Turbine is purpose-built for this.

@Test
fun observeAll_emitsUpdatedListAfterInsert() = runTest {
    dao.observeAll().test {
        // .test { } is an EXTENSION FUNCTION on Flow from Turbine
        assertEquals(emptyList<ArticleEntity>(), awaitItem())
        // Initial emission — empty database

        dao.insert(ArticleEntity("1", "First", "..."))
        val afterInsert = awaitItem()
        assertEquals(1, afterInsert.size)
        assertEquals("First", afterInsert[0].title)

        dao.insert(ArticleEntity("2", "Second", "..."))
        val afterSecond = awaitItem()
        assertEquals(2, afterSecond.size)

        cancelAndIgnoreRemainingEvents()
    }
}

@Test
fun observeAll_emitsAfterDelete() = runTest {
    dao.insert(ArticleEntity("1", "Doomed", "..."))

    dao.observeAll().test {
        assertEquals(1, awaitItem().size)   // initial state has the row

        dao.deleteById("1")
        assertEquals(0, awaitItem().size)   // emission after delete

        cancelAndIgnoreRemainingEvents()
    }
}

One quirk worth knowing: Room’s Flow emissions are conflated. If you do five rapid inserts, you might not see five emissions — you might see two or three. Don’t write tests that assert on exact emission counts. Assert on the final state instead. If you really need to see every change, you’re probably looking for an event log, not a query.

Foreign Keys and Relations

Foreign keys are the area where I’ve found the most bugs in production code. Cascade rules, orphaned children, the difference between ON DELETE CASCADE and ON DELETE SET NULL — this stuff is easy to get wrong and impossible to verify by clicking around in the app.

// Schema:
@Entity
data class AuthorEntity(@PrimaryKey val id: String, val name: String)

@Entity(
    foreignKeys = [ForeignKey(
        entity = AuthorEntity::class,
        parentColumns = ["id"],
        childColumns = ["authorId"],
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index("authorId")]
)
data class ArticleEntity(
    @PrimaryKey val id: String,
    val title: String,
    val authorId: String
)

// Test the cascade actually cascades:
@Test
fun deletingAuthor_cascadesToArticles() = runTest {
    db.authorDao().insert(AuthorEntity("a1", "Hemant"))
    db.articleDao().insert(ArticleEntity("art1", "Kotlin", authorId = "a1"))
    db.articleDao().insert(ArticleEntity("art2", "Compose", authorId = "a1"))

    db.authorDao().deleteById("a1")

    assertEquals(0, db.articleDao().getAll().size)
    // Without the CASCADE rule, this would still be 2 — orphan rows
}

@Test
fun insertingArticle_withMissingAuthor_throws() = runTest {
    // Foreign key violation — author "ghost" does not exist
    assertFailsWith<SQLiteConstraintException> {
        db.articleDao().insert(ArticleEntity("art1", "T", authorId = "ghost"))
    }
}

If you’re using @Relation for one-to-many or many-to-many queries, test those too. The query Room generates is non-trivial and easy to break with a wrong column name.

data class AuthorWithArticles(
    @Embedded val author: AuthorEntity,
    @Relation(parentColumn = "id", entityColumn = "authorId")
    val articles: List<ArticleEntity>
)

@Test
fun authorWithArticles_returnsAllArticlesForAuthor() = runTest {
    db.authorDao().insert(AuthorEntity("a1", "Hemant"))
    db.articleDao().insert(ArticleEntity("art1", "Kotlin", "a1"))
    db.articleDao().insert(ArticleEntity("art2", "Compose", "a1"))
    db.articleDao().insert(ArticleEntity("art3", "Room", "a2"))   // different author

    val result = db.authorDao().getAuthorWithArticles("a1")

    assertEquals(2, result.articles.size)
    assertTrue(result.articles.all { it.authorId == "a1" })
}

Migrations — The Tests That Save Production

This is the section that motivated the whole post. Migration tests are the difference between “works on my emulator” and “doesn’t crash 12% of my users.” Room ships MigrationTestHelper specifically for this. The flow is: create a database at the old version with some data, run the migration, then verify the schema and data look right.

You need schema files for this. Earlier in the build.gradle setup we exported them to $projectDir/schemas. After running a build, you’ll see 1.json, 2.json, etc. in there — one per database version. MigrationTestHelper reads these to construct old-version databases.

private const val TEST_DB = "migration-test"

@RunWith(AndroidJUnit4::class)
class MigrationTest {

    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )
    // MigrationTestHelper is a CLASS from room-testing
    // Reads exported schemas, creates databases at specific versions

    @Test
    fun migrate_1_to_2_addsAuthorColumn() {
        // Step 1: create v1 database and insert v1-shaped data
        helper.createDatabase(TEST_DB, 1).apply {
            execSQL("INSERT INTO articles (id, title) VALUES ('1', 'Kotlin')")
            close()
        }

        // Step 2: run the migration up to v2
        val migratedDb = helper.runMigrationsAndValidate(
            TEST_DB,
            2,
            true,
            MIGRATION_1_2
        )

        // Step 3: verify the new column exists with the expected default
        migratedDb.query("SELECT id, title, author FROM articles").use { cursor ->
            assertTrue(cursor.moveToFirst())
            assertEquals("1", cursor.getString(0))
            assertEquals("Kotlin", cursor.getString(1))
            assertEquals("Unknown", cursor.getString(2))
            // The migration set "Unknown" as the default for existing rows
        }
    }
}

// The migration being tested:
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE articles ADD COLUMN author TEXT NOT NULL DEFAULT 'Unknown'")
    }
}

The true argument to runMigrationsAndValidate is doing real work — it asks Room to validate that after the migration, the database schema matches the schema Room expects for version 2 (from 2.json). If you forget a column, change a type, or skip an index, this fails loudly.

For multi-step migrations, just chain them:

@Test
fun migrate_1_to_4_throughAllSteps() {
    helper.createDatabase(TEST_DB, 1).apply {
        execSQL("INSERT INTO articles (id, title) VALUES ('1', 'A')")
        close()
    }

    val db = helper.runMigrationsAndValidate(
        TEST_DB, 4, true,
        MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4
    )

    // Verify the final state — data preserved through all three migrations
    db.query("SELECT title FROM articles WHERE id = '1'").use {
        assertTrue(it.moveToFirst())
        assertEquals("A", it.getString(0))
    }
}

One more thing: there’s a magic test that catches schema drift across all versions in one shot.

@Test
fun allMigrationsRunCleanlyFromV1() {
    helper.createDatabase(TEST_DB, 1).close()

    // Open the latest version through Room itself, with all migrations supplied
    Room.databaseBuilder(
        InstrumentationRegistry.getInstrumentation().targetContext,
        AppDatabase::class.java,
        TEST_DB
    )
        .addMigrations(*ALL_MIGRATIONS)
        .build()
        .apply {
            openHelper.writableDatabase   // forces the migration chain
            close()
        }
}
// If any migration is missing or broken, this fails at build time of the DB

TypeConverters

TypeConverters are pure functions, so they’re actually easier to test than DAOs. Skip the in-memory database entirely and just test the converter class.

class DateConverter {
    @TypeConverter fun fromTimestamp(value: Long?): Date? = value?.let { Date(it) }
    @TypeConverter fun dateToTimestamp(date: Date?): Long? = date?.time
}

class DateConverterTest {
    private val converter = DateConverter()

    @Test
    fun roundTrip_preservesValue() {
        val original = Date(1_700_000_000_000)
        val timestamp = converter.dateToTimestamp(original)
        val restored = converter.fromTimestamp(timestamp)
        assertEquals(original, restored)
    }

    @Test
    fun nullsArePreserved() {
        assertNull(converter.fromTimestamp(null))
        assertNull(converter.dateToTimestamp(null))
    }
}

It’s tempting to skip these because they look trivial. Don’t. The most painful Room bug I’ve ever debugged was a List<String> converter that joined with "," and split on "," — which broke the day a user typed a comma in a tag name. A two-line round-trip test with the input listOf("hello, world") would have caught it.

Transactions and @Transaction

Room’s @Transaction annotation guarantees atomicity — either every operation in the function commits, or none of them do. Test the rollback behaviour explicitly.

@Dao
abstract class ArticleDao {
    @Insert abstract suspend fun insert(article: ArticleEntity)
    @Insert abstract suspend fun insertTag(tag: TagEntity)

    @Transaction
    open suspend fun insertArticleWithTags(article: ArticleEntity, tags: List<TagEntity>) {
        insert(article)
        tags.forEach { insertTag(it) }
        // If any tag insert throws, the whole thing rolls back
    }
}

@Test
fun insertArticleWithTags_rollsBackOnFailure() = runTest {
    val tags = listOf(
        TagEntity("t1", articleId = "a1"),
        TagEntity("t1", articleId = "a1")   // duplicate primary key — will throw
    )

    assertFailsWith<SQLiteConstraintException> {
        dao.insertArticleWithTags(
            ArticleEntity("a1", "Title", "..."),
            tags
        )
    }

    // Article should NOT be in the DB — the transaction rolled back
    assertNull(dao.getById("a1"))
    assertEquals(0, dao.getAllTags().size)
}

Repository Tests — Mixing Room With the Network

Once your Room tests are solid, you usually want one layer up: the Repository that combines a remote source (Retrofit) with a local source (Room). Here you have a choice. You can use a real in-memory Room database with a fake API, or you can fake both. I lean toward the first: real Room catches mapping bugs, query bugs, and reactive Flow bugs that fakes silently paper over.

@RunWith(AndroidJUnit4::class)
class ArticleRepositoryTest {

    private lateinit var db: AppDatabase
    private lateinit var dao: ArticleDao
    private val fakeApi = FakeArticleApi()
    private lateinit var repository: ArticleRepository

    @Before
    fun setUp() {
        val context = ApplicationProvider.getApplicationContext<Context>()
        db = Room.inMemoryDatabaseBuilder(context, AppDatabase::class.java)
            .allowMainThreadQueries()
            .build()
        dao = db.articleDao()
        repository = ArticleRepository(fakeApi, dao)
    }

    @After fun tearDown() = db.close()

    @Test
    fun refreshArticles_savesApiResponseToDao() = runTest {
        fakeApi.articlesToReturn = listOf(
            ArticleDto("1", "Kotlin", "..."),
            ArticleDto("2", "Compose", "...")
        )

        repository.refreshArticles()

        val stored = dao.getAll()
        assertEquals(2, stored.size)
        assertEquals("Kotlin", stored[0].title)
    }

    @Test
    fun observeArticles_emitsCachedDataImmediately_thenNetworkData() = runTest {
        // Seed the cache
        dao.insert(ArticleEntity("1", "Cached", "..."))

        // The network will eventually return different data
        fakeApi.articlesToReturn = listOf(ArticleDto("1", "Fresh", "..."))

        repository.observeArticles().test {
            // First emission: cached data, fast
            assertEquals("Cached", awaitItem()[0].title)

            // Repository triggers a refresh; eventually new data flows through
            repository.refreshArticles()
            assertEquals("Fresh", awaitItem()[0].title)

            cancelAndIgnoreRemainingEvents()
        }
    }
}

That second test is doing a lot of useful work. It verifies the repository’s cache-first behaviour, the network refresh path, and that downstream collectors see the new data — all in one test, all driven by real Room.

A Few Honest Pitfalls

Don’t reuse the database across tests. If you make db a static or class-level field that’s only initialized once, ordering bugs will sneak in. Test A leaves a row, test B passes because of it, then someone reorders the tests in the runner and B fails. @Before creates, @After closes — every time.

Don’t test Room itself. If you find yourself writing a test that says “@Insert actually inserts a row,” stop. That’s testing AndroidX, not your code. Test your queries, your migrations, your mappers, your conflict strategies.

Watch out for autoMigrations on string columns. Room’s automatic migrations handle most cases, but type changes and renames need an @AutoMigration(spec = ...) hint. Even with autoMigrations, write a migration test — the autoMigration is generated code, and generated code can be wrong if your annotations are subtly wrong.

Allowing main thread queries leaks into your code. Test code is fine to use allowMainThreadQueries(), but make sure your production database builder does not. Easy mistake to copy-paste from a test setup.

The schemas folder must be checked into git. If schemas/AppDatabase/2.json isn’t in version control, your migration tests will fail on CI and you’ll spend an afternoon figuring out why. Add the folder explicitly to your repo.

What to Test, What to Skip

If I had to rank Room test priorities by ROI:

1. Migrations. If you have any. Every single one. They’re the only tests that protect your users from data loss.
2. Custom queries with WHERE / JOIN. Anything more complex than SELECT * deserves a test — ordering, filtering, grouping, all of it.
3. Conflict strategies. OnConflictStrategy.REPLACE vs IGNORE vs ABORT — every project has gotten one of these wrong.
4. Foreign key cascades. Especially SET_NULL and CASCADE — the rules are easy to misread.
5. TypeConverters. Pure functions, cheap to test, expensive to debug in production.
6. Flow-returning queries. One Turbine test per query is enough — verify it actually re-emits.
7. @Relation queries. The generated SQL is non-trivial; test at least the happy path.

What I usually skip: trivial single-column SELECTs, basic inserts that just exercise the framework, getter/setter ceremony. Your time is better spent on the seven items above.

Wrap-Up

Database tests are the rare category where the investment is small and the downside they prevent is enormous. A migration bug doesn’t just crash the app — it can corrupt user data permanently. A bad conflict strategy can silently drop writes for months before anyone notices. A broken cascade rule leaves orphaned rows that bloat the database forever.

The setup once you have it — in-memory DB in @Before, schemas exported to a folder, MigrationTestHelper wired up — takes maybe twenty minutes. After that, every new DAO method is a five-minute test, every new migration is a ten-minute test, and every refactor is something you can do at 5pm on a Friday without checking the calendar for who’s on call.

Write the migration tests. Future-you on a Monday morning will be grateful.

Happy coding!