Tools: Room Database Migrations: Changing Your Schema Without Losing User Data

Introduction ## Why Database Migrations Matter ## The Room Entity and Schema Version ## Writing Your First Migration ## Common Migration Patterns ## Adding a Column ## Renaming a Column ## Removing a Column ## Adding a Foreign Key Constraint ## Destructive Migrations ## Automatic Migrations (Room 2.4+) ## Testing Migrations ## Best Practices ## Conclusion Room Database is Android's abstraction layer over SQLite, providing compile-time checked SQL queries and reactive data flow. As your app evolves, your database schema will inevitably change: adding new columns, changing data types, removing obsolete fields. This is where migrations come in. Database migrations are one of the trickiest parts of app development. Mishandled migrations can corrupt user data, cause app crashes in production, or result in data loss. In this guide, we'll explore Room's migration system, from basic column additions to advanced schema changes, and how to test them properly. When you release your app to production and users install it, their local databases are in a specific state defined by your app's schema. When you push an update with schema changes, Room needs to know how to transform the old database to the new one without losing user data. Consider this scenario: If you simply change your Entity definition, Room will see the schema mismatch and throw a crash on app startup. Without a migration, users can't upgrade your app. With a migration, their existing data stays intact while the schema evolves. Let's start with the basics. Every Room database has a schema version number: When you update your schema, increment the version number. Room uses this number to determine which migrations to apply: Important: Always set exportSchema = true. This generates JSON schema files in your build/ directory, which you should commit to version control. These serve as documentation and help you write accurate migrations. A migration is a class that extends Migration and implements the upgrade logic: You then add this migration to your database instance: Room will apply migrations automatically in order. When a user upgrades from version 1 to version 2, Room detects the schema mismatch, finds that a migration exists from 1→2, and applies it. The simplest migration: SQLite's ALTER TABLE is limited, but adding columns is always safe. Provide a DEFAULT value or allow NULL to avoid issues with existing rows. SQLite's ALTER TABLE RENAME COLUMN was added in SQLite 3.25 (Room 2.1+). Older versions require a workaround: Also requires the table recreation pattern: A destructive migration loses data. Examples: Room does NOT apply destructive migrations by default, throwing an error instead: If you're certain a destructive migration is acceptable (perhaps for development or a feature where data loss is acceptable), enable it: Warning: Never ship this to production without careful consideration. Users will lose data. Room 2.4 introduced automatic migrations, which generate migration code for simple schema changes like adding columns: For complex changes, you can specify spec classes: Automatic migrations reduce boilerplate but only work for non-destructive changes. Always test migrations before shipping: Version your schema: Always increment version when changing entities. Never skip versions. Provide sensible defaults: When adding columns, use DEFAULT values to avoid NULL issues. Test all migrations: Use MigrationTestHelper for every migration path. Test edge cases. Commit schema JSON files: Check in schemas/ directory so you can generate migrations from diffs. Chain migrations carefully: If jumping from v1 to v5, Room applies v1→2, v2→3, v3→4, v4→5 in order. Ensure they're backward compatible. Document schema changes: Add comments explaining why a migration exists and what it does. Avoid destructive changes in production: If you must remove a column, handle it gracefully. Consider deprecation periods. Use transactions: Room applies all migrations in a transaction. If one fails, the entire set rolls back. Database migrations are essential for shipping app updates with schema changes. Room provides a robust migration system through explicit Migration classes and (in 2.4+) automatic migrations. By testing migrations thoroughly and following best practices, you can evolve your app's data model confidently. My 8 templates use Room with migration-ready schemas. https://myougatheax.gumroad.com Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse CODE_BLOCK: @Database( entities = [User::class], version = 1, exportSchema = true ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: @Database( entities = [User::class], version = 1, exportSchema = true ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } CODE_BLOCK: @Database( entities = [User::class], version = 1, exportSchema = true ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } CODE_BLOCK: @Database( entities = [User::class], version = 2, // Updated from 1 to 2 exportSchema = true ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: @Database( entities = [User::class], version = 2, // Updated from 1 to 2 exportSchema = true ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } CODE_BLOCK: @Database( entities = [User::class], version = 2, // Updated from 1 to 2 exportSchema = true ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } CODE_BLOCK: val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(database: SupportSQLiteDatabase) { // Add new email column with default value database.execSQL( "ALTER TABLE User ADD COLUMN email TEXT NOT NULL DEFAULT ''" ) } } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(database: SupportSQLiteDatabase) { // Add new email column with default value database.execSQL( "ALTER TABLE User ADD COLUMN email TEXT NOT NULL DEFAULT ''" ) } } CODE_BLOCK: val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(database: SupportSQLiteDatabase) { // Add new email column with default value database.execSQL( "ALTER TABLE User ADD COLUMN email TEXT NOT NULL DEFAULT ''" ) } } CODE_BLOCK: val db = Room.databaseBuilder( context, AppDatabase::class.java, "myapp.db" ) .addMigrations(MIGRATION_1_2) .build() Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: val db = Room.databaseBuilder( context, AppDatabase::class.java, "myapp.db" ) .addMigrations(MIGRATION_1_2) .build() CODE_BLOCK: val db = Room.databaseBuilder( context, AppDatabase::class.java, "myapp.db" ) .addMigrations(MIGRATION_1_2) .build() CODE_BLOCK: val MIGRATION_2_3 = object : Migration(2, 3) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL( "ALTER TABLE User ADD COLUMN phone TEXT DEFAULT NULL" ) } } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: val MIGRATION_2_3 = object : Migration(2, 3) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL( "ALTER TABLE User ADD COLUMN phone TEXT DEFAULT NULL" ) } } CODE_BLOCK: val MIGRATION_2_3 = object : Migration(2, 3) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL( "ALTER TABLE User ADD COLUMN phone TEXT DEFAULT NULL" ) } } CODE_BLOCK: val MIGRATION_3_4 = object : Migration(3, 4) { override fun migrate(database: SupportSQLiteDatabase) { // Create new table with renamed column database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email_address TEXT)") // Copy data database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User") // Drop old table and rename database.execSQL("DROP TABLE User") database.execSQL("ALTER TABLE User_new RENAME TO User") } } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: val MIGRATION_3_4 = object : Migration(3, 4) { override fun migrate(database: SupportSQLiteDatabase) { // Create new table with renamed column database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email_address TEXT)") // Copy data database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User") // Drop old table and rename database.execSQL("DROP TABLE User") database.execSQL("ALTER TABLE User_new RENAME TO User") } } CODE_BLOCK: val MIGRATION_3_4 = object : Migration(3, 4) { override fun migrate(database: SupportSQLiteDatabase) { // Create new table with renamed column database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email_address TEXT)") // Copy data database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User") // Drop old table and rename database.execSQL("DROP TABLE User") database.execSQL("ALTER TABLE User_new RENAME TO User") } } CODE_BLOCK: val MIGRATION_4_5 = object : Migration(4, 5) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email TEXT)") database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User") database.execSQL("DROP TABLE User") database.execSQL("ALTER TABLE User_new RENAME TO User") } } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: val MIGRATION_4_5 = object : Migration(4, 5) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email TEXT)") database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User") database.execSQL("DROP TABLE User") database.execSQL("ALTER TABLE User_new RENAME TO User") } } CODE_BLOCK: val MIGRATION_4_5 = object : Migration(4, 5) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email TEXT)") database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User") database.execSQL("DROP TABLE User") database.execSQL("ALTER TABLE User_new RENAME TO User") } } CODE_BLOCK: val MIGRATION_5_6 = object : Migration(5, 6) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("CREATE TABLE Address (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, street TEXT, FOREIGN KEY(user_id) REFERENCES User(id))") } } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: val MIGRATION_5_6 = object : Migration(5, 6) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("CREATE TABLE Address (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, street TEXT, FOREIGN KEY(user_id) REFERENCES User(id))") } } CODE_BLOCK: val MIGRATION_5_6 = object : Migration(5, 6) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("CREATE TABLE Address (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, street TEXT, FOREIGN KEY(user_id) REFERENCES User(id))") } } CODE_BLOCK: Schema is not a valid Room database. The following problems were detected: Migration didn't properly handle... Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: Schema is not a valid Room database. The following problems were detected: Migration didn't properly handle... CODE_BLOCK: Schema is not a valid Room database. The following problems were detected: Migration didn't properly handle... CODE_BLOCK: val db = Room.databaseBuilder( context, AppDatabase::class.java, "myapp.db" ) .addMigrations(MIGRATION_5_6) .fallbackToDestructiveMigrationOnDowngrade() // Beware! .build() Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: val db = Room.databaseBuilder( context, AppDatabase::class.java, "myapp.db" ) .addMigrations(MIGRATION_5_6) .fallbackToDestructiveMigrationOnDowngrade() // Beware! .build() CODE_BLOCK: val db = Room.databaseBuilder( context, AppDatabase::class.java, "myapp.db" ) .addMigrations(MIGRATION_5_6) .fallbackToDestructiveMigrationOnDowngrade() // Beware! .build() CODE_BLOCK: @Database( entities = [User::class], version = 3, autoMigrations = [ AutoMigration(from = 2, to = 3) ] ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: @Database( entities = [User::class], version = 3, autoMigrations = [ AutoMigration(from = 2, to = 3) ] ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } CODE_BLOCK: @Database( entities = [User::class], version = 3, autoMigrations = [ AutoMigration(from = 2, to = 3) ] ) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } CODE_BLOCK: class MIGRATION_2_3 : AutoMigrationSpec { override fun onPostMigrate(database: SupportSQLiteDatabase) { // Custom logic after auto-generated SQL database.execSQL("UPDATE User SET updated_at = datetime('now')") } } @Database( entities = [User::class], version = 3, autoMigrations = [ AutoMigration(from = 2, to = 3, spec = MIGRATION_2_3::class) ] ) abstract class AppDatabase : RoomDatabase() { } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: class MIGRATION_2_3 : AutoMigrationSpec { override fun onPostMigrate(database: SupportSQLiteDatabase) { // Custom logic after auto-generated SQL database.execSQL("UPDATE User SET updated_at = datetime('now')") } } @Database( entities = [User::class], version = 3, autoMigrations = [ AutoMigration(from = 2, to = 3, spec = MIGRATION_2_3::class) ] ) abstract class AppDatabase : RoomDatabase() { } CODE_BLOCK: class MIGRATION_2_3 : AutoMigrationSpec { override fun onPostMigrate(database: SupportSQLiteDatabase) { // Custom logic after auto-generated SQL database.execSQL("UPDATE User SET updated_at = datetime('now')") } } @Database( entities = [User::class], version = 3, autoMigrations = [ AutoMigration(from = 2, to = 3, spec = MIGRATION_2_3::class) ] ) abstract class AppDatabase : RoomDatabase() { } CODE_BLOCK: @RunWith(AndroidJUnit4::class) class MigrationTest { private lateinit var testHelper: MigrationTestHelper @get:Rule val helper: MigrationTestHelper = MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), AppDatabase::class.java.canonicalName, FrameworkSQLiteOpenHelperFactory() ) @Test fun testMigrationFrom1To2() { // Create v1 database var db = helper.createDatabase(TEST_DB_NAME, 1) db.execSQL("INSERT INTO User VALUES (1, 'Alice')") db.close() // Run migration db = helper.runMigrationsAndValidate( TEST_DB_NAME, 2, validateDroppedTables = true, MIGRATION_1_2 ) // Verify results val cursor = db.query("SELECT * FROM User WHERE id = 1") cursor.moveToFirst() assert(cursor.getString(1) == "Alice") // Name preserved assert(cursor.isNull(2)) // Email is NULL db.close() } } Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: @RunWith(AndroidJUnit4::class) class MigrationTest { private lateinit var testHelper: MigrationTestHelper @get:Rule val helper: MigrationTestHelper = MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), AppDatabase::class.java.canonicalName, FrameworkSQLiteOpenHelperFactory() ) @Test fun testMigrationFrom1To2() { // Create v1 database var db = helper.createDatabase(TEST_DB_NAME, 1) db.execSQL("INSERT INTO User VALUES (1, 'Alice')") db.close() // Run migration db = helper.runMigrationsAndValidate( TEST_DB_NAME, 2, validateDroppedTables = true, MIGRATION_1_2 ) // Verify results val cursor = db.query("SELECT * FROM User WHERE id = 1") cursor.moveToFirst() assert(cursor.getString(1) == "Alice") // Name preserved assert(cursor.isNull(2)) // Email is NULL db.close() } } CODE_BLOCK: @RunWith(AndroidJUnit4::class) class MigrationTest { private lateinit var testHelper: MigrationTestHelper @get:Rule val helper: MigrationTestHelper = MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), AppDatabase::class.java.canonicalName, FrameworkSQLiteOpenHelperFactory() ) @Test fun testMigrationFrom1To2() { // Create v1 database var db = helper.createDatabase(TEST_DB_NAME, 1) db.execSQL("INSERT INTO User VALUES (1, 'Alice')") db.close() // Run migration db = helper.runMigrationsAndValidate( TEST_DB_NAME, 2, validateDroppedTables = true, MIGRATION_1_2 ) // Verify results val cursor = db.query("SELECT * FROM User WHERE id = 1") cursor.moveToFirst() assert(cursor.getString(1) == "Alice") // Name preserved assert(cursor.isNull(2)) // Email is NULL db.close() } } - Version 1 of your app: Users table has id and name columns - Version 2 of your app: You want to add an email column - Removing a column with important data - Truncating a table - Changing a column's data type (often requires recreating the table with conversion logic) - Creates a v1 database with test data - Applies the migration to v2 - Validates the schema matches your entities - Verifies data integrity - Version your schema: Always increment version when changing entities. Never skip versions. - Provide sensible defaults: When adding columns, use DEFAULT values to avoid NULL issues. - Test all migrations: Use MigrationTestHelper for every migration path. Test edge cases. - Commit schema JSON files: Check in schemas/ directory so you can generate migrations from diffs. - Chain migrations carefully: If jumping from v1 to v5, Room applies v1→2, v2→3, v3→4, v4→5 in order. Ensure they're backward compatible. - Document schema changes: Add comments explaining why a migration exists and what it does. - Avoid destructive changes in production: If you must remove a column, handle it gracefully. Consider deprecation periods. - Use transactions: Room applies all migrations in a transaction. If one fails, the entire set rolls back.