# Room Database Patterns

Full code examples backing the guidance in `SKILL.md`. Headings mirror the
SKILL.md sections.

## 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)
- [Encrypted Database (SQLCipher)](#encrypted-database-sqlcipher)
- [Async Database Provisioning](#async-database-provisioning)
- [Runtime Hygiene](#runtime-hygiene)

## Entity Definition

```kotlin
@Entity(
    tableName = "orders",
    indices = [
        Index(value = ["store_code", "region_code"]),
        Index(value = ["placed_at"]),
    ],
)
data class OrderEntity(
    @PrimaryKey
    val id: String,
    @ColumnInfo(name = "store_code")
    val storeCode: String,
    @ColumnInfo(name = "store_name")
    val storeName: String,
    @ColumnInfo(name = "region_code")
    val regionCode: String,
    @ColumnInfo(name = "region_name")
    val regionName: String,
    @ColumnInfo(name = "placed_at")
    val placedAt: String,
    @ColumnInfo(name = "delivered_at")
    val deliveredAt: 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 = "shipment_packages",
    primaryKeys = ["shipment_id", "package_id"],
)
data class ShipmentPackageEntity(
    @ColumnInfo(name = "shipment_id")
    val shipmentId: String,
    @ColumnInfo(name = "package_id")
    val packageId: String,
    @ColumnInfo(name = "tracking_number")
    val trackingNumber: String?,
)
```

## DAO Interface

```kotlin
@Dao
interface OrderDao {

    @Query("SELECT * FROM orders WHERE store_code = :store AND region_code = :region ORDER BY placed_at ASC")
    fun getOrders(store: String, region: String): Flow<List<OrderEntity>>

    @Query("SELECT * FROM orders WHERE id = :id")
    fun getOrderById(id: String): Flow<OrderEntity?>

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

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

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertOrders(orders: List<OrderEntity>)

    @Upsert
    suspend fun upsertOrders(orders: List<OrderEntity>)

    @Update
    suspend fun updateOrder(order: OrderEntity)

    @Delete
    suspend fun deleteOrder(order: OrderEntity)

    @Query("DELETE FROM orders WHERE id = :id")
    suspend fun deleteOrderById(id: String)

    @Query("DELETE FROM orders WHERE updated_at < :threshold")
    suspend fun deleteStaleOrders(threshold: Long)

    @Query("SELECT COUNT(*) FROM orders")
    fun getOrderCount(): Flow<Int>

    @Transaction
    suspend fun replaceAllOrders(orders: List<OrderEntity>) {
        deleteAllOrders()
        insertOrders(orders)
    }

    @Query("DELETE FROM orders")
    suspend fun deleteAllOrders()
}
```

### @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 = [
        OrderEntity::class,
        ShipmentEntity::class,
        ShipmentPackageEntity::class,
    ],
    version = 2,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),
    ],
    exportSchema = true,
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun orderDao(): OrderDao
    abstract fun shipmentDao(): ShipmentDao
}
```

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 orders WHERE store_code = :store")
fun getOrdersByStore(store: String): Flow<List<OrderEntity>>

// Repository maps to domain models
class OrderRepositoryImpl @Inject constructor(
    private val dao: OrderDao,
    private val mapper: OrderMapper,
) : OrderRepository {

    override fun getOrders(store: String): Flow<List<Order>> =
        dao.getOrdersByStore(store)
            .map { entities -> entities.map(mapper::toDomain) }
            .flowOn(Dispatchers.IO)
}

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

    val orders: StateFlow<List<Order>> = repository.getOrders("LDN")
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5_000),
            initialValue = emptyList(),
        )
}

// Compose collects as state
@Composable
fun OrderListScreen(viewModel: OrderListViewModel = hiltViewModel()) {
    val orders by viewModel.orders.collectAsStateWithLifecycle()
    LazyColumn {
        items(orders, key = { it.id }) { order ->
            OrderCard(order = order)
        }
    }
}
```

## 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 fromOrderStatus(value: OrderStatus): String = value.name

    @TypeConverter
    fun toOrderStatus(value: String): OrderStatus = OrderStatus.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 = "customers")
data class CustomerEntity(
    @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 ShipmentWithPackages(
    @Embedded val shipment: ShipmentEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "shipment_id",
    )
    val packages: List<ShipmentPackageEntity>,
)

@Dao
interface ShipmentDao {
    @Transaction
    @Query("SELECT * FROM shipments WHERE id = :shipmentId")
    fun getShipmentWithPackages(shipmentId: String): Flow<ShipmentWithPackages?>
}
```

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

```kotlin
@Entity(
    tableName = "order_tags",
    primaryKeys = ["order_id", "tag_id"],
)
data class OrderTagCrossRef(
    @ColumnInfo(name = "order_id") val orderId: String,
    @ColumnInfo(name = "tag_id") val tagId: String,
)

data class OrderWithTags(
    @Embedded val order: OrderEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = OrderTagCrossRef::class,
            parentColumn = "order_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 = [OrderEntity::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 = "orders", 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 shipments_new (
                id TEXT NOT NULL PRIMARY KEY,
                order_id TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'PENDING',
                created_at INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
            )
        """)
        db.execSQL("""
            INSERT INTO shipments_new (id, order_id, status, created_at)
            SELECT id, order_id, status, 0 FROM shipments
        """)
        db.execSQL("DROP TABLE shipments")
        db.execSQL("ALTER TABLE shipments_new RENAME TO shipments")
    }
}

// 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 provideOrderDao(database: AppDatabase): OrderDao =
        database.orderDao()

    @Provides
    fun provideShipmentDao(database: AppDatabase): ShipmentDao =
        database.shipmentDao()
}
```

## Testing with In-Memory Database

```kotlin
class OrderDaoTest {

    private lateinit var database: AppDatabase
    private lateinit var dao: OrderDao

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

        dao = database.orderDao()
    }

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

    @Test
    fun insertAndQuery_returnsCorrectOrder() = runTest {
        val order = OrderEntity(
            id = "ORD-101",
            storeCode = "LDN",
            storeName = "London",
            regionCode = "NYC",
            regionName = "New York",
            placedAt = "2025-01-15T10:00:00Z",
            deliveredAt = "2025-01-15T18:00:00Z",
            priceAmount = 799.0,
            priceCurrency = "USD",
            status = "PLACED",
        )

        dao.insertOrder(order)

        val result = dao.getOrderByIdOnce("ORD-101")
        assertThat(result).isNotNull()
        assertThat(result?.storeCode).isEqualTo("LDN")
    }

    @Test
    fun flowQuery_emitsUpdates() = runTest {
        dao.getOrders("LDN", "NYC").test {
            assertThat(awaitItem()).isEmpty()

            dao.insertOrder(testOrder)

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

            cancelAndIgnoreRemainingEvents()
        }
    }

    @Test
    fun upsert_updatesExistingOrder() = runTest {
        dao.insertOrder(testOrder)
        val updated = testOrder.copy(status = "SHIPPED")
        dao.upsertOrders(listOf(updated))

        val result = dao.getOrderByIdOnce(testOrder.id)
        assertThat(result?.status).isEqualTo("SHIPPED")
    }
}
```

### 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 shipments (id, order_id, status) VALUES ('S1', 'O1', 'CONFIRMED')")
            close()
        }

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

## Encrypted Database (SQLCipher)

Encrypt the database file by passing a SQLCipher `SupportOpenHelperFactory` to
`.openHelperFactory(...)`. This skill owns only the Room wiring. The passphrase
must come from a hardware-backed key hierarchy; the derivation and storage of
that key are the `android-security` skill's responsibility, not reproduced here.

```kotlin
fun buildEncryptedDatabase(
    context: Context,
    passphrase: ByteArray,
): AppDatabase {
    val factory = SupportOpenHelperFactory(passphrase)
    return Room.databaseBuilder(context, AppDatabase::class.java, "secure-app.db")
        .openHelperFactory(factory)
        .addCallback(WalHygieneCallback())
        .build()
}
```

`SupportOpenHelperFactory` is from the AndroidX-compatible SQLCipher artifact
(`net.zetetic.database.sqlcipher`); it zeroes the passphrase array after use.

## Async Database Provisioning

When the passphrase must be derived or fetched before `Room.databaseBuilder`, a
synchronous `@Provides` cannot express the await. Expose the database through a
suspend, `Mutex`-guarded, double-checked lazy singleton provider.

```kotlin
@Singleton
class DatabaseProvider @Inject constructor(
    @ApplicationContext private val context: Context,
    private val keyProvider: DatabaseKeyProvider,
) {
    private val mutex = Mutex()
    @Volatile private var instance: AppDatabase? = null

    suspend fun database(): AppDatabase {
        instance?.let { return it }
        return mutex.withLock {
            instance ?: buildDatabase().also { instance = it }
        }
    }

    private suspend fun buildDatabase(): AppDatabase {
        val passphrase = keyProvider.passphrase()
        return Room.databaseBuilder(context, AppDatabase::class.java, "secure-app.db")
            .openHelperFactory(SupportOpenHelperFactory(passphrase))
            .addCallback(WalHygieneCallback())
            .build()
    }
}
```

The Hilt module provides `DatabaseProvider` (no I/O); repositories obtain DAOs
from a suspend accessor (`provider.database().recentSearchDao()`) at call time.

## Runtime Hygiene

Cap WAL growth on a write-heavy database with an `onOpen` PRAGMA:

```kotlin
class WalHygieneCallback : RoomDatabase.Callback() {
    override fun onOpen(db: SupportSQLiteDatabase) {
        db.query("PRAGMA wal_autocheckpoint=1000").close()
    }
}
```

Do multi-step read-then-write logic atomically in a `@Transaction` DAO default
method -- dedupe, insert, and trim-to-cap in one transaction:

```kotlin
@Dao
interface RecentSearchDao {
    @Query("SELECT id FROM recent_searches WHERE query = :query LIMIT 1")
    suspend fun findId(query: String): Long?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(entity: RecentSearchEntity): Long

    @Query(
        "DELETE FROM recent_searches WHERE id IN " +
            "(SELECT id FROM recent_searches ORDER BY updated_at DESC LIMIT -1 OFFSET :cap)",
    )
    suspend fun trimToCap(cap: Int)

    @Transaction
    suspend fun record(entity: RecentSearchEntity, cap: Int) {
        val existing = findId(entity.query)
        insert(if (existing != null) entity.copy(id = existing) else entity)
        trimToCap(cap)
    }
}
```

`exportSchema = false` is acceptable only for a cache or destructive-fallback
database, where the file is disposable. A migration-tested production database
must keep `exportSchema = true` and commit the schema JSON, since
`MigrationTestHelper` validates against the exported schema.