Room: entities, DAOs and the database

Module 7 ended on a promise. Kavita taps "Refresh" at 4 pm, the closing prices arrive, Lasalgaon onion is ₹2,525 and Nashik ₹2,560. She goes to cook. Android needs the memory, kills Mandi Bhav in the background, and when she comes back from Recents the screen says ₹2,450 and ₹2,300 again: on the emulator, after adb shell am kill, that is exactly what module 7's build shows, because the repository held the prices in a MutableStateFlow and the process that held it is gone. This lesson puts the prices in Room, Jetpack's library over SQLite, the database built into every Android phone. By the end the repository reads from a table, a refresh writes to it, and Kavita's ₹2,525 is still there after the process dies, after a force stop and after a reboot. The screens do not change: that was the point of module 7.

Why a database, and why Room

DataStore from lesson 1 rewrites its whole file on every change and can only be read whole. Prices are many rows, each changed on its own, asked for by id, by market and by date. That is what a relational database is for, and SQLite is already on the phone.

You could use SQLite directly, through SQLiteDatabase, with SQL in strings and a cursor to read each column by number. Google's guide recommends against it, and the reason is the bug you would meet first: a typo in a column name or a query that returns the wrong type is found when a farmer opens the screen. Room checks every query against the tables when the app is built, generates the code that turns rows into Kotlin objects and back, and gives you flows that emit again when a table changes, which is the shape module 7 left PriceRepository in.

What it costs: a code generator in the build, the same KSP that Hilt uses, and so a slower build; a schema you now have to migrate whenever you change it (lesson 3); and an extra layer to read when something goes wrong underneath.

Choosing a version: Room 3

On 8 November 2026 Google Maven had two stable lines of Room:

Room 3.0.0 became stable on 1 July 2026, and Google's "Save data in a local database using Room" guide now shows 3.0.3 in its setup. Mandi Bhav uses Room 3: it is the current line, the guide teaches it, and Mandi Bhav already has KSP and uses coroutines everywhere, which is all Room 3 asks for. It built first time with AGP 9.5.1, Kotlin 2.4.21, KSP 2.3.13 and Hilt 2.60.1. You will still meet Room 2 in most existing apps, and in nearly every tutorial written before mid-2026: the annotations are the same, so read them with the table above and change the package, the converter annotation and the builder. Older tutorials also add room-ktx for flows; Room 2.7 merged it into the runtime and left the artifact empty, and Room 3 has none.

In gradle/libs.versions.toml:

[versions]
room = "3.0.3"

[libraries]
androidx-room-runtime = { group = "androidx.room3", name = "room3-runtime", version.ref = "room" }
androidx-room-compiler = { group = "androidx.room3", name = "room3-compiler", version.ref = "room" }

[plugins]
room = { id = "androidx.room3", version.ref = "room" }

Add alias(libs.plugins.room) apply false to the root build file, alias(libs.plugins.room) to the app's plugins, and in app/build.gradle.kts:

room3 {
    schemaDirectory("$projectDir/schemas")
}

with implementation(libs.androidx.room.runtime) and ksp(libs.androidx.room.compiler) in dependencies. The Room Gradle plugin and its schemaDirectory make Room write a JSON description of the database for every version you build. Lesson 3 needs those files; for now, know that they appear in app/schemas/ and belong in git.

Entities: the tables

An entity is a class Room turns into a table: each property a column, each object a row. Prices go in data/local/PriceEntity.kt:

/**
 * One row of the `prices` table: today's price for one commodity at one
 * market. It mirrors [CommodityPrice], but belongs to the data layer, so the
 * model the screens use carries no database annotations.
 */
@Entity(tableName = "prices")
data class PriceEntity(
    @PrimaryKey val id: String,
    val commodity: String,
    val market: String,
    val date: LocalDate,
    val minPrice: Long,
    val maxPrice: Long,
    val modalPrice: Long,
)

with two small functions in the same file, PriceEntity.asModel() and CommodityPrice.asEntity(), that copy each field across. Why a second class that looks the same? Because the screens, the domain rule and every test use CommodityPrice, and module 7's layers say the data layer's storage details stay in the data layer. The cost is the two mapping functions, which you must update when a field is added; the saving arrives in lesson 4, when the table gains a column the screens should never see.

The seven-day history is a second table. A history row is one commodity at one market on one earlier day, so those three columns together are the key:

@Entity(tableName = "price_history", primaryKeys = ["market", "commodity", "date"])
data class PriceHistoryEntity(
    val market: String,
    val commodity: String,
    val date: LocalDate,
    val modalPrice: Long,
)

It holds the six days before today. The seventh day of the week is today's price itself, so a refresh that changes today's price changes the week too, as module 7's version did.

Teaching Room a date

SQLite stores text, whole numbers, real numbers and bytes, and nothing else. LocalDate is none of those. Leave Room to work it out and the build stops:

e: [ksp] .../data/local/PriceEntity.kt:18: Cannot figure out how to save this property into database. Consider also adding a @ColumnTypeConverter for the property type.
e: [ksp] .../data/local/PriceEntity.kt:18: Cannot figure out how to read this property from a statement.
e: [ksp] .../data/local/PriceDao.kt:34: Query function parameters should either be a type that can be converted into a database column or a List / Array that contains such type. Consider also adding a @ColumnTypeConverter for the parameter type.

Six errors in all, two per table and two for the query that takes dates; this is Room checking at build time, as promised. A type converter is a pair of functions, one each way, in data/local/Converters.kt:

/**
 * How Room stores the types SQLite does not have. A date is stored as its
 * epoch day, a whole number, so `ORDER BY date` and `BETWEEN` compare numbers.
 */
class Converters {
    @ColumnTypeConverter
    fun localDateToEpochDay(date: LocalDate): Long = date.toEpochDay()

    @ColumnTypeConverter
    fun epochDayToLocalDate(epochDay: Long): LocalDate = LocalDate.ofEpochDay(epochDay)
}

7 November 2026 is stored as 20399, the number of days since 1 January 1970. You could store "2026-11-07" as text instead, and ISO dates even sort correctly as text; a number is smaller, compares faster, and cannot be written in some other format by a second piece of code. Whichever you choose, it is permanent: the next version of the app has to read what this one wrote.

The DAO: every question, in SQL

A DAO, data access object, is an interface of functions, each with the SQL it runs. Room writes the class. data/local/PriceDao.kt:

@Dao
interface PriceDao {

    @Query("SELECT * FROM prices ORDER BY commodity, modalPrice DESC")
    fun observeAll(): Flow<List<PriceEntity>>

    @Query("SELECT * FROM prices WHERE id = :id")
    fun observePrice(id: String): Flow<PriceEntity?>

    @Query("SELECT * FROM prices WHERE market = :market ORDER BY commodity")
    fun observePricesAt(market: String): Flow<List<PriceEntity>>

    @Query(
        """
        SELECT * FROM price_history
        WHERE market = :market AND commodity = :commodity AND date BETWEEN :from AND :to
        ORDER BY date
        """
    )
    fun observeHistory(market: String, commodity: String, from: LocalDate, to: LocalDate): Flow<List<PriceHistoryEntity>>

    @Query("SELECT * FROM prices")
    suspend fun getAll(): List<PriceEntity>

    /** Inserts each price, or replaces the row that already has its id. */
    @Upsert
    suspend fun upsertPrices(prices: List<PriceEntity>)

    @Upsert
    suspend fun upsertHistory(history: List<PriceHistoryEntity>)

    /**
     * Writes [prices] and returns how many differ from what was stored, all
     * in one transaction, so nothing can change the table in between.
     */
    @Transaction
    suspend fun upsertAndCountChanges(prices: List<PriceEntity>): Int {
        val before = getAll().associateBy { it.id }
        upsertPrices(prices)
        return prices.count { before[it.id] != it }
    }
}

:id in the SQL is the function's id parameter, and Room checks that every name matches. In Room 3 every DAO function must be suspend or return a Flow: there is no way to run a query on the main thread by accident.

ORDER BY is not decoration. SQL promises no order without it, so the list is now sorted by commodity, and within a commodity by the best modal price first. The chips follow, in alphabetical order: "Onion, Potato, Soybean, Tomato" where module 7 had "Onion, Tomato, Potato". A visible change, and a deliberate one.

The trap: @Insert on a refresh

@Insert reads like the obvious way to save prices. In a scratch copy, upsertPrices is @Insert instead of @Upsert. It builds; the app opens with the morning's prices; tap "Refresh":

E/AndroidRuntime(14814): FATAL EXCEPTION: main
E/AndroidRuntime(14814): android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: prices.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
E/AndroidRuntime(14814): 	at androidx.room3.EntityInsertAdapter.insert(EntityInsertAdapter.kt:93)
E/AndroidRuntime(14814): 	at com.riztechacademy.mandibhav.data.local.PriceDao_Impl$upsertPrices$2.invokeSuspend(PriceDao_Impl.kt:81)
E/AndroidRuntime(14814): 	at com.riztechacademy.mandibhav.data.local.PriceDao.upsertAndCountChanges$suspendImpl(PriceDao.kt:54)
E/AndroidRuntime(14814): 	at kotlinx.coroutines.internal.LimitedDispatcher$Worker.run(LimitedDispatcher.kt:124)
E/AndroidRuntime(14814): 	Suppressed: kotlinx.coroutines.internal.DiagnosticCoroutineContextException: [StandaloneCoroutine{Cancelling}@110f727, Dispatchers.Main.immediate]

The 4 pm Lasalgaon onion has the same id as the morning's, so inserting it breaks the primary key. Two more things in that trace are worth reading. The SQL ran on LimitedDispatcher$Worker, one of Room's own threads, not the main thread; the exception then travelled back to the coroutine that asked, viewModelScope on Dispatchers.Main.immediate, and nothing there caught it. And because upsertAndCountChanges is a transaction, a failure anywhere inside it undoes the whole of it, so a refresh can never leave half the new prices written. A refresh writes the same keys again every time, so it is an upsert, never an insert.

The database, and one of it

The database class lists the entities and hands out the DAOs; Room generates the rest. data/local/MandiBhavDatabase.kt:

@Database(
    entities = [PriceEntity::class, PriceHistoryEntity::class],
    version = 1,
)
@ColumnTypeConverters(Converters::class)
abstract class MandiBhavDatabase : RoomDatabase() {
    abstract fun priceDao(): PriceDao
}

Opening a database is expensive and a second instance on the same file brings back lesson 1's problem, so Hilt makes one, in di/DatabaseModule.kt:

@Provides
@Singleton
fun provideDatabase(@ApplicationContext context: Context): MandiBhavDatabase =
    Room.databaseBuilder<MandiBhavDatabase>(context, "mandi-bhav.db")
        .setDriver(AndroidSQLiteDriver())
        .addCallback(SeedDatabase)
        .build()

@Provides
fun providePriceDao(database: MandiBhavDatabase): PriceDao = database.priceDao()

setDriver is new in Room 3 and required. AndroidSQLiteDriver uses the SQLite that ships with Android, which costs nothing in APK size and differs in version from phone to phone; BundledSQLiteDriver, from androidx.sqlite:sqlite-bundled, puts one SQLite version inside your APK, the same on every phone, at the price of a native library per CPU type. Mandi Bhav's SQL is plain, so the built-in one is enough. The DAO provider is unscoped: the database hands back the same DAO each time, and asking it is cheap.

A new database is empty. Until module 9 brings a real first download, SeedDatabase is a RoomDatabase.Callback whose onCreate(connection: SQLiteConnection) inserts the morning's prices and their six days of history with plain SQL. Room calls it once, when it creates the file, inside the same transaction. It says so in its comment, and module 9 deletes it.

The repository, over the database

The fake "network" moves out of the repository into its own class, in data/PriceNetworkDataSource.kt, so that module 9 can replace exactly this piece:

/** Where the latest prices come from. Module 9 makes this a real server. */
interface PriceNetworkDataSource {
    /** Today's prices as the server has them now. */
    suspend fun fetchLatestPrices(): List<CommodityPrice>
}

class DemoPriceNetworkDataSource @Inject constructor() : PriceNetworkDataSource {
    override suspend fun fetchLatestPrices(): List<CommodityPrice> {
        delay(1_000)
        return SamplePrices.latest
    }
}

SamplePrices.latest is the morning's list with the three closing prices applied. Then data/RoomPriceRepository.kt:

@OptIn(ExperimentalCoroutinesApi::class)
class RoomPriceRepository @Inject constructor(
    private val priceDao: PriceDao,
    private val network: PriceNetworkDataSource,
) : PriceRepository {

    override fun todaysPrices(): Flow<List<CommodityPrice>> =
        priceDao.observeAll().map { rows -> rows.map { it.asModel() } }.distinctUntilChanged()

    override fun price(id: String): Flow<CommodityPrice?> =
        priceDao.observePrice(id).map { it?.asModel() }.distinctUntilChanged()

    override suspend fun refresh(): Int {
        val latest = network.fetchLatestPrices()
        return priceDao.upsertAndCountChanges(latest.map { it.asEntity() })
    }

pricesAt follows price. week(id) uses module 7's flatMapLatest: watch the price, and for each value watch its six days of history, from = price.date.minusDays(6) and to = price.date.minusDays(1), then add today's price as the seventh.

Read refresh carefully, because it is the whole of this module's design in three lines. It does not hand the new prices to anybody. It writes them to the database, and every screen that is watching a query sees them because the table changed. The database is the only source of truth; the network only ever feeds it. Lesson 4 is about what that buys when the network fails.

Module 7's DataModule comment said this module changes one line. It changes that line, and adds the network's binding below it:

/** One repository for the whole app, backed by the database. */
@Binds
@Singleton
abstract fun bindPriceRepository(repository: RoomPriceRepository): PriceRepository

/** The fake network, until module 9. */
@Binds
abstract fun bindPriceNetworkDataSource(source: DemoPriceNetworkDataSource): PriceNetworkDataSource

SamplePriceRepository and its test are deleted. No ViewModel, screen or ViewModel test changed.

Proving it on the emulator

Install, tap "Refresh", wait for "3 prices updated.", press Home, adb shell am kill com.riztechacademy.mandibhav, reopen from Recents:

'Onion at Nashik APMC, 2,560 rupees per quintal. …'
'Onion at Lasalgaon APMC, 2,525 rupees per quintal. …'

The same after adb shell am force-stop and a fresh launch. The process is gone; the rows are not.

Testing Room

A DAO test should run real SQL, because SQL is what can be wrong. There are two ways: an instrumented test in androidTest, which runs on an emulator or phone, or a Robolectric test in test, which runs on your computer's JVM with Robolectric standing in for Android, including its SQLite. Mandi Bhav uses Robolectric: the tests run in ./gradlew testDebugUnitTest with the rest, need no emulator, and take seconds; an instrumented suite would need a device attached for every run. What it costs: Robolectric is not a real phone, and the SQLite it runs is its own build, not the one on any particular phone. For plain SQL that has not mattered; for anything that depends on a device, test on one.

Add to the catalog robolectric = "4.17", androidxTestCore = "1.7.0", androidxTestExtJunit = "1.3.0", and testImplementation lines for org.robolectric:robolectric, androidx.test:core and androidx.test.ext:junit, plus in the android block:

testOptions {
    unitTests.isIncludeAndroidResources = true
}

The first run failed with a message worth knowing:

java.lang.UnsupportedOperationException: Failed to create a Robolectric sandbox: Android SDK 36 requires Java 21 (have Java 17)

Robolectric runs as the app's targetSdk, 36, and its Android 36 sandbox needs Java 21. The course builds on JDK 17, so app/src/test/resources/robolectric.properties pins the tests to API 35, which behaves the same for everything they touch:

sdk=35

The tests build an in-memory database, which disappears when it is closed:

@RunWith(AndroidJUnit4::class)
class PriceDaoTest {

    private val database = Room.inMemoryDatabaseBuilder<MandiBhavDatabase>(
        ApplicationProvider.getApplicationContext<Context>(),
    ).setDriver(AndroidSQLiteDriver()).build()

    private val dao = database.priceDao()

    @Test
    fun countsOnlyThePricesThatChanged() = runTest {
        dao.upsertPrices(SamplePrices.all.map { it.asEntity() })

        assertEquals(3, dao.upsertAndCountChanges(SamplePrices.latest.map { it.asEntity() }))
        assertEquals(0, dao.upsertAndCountChanges(SamplePrices.latest.map { it.asEntity() }))
    }

with an @After that closes it. Six DAO tests cover a round trip, upsert replacing by id, the order, the change count, the history range, and an open query emitting again after a write. RoomPriceRepositoryTest runs the real repository over the in-memory database with a fake network: six of the eight tests SamplePriceRepositoryTest had, rewritten, and one new one that reads like the bug it prevents: a second repository on the same database, as after process death, sees the refreshed price. ./gradlew testDebugUnitTest runs 69.

Check your work

Why the 4 pm prices disappeared in module 7. They lived in the process's memory, and process death takes the memory with it.

What Room checks that raw SQLite does not. Every query against the tables, when the app is built.

Why LocalDate needed a converter, and what is stored. SQLite has no date type; the epoch day, a whole number.

What @Upsert does that @Insert does not. Updates the row that already has the primary key instead of failing.

Where the trap's SQL ran, and why it still crashed the app. On a Room worker thread; the exception came back to viewModelScope on the main thread and nothing caught it.

What refresh does with the new prices. Writes them to the database; the screens see them because they are watching the tables.

Why the DAO tests use Robolectric here. They run with the other unit tests, on the JVM, with no emulator; the cost is that it is not a real phone.

Practice 4. It builds, and the first launch works, because the seed writes with its own SQL. The crash comes on the first refresh, with the UNIQUE constraint failed: prices.id message: upsertAndCountChanges calls upsertPrices, and every price in the latest list already has a row from the seed. Read from the bottom, the trace starts on a Room worker thread, passes through your upsertAndCountChanges, and ends in SQLite's constraint check.

Practice

1. Add Room, the two entities, the converter, the DAO, the database, DatabaseModule, the seed, the network data source, RoomPriceRepository and the new bindings. Delete SamplePriceRepository and its test. Write the DAO and repository tests with Robolectric and run the 69.
2. On the emulator, refresh, kill the process, reopen from Recents, and confirm ₹2,525. Then force-stop and reopen.
3. Delete @ColumnTypeConverters(Converters::class) and build. Count the errors and match each to a line.
4. Break it on purpose: change @Upsert on upsertPrices to @Insert, predict when it crashes, then run it and read the trace from the bottom up.
5. Open the database with Android Studio's Database Inspector while the app runs, find the prices table, and run SELECT * FROM prices WHERE commodity = 'Onion'. Find 20399 and say what it is.
6. Harder: change the date column to store text, "2026-11-07", by changing the converter. Decide whether ORDER BY date and BETWEEN still work, then think about what happens to a farmer who already has version 1 of the app installed with numbers in that column.

Official documentation

• Save data in a local database using Room — The guide, now written for Room 3: entities, DAOs and the database class.
• Room 3.0 release notes — What changed from Room 2, the driver, and the current version.
• Accessing data using Room DAOs — Every kind of DAO function, including upserts and transactions.
• Robolectric — The project's own site: what it simulates, configuration, and supported SDKs.

Next: queries, a relation between markets and prices, and changing a database that is already on people's phones.