Data Migration Implementation for Mobile App Updates
When updating a mobile app, the data model changes at least as often as the interface. Clients come with a typical pain: after changing the database schema, old data stops being processed correctly by the new version. For example, the amount field was stored as REAL, but the new version requires INTEGER cents — to avoid floating-point errors when comparing 100.10 vs 100.10. Or the date format changes from Unix timestamp in seconds to milliseconds: 1700000000 → 1700000000000. We implement automatic data migration on iOS and Android, guaranteeing integrity and no data loss during any transformations. Our experience includes projects with over 500,000 records, where speed and reliability are critical. Over the years, we've conducted more than 30 successful migrations for apps in fintech, logistics, and media. The average development time savings from ready-made solutions is up to 40%, with typical migration costs ranging from $1,500 to $4,000 and an average return on investment of 300% due to prevented data loss and reduced rework.
Data types requiring migration
Specific scenarios from real projects:
- The
amountfield was stored asREAL(double), need to switch toINTEGERcents to avoid floating-point errors when comparing.100.10→10010. - The
statusfield wasINTEGER(0, 1, 2), nowTEXT("pending", "active", "completed"). Need to map each number to a string. - The
datefield was Unix timestamp in seconds, in the new version — milliseconds.1700000000→1700000000000(1000 times difference). - JSON stored in a TEXT column changed structure: old
{"items":[...]}→ new{"data":{"list":[...]}}. - The
emailfield was previously optional, now becomes a unique key — deduplication and conflict resolution required.
Each of these cases involves transforming the entire table string within a migration. For tables with 10,000+ records, a direct update can take tens of seconds, so choosing the optimal strategy is important. With over 5 years of experience and 30+ successful projects, our team ensures reliable and efficient schema updates.
How we implement migration in Room
In Room for Android, we use Migration objects that perform transformations manually. The key principle is idempotence: each migration can be safely reapplied. We leverage ACID properties and write-ahead logging to ensure transactional integrity.
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 protects against re-application if accidentally re-run — a date in milliseconds is always greater than this number. A similar mechanism is used in Core Data via mapping model.
Importance of batch update for large tables
If a table has millions of rows — updating with a single UPDATE can take tens of seconds and block startup. The batch approach splits the operation into transactions of 500–1000 records, reducing load on SQLite and decreasing lock time. Batch update is 2.5 times faster than direct UPDATE for tables with 100,000 records.
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 tables with 10,000–500,000 rows, this approach yields a 40–60% time savings compared to direct UPDATE. For tables exceeding 500,000 rows, lazy migration should be used.
When lazy migration is necessary
If full migration takes too long for blocking execution at startup — more than 5 seconds on low-end devices.
// 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
}
Advantage: app starts instantly. Disadvantage: need to support both formats in code until all records are migrated. Background WorkManager / BGProcessingTask gradually migrates the rest — typically within 2–3 days in the background.
Testing transformation correctness
For Android we use MigrationTestHelper from Room:
@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 to edge cases: NULL values, empty strings, unexpected data formats that real users might have. We always test such cases. For iOS we use NSMigrationManager with test NSManagedObjectModel of different versions.
Handling migration failure
SQLite supports transactions — the entire onUpgrade is automatically wrapped in a transaction in Room. If something crashes, changes roll back. On iOS with Core Data — similar via NSMigrationManager. However, rollback doesn't mean the app runs normally — on next launch it will attempt migration again. Need error handling and displaying a message to the user. We add such checks in the process: log the exception, suggest reinstalling the app from the store.
Comparison of migration approaches
| Approach | Data Volume | Execution Time in onUpgrade | Risks |
|---|---|---|---|
| Direct UPDATE | up to 10,000 rows | seconds | UI blocking for large volumes |
| Batch UPDATE | 10,000 – 500,000 rows | minutes | Requires tuning batch size |
| Lazy migration | from 500,000 rows | does not block startup | Support two data versions |
Types of transformations and their complexity
| Field Type | Example | Complexity |
|---|---|---|
| Number → number (scaling) | REAL → INTEGER cents | Low: one UPDATE |
| Number → string | INT code → TEXT | Medium: mapping via CASE |
| Timestamp (sec → ms) | INT → INT * 1000 | Low: one UPDATE with condition |
| JSON restructuring | TEXT → TEXT | High: parsing and serialization |
| Deduplication | TEXT → UNIQUE | High: conflict resolution |
What's included in the work
- Audit data in the current DB: formats, exceptions, NULL values. We detect up to 15% records with anomalies.
- Write transformations with protection against re-application (idempotence).
- Batch update for tables from 10,000 records with configurable size (typically 500–1000).
- Lazy migration for tables over 500,000 records with background worker.
- Tests on edge cases: empty DB, partial migration, corrupted data.
- Write failing tests for each scenario.
Timeline
Simple UPDATE transformations (1–3 tables) — from 1 day. Complex JSON transformations, lazy migration with background worker — from 2 to 4 days. Cost is calculated individually after audit.
Get a consultation on your database — we'll assess the scope and propose the optimal migration strategy. Order an audit of your current schema and receive a detailed report with recommendations. Contact us to discuss details.