---
name: room-database
description: "Implement Room database persistence on Android with @Entity, @Dao, @Database, Flow-based reactive queries, TypeConverters for complex types, migration strategies (auto and manual), relations with @Embedded/@Relation and junction tables, in-memory database testing, and Hilt integration. Use when adding local persistence, defining database schemas, writing DAOs, handling migrations, or testing Room queries."
---

# Room Database

Room persistence library patterns for Android targeting Room 2.6+ with
KSP, Kotlin coroutines, and Flow-based reactive queries.

## Contents

- [Entity Definition](#entity-definition)
- [DAO Interface](#dao-interface)
- [Database Setup](#database-setup)
- [Reactive Queries with Flow](#reactive-queries-with-flow)
- [TypeConverters](#typeconverters)
- [Relations](#relations)
- [Migration Strategies](#migration-strategies)
- [Hilt Integration](#hilt-integration)
- [Testing with In-Memory Database](#testing-with-in-memory-database)
- [Do's and Don'ts](#dos-and-donts)
- [Troubleshooting](#troubleshooting)
- [Review Checklist](#review-checklist)

## Entity Definition

```kotlin
@Entity(
    tableName = "flights",
    indices = [
        Index(value = ["origin_code", "destination_code"]),
        Index(value = ["departure_time"]),
    ],
)
data class FlightEntity(
    @PrimaryKey
    val id: String,
    @ColumnInfo(name = "origin_code")
    val originCode: String,
    @ColumnInfo(name = "origin_name")
    val originName: String,
    @ColumnInfo(name = "destination_code")
    val destinationCode: String,
    @ColumnInfo(name = "destination_name")
    val destinationName: String,
    @ColumnInfo(name = "departure_time")
    val departureTime: String,
    @ColumnInfo(name = "arrival_time")
    val arrivalTime: String,
    @ColumnInfo(name = "price_amount")
    val priceAmount: Double,
    @ColumnInfo(name = "price_currency")
    val priceCurrency: String,
    val status: String,
    @ColumnInfo(name = "updated_at", defaultValue = "0")
    val updatedAt: Long = System.currentTimeMillis(),
)
```

### Composite Primary Key

```kotlin
@Entity(
    tableName = "booking_passengers",
    primaryKeys = ["booking_id", "passenger_id"],
)
data class BookingPassengerEntity(
    @ColumnInfo(name = "booking_id")
    val bookingId: String,
    @ColumnInfo(name = "passenger_id")
    val passengerId: String,
    @ColumnInfo(name = "seat_number")
    val seatNumber: String?,
)
```

## DAO Interface

```kotlin
@Dao
interface FlightDao {

    @Query("SELECT * FROM flights WHERE origin_code = :origin AND destination_code = :destination ORDER BY departure_time ASC")
    fun getFlights(origin: String, destination: String): Flow<List<FlightEntity>>

    @Query("SELECT * FROM flights WHERE id = :id")
    fun getFlightById(id: String): Flow<FlightEntity?>

    @Query("SELECT * FROM flights WHERE id = :id")
    suspend fun getFlightByIdOnce(id: String): FlightEntity?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertFlight(flight: FlightEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertFlights(flights: List<FlightEntity>)

    @Upsert
    suspend fun upsertFlights(flights: List<FlightEntity>)

    @Update
    suspend fun updateFlight(flight: FlightEntity)

    @Delete
    suspend fun deleteFlight(flight: FlightEntity)

    @Query("DELETE FROM flights WHERE id = :id")
    suspend fun deleteFlightById(id: String)

    @Query("DELETE FROM flights WHERE updated_at < :threshold")
    suspend fun deleteStaleFlights(threshold: Long)

    @Query("SELECT COUNT(*) FROM flights")
    fun getFlightCount(): Flow<Int>

    @Transaction
    suspend fun replaceAllFlights(flights: List<FlightEntity>) {
        deleteAllFlights()
        insertFlights(flights)
    }

    @Query("DELETE FROM flights")
    suspend fun deleteAllFlights()
}
```

### @Upsert vs @Insert(onConflict = REPLACE)

| Strategy | Behavior | Use When |
|----------|----------|----------|
| `@Upsert` | Insert if absent, update if present (by PK) | Syncing remote data; preserves non-conflicting columns |
| `@Insert(REPLACE)` | Delete + re-insert on conflict | Simpler; triggers delete cascade |
| `@Insert(IGNORE)` | Skip on conflict | Inserting if not exists, no update needed |

## Database Setup

```kotlin
@Database(
    entities = [
        FlightEntity::class,
        BookingEntity::class,
        BookingPassengerEntity::class,
    ],
    version = 2,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),
    ],
    exportSchema = true,
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun flightDao(): FlightDao
    abstract fun bookingDao(): BookingDao
}
```

Always set `exportSchema = true` and configure the schema export directory in
`build.gradle.kts`:

```kotlin
ksp {
    arg("room.schemaLocation", "$projectDir/schemas")
}
```

## Reactive Queries with Flow

DAO methods returning `Flow` automatically emit new values when the underlying
table changes.

```kotlin
// DAO returns Flow
@Query("SELECT * FROM flights WHERE origin_code = :origin")
fun getFlightsByOrigin(origin: String): Flow<List<FlightEntity>>

// Repository maps to domain models
class FlightRepositoryImpl @Inject constructor(
    private val dao: FlightDao,
    private val mapper: FlightMapper,
) : FlightRepository {

    override fun getFlights(origin: String): Flow<List<Flight>> =
        dao.getFlightsByOrigin(origin)
            .map { entities -> entities.map(mapper::toDomain) }
            .flowOn(Dispatchers.IO)
}

// ViewModel collects in viewModelScope
@HiltViewModel
class FlightListViewModel @Inject constructor(
    private val repository: FlightRepository,
) : ViewModel() {

    val flights: StateFlow<List<Flight>> = repository.getFlights("IST")
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5_000),
            initialValue = emptyList(),
        )
}

// Compose collects as state
@Composable
fun FlightListScreen(viewModel: FlightListViewModel = hiltViewModel()) {
    val flights by viewModel.flights.collectAsStateWithLifecycle()
    LazyColumn {
        items(flights, key = { it.id }) { flight ->
            FlightCard(flight = flight)
        }
    }
}
```

## TypeConverters

For types Room does not natively support (lists, enums, dates).

```kotlin
class Converters {

    private val json = Json { ignoreUnknownKeys = true }

    @TypeConverter
    fun fromStringList(value: List<String>): String =
        json.encodeToString(value)

    @TypeConverter
    fun toStringList(value: String): List<String> =
        json.decodeFromString(value)

    @TypeConverter
    fun fromInstant(value: Instant?): Long? = value?.toEpochMilliseconds()

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

    @TypeConverter
    fun fromFlightStatus(value: FlightStatus): String = value.name

    @TypeConverter
    fun toFlightStatus(value: String): FlightStatus = FlightStatus.valueOf(value)
}
```

Register in `@Database`:

```kotlin
@Database(entities = [...], version = 1)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase()
```

## Relations

### @Embedded

Flatten a nested object into the parent table columns:

```kotlin
data class AddressEntity(
    val street: String,
    val city: String,
    val country: String,
)

@Entity(tableName = "passengers")
data class PassengerEntity(
    @PrimaryKey val id: String,
    val name: String,
    @Embedded(prefix = "address_")
    val address: AddressEntity,
)
// Creates columns: id, name, address_street, address_city, address_country
```

### @Relation (One-to-Many)

```kotlin
data class BookingWithPassengers(
    @Embedded val booking: BookingEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "booking_id",
    )
    val passengers: List<BookingPassengerEntity>,
)

@Dao
interface BookingDao {
    @Transaction
    @Query("SELECT * FROM bookings WHERE id = :bookingId")
    fun getBookingWithPassengers(bookingId: String): Flow<BookingWithPassengers?>
}
```

### Junction Table (Many-to-Many)

```kotlin
@Entity(
    tableName = "flight_tags",
    primaryKeys = ["flight_id", "tag_id"],
)
data class FlightTagCrossRef(
    @ColumnInfo(name = "flight_id") val flightId: String,
    @ColumnInfo(name = "tag_id") val tagId: String,
)

data class FlightWithTags(
    @Embedded val flight: FlightEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = FlightTagCrossRef::class,
            parentColumn = "flight_id",
            entityColumn = "tag_id",
        ),
    )
    val tags: List<TagEntity>,
)
```

Always annotate relation queries with `@Transaction` to ensure consistent reads.

## Migration Strategies

### Auto Migration (Room 2.4+)

For simple schema changes (add column, add table, add index):

```kotlin
@Database(
    entities = [FlightEntity::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),
        AutoMigration(from = 2, to = 3, spec = Migration2To3::class),
    ],
)
abstract class AppDatabase : RoomDatabase()

// Spec needed for column/table renames or deletes
@RenameColumn(tableName = "flights", fromColumnName = "price", toColumnName = "price_amount")
class Migration2To3 : AutoMigrationSpec
```

### Manual Migration

For complex changes (data transformation, merging tables):

```kotlin
val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS bookings_new (
                id TEXT NOT NULL PRIMARY KEY,
                flight_id TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'PENDING',
                created_at INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (flight_id) REFERENCES flights(id) ON DELETE CASCADE
            )
        """)
        db.execSQL("""
            INSERT INTO bookings_new (id, flight_id, status, created_at)
            SELECT id, flight_id, status, 0 FROM bookings
        """)
        db.execSQL("DROP TABLE bookings")
        db.execSQL("ALTER TABLE bookings_new RENAME TO bookings")
    }
}

// Register in Hilt module
Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addMigrations(MIGRATION_3_4)
    .build()
```

### Fallback to Destructive Migration

Only for development or non-critical data:

```kotlin
Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .fallbackToDestructiveMigration()
    .build()
```

## Hilt Integration

```kotlin
@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {

    @Provides
    @Singleton
    fun provideDatabase(@ApplicationContext context: Context): AppDatabase =
        Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "app.db",
        )
        .addMigrations(MIGRATION_3_4)
        .build()

    @Provides
    fun provideFlightDao(database: AppDatabase): FlightDao =
        database.flightDao()

    @Provides
    fun provideBookingDao(database: AppDatabase): BookingDao =
        database.bookingDao()
}
```

## Testing with In-Memory Database

```kotlin
class FlightDaoTest {

    private lateinit var database: AppDatabase
    private lateinit var dao: FlightDao

    @Before
    fun setup() {
        database = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java,
        )
        .allowMainThreadQueries() // OK for tests only
        .build()

        dao = database.flightDao()
    }

    @After
    fun tearDown() {
        database.close()
    }

    @Test
    fun insertAndQuery_returnsCorrectFlight() = runTest {
        val flight = FlightEntity(
            id = "TK1",
            originCode = "IST",
            originName = "Istanbul",
            destinationCode = "JFK",
            destinationName = "New York",
            departureTime = "2025-01-15T10:00:00Z",
            arrivalTime = "2025-01-15T18:00:00Z",
            priceAmount = 799.0,
            priceCurrency = "USD",
            status = "SCHEDULED",
        )

        dao.insertFlight(flight)

        val result = dao.getFlightByIdOnce("TK1")
        assertThat(result).isNotNull()
        assertThat(result?.originCode).isEqualTo("IST")
    }

    @Test
    fun flowQuery_emitsUpdates() = runTest {
        dao.getFlights("IST", "JFK").test {
            assertThat(awaitItem()).isEmpty()

            dao.insertFlight(testFlight)

            val updated = awaitItem()
            assertThat(updated).hasSize(1)

            cancelAndIgnoreRemainingEvents()
        }
    }

    @Test
    fun upsert_updatesExistingFlight() = runTest {
        dao.insertFlight(testFlight)
        val updated = testFlight.copy(status = "DELAYED")
        dao.upsertFlights(listOf(updated))

        val result = dao.getFlightByIdOnce(testFlight.id)
        assertThat(result?.status).isEqualTo("DELAYED")
    }
}
```

### Testing Migrations

```kotlin
@RunWith(AndroidJUnit4::class)
class MigrationTest {

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

    @Test
    fun migrate3To4() {
        // Create database at version 3
        helper.createDatabase("test-db", 3).apply {
            execSQL("INSERT INTO bookings (id, flight_id, status) VALUES ('B1', 'F1', 'CONFIRMED')")
            close()
        }

        // Run migration and validate
        val db = helper.runMigrationsAndValidate("test-db", 4, true, MIGRATION_3_4)
        val cursor = db.query("SELECT * FROM bookings WHERE id = 'B1'")
        assertThat(cursor.moveToFirst()).isTrue()
        assertThat(cursor.getColumnIndex("created_at")).isAtLeast(0)
    }
}
```

## Do's and Don'ts

### Do's
- Set `exportSchema = true` and commit schemas to version control
- Use `@Upsert` for sync-from-server patterns
- Return `Flow` from DAO for reactive UI updates
- Use `@Transaction` on all relation queries
- Create indices on frequently queried columns
- Use in-memory databases for unit tests
- Test migrations with `MigrationTestHelper`
- Use `stateIn` with `WhileSubscribed(5000)` in ViewModels for Flow collection

### Don'ts
- Do not perform Room operations on the main thread (except in tests with `allowMainThreadQueries`)
- Do not use `fallbackToDestructiveMigration` in production builds
- Do not store large blobs (images, files) in Room -- store file paths instead
- Do not use `@RawQuery` without parameterized inputs (SQL injection risk)
- Do not forget `@ColumnInfo(name = "...")` for columns with snake_case naming
- Do not skip `@Transaction` on relation queries -- partial reads cause inconsistency
- Do not use Room's callback methods for initial data -- use `RoomDatabase.Callback.onCreate`

## Troubleshooting

| Problem | Cause | Fix |
|---------|-------|-----|
| `Cannot access database on the main thread` | DAO called from main thread | Use `suspend` functions or `Flow`, call from coroutine |
| `Room cannot verify the data integrity` | Missing or wrong migration | Add migration or use `fallbackToDestructiveMigration` for dev |
| Schema export not working | Missing KSP arg | Add `ksp { arg("room.schemaLocation", ...) }` |
| `@Relation` returns empty list | Missing `@Transaction` or wrong column mapping | Add `@Transaction`, verify `parentColumn`/`entityColumn` match |
| TypeConverter not found | Not registered in `@TypeConverters` | Add `@TypeConverters(Converters::class)` on `@Database` |
| Auto migration fails on rename/delete | Missing spec class | Create `AutoMigrationSpec` with `@RenameColumn`/`@DeleteColumn` |
| KSP not generating DAO implementations | Missing KSP plugin or dependency | Add `id("com.google.devtools.ksp")` and `ksp(libs.room.compiler)` |
| Flow not emitting after insert | Querying different table or wrong parameters | Verify table name and query parameters match insert |

## Review Checklist

- [ ] `exportSchema = true` and schema location configured
- [ ] Schema JSON files committed to version control
- [ ] Indices on frequently queried columns
- [ ] `@Transaction` on all `@Relation` queries
- [ ] `Flow` returned for reactive queries; `suspend` for one-shot
- [ ] TypeConverters registered at database level
- [ ] Migrations tested with `MigrationTestHelper`
- [ ] No `fallbackToDestructiveMigration` in release builds
- [ ] DAOs provided via Hilt as singletons (database) / transient (DAOs)
- [ ] In-memory database used in unit tests