Data migration on mobile app update

Implementing Data Migration During Mobile App Updates

Schema migration and data migration are different tasks. You can write perfect ALTER TABLE, but still get corrupted data in production. This happens when not table structure changes, but format or semantics of stored values: dates transition from Unix timestamp to ISO 8601, amounts from float to integer-cents, statuses from numeric codes to string enums. Such transformations require explicit processing for each row.

What Data Migration Means in Practice

Concrete scenarios from real projects:

• Field amount stored as REAL (double), need to transition to INTEGER cents to avoid floating-point comparison errors. 100.10 → 10010.
• Field status was INTEGER (0, 1, 2), now TEXT ("pending", "active", "completed"). Need to map each number to string.
• Field date was Unix timestamp in seconds, new version — milliseconds. 1700000000 → 1700000000000.
• JSON stored in TEXT column changed structure: old {"items":[...]} → new {"data":{"list":[...]}}.

Each of these cases — row transformation of entire table inside migration.

Implementation in Room (Android)

val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Convert amount from float to integer cents
        db.execSQL("""
            UPDATE transactions
            SET amount_cents = CAST(ROUND(amount * 100) AS INTEGER)
        """)

        // Convert status from int to string
        db.execSQL("UPDATE transactions SET status = 'pending' WHERE status_code = 0")
        db.execSQL("UPDATE transactions SET status = 'active'  WHERE status_code = 1")
        db.execSQL("UPDATE transactions SET status = 'completed' WHERE status_code = 2")

        // Convert timestamp from seconds to milliseconds
        db.execSQL("UPDATE events SET created_at = created_at * 1000 WHERE created_at < 9999999999")
    }
}

Condition WHERE created_at < 9999999999 in last example protects from accidental re-application — date in milliseconds always greater than this number.

Batch Update for Large Tables

If table has millions of rows — single UPDATE can take tens of seconds and block startup. Batch approach:

val MIGRATION_4_5 = object : Migration(4, 5) {
    override fun migrate(db: SupportSQLiteDatabase) {
        var offset = 0
        val batchSize = 1000
        while (true) {
            val updated = db.compileStatement("""
                UPDATE transactions
                SET metadata = transform_metadata(metadata)
                WHERE id IN (
                    SELECT id FROM transactions
                    WHERE metadata_migrated = 0
                    LIMIT $batchSize
                )
            """).executeUpdateDelete()
            if (updated == 0) break
        }
    }
}

For very large tables (500,000+ rows) — migrate data lazily: on first record access, not in onUpgrade. Add flag field migrated INTEGER DEFAULT 0 and transform on read.

Lazy Data Migration

When full migration takes too long for blocking execution at startup:

// iOS — lazy migration on data access
func fetchTransaction(id: String) -> Transaction {
    let raw = database.fetch(id: id)
    if !raw.isMigrated {
        let migrated = DataMigrator.migrate(raw)
        database.save(migrated)
        return migrated
    }
    return raw
}

Plus: app starts instantly. Minus: need to support both formats in code while not all records migrated. Background WorkManager / BGProcessingTask gradually migrates remaining.

Testing Transformations

@Test
fun testAmountConversion() {
    val helper = MigrationTestHelper(instrumentation, AppDatabase::class.java)
    val db = helper.createDatabase("test.db", 3)
    db.execSQL("INSERT INTO transactions (id, amount) VALUES ('t1', 100.10)")
    db.close()

    val migrated = helper.runMigrationsAndValidate("test.db", 4, true, MIGRATION_3_4)
    val cursor = migrated.query("SELECT amount_cents FROM transactions WHERE id = 't1'")
    cursor.moveToFirst()
    assertEquals(10010, cursor.getInt(0))
}

Special attention — edge cases: NULL values, empty strings, unexpected data formats that real users may have in database.

Rollback on Error

SQLite supports transactions — entire onUpgrade automatically wrapped in transaction in Room. If something fails — changes rolled back. On iOS with Core Data — same via NSMigrationManager.

But rollback doesn't mean "app works normally" — on next launch will try to migrate again. Need error handling and user message about problem.

Work Scope

• Audit data in current database: formats, exceptions, NULL values
• Write transformations with protection from re-application
• Batch update for large tables
• Lazy migration for critically large volumes
• Tests on edge cases

Timeline

Simple UPDATE transformations (1–3 tables): 1 day. Complex JSON transformations, lazy migration with background worker: 2–4 days.