Room Architecture & Migrations

Copy code@Entity(
    tableName = "articles",
    indices = [
        Index(value = ["author_id"]),           // speed up foreign key joins
        Index(value = ["slug"], unique = true)  // enforce uniqueness
    ],
    foreignKeys = [
        ForeignKey(
            entity = AuthorEntity::class,
            parentColumns = ["id"],
            childColumns = ["author_id"],
            onDelete = ForeignKey.CASCADE  // delete articles when author deleted
        )
    ]
)
data class ArticleEntity(
    @PrimaryKey val id: String,
    @ColumnInfo(name = "author_id") val authorId: String,
    val title: String,
    val slug: String,
    val body: String,
    @ColumnInfo(name = "published_at") val publishedAt: Long,
    @ColumnInfo(name = "is_bookmarked") val isBookmarked: Boolean = false
)

// Embedded: flatten a nested object into the same table row
data class Address(
    val street: String,
    val city: String,
    val country: String
)

@Entity(tableName = "users")
data class UserEntity(
    @PrimaryKey val id: String,
    val name: String,
    @Embedded(prefix = "home_") val homeAddress: Address?,
    @Embedded(prefix = "work_") val workAddress: Address?
)

Copy codeclass Converters {
    // List<String> <-> JSON string
    @TypeConverter
    fun fromStringList(list: List<String>): String = Gson().toJson(list)

    @TypeConverter
    fun toStringList(json: String): List<String> =
        Gson().fromJson(json, object : TypeToken<List<String>>() {}.type)

    // Instant <-> Long
    @TypeConverter
    fun fromInstant(instant: Instant?): Long? = instant?.toEpochMilli()

    @TypeConverter
    fun toInstant(epoch: Long?): Instant? = epoch?.let { Instant.ofEpochMilli(it) }
}

// Register converters on the database
@Database(entities = [ArticleEntity::class], version = 1)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun articleDao(): ArticleDao
}

Copy code@Dao
interface ArticleDao {

    // Suspend functions: one-shot operations
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(articles: List<ArticleEntity>)

    @Update
    suspend fun update(article: ArticleEntity)

    @Delete
    suspend fun delete(article: ArticleEntity)

    @Query("DELETE FROM articles WHERE author_id = :authorId")
    suspend fun deleteByAuthor(authorId: String)

    // Flow: reactive queries — emits new value whenever data changes
    @Query("SELECT * FROM articles ORDER BY published_at DESC")
    fun observeAll(): Flow<List<ArticleEntity>>

    @Query("SELECT * FROM articles WHERE id = :id")
    fun observeById(id: String): Flow<ArticleEntity?>

    // Filtered query with parameters
    @Query("""
        SELECT * FROM articles 
        WHERE author_id = :authorId 
        AND is_bookmarked = :bookmarked 
        ORDER BY published_at DESC
        LIMIT :limit OFFSET :offset
    """)
    suspend fun getByAuthorPaged(
        authorId: String,
        bookmarked: Boolean = false,
        limit: Int = 20,
        offset: Int = 0
    ): List<ArticleEntity>

    // Partial update with @Query is more efficient than loading + updating
    @Query("UPDATE articles SET is_bookmarked = :bookmarked WHERE id = :id")
    suspend fun setBookmarked(id: String, bookmarked: Boolean)

    // Count
    @Query("SELECT COUNT(*) FROM articles WHERE author_id = :authorId")
    fun observeCountByAuthor(authorId: String): Flow<Int>
}

Copy code// Relation: join two entities in Kotlin space (not SQL JOIN)
data class ArticleWithAuthor(
    @Embedded val article: ArticleEntity,
    @Relation(
        parentColumn = "author_id",
        entityColumn = "id"
    )
    val author: AuthorEntity
)

@Dao
interface ArticleDao {
    // @Transaction required for @Relation queries
    @Transaction
    @Query("SELECT * FROM articles WHERE id = :id")
    fun observeArticleWithAuthor(id: String): Flow<ArticleWithAuthor?>

    // Atomic write: delete old, insert new
    @Transaction
    suspend fun replaceAll(articles: List<ArticleEntity>) {
        deleteAll()
        insertAll(articles)
    }

    @Query("DELETE FROM articles")
    suspend fun deleteAll()
}

Copy code@Database(
    entities = [ArticleEntity::class, AuthorEntity::class, RemoteKeyEntity::class],
    version = 2,
    exportSchema = true  // generates schema JSON files — check into version control!
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun articleDao(): ArticleDao
    abstract fun authorDao(): AuthorDao
    abstract fun remoteKeyDao(): RemoteKeyDao

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

        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )
                    .addMigrations(MIGRATION_1_2)
                    .build()
                    .also { INSTANCE = it }
            }
        }
    }
}

Copy code@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {

    @Provides
    @Singleton
    fun provideDatabase(@ApplicationContext context: Context): AppDatabase =
        Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
            .addMigrations(MIGRATION_1_2)
            .build()

    @Provides
    fun provideArticleDao(db: AppDatabase): ArticleDao = db.articleDao()
}

Copy code// Migration from version 1 to version 2: add a column
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // SQLite doesn't support ALTER COLUMN — only ADD COLUMN
        database.execSQL("""
            ALTER TABLE articles 
            ADD COLUMN read_count INTEGER NOT NULL DEFAULT 0
        """)
    }
}

// Migration 2 to 3: rename table (requires recreate in SQLite)
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // SQLite can't rename columns/tables in older Android versions
        // Create new table, copy data, drop old
        database.execSQL("""
            CREATE TABLE articles_new (
                id TEXT PRIMARY KEY NOT NULL,
                title TEXT NOT NULL,
                body TEXT NOT NULL,
                author_id TEXT NOT NULL,
                slug TEXT NOT NULL,
                published_at INTEGER NOT NULL,
                is_bookmarked INTEGER NOT NULL DEFAULT 0,
                read_count INTEGER NOT NULL DEFAULT 0
            )
        """)
        database.execSQL("""
            INSERT INTO articles_new SELECT * FROM articles
        """)
        database.execSQL("DROP TABLE articles")
        database.execSQL("ALTER TABLE articles_new RENAME TO articles")
    }
}

// Multi-step migration path (Room handles skipped versions automatically)
Room.databaseBuilder(...)
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
    .build()

Copy code@RunWith(AndroidJUnit4::class)
class ArticleDaoTest {

    private lateinit var db: AppDatabase
    private lateinit var dao: ArticleDao

    @Before
    fun setup() {
        db = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        )
            .allowMainThreadQueries() // only in tests
            .build()
        dao = db.articleDao()
    }

    @After
    fun teardown() = db.close()

    @Test
    fun insertAndRetrieve() = runTest {
        val article = ArticleEntity(
            id = "1", authorId = "a1", title = "Test", slug = "test",
            body = "Body", publishedAt = 1000L
        )
        dao.insertAll(listOf(article))
        val all = dao.observeAll().first()
        assertEquals(1, all.size)
        assertEquals("Test", all.first().title)
    }

    @Test
    fun bookmarkUpdatesCorrectly() = runTest {
        dao.insertAll(listOf(article))
        dao.setBookmarked("1", true)
        val result = dao.observeAll().first()
        assertTrue(result.first().isBookmarked)
    }
}

Copy code@RunWith(AndroidJUnit4::class)
class MigrationTest {

    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test
    fun migrate1to2() {
        // Create database at version 1
        helper.createDatabase("test_db", 1).apply {
            execSQL("INSERT INTO articles VALUES ('1', 'a1', 'Title', 'slug', 'Body', 1000, 0)")
            close()
        }

        // Run migration and validate
        val db = helper.runMigrationsAndValidate("test_db", 2, true, MIGRATION_1_2)
        val cursor = db.query("SELECT read_count FROM articles WHERE id = '1'")
        cursor.moveToFirst()
        assertEquals(0, cursor.getInt(0)) // default value applied
        cursor.close()
    }
}