Room Database Migrations: A Survival Guide

// Step 1: update the entity
@Entity(tableName = "users")
data class User(
    @PrimaryKey val id: Long,
    val name: String,
    val email: String,
    val avatarUrl: String? = null  // NEW COLUMN (v2)
)

// Step 2: bump the database version
@Database(
    entities = [User::class],
    version = 2,  // Was 1
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

// Step 3: define the migration (Room 3 signature)
val MIGRATION_1_2 = object : Migration(1, 2) {
    override suspend fun migrate(connection: SQLiteConnection) {
        connection.executeSQL(
            "ALTER TABLE users ADD COLUMN avatarUrl TEXT"
        )
    }
}

// Step 4: register it
val db = Room.databaseBuilder<AppDatabase>(context, "app.db")
    .setDriver(AndroidSQLiteDriver())
    .addMigrations(MIGRATION_1_2)
    .build()

// build.gradle.kts: where the schema JSON is written
plugins {
    id("androidx.room3")
}

room3 {
    schemaDirectory("$projectDir/schemas")
}

// Complex migration: split name into first_name + last_name
val MIGRATION_2_3 = object : Migration(2, 3) {
    override suspend fun migrate(connection: SQLiteConnection) {
        // 1. Create the new table under a temporary name
        connection.executeSQL("""
            CREATE TABLE users_new (
                id INTEGER PRIMARY KEY NOT NULL,
                first_name TEXT NOT NULL DEFAULT '',
                last_name TEXT NOT NULL DEFAULT '',
                email TEXT NOT NULL,
                avatarUrl TEXT
            )
        """)

        // 2. Copy and transform the data
        connection.executeSQL("""
            INSERT INTO users_new (
                id, first_name, last_name, email, avatarUrl
            )
            SELECT
                id,
                CASE
                    WHEN INSTR(name, ' ') > 0
                    THEN SUBSTR(name, 1, INSTR(name, ' ') - 1)
                    ELSE name
                END,
                CASE
                    WHEN INSTR(name, ' ') > 0
                    THEN SUBSTR(name, INSTR(name, ' ') + 1)
                    ELSE ''
                END,
                email,
                avatarUrl
            FROM users
        """)

        // 3. Drop the old table, 4. rename the new one. In this order.
        connection.executeSQL("DROP TABLE users")
        connection.executeSQL("ALTER TABLE users_new RENAME TO users")

        // 5. Recreate the indexes, triggers and views the old
        //    table had. This one had none.
    }
}

@Database(
    entities = [User::class, Settings::class],
    version = 5,
    autoMigrations = [
        AutoMigration(from = 3, to = 4),
        AutoMigration(
            from = 4,
            to = 5,
            spec = Migration4To5::class
        ),
    ],
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun settingsDao(): SettingsDao
}

// A spec is needed when Room cannot infer the change
@RenameColumn(
    tableName = "users",
    fromColumnName = "avatarUrl",
    toColumnName = "profile_image_url"
)
@DeleteColumn(
    tableName = "settings",
    columnName = "legacy_theme"
)
class Migration4To5 : AutoMigrationSpec

@RunWith(AndroidJUnit4::class)
class MigrationTest {

    private val instrumentation =
        InstrumentationRegistry.getInstrumentation()

    @get:Rule
    val helper = MigrationTestHelper(
        instrumentation = instrumentation,
        databaseClass = AppDatabase::class,
        driver = AndroidSQLiteDriver(),
        file = instrumentation.targetContext.getDatabasePath("migration-test"),
    )

    @Test
    fun migrate1To2() = runTest {
        // Create the database at version 1 and insert with SQL.
        // DAOs expect the latest schema, so they cannot be used here.
        val connection = helper.createDatabase(1)
        connection.execSQL(
            "INSERT INTO users (id, name, email) " +
                "VALUES (1, 'Jane Doe', 'jane@example.com')"
        )
        connection.close()

        // Re-open at version 2. The helper validates the schema.
        val migrated = helper.runMigrationsAndValidate(
            2, listOf(MIGRATION_1_2)
        )

        // The data is yours to check.
        val survived = migrated
            .prepare("SELECT COUNT(*) FROM users").use {
                it.step()
                it.getLong(0) == 1L
            }
        assertTrue("Row was lost in the migration", survived)
        migrated.close()
    }

    @Test
    fun migrateAll() = runTest {
        // Earliest version of the database
        helper.createDatabase(1).close()

        // Latest version, with every manual migration registered.
        // Room validates the schema when it opens.
        val db = Room.databaseBuilder<AppDatabase>(
            instrumentation.targetContext, "migration-test"
        )
            .setDriver(AndroidSQLiteDriver())
            .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
            .build()

        db.useReaderConnection { }
        db.close()
    }
}