---
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."
metadata:
  source: multi-agent-pipeline
---

# Room Database

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

Detailed, copy-ready code for every section below lives in
[references/patterns.md](references/patterns.md). Load that file when you need a
full example to adapt (entity, DAO, migration, Hilt module, or a test). Each
heading there mirrors a section here.

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

Annotate a `data class` with `@Entity(tableName = ...)`. Declare a `@PrimaryKey`,
map snake_case columns with `@ColumnInfo(name = ...)`, and add `indices` for
columns that appear in `WHERE`/`ORDER BY`. Use `primaryKeys = [...]` on the
entity for a composite key instead of `@PrimaryKey`.

See [references/patterns.md#entity-definition](references/patterns.md#entity-definition)
for a full entity and a composite-key entity.

## DAO Interface

Mark the interface `@Dao`. Return `Flow<T>` for reactive reads and `suspend`
for one-shot reads/writes. Use `@Query`, `@Insert`, `@Update`, `@Delete`,
`@Upsert`, and wrap multi-statement operations in a `@Transaction` default
method.

Pick the write strategy by intent:

| 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 |

Full DAO surface: [references/patterns.md#dao-interface](references/patterns.md#dao-interface).

## Database Setup

Declare an `abstract class ... : RoomDatabase()` with `@Database(entities, version)`,
one abstract accessor per DAO, and `@TypeConverters` when needed. Always set
`exportSchema = true` and point KSP at a schema directory:

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

Full `@Database` declaration:
[references/patterns.md#database-setup](references/patterns.md#database-setup).

## Reactive Queries with Flow

A DAO method returning `Flow` re-emits whenever its table changes. Map entities
to domain models in the repository (`.map { ... }.flowOn(Dispatchers.IO)`),
expose a `StateFlow` from the ViewModel via `stateIn(..., WhileSubscribed(5_000),
...)`, and collect with `collectAsStateWithLifecycle()` in Compose.

Full repository -> ViewModel -> Compose chain:
[references/patterns.md#reactive-queries-with-flow](references/patterns.md#reactive-queries-with-flow).

## TypeConverters

For types Room does not natively support (lists, enums, dates), write a
`Converters` class with paired `@TypeConverter` functions and register it with
`@TypeConverters(Converters::class)` on the `@Database` (or a specific entity/DAO).

Full converter set (list, `Instant`, enum):
[references/patterns.md#typeconverters](references/patterns.md#typeconverters).

## Relations

- `@Embedded(prefix = ...)` flattens a nested object into the parent table.
- `@Relation(parentColumn, entityColumn)` models one-to-many.
- `associateBy = Junction(...)` models many-to-many through a cross-ref entity.

Always annotate relation queries with `@Transaction` for consistent reads.
Full embedded / one-to-many / junction examples:
[references/patterns.md#relations](references/patterns.md#relations).

## Migration Strategies

- Auto migration (`autoMigrations = [AutoMigration(from, to)]`) for additive
  changes; supply an `AutoMigrationSpec` with `@RenameColumn`/`@DeleteColumn`
  for renames or deletes.
- Manual `Migration(from, to)` with raw `execSQL` for data transformation or
  table merges; register via `.addMigrations(...)`.
- `.fallbackToDestructiveMigration()` only for development or throwaway data,
  never in release builds.

Full auto, manual, and destructive examples:
[references/patterns.md#migration-strategies](references/patterns.md#migration-strategies).

## Hilt Integration

Provide the database as a `@Singleton` from a `@Module @InstallIn(SingletonComponent::class)`
object, and provide each DAO from the database instance. Full `DatabaseModule`:
[references/patterns.md#hilt-integration](references/patterns.md#hilt-integration).

## Encrypted Database (SQLCipher)

Encrypt the database file by passing a SQLCipher `SupportOpenHelperFactory` to
`.openHelperFactory(...)` on the builder. This skill owns the encrypted-Room
wiring; it does not own the passphrase. Derive and store the passphrase through
the key hierarchy in the `android-security` skill (Keystore-wrapped key), and
pass the resulting bytes into the factory.

Full builder wiring:
[references/patterns.md#encrypted-database-sqlcipher](references/patterns.md#encrypted-database-sqlcipher).

## Async Database Provisioning

When the passphrase (or any setup input) must be derived or fetched before
`Room.databaseBuilder`, a synchronous `@Provides` cannot await it. Expose the
database from a suspend, `Mutex`-guarded, double-checked lazy singleton provider
instead: return the cached instance if present, otherwise build it once inside
the lock. Hilt provides the provider; repositories fetch DAOs from a suspend
accessor.

Full `DatabaseProvider`:
[references/patterns.md#async-database-provisioning](references/patterns.md#async-database-provisioning).

## Runtime Hygiene

- Cap WAL file growth on a write-heavy database with an `onOpen` callback issuing
  `PRAGMA wal_autocheckpoint`.
- Do multi-step read-then-write logic (dedupe, insert, trim-to-cap) atomically in
  a `@Transaction` DAO default method, not across separate DAO calls.
- `exportSchema = false` is acceptable only for a cache or destructive-fallback
  database whose file is disposable. A migration-tested production database keeps
  `exportSchema = true` and commits its schema JSON, which `MigrationTestHelper`
  validates against.

Full callback and transactional DAO:
[references/patterns.md#runtime-hygiene](references/patterns.md#runtime-hygiene).

## Testing with In-Memory Database

Build a `Room.inMemoryDatabaseBuilder(...)` in `@Before`, close it in `@After`,
and run DAO tests with `runTest`. Use Turbine's `.test { awaitItem() }` for
`Flow` assertions. Test migrations with `MigrationTestHelper`
(`createDatabase` at the old version, then `runMigrationsAndValidate`).

Full DAO tests and migration test:
[references/patterns.md#testing-with-in-memory-database](references/patterns.md#testing-with-in-memory-database).

## 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
- Encrypt sensitive databases with a SQLCipher `SupportOpenHelperFactory`
- Build a database needing async setup from a suspend, `Mutex`-guarded provider
- Cap WAL growth with an `onOpen` `PRAGMA wal_autocheckpoint`
- Keep multi-step read-then-write logic inside a `@Transaction` default method

### 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)
- [ ] Sensitive databases encrypted via a SQLCipher `SupportOpenHelperFactory`
- [ ] Passphrase sourced from the `android-security` key hierarchy, not reproduced
- [ ] Async-setup databases built from a suspend, `Mutex`-guarded provider
- [ ] `onOpen` `PRAGMA wal_autocheckpoint` on write-heavy databases
- [ ] Multi-step read-then-write logic wrapped in a `@Transaction` default method
- [ ] In-memory database used in unit tests