Android Room Database Explained - Complete Guide with Kotlin

In this tutorial, you'll learn the Room persistence library for Android: defining entities, creating DAOs, handling migrations, and integrating with Kotlin coroutines and Flow.

What You'll Learn

the Room persistence library for Android: defining entities, creating DAOs, handling migrations, and integrating with Kotlin coroutines and Flow — Room is Android's recommended local database solution. It provides compile-time SQL validation, seamless coroutine/Flow integration, and eliminates SQLite boilerplate.

Why It Matters

Room is Android's recommended local database solution. It provides compile-time SQL validation, seamless coroutine/Flow integration, and eliminates SQLite boilerplate.

Real-World Use

A todo app stores tasks in Room, observes changes with Flow to auto-update the UI, supports offline-first architecture by syncing with an API, and handles schema changes with migrations.

Learning Path

flowchart LR
    [Data Storage] --> [Room Database] --> [Migrations] --> [Testing]
    style 2 fill:#4CAF50,color:#fff

Entity Definition

@Entity(tableName = "tasks", indices = [Index(value = ["priority", "due_date"])],
    foreignKeys = [ForeignKey(entity = Category::class, parentColumns = ["id"],
        childColumns = ["category_id"], onDelete = ForeignKey.CASCADE)])
data class Task(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val description: String?,
    val priority: Int = 0,
    val dueDate: Long?,
    val isCompleted: Boolean = false,
    @ColumnInfo(name = "category_id") val categoryId: Long?,
    @ColumnInfo(name = "created_at") val createdAt: Long = System.currentTimeMillis()
)

Expected output: Room generates the tasks table with proper indices, foreign keys, and column defaults.

DAO with Reactive Queries

@Dao
interface TaskDao {
    @Query("SELECT * FROM tasks WHERE is_completed = 0 ORDER BY priority DESC, due_date ASC")
    fun getActiveTasks(): Flow<List<Task>>
    @Query("SELECT * FROM tasks WHERE id = :taskId")
    suspend fun getTaskById(taskId: Long): Task?
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertTask(task: Task): Long
    @Update
    suspend fun updateTask(task: Task)
    @Delete
    suspend fun deleteTask(task: Task)
}

Expected output: The DAO provides both reactive (Flow) and one-shot (suspend) query methods with compile-time SQL validation.

Database with Migration

@Database(entities = [Task::class, Category::class], version = 2, exportSchema = true)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
    companion object {
        @Volatile private var INSTANCE: AppDatabase? = null
        fun getInstance(context: Context): AppDatabase = INSTANCE ?: synchronized(this) {
            val instance = Room.databaseBuilder(context.applicationContext, AppDatabase::class.java, "app_database")
                .addMigrations(MIGRATION_1_2).build()
            INSTANCE = instance; instance
        }
    }
}
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE tasks ADD COLUMN priority INTEGER NOT NULL DEFAULT 0")
    }
}

Expected output: The database class provides Singleton access with proper migration handling for version upgrades.

Common Errors

1. Room cannot verify data integrity - missing migration; use fallbackToDestructiveMigration() in dev only
2. Queries returning Flow must return observable types - Flow<> or LiveData<>, not List<>
3. suspend DAO methods outside coroutine scope - must be called from coroutine or another suspend function
4. Foreign key constraint failed - ensure parent entities exist before inserting children
5. Schema export path not configured - add room.schemaLocation to build.gradle KSP block

Practice Questions

1. What annotations are required to define a Room entity?

2. How does Room provide compile-time SQL validation?

3. When should you use @DatabaseView instead of a regular entity?

4. What is the purpose of @TypeConverter?

5. How do you handle destructive migrations safely?

Challenge

Build a book library with two related tables: books and authors. Create a @Transaction query that returns books with their authors. Add a migration introducing a genres table. Test with MigrationTestHelper.

Real-World Task

Convert an existing SQLiteOpenHelper app to Room. Create the migration path so existing user data is preserved. Add a Flow-based query for the main list and verify reactive updates.

Frequently Asked Questions

{{< faq question="Is Room faster than raw SQLite?">}} Room has negligible overhead. The main advantage is compile-time query verification and coroutine/Flow integration. {{< /faq >}}

{{< faq question="Can I use Room with reactive streams other than Flow?">}} Yes. Room supports LiveData, Flow, and RxJava (via room-rxjava3). Flow is recommended for Kotlin projects. {{< /faq >}}

{{< faq question="Does Room support full-text search?">}} Yes. Use @Entity with FTS4 or FTS5 options and MATCH queries. {{< /faq >}}

Security Tip: Encrypt sensitive databases with SQLCipher using Room.databaseBuilder(context, AppDatabase.class, 'encrypted.db').openHelperFactory(SupportFactory(passphrase)).build(). Never hardcode the passphrase.