Exercise: Zero-Downtime Migration

Copy code@Entity(tableName = "posts")
data class PostEntity(
    @PrimaryKey val id: String,
    val title: String,
    val body: String,
    val category: String,   // was stored as "TECH", "SPORTS", "NEWS"
    val publishedAt: Long
)

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

Copy codeval MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Add new columns — non-destructive, safe for existing users
        database.execSQL(
            "ALTER TABLE posts ADD COLUMN summary TEXT"
        )
        database.execSQL(
            "ALTER TABLE posts ADD COLUMN is_featured INTEGER NOT NULL DEFAULT 0"
        )

        // Normalize existing category values (clean up inconsistent casing)
        database.execSQL(
            "UPDATE posts SET category = UPPER(category)"
        )
    }
}

Copy codeenum class PostCategory { TECH, SPORTS, NEWS, OTHER }

@Entity(tableName = "posts")
data class PostEntity(
    @PrimaryKey val id: String,
    val title: String,
    val body: String,
    val category: String,       // still stored as string; converter handles enum
    val summary: String?,       // NEW — nullable
    val isFeatured: Boolean,    // NEW — non-null with default
    val publishedAt: Long
)

// TypeConverter for the enum
class PostConverters {
    @TypeConverter fun categoryToString(c: PostCategory): String = c.name
    @TypeConverter fun stringToCategory(s: String): PostCategory =
        try { PostCategory.valueOf(s) } catch (e: IllegalArgumentException) { PostCategory.OTHER }
}

Copy code@Database(entities = [PostEntity::class], version = 2)
@TypeConverters(PostConverters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun postDao(): PostDao

    companion object {
        fun build(context: Context) = Room.databaseBuilder(
            context.applicationContext,
            AppDatabase::class.java,
            "app.db"
        )
        .addMigrations(MIGRATION_1_2)
        // ❌ NEVER use this in production:
        // .fallbackToDestructiveMigration()
        .build()
    }
}

Copy code@RunWith(AndroidJUnit4::class)
class PostMigrationTest {
    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test
    fun migrate1To2PreservesData() {
        // Create v1 database with data
        val db = helper.createDatabase(TEST_DB, 1)
        db.execSQL("""
            INSERT INTO posts VALUES ('p1', 'Hello', 'World', 'tech', 1700000000)
        """)
        db.close()

        // Run migration
        val migratedDb = helper.runMigrationsAndValidate(TEST_DB, 2, true, MIGRATION_1_2)

        // Verify data preserved
        val cursor = migratedDb.query("SELECT * FROM posts WHERE id = 'p1'")
        assertTrue("Post should exist after migration", cursor.moveToFirst())
        assertEquals("Hello", cursor.getString(cursor.getColumnIndex("title")))

        // Verify new columns
        assertNull(cursor.getString(cursor.getColumnIndex("summary")))  // nullable
        assertEquals(0, cursor.getInt(cursor.getColumnIndex("is_featured")))  // default

        // Verify category normalized
        assertEquals("TECH", cursor.getString(cursor.getColumnIndex("category")))
        cursor.close()
    }

    @Test
    fun migrate1To2WithLargeDataset() {
        val db = helper.createDatabase(TEST_DB, 1)
        // Insert 10,000 rows
        db.beginTransaction()
        try {
            repeat(10_000) { i ->
                db.execSQL("INSERT INTO posts VALUES ('p$i', 'Title $i', 'Body', 'TECH', ${System.currentTimeMillis()})")
            }
            db.setTransactionSuccessful()
        } finally {
            db.endTransaction()
        }
        db.close()

        // Migration should complete in reasonable time
        val startTime = System.currentTimeMillis()
        helper.runMigrationsAndValidate(TEST_DB, 2, true, MIGRATION_1_2)
        val elapsed = System.currentTimeMillis() - startTime

        assertTrue("Migration should complete in < 5 seconds", elapsed < 5000)
    }
}

Copy codeval MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Only do schema changes here — fast and required
        database.execSQL("ALTER TABLE posts ADD COLUMN summary TEXT")
        database.execSQL("ALTER TABLE posts ADD COLUMN is_featured INTEGER NOT NULL DEFAULT 0")

        // Heavy data transformation: schedule as background job
        // Store a flag indicating backfill is needed
        database.execSQL("CREATE TABLE IF NOT EXISTS migration_flags (name TEXT PRIMARY KEY, done INTEGER NOT NULL DEFAULT 0)")
        database.execSQL("INSERT OR REPLACE INTO migration_flags VALUES ('normalize_categories', 0)")
    }
}

// BackfillWorker reads the flag, does the work, sets done=1
class CategoryNormalizationWorker(...) : CoroutineWorker(...) {
    override suspend fun doWork(): Result {
        db.query("SELECT done FROM migration_flags WHERE name = 'normalize_categories'").use { c ->
            if (c.moveToFirst() && c.getInt(0) == 1) return Result.success()  // already done
        }
        db.execSQL("UPDATE posts SET category = UPPER(category)")
        db.execSQL("UPDATE migration_flags SET done = 1 WHERE name = 'normalize_categories'")
        return Result.success()
    }
}