Android

Room Database Testing Guide: In-Memory Database, DAO Unit Tests, Migration Testing & Paging

HelpMeTest

17 May 2026 — 6 min read

Room is Android's recommended database library, wrapping SQLite with type-safe query builders and Kotlin coroutines support. Testing Room properly means validating DAO queries, schema migrations, and Paging 3 integration—without ever touching a real disk database.

Room Testing Strategy

Room provides first-class support for testing through in-memory databases:

In-memory databases — zero disk I/O, zero cleanup, test-isolated
@Dao test classes — test every query and observable in isolation
Migration test helpers — validate that schema changes don't corrupt existing data
Paging test utilities — validate PagingSource and RemoteMediator behavior

Setting Up Room Tests

// build.gradle.kts
dependencies {
    testImplementation("androidx.room:room-testing:$room_version")
    testImplementation("androidx.arch.core:core-testing:2.2.0")
    testImplementation("org.jetbrains.kotlinx:kotlinx-coroutines-test:1.7.3")
    testImplementation("app.cash.turbine:turbine:1.1.0")

    // For instrumented tests
    androidTestImplementation("androidx.room:room-testing:$room_version")
}

In-Memory Database Setup

@RunWith(AndroidJUnit4::class)
class ProductDaoTest {

    private lateinit var db: AppDatabase
    private lateinit var productDao: ProductDao

    @Before
    fun createDatabase() {
        val context = InstrumentationRegistry.getInstrumentation().targetContext
        db = Room.inMemoryDatabaseBuilder(context, AppDatabase::class.java)
            .allowMainThreadQueries()  // Only for tests
            .build()
        productDao = db.productDao()
    }

    @After
    fun closeDatabase() {
        db.close()
    }
}

For JVM (non-instrumented) tests using Robolectric:

@RunWith(RobolectricTestRunner::class)
class ProductDaoRobolectricTest {

    private lateinit var db: AppDatabase
    private lateinit var productDao: ProductDao

    @Before
    fun createDatabase() {
        val context = ApplicationProvider.getApplicationContext<Context>()
        db = Room.inMemoryDatabaseBuilder(context, AppDatabase::class.java)
            .allowMainThreadQueries()
            .build()
        productDao = db.productDao()
    }

    @After
    fun closeDatabase() = db.close()
}

DAO Unit Tests

Testing Basic CRUD

@Test
fun shouldInsertAndRetrieveProduct() = runTest {
    val product = Product(
        id = 0,  // Room auto-generates ID
        name = "Wireless Headphones",
        price = 79.99,
        category = "electronics",
        inStock = true
    )

    val insertedId = productDao.insert(product)

    val retrieved = productDao.getById(insertedId)
    assertNotNull(retrieved)
    assertEquals("Wireless Headphones", retrieved!!.name)
    assertEquals(79.99, retrieved.price, 0.01)
    assertTrue(retrieved.inStock)
}

@Test
fun shouldUpdateProduct() = runTest {
    val product = Product(0, "Old Name", 50.0, "electronics", true)
    val id = productDao.insert(product)

    val updated = product.copy(id = id, name = "New Name", price = 75.0)
    productDao.update(updated)

    val retrieved = productDao.getById(id)!!
    assertEquals("New Name", retrieved.name)
    assertEquals(75.0, retrieved.price, 0.01)
}

@Test
fun shouldDeleteProduct() = runTest {
    val id = productDao.insert(Product(0, "To Delete", 10.0, "misc", true))
    val product = productDao.getById(id)!!

    productDao.delete(product)

    assertNull(productDao.getById(id))
}

Testing Query Methods

@Test
fun shouldFilterProductsByCategory() = runTest {
    productDao.insertAll(listOf(
        Product(0, "Laptop", 999.0, "electronics", true),
        Product(0, "Phone", 599.0, "electronics", true),
        Product(0, "Novel", 14.99, "books", true),
        Product(0, "Cookbook", 29.99, "books", false)
    ))

    val electronics = productDao.getByCategory("electronics")
    assertEquals(2, electronics.size)
    assertTrue(electronics.all { it.category == "electronics" })

    val books = productDao.getByCategory("books")
    assertEquals(2, books.size)
}

@Test
fun shouldReturnProductsInPriceRange() = runTest {
    productDao.insertAll(listOf(
        Product(0, "Cheap", 5.0, "misc", true),
        Product(0, "Mid", 50.0, "misc", true),
        Product(0, "Expensive", 500.0, "misc", true)
    ))

    val midRange = productDao.getByPriceRange(10.0, 100.0)
    assertEquals(1, midRange.size)
    assertEquals("Mid", midRange[0].name)
}

@Test
fun shouldReturnEmptyListWhenNoCategoryMatch() = runTest {
    productDao.insert(Product(0, "Item", 10.0, "electronics", true))

    val results = productDao.getByCategory("nonexistent")
    assertTrue(results.isEmpty())
}

Testing Flow/Observable Queries

@Test
fun shouldEmitUpdatedListWhenProductInserted() = runTest {
    productDao.getAllProducts().test {
        // Initial state: empty
        assertEquals(emptyList<Product>(), awaitItem())

        // Insert a product
        productDao.insert(Product(0, "New Product", 25.0, "misc", true))

        // Should emit updated list
        val updated = awaitItem()
        assertEquals(1, updated.size)
        assertEquals("New Product", updated[0].name)

        cancelAndIgnoreRemainingEvents()
    }
}

@Test
fun shouldEmitUpdatesOnProductChange() = runTest {
    val id = productDao.insert(Product(0, "Original", 10.0, "misc", true))

    productDao.getAllProducts().test {
        awaitItem() // Initial state with 1 product

        // Update price
        val product = productDao.getById(id)!!
        productDao.update(product.copy(price = 20.0))

        val updated = awaitItem()
        assertEquals(20.0, updated[0].price, 0.01)

        cancelAndIgnoreRemainingEvents()
    }
}

Testing Many-to-Many Relationships

@Entity
data class Tag(
    @PrimaryKey val id: Long,
    val name: String
)

@Entity(primaryKeys = ["productId", "tagId"])
data class ProductTagCrossRef(
    val productId: Long,
    val tagId: Long
)

data class ProductWithTags(
    @Embedded val product: Product,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(ProductTagCrossRef::class)
    )
    val tags: List<Tag>
)

@Test
fun shouldLoadProductWithTags() = runTest {
    val productId = productDao.insert(Product(0, "Camera", 399.0, "electronics", true))
    val tag1Id = tagDao.insert(Tag(0, "photography"))
    val tag2Id = tagDao.insert(Tag(0, "gadget"))

    productTagDao.insert(ProductTagCrossRef(productId, tag1Id))
    productTagDao.insert(ProductTagCrossRef(productId, tag2Id))

    val productWithTags = productDao.getProductWithTags(productId)
    assertNotNull(productWithTags)
    assertEquals(2, productWithTags!!.tags.size)
    assertTrue(productWithTags.tags.map { it.name }.contains("photography"))
}

Migration Testing

Room's MigrationTestHelper validates that schema migrations don't corrupt existing data:

@RunWith(AndroidJUnit4::class)
class MigrationTest {

    private val TEST_DB = "migration-test"

    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test
    fun shouldMigrateFrom1To2WithoutDataLoss() {
        // Create version 1 database with test data
        helper.createDatabase(TEST_DB, 1).apply {
            execSQL(
                "INSERT INTO products (name, price) VALUES ('Test Product', 49.99)"
            )
            close()
        }

        // Run migration to version 2
        val db = helper.runMigrationsAndValidate(
            TEST_DB,
            2,
            true,  // validate schema
            MIGRATION_1_2
        )

        // Verify data survived migration
        val cursor = db.query("SELECT * FROM products")
        cursor.moveToFirst()
        assertEquals("Test Product", cursor.getString(cursor.getColumnIndex("name")))
        assertEquals(49.99, cursor.getDouble(cursor.getColumnIndex("price")), 0.01)

        // Verify new column exists with default value
        val newColumn = cursor.getColumnIndex("category")
        assertTrue(newColumn >= 0)
        assertEquals("uncategorized", cursor.getString(newColumn))

        cursor.close()
        db.close()
    }

    @Test
    fun shouldMigrateAllVersionsToLatest() {
        // Create at version 1
        helper.createDatabase(TEST_DB, 1).close()

        // Run all migrations at once
        helper.runMigrationsAndValidate(
            TEST_DB,
            AppDatabase.DATABASE_VERSION,
            true,
            *ALL_MIGRATIONS
        ).close()
    }
}

Defining Migrations

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            "ALTER TABLE products ADD COLUMN category TEXT NOT NULL DEFAULT 'uncategorized'"
        )
    }
}

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Create new table
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                name TEXT NOT NULL
            )
        """)
        // Create junction table
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS product_tag_cross_ref (
                productId INTEGER NOT NULL,
                tagId INTEGER NOT NULL,
                PRIMARY KEY (productId, tagId)
            )
        """)
    }
}

val ALL_MIGRATIONS = arrayOf(MIGRATION_1_2, MIGRATION_2_3)

Auto-Migration Testing

@Database(
    entities = [Product::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),
        AutoMigration(from = 2, to = 3, spec = Migration2To3Spec::class)
    ]
)
abstract class AppDatabase : RoomDatabase() { ... }

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

    helper.runMigrationsAndValidate(
        TEST_DB,
        3,
        true  // Validates against current schema
    ).close()
}

Paging 3 Testing

Testing PagingSource

@Test
fun shouldLoadFirstPageOfProducts() = runTest {
    // Insert test data
    val products = (1..30).map { i ->
        Product(i.toLong(), "Product $i", i * 10.0, "misc", true)
    }
    productDao.insertAll(products)

    val pagingSource = productDao.getPagedProducts()

    val loadResult = pagingSource.load(
        PagingSource.LoadParams.Refresh(
            key = null,
            loadSize = 10,
            placeholdersEnabled = false
        )
    )

    assertTrue(loadResult is PagingSource.LoadResult.Page)
    val page = loadResult as PagingSource.LoadResult.Page
    assertEquals(10, page.data.size)
    assertEquals("Product 1", page.data[0].name)
    assertNotNull(page.nextKey)  // More pages available
    assertNull(page.prevKey)     // First page has no previous
}

@Test
fun shouldReturnNullNextKeyOnLastPage() = runTest {
    val products = (1..5).map { i ->
        Product(i.toLong(), "Product $i", i * 10.0, "misc", true)
    }
    productDao.insertAll(products)

    val pagingSource = productDao.getPagedProducts()

    val loadResult = pagingSource.load(
        PagingSource.LoadParams.Refresh(
            key = null,
            loadSize = 10,  // Larger than total items
            placeholdersEnabled = false
        )
    )

    assertTrue(loadResult is PagingSource.LoadResult.Page)
    assertNull((loadResult as PagingSource.LoadResult.Page).nextKey)
}

Testing Paging with `TestPager`

@Test
fun shouldLoadMultiplePagesInOrder() = runTest {
    productDao.insertAll(
        (1..25).map { Product(it.toLong(), "Product $it", it * 5.0, "misc", true) }
    )

    val pager = TestPager(PagingConfig(pageSize = 10), productDao.getPagedProducts())

    val firstPage = pager.refresh() as PagingSource.LoadResult.Page
    assertEquals(10, firstPage.data.size)

    val secondPage = pager.append() as PagingSource.LoadResult.Page
    assertEquals(10, secondPage.data.size)
    assertEquals("Product 11", secondPage.data[0].name)

    val thirdPage = pager.append() as PagingSource.LoadResult.Page
    assertEquals(5, thirdPage.data.size)
    assertEquals("Product 25", thirdPage.data.last().name)
}

Testing TypeConverters

class Converters {
    @TypeConverter
    fun fromStringList(value: String): List<String> = value.split(",").filter { it.isNotEmpty() }

    @TypeConverter
    fun toStringList(list: List<String>): String = list.joinToString(",")

    @TypeConverter
    fun fromTimestamp(value: Long?): Instant? = value?.let { Instant.ofEpochMilli(it) }

    @TypeConverter
    fun toTimestamp(instant: Instant?): Long? = instant?.toEpochMilli()
}

@Test
fun shouldStoreAndRetrieveListColumn() = runTest {
    val product = Product(
        id = 0,
        name = "Item",
        price = 10.0,
        tags = listOf("sale", "featured", "new"),
        createdAt = Instant.now()
    )

    val id = productDao.insert(product)
    val retrieved = productDao.getById(id)!!

    assertEquals(listOf("sale", "featured", "new"), retrieved.tags)
    assertThat(retrieved.createdAt).isCloseTo(product.createdAt, within(1, ChronoUnit.SECONDS))
}

Room Testing with HelpMeTest

For apps where Room backs a local-first sync architecture with remote APIs, HelpMeTest validates the complete user experience:

*** Test Cases ***
Offline Mode Shows Cached Data
    As    LoggedInUser
    Go To    https://app.example.com/products
    Wait Until Element Is Visible    [data-testid=product-list]
    Simulate Offline Mode
    Reload Page
    Wait Until Element Is Visible    [data-testid=product-list]
    Element Should Contain    [data-testid=cache-indicator]    Offline
    Product Count Should Be Greater Than    0

This catches issues where Room queries fail silently or offline fallback logic is broken.

Common Pitfalls

1. Forgetting allowMainThreadQueries() in tests By default, Room throws on main-thread queries. Add allowMainThreadQueries() to your in-memory builder or use runTest with proper dispatchers.

2. Not closing the database in @After Unclosed in-memory databases cause SQLiteDatabaseLockedException in subsequent tests. Always call db.close() in teardown.

3. Using auto-generated IDs of 0 in copy() When inserting with id = 0 for auto-generation, the copy() ID will still be 0 unless you use the inserted ID. Always retrieve the inserted ID and use it in copy().

4. Not testing migration rollback scenarios Test that a corrupted or incomplete migration fails cleanly, not silently. Use fallbackToDestructiveMigration() only in development, and test explicit migration paths for production.

Summary

Room testing provides comprehensive coverage across three levels:

DAO tests with in-memory databases for query correctness, Flow observability, and relationship loading
Migration tests with MigrationTestHelper to verify schema changes don't lose data
Paging tests with TestPager for PagingSource behavior validation

The in-memory database is Room's testing superpower—it's identical to the production SQLite engine but requires zero setup, leaves no artifacts, and runs at full speed.

Room Database Testing Guide: In-Memory Database, DAO Unit Tests, Migration Testing & Paging

HelpMeTest

Room Testing Strategy

Setting Up Room Tests

In-Memory Database Setup

DAO Unit Tests

Testing Basic CRUD

Testing Query Methods

Testing Flow/Observable Queries

Testing Many-to-Many Relationships

Migration Testing

Defining Migrations

Auto-Migration Testing

Paging 3 Testing

Testing PagingSource

Testing Paging with `TestPager`

Testing TypeConverters

Room Testing with HelpMeTest

Common Pitfalls

Summary

Read more

Blue-Green Deployment Testing: Smoke Tests, Traffic Cutover & Rollback Validation

Kong API Gateway Testing Guide: kong-pongo, Rate Limiting & Plugin Tests

Chaos Mesh Testing Guide: PodChaos, NetworkChaos & Kubernetes Fault Injection

AWS API Gateway Testing Guide: Lambda Authorizers, Mock Integrations & CDK Tests

Room Testing Strategy

Setting Up Room Tests

In-Memory Database Setup

DAO Unit Tests

Testing Basic CRUD

Testing Query Methods

Testing Flow/Observable Queries

Testing Many-to-Many Relationships

Migration Testing

Defining Migrations

Auto-Migration Testing

Paging 3 Testing

Testing PagingSource

Testing Paging with TestPager

Testing TypeConverters

Room Testing with HelpMeTest

Common Pitfalls

Summary

Read more

Blue-Green Deployment Testing: Smoke Tests, Traffic Cutover & Rollback Validation

Kong API Gateway Testing Guide: kong-pongo, Rate Limiting & Plugin Tests

Chaos Mesh Testing Guide: PodChaos, NetworkChaos & Kubernetes Fault Injection

AWS API Gateway Testing Guide: Lambda Authorizers, Mock Integrations & CDK Tests

Testing Paging with `TestPager`