Running Schema Migrations Inside the App Update Window

A server migration runs in a maintenance window, on hardware chosen for the job, with an operator watching and a rollback plan. A phone migration runs on the launch after an update, on a device that may be a low-end model with a nearly full filesystem, while the user is waiting for the first screen. The same schema change that takes seconds against a server table can take minutes against a phone’s table if the change requires rewriting every row, and the platform will kill the process long before it finishes.

The constraints are not about SQL. They are about the fact that the migration is a data transformation whose cost is proportional to the user’s data volume, executed under a watchdog, inside a transaction that must either complete or leave nothing behind.

Version state belongs in the file

The minimum requirement is that the database knows its own schema version and that the number is stored in the database rather than in the app’s preferences. If the version is in preferences, an app update that changes the schema but not the preference, or a restore that copies one and not the other, produces a database whose version is wrong and whose tables are a mixture.

SQLite provides two four-byte integers in the file header for this: user_version at offset 60, writable with PRAGMA user_version, and application_id at offset 68. The first is the natural place for a monotonic schema version; the second identifies the file as belonging to this application at all, which is worth setting so that a stray file copied into the container is rejected rather than opened.

A version number in the header is read before any table is touched, which means the decision about which migrations to run does not depend on the schema being valid. That ordering matters: a migration that fails halfway leaves a database whose tables do not match the version it claims, and the only way to detect that is to compare the version to what the migration ledger says should be true.

PRAGMA application_id = 1296190513;  -- identifies this app's databases
PRAGMA user_version;                 -- read at open, before any query

BEGIN IMMEDIATE;
-- one migration step, then:
PRAGMA user_version = 7;
COMMIT;

BEGIN IMMEDIATE rather than the default deferred begin is deliberate. A deferred transaction takes its write lock at the first write, so two connections opening the same database can both read version 6, both decide to migrate, and one will fail at its first write with a busy error after having done work. An immediate transaction takes the write lock at the start, serialising the decision as well as the work.

Each step must be independently committed

A single transaction covering all migrations is the safest from the perspective of atomicity and the worst from the perspective of a device that can be killed. If a migration from version 3 to version 9 takes four minutes, a single transaction means a crash at minute three undoes everything, and the next launch starts over and is killed again. The app never reaches a usable state.

The alternative is a transaction per step, with the version number advanced inside the same transaction as the step’s effect. A crash between steps leaves a database at a consistent intermediate version, and the next launch resumes from there. The cost is that intermediate states must be valid schemas (not necessarily the final one, but self-consistent), and that the application code that runs between migration completion and normal operation must tolerate being interrupted.

That second requirement is the one that catches people. If migration 5 adds a column and migration 6 backfills it, then an app killed after 5 and restarted must not read the column as if it were populated. Either the code that reads the column tolerates nulls, or the backfill is part of the same step that adds the column, or the app refuses to open the database until the migration reaches its final version and treats every intermediate state as “still migrating”.

The last option is the simplest and the most common: a migration gate that runs all pending steps before any feature code touches the database. It is also the option that makes the migration duration visible to the user, which is why it needs to be fast or needs a progress indicator.

What each kind of change costs

The cost of a migration is determined by whether the storage engine can alter the schema as metadata or must rewrite the table.

Adding a nullable column with no default, or a column with a constant default, is a metadata change in modern versions of SQLite: the row format stores the column count and the values present, and a missing trailing column is read as its default. Dropping a column is also metadata in recent versions. Renaming a column or a table is metadata. These are O(1) in the number of rows, though they still invalidate prepared statements and bump the schema cookie.

Changing a column’s type, adding a constraint that existing rows might violate, changing a primary key, or reordering columns requires the twelve-step procedure that SQLite’s own documentation describes: create a new table with the desired definition, copy the rows, drop the old table, rename the new one. The copy is O(n) in rows and O(bytes) in I/O, and it temporarily needs space for both tables at once.

That space requirement is the mobile-specific hazard. A table occupying 300 MB needs another 300 MB free to be rebuilt, plus room for the write-ahead log’s frames. A device with 400 MB free will fail partway through with a disk-full error, and the failure leaves the new table partially populated. Because the whole thing is inside a transaction, the rollback restores the old table, but the rollback itself needs to write, and a rollback that cannot write leaves the database in a state that only journal recovery can repair.

The defensive measure is to check free space before starting a rewrite and to refuse the migration if the requirement is not met, leaving the app in a state that tells the user to free space. A migration that refuses is recoverable; one that fills the disk is not.

A third cost class is index rebuilds. Dropping and recreating an index on a large table is a sort of the whole table, and it needs temporary space proportional to the key plus rowid. Building the index after the data is in place is usually cheaper than maintaining it during the copy, because the insert order during a table copy is arbitrary and a maintained index would randomise writes.

Migrating a table without holding a lock for minutes

On a phone the migration runs in the app’s process, and the app has a lifecycle. The screen can be locked, the user can switch away, the system can suspend the process. A migration that assumes it can run to completion is assuming something the platform does not grant.

The pattern that survives is chunked migration with a resumable cursor. The new table is created under a temporary name, rows are copied in batches of a few hundred, and the batch boundary is committed along with a record of the highest rowid copied. On restart the copy resumes from that rowid. Once the copy is complete, a final transaction renames the tables and advances the version.

The batch size is a trade between transaction overhead and the amount of work lost to an interruption. A few hundred rows per transaction at a 4096-byte page size keeps each commit to a handful of pages, so a flush per batch costs little, and the whole migration is a sequence of small durable steps rather than one long one.

There is a subtlety about ordering. If rows are copied in rowid order and new rows are inserted into the old table during the migration, the copy can miss them. The migration must either block writes to the old table for its duration, which defeats the purpose, or to record the maximum rowid at the start and copy only up to it, then copy the remainder in a second pass, or apply the migration lazily to rows as they are read. The last option, a read path that understands both the old and new shape, is the most complex and the only one that keeps the app fully usable throughout.

Downgrades and the version that comes back

App updates are not always forward. A user can restore an older version from a backup, a staged rollout can be rolled back, and a beta build can be replaced by a release build with a lower version number. A database migrated to version 9 and then opened by code that expects version 8 is a database whose tables contain columns the code does not know about and whose indexes may not exist.

Two policies are defensible. The first refuses to open: if the file’s version exceeds the code’s known maximum, the app reports an incompatibility rather than guessing. The second makes every migration additive and backward-compatible for one version (new columns nullable, no columns dropped, no renames) so that the older code can still read and write the tables it knows. The second policy is what makes rollback safe, and it constrains the migrations that can be expressed, which is a real cost.

Either way, the decision must be explicit in the code rather than emergent from whatever the migration runner happens to do. A runner that applies migrations in a loop from the current version to the target and does nothing when the current version is higher will silently open a future database with old code, and the first query against a dropped column fails at a point unrelated to the migration.

The related problem is a migration interrupted by an app update. Version 7 was half-applied when the user updated to a build that expects version 8. The step-per-transaction rule means version 7 is either fully applied or not applied at all, so the new build starts from 6 or from 7 and runs the rest. This is why change logs and tombstones should be designed so that a pending sync record survives a schema change, and why the durable-versus-cache split described in app container layout matters: a database that is small enough to be backed up is one that can also be rebuilt from the server if a migration goes wrong.