The Compose tutorial covered basic Room: one entity, one DAO, simple CRUD operations. That is enough for a notes app.

Real apps need more. Users update their app and you need to migrate the database without losing data. You have notes with tags — a many-to-many relationship. Users search through thousands of notes and expect instant results.

This tutorial covers the advanced Room patterns you need for production apps.

Prerequisites: Compose Tutorial #13: Room basics and Kotlin Tutorial: Flow.

Migrations

When you change your database schema (add a column, rename a table), Room needs to know how to update existing databases on user devices.

Auto-Migrations (Room 2.4+)

For simple changes, Room can generate migrations automatically:

@Database(
    entities = [TaskEntity::class],
    version = 2,
    autoMigrations = [
        AutoMigration(from = 1, to = 2)
    ]
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
}

Auto-migrations work for:

  • Adding a new column with a default value
  • Adding a new entity/table
  • Adding a new index

When Auto-Migrations Need Help

When you rename or delete a column, Room needs extra info:

@Database(
    entities = [TaskEntity::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),
        AutoMigration(from = 2, to = 3, spec = Migration2To3::class)
    ]
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
}

@RenameColumn(tableName = "tasks", fromColumnName = "due", toColumnName = "due_date")
class Migration2To3 : AutoMigrationSpec

Other spec annotations: @DeleteColumn, @DeleteTable, @RenameTable.

Manual Migrations

For complex changes, write the migration SQL yourself:

val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Add a new column
        db.execSQL("ALTER TABLE tasks ADD COLUMN priority INTEGER NOT NULL DEFAULT 1")

        // Create a new table
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                name TEXT NOT NULL,
                color TEXT NOT NULL DEFAULT '#000000'
            )
        """)

        // Create junction table for many-to-many
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS task_tag_cross_ref (
                task_id INTEGER NOT NULL,
                tag_id INTEGER NOT NULL,
                PRIMARY KEY(task_id, tag_id),
                FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,
                FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
            )
        """)
    }
}

Register migrations when building the database:

Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addMigrations(MIGRATION_3_4)
    .build()

Schema Export

Always export schemas for migration verification:

// build.gradle.kts
ksp {
    arg("room.schemaLocation", "$projectDir/schemas")
}

This creates JSON schema files that Room uses to validate auto-migrations and that you can check into version control.

Entity Relations

One-to-Many: Tasks with Subtasks

@Entity(tableName = "tasks")
data class TaskEntity(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val isCompleted: Boolean = false
)

@Entity(
    tableName = "subtasks",
    foreignKeys = [ForeignKey(
        entity = TaskEntity::class,
        parentColumns = ["id"],
        childColumns = ["task_id"],
        onDelete = ForeignKey.CASCADE
    )]
)
data class SubtaskEntity(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    @ColumnInfo(name = "task_id") val taskId: Long,
    val title: String,
    val isCompleted: Boolean = false
)

Querying Relations with @Relation

data class TaskWithSubtasks(
    @Embedded val task: TaskEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "task_id"
    )
    val subtasks: List<SubtaskEntity>
)

@Dao
interface TaskDao {
    @Transaction
    @Query("SELECT * FROM tasks ORDER BY id DESC")
    fun getTasksWithSubtasks(): Flow<List<TaskWithSubtasks>>

    @Transaction
    @Query("SELECT * FROM tasks WHERE id = :taskId")
    fun getTaskWithSubtasks(taskId: Long): Flow<TaskWithSubtasks?>
}

The @Transaction annotation is important. Without it, Room might read the task and subtasks in separate transactions, giving you inconsistent data.

Many-to-Many: Tasks with Tags

@Entity(tableName = "tags")
data class TagEntity(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val name: String,
    val color: String = "#4CAF50"
)

// Junction table
@Entity(
    tableName = "task_tag_cross_ref",
    primaryKeys = ["taskId", "tagId"],
    foreignKeys = [
        ForeignKey(entity = TaskEntity::class, parentColumns = ["id"], childColumns = ["taskId"], onDelete = ForeignKey.CASCADE),
        ForeignKey(entity = TagEntity::class, parentColumns = ["id"], childColumns = ["tagId"], onDelete = ForeignKey.CASCADE)
    ]
)
data class TaskTagCrossRef(
    val taskId: Long,
    val tagId: Long
)

// Result class
data class TaskWithTags(
    @Embedded val task: TaskEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = TaskTagCrossRef::class,
            parentColumn = "taskId",
            entityColumn = "tagId"
        )
    )
    val tags: List<TagEntity>
)

@Dao
interface TaskDao {
    @Transaction
    @Query("SELECT * FROM tasks ORDER BY id DESC")
    fun getTasksWithTags(): Flow<List<TaskWithTags>>

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun addTagToTask(crossRef: TaskTagCrossRef)

    @Delete
    suspend fun removeTagFromTask(crossRef: TaskTagCrossRef)
}

@Embedded for Nested Objects

When an entity has a logical group of fields, use @Embedded:

data class Address(
    val street: String,
    val city: String,
    val zipCode: String
)

@Entity(tableName = "users")
data class UserEntity(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val name: String,
    val email: String,
    @Embedded(prefix = "address_") val address: Address
)

Room flattens this into columns: address_street, address_city, address_zipCode. The prefix avoids column name conflicts.

Database Views

A @DatabaseView is a pre-defined query that Room treats like a read-only table:

@DatabaseView("""
    SELECT tasks.id, tasks.title, tasks.isCompleted,
           COUNT(subtasks.id) as subtaskCount,
           SUM(CASE WHEN subtasks.isCompleted THEN 1 ELSE 0 END) as completedSubtaskCount
    FROM tasks
    LEFT JOIN subtasks ON tasks.id = subtasks.task_id
    GROUP BY tasks.id
""")
data class TaskSummary(
    val id: Long,
    val title: String,
    val isCompleted: Boolean,
    val subtaskCount: Int,
    val completedSubtaskCount: Int
)

@Database(
    entities = [TaskEntity::class, SubtaskEntity::class],
    views = [TaskSummary::class],
    version = 1
)
abstract class AppDatabase : RoomDatabase()

@Dao
interface TaskDao {
    @Query("SELECT * FROM TaskSummary ORDER BY id DESC")
    fun getTaskSummaries(): Flow<List<TaskSummary>>
}

Views are great for dashboard screens that aggregate data from multiple tables.

Full-Text Search (FTS4)

For fast text search across thousands of records, use FTS4:

// Regular entity
@Entity(tableName = "notes")
data class NoteEntity(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val content: String,
    val createdAt: Long = System.currentTimeMillis()
)

// FTS table that indexes the regular entity
@Fts4(contentEntity = NoteEntity::class)
@Entity(tableName = "notes_fts")
data class NoteFts(
    val title: String,
    val content: String
)

@Database(
    entities = [NoteEntity::class, NoteFts::class],
    version = 1
)
abstract class AppDatabase : RoomDatabase()

Searching with FTS

@Dao
interface NoteDao {
    @Query("""
        SELECT notes.* FROM notes
        JOIN notes_fts ON notes.rowid = notes_fts.rowid
        WHERE notes_fts MATCH :query
        ORDER BY notes.createdAt DESC
    """)
    fun search(query: String): Flow<List<NoteEntity>>

    @Insert
    suspend fun insert(note: NoteEntity)

    // Room keeps the FTS table in sync automatically
    // when you use contentEntity
}

FTS4 search is significantly faster than LIKE '%query%' for large datasets. It supports prefix queries (query*), phrase queries ("exact phrase"), and boolean operators.

Type Converters

Room stores primitives and strings. For other types, you need converters.

Date Converter

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

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time
    }
}

// For java.time (recommended)
class LocalDateTimeConverters {
    @TypeConverter
    fun fromEpochMilli(value: Long?): LocalDateTime? {
        return value?.let {
            LocalDateTime.ofInstant(
                Instant.ofEpochMilli(it),
                ZoneId.systemDefault()
            )
        }
    }

    @TypeConverter
    fun toEpochMilli(dateTime: LocalDateTime?): Long? {
        return dateTime?.atZone(ZoneId.systemDefault())?.toInstant()?.toEpochMilli()
    }
}

Enum Converter

class PriorityConverter {
    @TypeConverter
    fun fromPriority(priority: Priority): String = priority.name

    @TypeConverter
    fun toPriority(value: String): Priority = Priority.valueOf(value)
}

List Converter (with Kotlin Serialization)

class StringListConverter {
    @TypeConverter
    fun fromList(list: List<String>): String {
        return Json.encodeToString(list)
    }

    @TypeConverter
    fun toList(json: String): List<String> {
        return Json.decodeFromString(json)
    }
}

Register Converters

@Database(
    entities = [TaskEntity::class, NoteEntity::class],
    version = 1
)
@TypeConverters(
    DateConverters::class,
    PriorityConverter::class,
    StringListConverter::class
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
    abstract fun noteDao(): NoteDao
}

Pre-Populated Databases

Ship default data with your app:

From Asset File

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

Place the .db file in app/src/main/assets/databases/.

With Callback

Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addCallback(object : RoomDatabase.Callback() {
        override fun onCreate(db: SupportSQLiteDatabase) {
            super.onCreate(db)
            // Insert default data on first creation
            db.execSQL("""
                INSERT INTO tags (name, color) VALUES
                ('Work', '#2196F3'),
                ('Personal', '#4CAF50'),
                ('Urgent', '#F44336')
            """)
        }
    })
    .build()

Room with Flow — Reactive Queries

Room’s Flow support means your UI updates automatically when data changes:

@Dao
interface TaskDao {
    // This Flow emits a new list every time the tasks table changes
    @Query("SELECT * FROM tasks WHERE isCompleted = 0 ORDER BY priority DESC")
    fun getPendingTasks(): Flow<List<TaskEntity>>

    // Suspend function for write operations
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(task: TaskEntity)

    @Query("UPDATE tasks SET isCompleted = :completed WHERE id = :taskId")
    suspend fun setCompleted(taskId: Long, completed: Boolean)
}

When you call insert() or setCompleted(), Room automatically triggers a new emission on getPendingTasks(). You don’t need to manually refresh.

Database Inspector

Android Studio has a built-in Database Inspector. It lets you:

  1. See all tables and their data in real time
  2. Run SQL queries directly
  3. Edit data while the app is running

To use it: View > Tool Windows > App Inspection > Database Inspector. Select your running app and you will see all Room databases.

Testing Room

Use an in-memory database for tests. It is fast and disappears after each test.

@RunWith(AndroidJUnit4::class)
class TaskDaoTest {

    private lateinit var database: AppDatabase
    private lateinit var taskDao: TaskDao

    @Before
    fun setup() {
        database = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        ).allowMainThreadQueries().build()

        taskDao = database.taskDao()
    }

    @After
    fun teardown() {
        database.close()
    }

    @Test
    fun insertAndRetrieveTask() = runTest {
        val task = TaskEntity(title = "Buy groceries", isCompleted = false)
        taskDao.insert(task)

        val tasks = taskDao.getPendingTasks().first()
        assertEquals(1, tasks.size)
        assertEquals("Buy groceries", tasks[0].title)
    }

    @Test
    fun completingTaskRemovesFromPending() = runTest {
        val task = TaskEntity(id = 1, title = "Test", isCompleted = false)
        taskDao.insert(task)

        taskDao.setCompleted(1, true)

        val pending = taskDao.getPendingTasks().first()
        assertEquals(0, pending.size)
    }

    @Test
    fun searchWithFts() = runTest {
        val noteDao = database.noteDao()
        noteDao.insert(NoteEntity(title = "Kotlin Tutorial", content = "Learn Kotlin basics"))
        noteDao.insert(NoteEntity(title = "Room Database", content = "SQLite made easy"))

        val results = noteDao.search("Kotlin").first()
        assertEquals(1, results.size)
        assertEquals("Kotlin Tutorial", results[0].title)
    }
}

Room 3.0 Preview

Room 3.0 (package androidx.room3) is the next major version, focusing on KMP support. Key changes:

  • KSP only — drops KAPT and Java annotation processing entirely
  • Kotlin code generation — generates Kotlin instead of Java
  • Full KMP support — shared database code across Android, iOS, desktop

For now, Room 2.8.x is the stable choice. When Room 3.0 reaches stable, migration from 2.x should be straightforward if you already use KSP.

What’s Next?

In this tutorial, you learned:

  • Auto-migrations and manual migrations
  • One-to-many and many-to-many entity relations
  • Database views for aggregate queries
  • FTS4 for fast full-text search
  • Type converters for dates, enums, and lists
  • Pre-populated databases
  • Testing with in-memory databases

Next up: Android Tutorial #8: DataStore — the modern replacement for SharedPreferences.

This is part 7 of the Android Development Tutorial series.