Room Relationships, Migrations and Offline-First
One-to-Many
kotlin
@Entity(tableName = "categories")
data class CategoryEntity(@PrimaryKey(autoGenerate = true) val id: Int = 0, val name: String)
@Entity(
tableName = "expenses",
foreignKeys = [ForeignKey(
entity = CategoryEntity::class,
parentColumns = ["id"], childColumns = ["categoryId"],
onDelete = ForeignKey.CASCADE // delete expenses when the category is deleted
)],
indices = [Index("categoryId")]
)
data class ExpenseEntity(
@PrimaryKey(autoGenerate = true) val id: Int = 0,
val title: String,
val amount: Double,
val categoryId: Int
)
data class CategoryWithExpenses(
@Embedded val category: CategoryEntity,
@Relation(parentColumn = "id", entityColumn = "categoryId")
val expenses: List<ExpenseEntity>
)
@Dao
interface CategoryDao {
@Transaction
@Query("SELECT * FROM categories")
fun observeWithExpenses(): Flow<List<CategoryWithExpenses>>
}
Many-to-Many
kotlin
@Entity(primaryKeys = ["postId", "tagId"])
data class PostTagCrossRef(val postId: Int, val tagId: Int)
data class PostWithTags(
@Embedded val post: PostEntity,
@Relation(parentColumn = "id", entityColumn = "id",
associateBy = Junction(PostTagCrossRef::class, parentColumn = "postId", entityColumn = "tagId"))
val tags: List<TagEntity>
)
Aggregations
kotlin
data class CategoryTotal(val name: String, val total: Double)
@Query("""
SELECT c.name AS name, SUM(e.amount) AS total
FROM expenses e INNER JOIN categories c ON c.id = e.categoryId
GROUP BY c.id
""")
fun totalsByCategory(): Flow<List<CategoryTotal>>
Migrations
When you change a table, increase the database version and tell Room how to upgrade. Otherwise users with old data will crash.
kotlin
@Database(
entities = [NoteEntity::class],
version = 2,
autoMigrations = [AutoMigration(from = 1, to = 2)], // simple changes like adding a column with a default
exportSchema = true
)
abstract class AppDatabase : RoomDatabase()
// Manual migration for more complex changes
val MIGRATION_2_3 = object : Migration(2, 3) {
override fun migrate(db: SupportSQLiteDatabase) {
db.execSQL("ALTER TABLE notes ADD COLUMN archived INTEGER NOT NULL DEFAULT 0")
}
}
Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
.addMigrations(MIGRATION_2_3)
.build()
Never use fallbackToDestructiveMigration in production: It deletes all user data on schema changes. It is acceptable only while developing.
Offline-First Repository
The database is the single source of truth. The UI reads from Room. The repository refreshes Room from the network.
kotlin
class ArticleRepository(
private val api: ArticleApi,
private val dao: ArticleDao
) {
val articles: Flow<List<Article>> = dao.observeAll().map { list -> list.map { it.toDomain() } }
suspend fun refresh(): Result<Unit> = runCatching {
val remote = api.getArticles()
dao.replaceAll(remote.map { it.toEntity() }) // @Transaction: delete old and insert new
}
}
@Dao
interface ArticleDao {
@Query("SELECT * FROM articles ORDER BY publishedAt DESC") fun observeAll(): Flow<List<ArticleEntity>>
@Query("DELETE FROM articles") suspend fun clear()
@Insert suspend fun insertAll(items: List<ArticleEntity>)
@Transaction suspend fun replaceAll(items: List<ArticleEntity>) { clear(); insertAll(items) }
}
Result: The app shows saved data instantly, even offline. When the refresh succeeds, Room emits new data and the UI updates by itself.
Conclusion
Relations connect tables, migrations protect user data on updates and an offline-first design makes your app fast and reliable.
Quick Summary: Use @Relation with @Embedded, add migrations whenever the schema changes and let Room be the source of truth that the network refreshes.

