serDao { @Query("SELECT * FROM users WHERE id = :userId") fun getUserById(userId: String): Flow<User?>

@Query("SELECT * FROM users ORDER BY last_active_timestamp DESC LIMIT :limit")
fun getActiveUsers(limit: Int): Flow<List<User>>

@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun upsertUser(user: User)

@Query("DELETE FROM users WHERE last_active_timestamp < :cutoff")
suspend fun pruneInactiveUsers(cutoff: Long)

}

**Key Decision:** `OnConflictStrategy.REPLACE` is preferred for upsert operations in sync scenarios, as it handles updates atomically. `OnConflictStrategy.IGNORE` is suitable when you want to preserve existing data and skip duplicates.

### 4. Database Singleton and Migration Strategy

The `RoomDatabase` instance must be a singleton. Multiple instances can cause locking issues and memory leaks. Migrations must be versioned and tested.

```kotlin
@Database(
    entities = [User::class],
    version = 2,
    exportSchema = true // Critical for migration testing
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )
                .addMigrations(MIGRATION_1_2)
                .setQueryExecutor(Executors.newSingleThreadExecutor()) // Dedicated query thread
                .addCallback(object : RoomDatabase.Callback() {
                    override fun onCreate(db: SupportSQLiteDatabase) {
                        super.onCreate(db)
                        // Pre-populate data if necessary
                    }
                })
                .build()
                .also { INSTANCE = it }
            }
        }
    }
}

Migration Implementation:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Example: Add a new column with a default value
        database.execSQL(
            "ALTER TABLE users ADD COLUMN last_active_timestamp INTEGER NOT NULL DEFAULT 0"
        )
    }
}

5. Repository Integration

The repository encapsulates the database logic and provides a clean API to the ViewModel.

class UserRepository @Inject constructor(private val userDao: UserDao) {
    fun getUser(userId: String): Flow<User?> = userDao.getUserById(userId)

    suspend fun saveUser(user: User) {
        userDao.upsertUser(user)
    }
}

Pitfall Guide

Production experience reveals recurring patterns of failure in Room implementations. Avoid these pitfalls to ensure stability and performance.

1. Main Thread Violations:

   • Mistake: Using allowMainThreadQueries() in production builds to bypass Room's default check.
   • Consequence: UI freezes, ANRs, and dropped frames. Room blocks the main thread by design; disabling this removes a critical safety guardrail.
   • Fix: Always use suspend functions or Flow. If synchronous access is absolutely required, wrap it in withContext(Dispatchers.IO).

2. N+1 Query Problem:

   • Mistake: Fetching a list of items in a loop and querying related data for each item individually.
   • Consequence: Exponential increase in query time and database lock contention.
   • Fix: Use SQL JOIN operations or @Relation annotations to fetch related data in a single query. For complex relations, consider flattening data into DTOs using @Query projections.

3. Migration Version Skew:

   • Mistake: Incrementing the version number but forgetting to add the Migration object to the builder, or writing a migration that only works for a specific previous version.
   • Consequence: IllegalStateException for users who skipped versions. Room requires a path from every previous version to the current version.
   • Fix: Implement migrations for all version hops (e.g., 1->2, 1->3, 2->3 if jumping to 3). Use exportSchema = true and test migrations using Room.inMemoryDatabaseBuilder with schema files.

4. Ignoring Indices:

   • Mistake: Relying on full table scans for frequent queries, especially on columns used in WHERE, ORDER BY, or JOIN clauses.
   • Consequence: Query performance degrades linearly with data size. A query on 100 rows might take 1ms, but on 100k rows it could take seconds.
   • Fix: Define @Index on frequently queried columns. Monitor query performance using the Android Studio Database Inspector.

5. Overusing @Relation:

   • Mistake: Creating deep object graphs with multiple @Relation annotations that load massive amounts of data when only a subset is needed.
   • Consequence: High memory usage and slow UI rendering.
   • Fix: Use @Relation only when the full graph is required. For partial data, write custom @Query methods that return specific columns or lightweight data classes.

6. TypeConverter Errors:

   • Mistake: Throwing exceptions inside TypeConverter methods or failing to handle null values.
   • Consequence: Runtime crashes during database access if data is malformed or missing.
   • Fix: Ensure converters handle null gracefully and log errors without crashing. Validate data integrity at the application layer before insertion.

7. Leaking Database Instances:

   • Mistake: Creating multiple instances of RoomDatabase in different parts of the app.
   • Consequence: Database locking exceptions and increased memory footprint.
   • Fix: Use a singleton pattern or dependency injection (Hilt/Dagger) to provide a single instance throughout the application lifecycle.

Production Bundle

Action Checklist

• Enable Schema Export: Set exportSchema = true in the @Database annotation to generate JSON schema files for migration testing.
• Define Indices: Review all @Query methods and add @Index annotations to columns used in WHERE, ORDER BY, and JOIN clauses.
• Implement Migration Tests: Write unit tests using Room.inMemoryDatabaseBuilder to verify migrations between all supported version pairs.
• Use Flow for UI Updates: Replace LiveData with Kotlin Flow in DAOs to leverage structured concurrency and cancellation.
• Configure Query Executor: Set a dedicated setQueryExecutor in the database builder to isolate database operations from the default IO dispatcher.
• Avoid allowMainThreadQueries: Remove any usage of allowMainThreadQueries from production code; enforce asynchronous access patterns.
• Profile with Database Inspector: Use Android Studio's Database Inspector to monitor query execution times and detect N+1 patterns during development.

Decision Matrix

Configuration Template

Copy this template to initialize a production-grade Room database with Hilt integration.

// DatabaseModule.kt
@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {

    @Provides
    @Singleton
    fun provideAppDatabase(@ApplicationContext context: Context): AppDatabase {
        return Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "production_database"
        )
        .addMigrations(
            MIGRATION_1_2,
            MIGRATION_2_3
        )
        .setQueryExecutor(Executors.newSingleThreadExecutor())
        .addCallback(object : RoomDatabase.Callback() {
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
                // Prepopulate logic here
            }
        })
        .build()
    }

    @Provides
    @Singleton
    fun provideUserDao(database: AppDatabase): UserDao {
        return database.userDao()
    }
}

Quick Start Guide

1. Add Dependencies: Include room-runtime, room-ktx, and ksp plugin in your build.gradle.kts.
2. Define Entity: Create a data class annotated with @Entity, define @PrimaryKey, and add @Index for query performance.
3. Create DAO: Define an interface with @Dao, write @Query methods returning Flow, and @Insert/@Update methods as suspend functions.
4. Build Database: Create an abstract class extending RoomDatabase, add @Database annotation with entities and version, and implement a singleton getter with migrations.
5. Integrate Repository: Inject the DAO into a Repository class, expose Flow to the ViewModel, and collect data in the UI layer using collectLatest in a coroutine scope.