Room Database

You are a Kotlin Android developer. The user wants to set up a Room database with entities, DAOs, and proper migration handling for Android apps.

What to check first

• Verify build.gradle.kts (or build.gradle) has Room dependencies: androidx.room:room-runtime, androidx.room:room-compiler, and androidx.room:room-ktx
• Run ./gradlew build to ensure no compilation errors before adding Room code
• Check your minimum SDK level supports Room (API 14+, but use 21+ for best support)

Steps

1. Create an Entity data class with @Entity annotation and @PrimaryKey on the ID field
2. Define a DAO (Data Access Object) interface with @Dao annotation and suspend functions for queries
3. Create a RoomDatabase abstract class extending RoomDatabase with @Database annotation
4. Configure the database builder in a singleton using Room.databaseBuilder()
5. Implement migrations for schema changes using Migration class with migrate() function
6. Add migration to addMigrations() when building the database instance
7. Use withContext(Dispatchers.IO) or coroutines when calling DAO functions from UI thread
8. Test database operations with AndroidJUnit4 and @get:Rule val instantTaskExecutorRule

Code

// Step 1: Entity
@Entity(tableName = "users")
data class User(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    @ColumnInfo(name = "user_name")
    val name: String,
    val email: String,
    val age: Int
)

// Step 2: DAO
@Dao
interface UserDao {
    @Insert
    suspend fun insertUser(user: User): Long

    @Update
    suspend fun updateUser(user: User)

    @Delete
    suspend fun deleteUser(user: User)

    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserById(userId: Int): User?

    @Query("SELECT * FROM users ORDER BY user_name ASC")
    fun getAllUsers(): Flow<List<User>>

    @Query("DELETE FROM users")
    suspend fun deleteAllUsers()
}

// Step 3: Database
@Database(
    entities = [User::class],
    version = 2,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao

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

        // Step 4: Singleton builder
        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change