The most dangerous database-migration success is “a fresh install works.” A new database tests only the latest creation SQL. It says nothing about whether someone upgrading from a three-year-old release will keep their data. A reliable migration combines version ownership, explicit transformations, constraint checks, and failure recovery into one release contract.

This guide concerns application-managed SQLite files. Frameworks such as Room or Core Data can generate part of the mechanism, but they still need the same version paths and old-store evidence.

Model versions as a directed path

PRAGMA user_version exposes an integer for applications to use. SQLite stores the number but assigns it no meaning, which makes it suitable for a completed application-schema version. Do not substitute SQLite’s internal schema_version; that value tracks schema changes for SQLite itself and is not an application migration identifier.

A migrator should advance one known edge at a time:

v3 --migrate_3_to_4--> v4 --migrate_4_to_5--> v5

Each edge should be a named function that tests can invoke independently. If the database version is newer than the client understands, refuse writes with a diagnosable error. Treating a future schema as an old database can be worse than failing at startup.

Separate fresh creation from upgrade too. A new installation may create v5 directly, while an existing installation must preserve the semantics of v3 while traversing every supported edge. Both paths should end with equivalent tables, indexes, triggers, and constraints.

Establish connection invariants first

Foreign-key enforcement must not depend on an environment default. SQLite documents that applications should explicitly set PRAGMA foreign_keys = ON for every connection. Changing it in the middle of a multi-statement transaction has no effect. Enable it immediately after opening a connection and read the value back to confirm it is 1.

Run migrations before ordinary application connections can access the file concurrently. BEGIN IMMEDIATE attempts to start the write transaction up front. If it returns SQLITE_BUSY, use a bounded retry policy instead of discovering a lock conflict halfway through the migration. Transaction mode, timeout, and retry count belong in testable configuration.

Also check whether the runtime SQLite library supports the migration SQL. DROP COLUMN working on a developer laptop does not mean an older phone’s embedded SQLite supports it. Base compatibility on the oldest runtime that the product supports, or use the table-rebuild procedure.

Give simple changes preconditions and postconditions

Adding a nullable column can be straightforward:

BEGIN IMMEDIATE;

ALTER TABLE notes ADD COLUMN archived_at TEXT;
CREATE INDEX notes_archived_at_idx ON notes(archived_at);

PRAGMA user_version = 4;
COMMIT;

Even ADD COLUMN does not accept every definition. A new NOT NULL column needs a non-null default, and some constraints are checked against existing rows. Assert the source version before executing the edge, then verify the new column, index, and important row counts before commit. Broad use of IF NOT EXISTS can hide an unknown half-migrated state; stop and preserve evidence when observed structure differs from the expected source.

Update user_version in the same transaction as the schema and data changes, after all transformation steps succeed. If the process dies or a write fails, rollback leaves the next launch at the old version. It must never advertise a new version over unfinished data.

Rebuild a table for structural changes

Do not edit sqlite_schema directly to change constraints, primary keys, or column types. SQLite’s documented general procedure creates a replacement table, copies data, drops the old table, and renames the replacement. The essential sequence looks like this:

PRAGMA foreign_keys = OFF;
BEGIN IMMEDIATE;

CREATE TABLE new_tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('open', 'done'))
);

INSERT INTO new_tasks (id, title, state)
SELECT id, trim(title),
       CASE WHEN completed = 1 THEN 'done' ELSE 'open' END
FROM tasks;

DROP TABLE tasks;
ALTER TABLE new_tasks RENAME TO tasks;
CREATE INDEX tasks_state_idx ON tasks(state);

PRAGMA foreign_key_check;
PRAGMA user_version = 5;
COMMIT;
PRAGMA foreign_keys = ON;

Disable foreign keys before the transaction; changing that PRAGMA inside the transaction is ineffective. Preserve and recreate affected indexes, triggers, and views. List source and destination columns explicitly during copying. SELECT * turns column order into an invisible contract that a later schema edit can silently break.

The example communicates ordering, not complete application control flow. Production code must consume the result of foreign_key_check and roll back if it returns any rows. After reenabling foreign keys, query the setting again rather than assuming that a correctly spelled statement took effect.

Integrity is more than one command

Before commit, PRAGMA integrity_check can verify pages, records, indexes, and UNIQUE, CHECK, and NOT NULL consistency. Success is exactly one row containing ok. It does not detect foreign-key violations, so run PRAGMA foreign_key_check separately.

Neither command understands product semantics. A task migration may require equal row counts before and after copying, no reduction in nonempty titles, and complete mapping from legacy status values to a new enum. Express those invariants as SQL assertions and abort the transaction on any mismatch. A structurally valid database can still contain incorrect business data.

Migration duration is also a release condition. Record source version, destination version, elapsed time, result, and error code, but never log user content. If a transformation can exceed the platform’s startup budget, split it into phases: first make the schema compatible with old and new representations, backfill resumable batches in the background, and remove the legacy field only in a later release.

Test production-shaped old databases

Keep a sanitized fixture for every source version still supported. Do not synthesize an “old database” in tests using today’s creation SQL. Fixtures should cover an empty store, typical content, maximum realistic scale, nullable edges, Unicode, deleted relationships, and legal anomalies left by historical bugs.

Run the complete application upgrade over every fixture. Verify the target version, tables and indexes, business assertions, foreign keys, and integrity results. Inject failures too: abort during copying, simulate exhausted storage, hold a competing lock, or terminate the process. On reopen, the file must be either a complete old version or a complete new version, never a mixture.

Exercise real chains such as v2→v3→v4→v5, not only v4→v5. CI can normalize and compare the schema of a fresh v5 store with one upgraded from the oldest fixture. That comparison catches a historical edge that forgot to create an index or trigger.

Binary rollback is not transaction rollback

An uncommitted migration transaction can roll back. Once a new release has committed and started writing a new format, replacing the application binary with an older build is usually unsafe. Choose a policy before release: keep the new schema readable by the old client, implement and test a down migration, or reject downgrade and restore a backup. Never decrement user_version alone; the data shape does not reverse with the integer.

Backups need restore drills as well. A backup must be consistent with the database’s WAL state and identify its application and schema versions. It becomes a recovery mechanism only after a test can restore it elsewhere, open it, and pass integrity and business checks.

A dependable SQLite upgrade rests on four boundaries: a version graph defines permitted paths; one transaction commits schema, data, and version together; SQLite checks and domain assertions jointly protect correctness; and realistic old files plus fault injection prove that failure preserves user data. Fresh-install success is only the starting point. Upgrade evidence is what earns a release.