You are an expert in the Room persistence library for building Android applications with Kotlin.

## Key Points

- Always return `Flow` from DAO queries that the UI observes — this provides automatic updates when data changes.
- Use `@Transaction` for queries involving `@Relation` or multiple table operations.
- Export schemas (`exportSchema = true`) and keep them in version control for migration testing.
- Write migrations for production apps rather than relying on `fallbackToDestructiveMigration`.
- Map between entity classes and domain models — do not expose Room annotations to your domain layer.
- Use `OnConflictStrategy.REPLACE` for upsert semantics when the primary key is known.
- Run all DAO operations off the main thread using suspend functions or Flow.
- Forgetting `@Transaction` on methods that return objects with `@Relation`, which can yield inconsistent results.
- Performing database operations on the main thread, causing ANR. Room throws an exception by default, but `allowMainThreadQueries()` bypasses it — never use that in production.
- Not handling nullable return types — `getById` should return `Entity?` since the row may not exist.
- Writing migrations that do not match the new schema exactly, causing a runtime crash.
- Using `LiveData` return types in DAOs without switching to Flow, missing out on coroutine integration and Compose compatibility.

## Quick Example

```kotlin
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE users ADD COLUMN avatar_url TEXT DEFAULT NULL")
    }
}
```

```kotlin
Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .createFromAsset("databases/prepopulated.db")
    .build()
```

Room Database — Android/Kotlin

You are an expert in the Room persistence library for building Android applications with Kotlin.

Core Philosophy

Room exists to give Android developers the power of SQLite with the safety of compile-time verification. Raw SQLite queries are strings that can contain typos, reference nonexistent columns, or return unexpected types -- all of which crash at runtime. Room catches these errors at compile time by generating DAO implementations from annotated interfaces and validating every SQL query against the schema. If it compiles, the query is syntactically correct and type-safe. This single guarantee eliminates an entire category of bugs.

The reactive model is central to Room's design. DAO methods that return Flow automatically emit new values whenever the underlying table changes, without polling or manual refresh. This means the UI always shows the latest data: insert a row anywhere in the app, and every screen observing that table updates automatically. This eliminates the "stale data" problem where one screen modifies data and another screen shows outdated information because it was not notified.

Room is a local persistence layer, not a remote data layer. It should be wrapped behind a repository that coordinates between Room (local cache) and the network (remote source). The repository decides when to fetch fresh data from the server, when to serve from cache, and how to merge the two. Room's job is to be a fast, reliable, reactive local store -- nothing more. Keeping this boundary clean means the database schema can evolve independently of the API contract.

Anti-Patterns

• Performing database operations on the main thread: Room throws an exception by default if you attempt database operations on the main thread, and for good reason -- even fast queries can cause jank. Using allowMainThreadQueries() silences the warning but does not fix the problem. Always use suspend functions or Flow to keep database work off the main thread.

• Forgetting @Transaction on methods with @Relation: Queries that return objects with @Relation fields perform multiple database queries internally. Without @Transaction, another thread could modify the data between these queries, producing inconsistent results -- for example, a parent object with children that belong to a different parent.

• Writing migrations that do not match the new schema: Room validates the database schema at runtime. If a migration adds a column with NOT NULL but the entity definition says nullable, or if the column type does not match, Room throws a IllegalStateException on startup. Always test migrations against a fresh database with MigrationTestHelper.

• Exposing Room entities directly to the UI layer: Room entities are database representations with @Entity, @ColumnInfo, and other annotations. Exposing them to the UI couples your presentation layer to your database schema. Map entities to domain models in the repository so the UI never depends on Room annotations.

• Storing large binary data in Room: SQLite is not designed for binary blobs larger than a few hundred kilobytes. Storing images or files in Room bloats the database file, slows queries, and increases memory pressure during cursor operations. Use the filesystem for binary data and store only file paths in Room.

Overview

Room is an abstraction layer over SQLite that provides compile-time verification of SQL queries, seamless integration with Kotlin coroutines and Flow, and a clean API through annotations. It is part of Android Jetpack and is the recommended approach for local data persistence on Android.

Core Concepts

Entity Definition

Entities map Kotlin data classes to database tables.

@Entity(tableName = "users")
data class UserEntity(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    @ColumnInfo(name = "display_name")
    val displayName: String,
    val email: String,
    @ColumnInfo(name = "created_at")
    val createdAt: Long = System.currentTimeMillis()
)

Data Access Object (DAO)

DAOs define the methods for accessing the database. Room generates the implementation at compile time.

@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY display_name ASC")
    fun observeAll(): Flow<List<UserEntity>>

    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getById(userId: Long): UserEntity?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(user: UserEntity): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsertAll(users: List<UserEntity>)

    @Delete
    suspend fun delete(user: UserEntity)

    @Query("DELETE FROM users WHERE id = :userId")
    suspend fun deleteById(userId: Long)
}

Database Declaration

@Database(
    entities = [UserEntity::class, NoteEntity::class],
    version = 2,
    exportSchema = true
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun noteDao(): NoteDao
}

Type Converters

Room only supports primitive types natively. Use TypeConverter for complex types.

class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? = value?.let { Date(it) }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? = date?.time

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

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

Implementation Patterns

Database Builder with Hilt

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

    @Provides
    @Singleton
    fun provideDatabase(@ApplicationContext context: Context): AppDatabase {
        return Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "app_database"
        )
            .addMigrations(MIGRATION_1_2)
            .fallbackToDestructiveMigration()
            .build()
    }

    @Provides
    fun provideUserDao(database: AppDatabase): UserDao = database.userDao()
}

Migrations

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE users ADD COLUMN avatar_url TEXT DEFAULT NULL")
    }
}

Repository Pattern

class UserRepository @Inject constructor(
    private val userDao: UserDao,
    private val apiService: ApiService
) {
    fun observeUsers(): Flow<List<User>> =
        userDao.observeAll().map { entities ->
            entities.map { it.toDomain() }
        }

    suspend fun refreshUsers() {
        val remoteUsers = apiService.getUsers()
        val entities = remoteUsers.map { it.toEntity() }
        userDao.upsertAll(entities)
    }

    suspend fun getUser(id: Long): User? =
        userDao.getById(id)?.toDomain()
}

Embedded Objects and Relations

data class AddressEmbedded(
    val street: String,
    val city: String,
    @ColumnInfo(name = "zip_code")
    val zipCode: String
)

@Entity(tableName = "companies")
data class CompanyEntity(
    @PrimaryKey val id: Long,
    val name: String,
    @Embedded(prefix = "addr_")
    val address: AddressEmbedded
)

// One-to-many relation
data class UserWithNotes(
    @Embedded val user: UserEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "user_id"
    )
    val notes: List<NoteEntity>
)

@Dao
interface UserDao {
    @Transaction
    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserWithNotes(userId: Long): UserWithNotes
}

Prepopulated Database

Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .createFromAsset("databases/prepopulated.db")
    .build()

Best Practices

• Always return Flow from DAO queries that the UI observes — this provides automatic updates when data changes.
• Use @Transaction for queries involving @Relation or multiple table operations.
• Export schemas (exportSchema = true) and keep them in version control for migration testing.
• Write migrations for production apps rather than relying on fallbackToDestructiveMigration.
• Map between entity classes and domain models — do not expose Room annotations to your domain layer.
• Use OnConflictStrategy.REPLACE for upsert semantics when the primary key is known.
• Run all DAO operations off the main thread using suspend functions or Flow.

Common Pitfalls

• Forgetting @Transaction on methods that return objects with @Relation, which can yield inconsistent results.
• Performing database operations on the main thread, causing ANR. Room throws an exception by default, but allowMainThreadQueries() bypasses it — never use that in production.
• Not handling nullable return types — getById should return Entity? since the row may not exist.
• Writing migrations that do not match the new schema exactly, causing a runtime crash.
• Using LiveData return types in DAOs without switching to Flow, missing out on coroutine integration and Compose compatibility.
• Storing large blobs in Room — SQLite is not suited for binary data over a few hundred KB; use the filesystem instead.