Room Deep Dive

Beyond Basic CRUD

Room's @Insert, @Update, and @Delete cover simple cases. Real apps need @Query with JOINs, subqueries, aggregations, and transaction boundaries.

JOIN Queries

@Dao
interface OrderDao {
    @Query("""
        SELECT o.id, o.total, c.name AS customerName
        FROM orders o
        INNER JOIN customers c ON o.customerId = c.id
        WHERE o.total > :minTotal
        ORDER BY o.total DESC
    """)
    suspend fun getOrdersWithCustomer(minTotal: Double): List<OrderWithCustomer>
}

data class OrderWithCustomer(
    val id: Long,
    val total: Double,
    val customerName: String
)

Aggregation Queries

@Dao
interface AnalyticsDao {
    @Query("""
        SELECT category, COUNT(*) as count, AVG(price) as avgPrice
        FROM products
        GROUP BY category
        HAVING count > 5
        ORDER BY avgPrice DESC
    """)
    suspend fun getCategoryStats(): List<CategoryStat>
}

Subqueries

@Query("""
    SELECT * FROM products
    WHERE id IN (
        SELECT productId FROM order_items
        WHERE orderId IN (
            SELECT id FROM orders WHERE customerId = :customerId
        )
    )
""")
suspend fun getProductsPurchasedByCustomer(customerId: Long): List<Product>

Transaction Annotations

@Dao
abstract class TransferDao {
    @Transaction
    open suspend fun transferMoney(fromId: Long, toId: Long, amount: Double) {
        deductBalance(fromId, amount)
        addBalance(toId, amount)
        recordTransfer(fromId, toId, amount)
    }

    @Query("UPDATE accounts SET balance = balance - :amount WHERE id = :id")
    abstract suspend fun deductBalance(id: Long, amount: Double)

    @Query("UPDATE accounts SET balance = balance + :amount WHERE id = :id")
    abstract suspend fun addBalance(id: Long, amount: Double)

    @Insert
    abstract suspend fun recordTransfer(transfer: Transfer)
}

@Transaction ensures all operations succeed or all roll back. Without it, a crash between deductBalance and addBalance leaves money in limbo.

FTS (Full-Text Search)

@Fts4
@Entity(tableName = "articles_fts")
data class ArticleFts(
    @ColumnInfo(name = "rowid") val rowId: Int,
    @ColumnInfo(name = "title") val title: String,
    @ColumnInfo(name = "body") val body: String
)

@Query("SELECT * FROM articles WHERE id IN (SELECT rowid FROM articles_fts WHERE articles_fts MATCH :query)")
suspend fun searchArticles(query: String): List<Article>

TypeConverters

SQLite stores only primitives: INTEGER, REAL, TEXT, BLOB. Room uses @TypeConverter to bridge complex Kotlin types.

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

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

    @TypeConverter
    fun fromList(value: List<String>): String = Gson().toJson(value)

    @TypeConverter
    fun toList(value: String): List<String> {
        val type = object : TypeToken<List<String>>() {}.type
        return Gson().fromJson(value, type)
    }
}

@Database(entities = [User::class], version = 1)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

Place converters at the @Database level so they apply globally. Per-entity converters work but cause confusion in larger codebases.

One-to-One

@Entity
val class User(@PrimaryKey val id: Long, val name: String)

@Entity(foreignKeys = [ForeignKey(
    entity = User::class,
    parentColumns = ["id"],
    childColumns = ["userId"],
    onDelete = ForeignKey.CASCADE
)])
data class UserProfile(
    @PrimaryKey val userId: Long,
    val bio: String
)

data class UserWithProfile(
    @Embedded val user: User,
    @Relation(parentColumn = "id", entityColumn = "userId")
    val profile: UserProfile?
)

One-to-Many

@Entity
data class Department(@PrimaryKey val id: Long, val name: String)

@Entity(foreignKeys = [ForeignKey(
    entity = Department::class,
    parentColumns = ["id"],
    childColumns = ["departmentId"]
)])
data class Employee(
    @PrimaryKey val id: Long,
    val name: String,
    val departmentId: Long
)

data class DepartmentWithEmployees(
    @Embedded val department: Department,
    @Relation(parentColumn = "id", entityColumn = "departmentId")
    val employees: List<Employee>
)

Many-to-Many

@Entity
data class Song(@PrimaryKey val id: Long, val title: String)

@Entity
data class Playlist(@PrimaryKey val id: Long, val name: String)

@Entity(
    primaryKeys = ["songId", "playlistId"],
    foreignKeys = [
        ForeignKey(entity = Song::class, parentColumns = ["id"], childColumns = ["songId"]),
        ForeignKey(entity = Playlist::class, parentColumns = ["id"], childColumns = ["playlistId"])
    ]
)
data class PlaylistSongCrossRef(
    val songId: Long,
    val playlistId: Long,
    val addedAt: Long
)

@Query("""
    SELECT * FROM Playlist
    INNER JOIN PlaylistSongCrossRef ON Playlist.id = PlaylistSongCrossRef.playlistId
    INNER JOIN Song ON Song.id = PlaylistSongCrossRef.songId
    WHERE Playlist.id = :playlistId
""")
suspend fun getSongsInPlaylist(playlistId: Long): List<Song>

Why Migrations Matter

When you change an entity, Room detects a schema mismatch and crashes the app at startup. Migrations bridge old and new schemas without losing user data.

Writing Manual Migrations

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

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("CREATE TABLE IF NOT EXISTS addresses (id INTEGER PRIMARY KEY NOT NULL, userId INTEGER NOT NULL, street TEXT, city TEXT)")
        db.execSQL("INSERT INTO addresses (id, userId, street, city) SELECT id, 0, '', '' FROM users")
    }
}

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

Auto-Migrations (Room 2.4+)

@Database(
    version = 3,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),
        AutoMigration(from = 2, to = 3, spec = Migration2To3::class)
    ]
)
abstract class AppDatabase : RoomDatabase()

@RenameColumn(tableName = "users", fromColumnName = "name", toColumnName = "fullName")
class Migration2To3 : AutoMigrationSpec

Auto-migrations handle column additions, renames, and table creations. Data transformations still require manual migrations.

Testing Migrations

@RunWith(AndroidJUnit4::class)
class MigrationTest {
    private val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test
    fun migrate1To2() {
        helper.createDatabase("app.db", 1).apply {
            execSQL("INSERT INTO users (id, name) VALUES (1, 'Alice')")
            close()
        }

        val db = helper.runMigrationsAndValidate("app.db", 2, true, MIGRATION_1_2)
        val cursor = db.query("SELECT email FROM users WHERE id = 1")
        cursor.moveToFirst()
        assertEquals("", cursor.getString(0))
    }
}

Always test migrations against real schemas. MigrationTestHelper validates that the migration SQL produces the expected result.