Queries, relations and migrations

Arjun trades onions, and the Markets tab tells him "Lasalgaon APMC, 2 prices today". He wants to know where Lasalgaon is: which district, and so how far his truck goes. A market is a thing of its own, with facts that belong to it and not to any price, so it gets a table of its own, and the Market screen reads a market together with its prices through a relation. That is a change to the shape of a database that is already on Kavita's phone, full of the 4 pm prices she refreshed yesterday. This lesson writes the queries, measures what a key is worth on a few thousand rows, adds the relation, and then changes the schema three ways on the emulator: without a migration, with Room's destructive fallback, and properly. Only one of them keeps Kavita's prices.

Queries, as a reference

Every @Query in Mandi Bhav is plain SQLite. The parts you will use most:

Room checks each one at build time: a misspelt column, a parameter with no :name, or a return type that cannot hold the result stops the build with the line number.

One behaviour is not visible in the SQL: Room watches tables, not rows. An open Flow query runs again whenever anything writes to a table it reads. A DAO test pins it down: watch Lasalgaon onion, change Pune onion, and the Lasalgaon query answers again:

@Test
fun aQueryRunsAgainWhenAnyRowOfItsTableChanges() = runTest {
    // Room watches tables, not rows: a change to Pune onion re-runs the
    // Lasalgaon query, which answers the same thing again. This is why
    // the repository adds distinctUntilChanged.
    dao.upsertPrices(listOf(SamplePrices.lasalgaonOnion.asEntity(), SamplePrices.puneOnion.asEntity()))
    val seen = mutableListOf<Long?>()
    val watching = backgroundScope.launch(UnconfinedTestDispatcher(testScheduler)) {
        dao.observePrice(SamplePrices.lasalgaonOnion.id).collect { seen += it?.modalPrice }
    }
    while (seen.isEmpty()) yield()

    dao.upsertPrices(listOf(SamplePrices.puneOnion.copy(modalPrice = 2_250).asEntity()))
    while (seen.size < 2) yield()

    assertEquals(listOf<Long?>(2_450, 2_450), seen)
    watching.cancel()
}

[2450, 2450]: an identical answer, delivered again. Module 7's distinctUntilChanged() in the repository is what stops that reaching a screen. Room's queries run on its own threads, which is why the test waits with yield() rather than assuming the answer is there at once.

What a key is worth

The week query asks price_history for one market, one commodity and a range of dates. The table's primary key is exactly those three columns, and SQLite builds an index for a primary key, so it can jump straight to the six rows. To see what that is worth, a scratch build filled two tables with the same 6,570 rows, nine price series for 730 days: the real price_history, and a copy whose only key is an INTEGER PRIMARY KEY AUTOINCREMENT id, the shape you get if you put @PrimaryKey(autoGenerate = true) val id: Long on every entity out of habit. Then it ran the week query 500 times on each, on the emulator:

About eight times slower, and the gap grows with the table: two years of history is small, and a farmer's phone keeps it for as long as the app is installed. The reason is in SQLite's query plan. The Android driver refused EXPLAIN QUERY PLAN through a prepared statement, with "Queries can be performed using SQLiteDatabase query or rawQuery methods only", so the database file was pulled off the emulator with adb exec-out run-as … cat databases/mandi-bhav.db and opened with sqlite3 on the computer:

`--SEARCH price_history USING INDEX sqlite_autoindex_price_history_1 (market=? AND commodity=? AND date>? AND date<?)

|--SCAN history_by_id
`--USE TEMP B-TREE FOR ORDER BY

SEARCH … USING INDEX reads only the matching rows, in order. SCAN reads every row in the table, and then sorts the six it kept. When a table has a natural key, make it the primary key. When a query searches by columns that are not the key, add an index: @Entity(indices = [Index("market")]). What an index costs: every insert and update must also update the index, and it takes space. It earns its place on a column you search or sort by, on a table that grows; on the nine-row prices table it would earn nothing.

Markets get a table

data/local/MarketEntity.kt:

/** One row of `markets`: an APMC market and the district it is in. */
@Entity(tableName = "markets")
data class MarketEntity(
    @PrimaryKey val name: String,
    val district: String,
)

/**
 * A market and its prices today, read together. Room runs two queries, one
 * for the markets and one for all their prices, inside one transaction, and
 * matches each price's `market` column to a market's `name`.
 */
data class MarketWithPrices(
    @Embedded val market: MarketEntity,
    @Relation(parentColumns = ["name"], entityColumns = ["market"])
    val prices: List<PriceEntity>,
)

MarketWithPrices is not a table. It is a shape for a query result: @Embedded puts the market's columns in it, and @Relation tells Room to fetch every price whose market equals this market's name. In Room 3 both sides are arrays, so a relation can join on more than one column. The DAO asks for it:

/** Every market, each with its prices today, by name. */
@Transaction
@Query("SELECT * FROM markets ORDER BY name")
fun observeMarketsWithPrices(): Flow<List<MarketWithPrices>>

/** One market and its prices today, or null if there is no such market. */
@Transaction
@Query("SELECT * FROM markets WHERE name = :name")
fun observeMarket(name: String): Flow<MarketWithPrices?>

@Transaction matters here: the relation is two queries, and without a transaction a refresh could land between them and the screen would show a market with half-old prices. The Flow watches both tables.

Why a relation and not a JOIN? A JOIN returns flat rows, one per price with the market's columns repeated on each, and a market with no prices today disappears from an inner join. The relation returns what the screen wants, a market and a list, and keeps markets with an empty list. Its cost is a second query, and that it only follows equality on columns. For "the Markets tab: each market, its district and how many prices", it fits exactly, and it replaces module 6's marketSummaries, which grouped the price list in Kotlin.

PriceRepository gains markets(): Flow<List<Market>> and replaces pricesAt(market) with market(name): Flow<Market?>, where Market is a new plain model with a name, a district and prices. MarketsViewModel maps markets to MarketSummary(name, district, priceCount), and the list says "Nashik district, 2 prices today"; MarketViewModel reads market(name) and the screen shows "Nashik district" above the home-market chip. FakePriceRepository gains the two functions too, and the compiler lists every place that called pricesAt.

There is a quiet trap in starting from the markets table: a price whose market has no row never appears on the Markets tab. Nothing crashes; it is not listed. So the refresh, inside the same transaction, adds a row for any market it has not seen, with an empty district:

// A price whose market has no row would never appear on the Markets
// tab, because the relation starts from the markets table.
insertMarketsIfMissing(prices.map { it.market }.distinct().map { MarketEntity(name = it, district = "") })

where insertMarketsIfMissing is @Insert(onConflict = OnConflictStrategy.IGNORE). A repository test refreshes with a price from "Solapur APMC" and finds it under that market.

Changing a database that is already out there

Add MarketEntity::class to entities and the app no longer matches the database on Kavita's phone. Room stores a version number in the file and a hash of the schema; the version must go up, from 1 to 2. Do only that, install the new app over the old one on the emulator, where version 1 holds the refreshed 4 pm prices, and open it:

E/AndroidRuntime(16131): java.lang.IllegalStateException: A migration from 1 to 2 was required but not found. Please provide the necessary Migration path via RoomDatabase.Builder.addMigration(...) or allow for destructive migrations via one of the RoomDatabase.Builder.fallbackToDestructiveMigration* functions.
E/AndroidRuntime(16131): 	at androidx.room3.BaseRoomConnectionManager.onMigrate$suspendImpl(RoomConnectionManager.kt:240)

On every launch, for every user who updates. The message offers two ways out. The first one people reach for is the second, because it is one line:

.fallbackToDestructiveMigration(dropAllTables = true)

It no longer crashes. This is the Prices tab it opens to:

'Nashik APMC, your home market, comes first.'
'No prices match "".'
'No prices match these filters.'  'Clear filters'

Room dropped every table and created them again, empty. The 4 pm prices are gone. So is the seed, because onCreate did not run for a database that already existed; the Markets tab is blank. And the screen blames Kavita: module 7's "no results" message says her filters match nothing, and offers to clear filters she never set. The home market survived, because it is in DataStore, not in the database. Destructive fallback is not a migration. It is a decision to delete every user's data, and the cost is paid by the person with the slowest connection, who now has nothing to show until a refresh succeeds. It is defensible for a pure cache you can rebuild from a server in a second, during development, or with fallbackToDestructiveMigrationFrom(…) for one old version you have decided to abandon. It is not defensible as a way to make a crash go away.

A real migration

A migration is SQL that turns version 1 into version 2 on the phone, keeping what is there. data/local/Migrations.kt:

/**
 * Version 1 to 2: markets get a table of their own, with the district each
 * is in. Every market that already has prices gets a row, so a farmer who
 * updates the app keeps everything the old version saved.
 *
 * Written out in full SQL, never built from the app's constants: this code
 * must do exactly what it did on the day version 2 shipped, for as long as
 * any phone still has a version 1 database.
 */
val MIGRATION_1_2 = object : Migration(1, 2) {
    override suspend fun migrate(connection: SQLiteConnection) {
        connection.execSQL(
            "CREATE TABLE IF NOT EXISTS `markets` (`name` TEXT NOT NULL, `district` TEXT NOT NULL, PRIMARY KEY(`name`))"
        )
        connection.execSQL("INSERT INTO markets (name, district) SELECT DISTINCT market, '' FROM prices")
        connection.execSQL(
            "UPDATE markets SET district = 'Nashik' WHERE name IN ('Lasalgaon APMC', 'Nashik APMC')"
        )
        connection.execSQL("UPDATE markets SET district = 'Pune' WHERE name = 'Pune (Gultekdi) APMC'")
        connection.execSQL("UPDATE markets SET district = 'Thane' WHERE name = 'Vashi APMC, Navi Mumbai'")
    }
}

execSQL is an extension from androidx.sqlite. Where does the CREATE TABLE come from? Not from memory: copy the createSql for the table from the version 2 schema file Room exported, app/schemas/…/2.json, so it matches what Room expects to the character. Then register it, .addMigrations(MIGRATION_1_2) on the builder, and the seed callback gains an insert for the four markets, so a fresh install starts at version 2 complete.

Install over the same version 1 database: the Markets tab says "Lasalgaon APMC, Nashik district, 2 prices today", and the Prices tab still has Lasalgaon onion at ₹2,525.

Room could not have written this migration for you. Its automatic migrations, which lesson 4 uses, can create the table from the two schemas, but they cannot know that every existing market needs a row or which district each is in. Adding data is a manual migration.

Testing the migration

A migration runs once per phone, on data you cannot see, so it is the code most worth testing and least often tested. Room's room3-testing artifact has MigrationTestHelper, which builds a database exactly as an old version left it, from that version's exported JSON. Add testImplementation(libs.androidx.room.testing), with androidx-room-testing = { group = "androidx.room3", name = "room3-testing", version.ref = "room" } in the catalog. Then, in data/local/MigrationTest.kt:

@get:Rule
val helper = MigrationTestHelper(
    instrumentation = instrumentation,
    databaseClass = MandiBhavDatabase::class,
    driver = AndroidSQLiteDriver(),
    file = instrumentation.targetContext.getDatabasePath("migration-test.db"),
)

@Test
fun version1To2KeepsThePricesAndGivesEachMarketItsDistrict() = runTest {
    // Version 1, with the 4 pm Lasalgaon price a farmer refreshed before updating.
    helper.createDatabase(1).apply {
        execSQL(
            "INSERT INTO prices (id, commodity, market, date, minPrice, maxPrice, modalPrice) " +
                "VALUES ('lasalgaon-onion-2026-11-07', 'Onion', 'Lasalgaon APMC', 20399, 1200, 2900, 2525)"
        )
        close()
    }

    // Fails here if the migrated schema differs from version 2's JSON.
    val migrated = helper.runMigrationsAndValidate(2, listOf(MIGRATION_1_2))

followed by queries that check the price is still ₹2,525 and the markets have their districts. The test inserts with SQL, not the DAO, because the DAO is written for the newest schema, not version 1's.

It failed first, with this:

Cannot find the schema file in the assets folder … Missing file: com.riztechacademy.mandibhav.data.local.MandiBhavDatabase/1.json

The helper reads the schemas as Android assets. The Room plugin adds them for instrumented tests only, and Robolectric reads the debug build's merged assets, not the unit tests', so adding them to the test source set did nothing. They go in debug, which also means only debug APKs carry the few kilobytes of JSON:

sourceSets {
    // The migration test reads the exported schemas as assets. The Room
    // plugin adds them for instrumented tests only, and Robolectric reads
    // the debug build's merged assets, so they go in debug. Release
    // builds do not carry them.
    getByName("debug").assets.directories.add("$projectDir/schemas")
}

Older answers online write assets.srcDir(…); in AGP 9 that builds with "'fun srcDir(srcDir: Any): Any' is deprecated. Use directories mutable set instead."

Now break the migration, to see what the helper catches: create district as TEXT instead of TEXT NOT NULL, one missing phrase:

java.lang.IllegalStateException: Migration didn't properly handle:  markets

Expected:

TableInfo {
    name = 'markets',
    columns = {
        Column {
           name = 'district',
           type = 'TEXT',
           affinity = '2',
           notNull = 'true',

and under "Found", notNull = 'false'. The app runs the same check when it opens the database after migrating, so on phones this one missing phrase would be an exception on launch for every user who updated, found by them instead of by a test. ./gradlew testDebugUnitTest runs 74.

Check your work

What ORDER BY changes. Without it SQL promises no order; with it the list is sorted the way the query says.

Why the Lasalgaon query answered twice. Room re-runs a query when any row of a table it reads changes; the second answer was identical.

What the composite primary key did for the week query. Let SQLite search the index for six rows instead of scanning 6,570: about 30 microseconds against 250.

Why @Transaction on a relation query. The relation is two queries; the transaction stops a write landing between them.

What happened without a migration. A crash on every launch: "A migration from 1 to 2 was required but not found."

What destructive fallback cost. Every price and the seed, and a screen that blamed the user's filters.

Why the migration's SQL is written out in full. It must keep doing what it did on the day it shipped, whatever the app's constants become later.

Why the schemas went into the debug source set. Robolectric reads the debug build's merged assets; test assets were ignored.

Practice 4. The Markets tab no longer lists Solapur, or any market the seed and the migration did not know, and of the nine repository tests exactly one fails, aPriceFromANewMarketStillReachesTheMarketsTab, with a NullPointerException at the !!, because market("Solapur APMC") is null. The Prices tab still shows the Solapur price, because it reads prices directly. Two screens disagree and nothing crashed, which is why it is pinned by a test.

Practice

1. Add MarketEntity, MarketWithPrices, the two relation queries, insertMarketsIfMissing, the Market model and the repository functions; bump the version to 2; write MIGRATION_1_2 from the exported 2.json; add the seed's markets and the migration test. Run the 74 tests.
2. On the emulator, install version 1, refresh, then install version 2 three times: with no migration, with destructive fallback, and with the migration. Record what each shows. Reinstall version 1 between runs.
3. Pull the database off the emulator with adb exec-out run-as com.riztechacademy.mandibhav cat databases/mandi-bhav.db > mandi.db and run EXPLAIN QUERY PLAN on the week query with sqlite3.
4. Break it on purpose: remove the insertMarketsIfMissing line from the refresh and run the repository tests. Predict which fails and what the two tabs would show.
5. Write the same "markets with their price counts" as one JOIN with GROUP BY into a small result class, and compare what happens to a market with no prices today.
6. Harder: make prices.market a real foreign key to markets.name, so SQLite itself refuses a price for an unknown market. SQLite cannot add a foreign key to an existing table, so write the migration that creates a new table, copies the rows, drops the old one and renames the new one, and test it from version 2.

Official documentation

• Write asynchronous DAO queries — Observable queries, and why a Flow query re-runs when its table changes.
• Define relationships between objects — @Embedded, @Relation, one-to-many and many-to-many.
• Migrate your Room database — Manual and automatic migrations, exporting schemas, and MigrationTestHelper.
• The SQLite query planner — SQLite's own explanation of indexes, searches and scans.
• EXPLAIN QUERY PLAN — How to read the SEARCH and SCAN lines.

Next: the database as the single source of truth, and an app that still works when the train goes into a cutting.