Data Migration

Migration Fundamentals

A migration runs a specific SQL script to transform the database schema from one version to the next. Room chains migrations — if you go from version 1 to 4, Room executes migration 1→2, 2→3, 3→4 in sequence.

Column-Level Operations

// Add a column
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE users ADD COLUMN phone TEXT")
    }
}

// Rename a column (SQLite doesn't support RENAME COLUMN before 3.25)
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("CREATE TABLE users_new (id INTEGER PRIMARY KEY, fullName TEXT, email TEXT)")
        db.execSQL("INSERT INTO users_new (id, fullName, email) SELECT id, name, email FROM users")
        db.execSQL("DROP TABLE users")
        db.execSQL("ALTER TABLE users_new RENAME TO users")
    }
}

Renaming a column requires creating a new table, copying data, dropping the old table, and renaming. This is the standard pattern for SQLite.

Data Transformations

val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Split "John Doe" into firstName + lastName
        db.execSQL("ALTER TABLE users ADD COLUMN firstName TEXT")
        db.execSQL("ALTER TABLE users ADD COLUMN lastName TEXT")
        db.execSQL("""
            UPDATE users SET
                firstName = substr(name, 1, instr(name, ' ') - 1),
                lastName = substr(name, instr(name, ' ') + 1)
            WHERE name LIKE '% %'
        """)
    }
}

Migration with Room

@Database(version = 5)
abstract class AppDatabase : RoomDatabase() {
    abstract fun noteDao(): NoteDao
}

val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4, MIGRATION_4_5)
    .build()

Fallback Strategy

When all else fails, destroy and recreate. This loses data but prevents crashes:

Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .fallbackToDestructiveMigration()
    .build()

Only use this during development. In production, try to recover data first.

When Cross-System Migration Is Needed

You may need to move data between storage systems when:

• Upgrading from SharedPreferences to DataStore
• Migrating from SQLite to Room
• Moving from local database to a cloud-synced architecture
• Changing the entire persistence layer during a rewrite

SharedPreferences to DataStore

suspend fun migrateToDataStore(context: Context) {
    val oldPrefs = context.getSharedPreferences("app_prefs", Context.MODE_PRIVATE)
    val dataStore = context.settingsDataStore
    
    dataStore.edit { prefs ->
        oldPrefs.all.forEach { (key, value) ->
            when (value) {
                is Boolean -> prefs[booleanPreferencesKey(key)] = value
                is Int -> prefs[intPreferencesKey(key)] = value
                is String -> prefs[stringPreferencesKey(key)] = value
                is Float -> prefs[floatPreferencesKey(key)] = value
                is Long -> prefs[longPreferencesKey(key)] = value
            }
        }
    }
    
    // Mark migration complete, then delete old file
    oldPrefs.edit().clear().apply()
    File(oldPrefs.fileParent, oldPrefs.file.name).delete()
}

SQLite to Room

Room can import an existing SQLite database:

// Copy the legacy database to Room's location
fun migrateToRoom(context: Context, legacyDbPath: String) {
    val roomDbPath = context.getDatabasePath("room_app.db")
    roomDbPath.parentFile?.mkdirs()
    
    File(legacyDbPath).inputStream().use { input ->
        roomDbPath.outputStream().use { output ->
            input.copyTo(output)
        }
    }
}

// Then open with Room and run any needed migrations
val db = Room.databaseBuilder(context, AppDatabase::class.java, "room_app.db")
    .createFromAsset("prepopulated.db")
    .addMigrations(MIGRATION_1_2)
    .build()

Progressive Rollout

Never migrate all users at once. Use feature flags:

class MigrationManager(private val context: Context) {
    suspend fun performMigration() {
        val dataStore = context.settingsDataStore.data.first()
        val migrated = dataStore[migrationCompleteKey] ?: false
        
        if (!migrated) {
            try {
                migrateToDataStore(context)
                context.settingsDataStore.edit {
                    it[migrationCompleteKey] = true
                }
            } catch (e: Exception) {
                Log.e("Migration", "Migration failed", e)
                // Don't set flag — will retry next launch
            }
        }
    }
}

Verifying Migration Success

suspend fun verifyMigration(context: Context): Boolean {
    val oldPrefs = context.getSharedPreferences("app_prefs", Context.MODE_PRIVATE)
    val newStore = context.settingsDataStore.data.first()
    
    return oldPrefs.all.all { (key, value) ->
        when (value) {
            is String -> newStore[stringPreferencesKey(key)] == value
            is Boolean -> newStore[booleanPreferencesKey(key)] == value
            is Int -> newStore[intPreferencesKey(key)] == value
            else -> true
        }
    }
}