Room, SQLite, Migrations, Transactions & Relations

Real-world analogy

What is it?

A Room transaction (@Transaction) runs several DAO queries as one atomic unit — all succeed or roll back together, so related writes never end up half-done.

Real-world relevance

Hazira Khata (school attendance/management app) stores student attendance records locally in Room for 500+ schools with intermittent connectivity. Migrations are critical — when a school added a 'late arrival' status, MIGRATION_1_2 added the column with a DEFAULT of 'present' to preserve historical records. The attendance DAO returns Flow> scoped to today's date — the UI updates instantly when the teacher marks a student present, even before the sync completes.

Key points

• @Database annotation — @Database(entities = [Order::class, Employee::class], version = 3, exportSchema = true) — defines the schema version and all entity classes. exportSchema = true exports the schema to a JSON file for migration verification and version history tracking. Always increment version when schema changes.
• @Entity and primary keys — @Entity(tableName = 'orders', indices = [Index(value = ['status', 'created_at'])]) — maps Kotlin class to a database table. @PrimaryKey(autoGenerate = true) for auto-increment IDs or @PrimaryKey for UUIDs. @ColumnInfo(name = 'created_at') maps field names to snake_case column names.
• @Dao interface — @Dao annotates the query interface — Room generates the implementation at compile-time. @Insert(onConflict = OnConflictStrategy.REPLACE) for upserts. @Update, @Delete take entity instances. @Query for custom SQL. @Transaction for multi-step operations. Returns suspend fun for one-shot, Flow for reactive.
• Flow from Room — Returning Flow> from a DAO query makes Room emit a new list every time the underlying table changes. Collect in ViewModel with stateIn(). No polling needed — Room uses SQLite's invalidation tracker to push updates. Combine multiple DAOs' Flows with combine() for derived state.
• TypeConverters — @TypeConverter converts non-primitive types SQLite can't store directly — List, enums, dates, custom objects. @TypeConverters(Converters::class) on the @Database class registers them globally. Prefer storing enums as strings (not ints) — string is stable if enum ordering changes.
• Migrations — val MIGRATION_2_3 = object : Migration(2, 3) { override fun migrate(db: SupportSQLiteDatabase) { db.execSQL('ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0') } }. Add to .addMigrations(MIGRATION_2_3) in the database builder. Missing migrations force a destructive fallback — all data lost.
• Transactions with @Transaction — @Transaction on a DAO function wraps all contained database operations in a single SQLite transaction — all succeed or all rollback. Essential for multi-table writes (create order + create order items atomically). Also required when querying entities with @Relation to prevent inconsistent reads.
• @Relation for one-to-many — data class OrderWithItems(val order: Order, val items: List) — use @Relation(parentColumn = 'id', entityColumn = 'order_id') on the items field. Annotate the DAO function with @Transaction — Room issues multiple queries internally and @Transaction ensures data consistency.
• @Embedded for composition — @Embedded embeds all columns of a nested object directly into the parent table row — no join needed. Useful for value objects like Address embedded into Employee. Use prefix parameter for column name disambiguation: @Embedded(prefix = 'billing_') for multiple embedded instances.
• Conflict strategies — OnConflictStrategy.REPLACE — delete old row, insert new (triggers cascade deletions). IGNORE — skip if conflict. ABORT — rollback transaction (default). FAIL — fail but don't rollback outer transaction. For offline-first upserts, REPLACE is common but watch for cascade delete side effects on related tables.
• Testing Room — Use in-memory database for unit tests: Room.inMemoryDatabaseBuilder(context, AppDatabase::class.java).build(). No migration needed. Run DAO tests with runTest (coroutine test). For migration testing, use MigrationTestHelper with schema export files to verify migrations are correct before shipping.
• Room performance tips — Add indices on columns used in WHERE and JOIN clauses. Use @Query with specific columns instead of SELECT * to reduce memory. Batch inserts with a single @Insert call taking List. Use LIMIT in queries for pagination. Avoid accessing Room on the main thread — Room enforces this by default (allowMainThreadQueries() is test-only).

Code example

// 1. Entity definition
@Entity(
    tableName = "orders",
    indices = [
        Index(value = ["status"]),
        Index(value = ["assigned_to_id", "created_at"])
    ]
)
data class OrderEntity(
    @PrimaryKey val id: String,
    @ColumnInfo(name = "title") val title: String,
    @ColumnInfo(name = "status") val status: String, // stored as string, not int
    @ColumnInfo(name = "assigned_to_id") val assignedToId: String?,
    @ColumnInfo(name = "created_at") val createdAt: Long = System.currentTimeMillis(),
    @ColumnInfo(name = "priority") val priority: Int = 0,
    @ColumnInfo(name = "tags") val tags: List<String> = emptyList() // TypeConverter needed
)

// 2. TypeConverter for List<String>
class Converters {
    @TypeConverter
    fun fromStringList(value: List<String>): String =
        Json.encodeToString(ListSerializer(String.serializer()), value)

    @TypeConverter
    fun toStringList(value: String): List<String> =
        Json.decodeFromString(ListSerializer(String.serializer()), value)
}

// 3. DAO interface
@Dao
interface OrderDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsertAll(orders: List<OrderEntity>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(order: OrderEntity)

    @Update
    suspend fun update(order: OrderEntity)

    @Delete
    suspend fun delete(order: OrderEntity)

    @Query("SELECT * FROM orders ORDER BY created_at DESC")
    fun observeAll(): Flow<List<OrderEntity>>

    @Query("SELECT * FROM orders WHERE id = :id")
    suspend fun getById(id: String): OrderEntity?

    @Query("SELECT * FROM orders WHERE status = :status AND assigned_to_id = :employeeId")
    fun observeByStatusAndEmployee(status: String, employeeId: String): Flow<List<OrderEntity>>

    @Transaction
    @Query("SELECT * FROM orders WHERE id = :orderId")
    suspend fun getOrderWithItems(orderId: String): OrderWithItems?

    @Query("DELETE FROM orders WHERE status = 'completed' AND created_at < :cutoffTime")
    suspend fun deleteOldCompleted(cutoffTime: Long): Int
}

// 4. Relations
@Entity(tableName = "order_items")
data class OrderItemEntity(
    @PrimaryKey val itemId: String,
    @ColumnInfo(name = "order_id") val orderId: String,
    val name: String,
    val quantity: Int
)

data class OrderWithItems(
    @Embedded val order: OrderEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "order_id"
    )
    val items: List<OrderItemEntity>
)

// 5. Database class
@Database(
    entities = [OrderEntity::class, OrderItemEntity::class],
    version = 3,
    exportSchema = true
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun orderDao(): OrderDao
}

// 6. Migration
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL(
            "ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0"
        )
        db.execSQL(
            "CREATE INDEX IF NOT EXISTS index_orders_status ON orders(status)"
        )
    }
}

// 7. Database builder (in Hilt module)
@Provides @Singleton
fun provideDatabase(@ApplicationContext context: Context): AppDatabase =
    Room.databaseBuilder(context, AppDatabase::class.java, "fieldops.db")
        .addMigrations(MIGRATION_2_3)
        .fallbackToDestructiveMigrationOnDowngrade()
        .build()

// 8. Atomic multi-table write (manual transaction)
@Dao
interface OrderTransactionDao {
    @Transaction
    suspend fun insertOrderWithItems(order: OrderEntity, items: List<OrderItemEntity>) {
        upsert(order)
        upsertItems(items)
        // If either fails, SQLite rolls back both — atomicity guaranteed
    }

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(order: OrderEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsertItems(items: List<OrderItemEntity>)
}

// 9. Testing Room in-memory
@RunWith(AndroidJUnit4::class)
class OrderDaoTest {
    private lateinit var db: AppDatabase
    private lateinit var dao: OrderDao

    @Before
    fun setup() {
        db = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        ).build()
        dao = db.orderDao()
    }

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

    @Test
    fun upsertAndObserve() = runTest {
        val order = OrderEntity("1", "Fix pump", "open", null)
        dao.upsert(order)
        val result = dao.observeAll().first()
        assertThat(result).hasSize(1)
        assertThat(result[0].title).isEqualTo("Fix pump")
    }
}

Line-by-line walkthrough

1. 1. OrderEntity's tags field is List — SQLite has no list type so the Converters TypeConverter serialises it to JSON string on write and deserialises on read.
2. 2. @Index on ['assigned_to_id', 'created_at'] creates a composite index — queries filtering by both columns use the index instead of a full table scan.
3. 3. observeAll() returns Flow> — the absence of 'suspend' is intentional; Flow is lazy and starts collecting when subscribed.
4. 4. observeByStatusAndEmployee takes primitive parameters — Room validates these at compile time, catching typos in parameter names before the app ships.
5. 5. @Transaction on getOrderWithItems ensures Room's two internal queries (one for OrderEntity, one for OrderItemEntity) run atomically.
6. 6. @Relation parentColumn='id' entityColumn='order_id' — Room matches order.id to orderItem.order_id and groups items under their parent order automatically.
7. 7. MIGRATION_2_3 uses ALTER TABLE — SQLite supports adding columns but not removing or renaming them (without rebuilding the table), so migrations must plan accordingly.
8. 8. fallbackToDestructiveMigrationOnDowngrade() only triggers on version downgrade (reinstalling an older APK) — it does NOT affect normal upgrades.
9. 9. insertOrderWithItems is annotated @Transaction and calls two @Insert DAOs — if the second insert fails, SQLite rolls back the first insert automatically.
10. 10. inMemoryDatabaseBuilder creates a database backed by memory — all data is lost when the database is closed, making teardown trivial and tests fully isolated.

Spot the bug

// Find 4 bugs in this Room setup
@Entity(tableName = "employees")
data class EmployeeEntity(
    @PrimaryKey val id: String,
    val name: String,
    val department: Department,  // Bug 1
    val skills: List<String>     // Bug 2
)

@Dao
interface EmployeeDao {
    @Insert
    suspend fun insert(employee: EmployeeEntity)

    @Query("SELECT * FROM employees WHERE departement = :dept")  // Bug 3
    fun getByDepartment(dept: String): List<EmployeeEntity>      // Bug 4
}

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• Room Persistence Library — Android Developers (Android Developers)
• Defining relationships in Room (Android Developers)
• Testing Room migrations (Android Developers)
• Room database — Advanced patterns and performance (Android Developers Medium)
• Room TypeConverters — Best practices (ProAndroidDev)