SQLite / Advanced: concurrency, recovery, and application boundaries / Lesson 16

Schema evolution and migration contracts

Plan SQLite schema changes with explicit compatibility, data checks, dependency preservation, transaction ownership, and recoverable application versioning.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Use supported ALTER operations for the tested runtime.
Explain when a reviewed table rebuild is needed.
Separate user_version metadata from proof of a valid migration.

A migration changes an application contract

A schema change affects existing rows, indexes, triggers, views, prepared statements, and every supported reader or writer. Record the starting schema version, intended target, compatibility window, validation, and recovery plan. A clean test database with no rows is insufficient evidence for a migration that must handle years of existing values and relationships.

SQLite supports selected ALTER TABLE operations, not every server database's ALTER syntax. The3.45.1 baseline supports the shown ADD COLUMN and rename operations; current documentation can describe later additions that this runtime does not have. Check the actual deployed feature before designing a rollout, especially when mobile or desktop clients can remain on older embedded libraries.

Choose a compatible default deliberately

The first script adds a required currency column with an explicit default to a small synthetic ledger. Existing rows read as USD and user_version is set only inside the migration transaction. This default is part of the business meaning, not a universal repair for unknown currency. Real historical rows might need a reviewed backfill or rejection rather than one invented value.

ADD COLUMN has restrictions on defaults and constraints, and some additions can require existing-data validation. Do not assume every ALTER is instantaneous or valid because the new column looks simple. Test the exact operation and representative old records on a copy. The engine's schema_version is not an application migration ledger to manipulate casually; user_version is application-owned metadata with its own validation obligations.

sql An explicit versioned column addition

CREATE TABLE ledger(id INTEGER PRIMARY KEY,amount INTEGER NOT NULL);
INSERT INTO ledger VALUES(1,20);
BEGIN;
ALTER TABLE ledger ADD COLUMN currency TEXT NOT NULL DEFAULT 'USD';
PRAGMA user_version=1;
COMMIT;
SELECT id,amount,currency FROM ledger ORDER BY id;
PRAGMA user_version;

Expected output

id|amount|currency
1|20|USD

user_version
1

The existing row acquires the declared read/default currency and the application metadata reports version1. That number alone is not a schema proof.

Scope a table rebuild instead of copying a ritual

Some constraint or layout changes require SQLite's documented generalized table-rebuild procedure. The second script is intentionally limited to one isolated table with no foreign-key dependents, custom triggers, views, or secondary indexes. It copies valid rows into a new constrained shape and renames it inside one transaction. This is not a complete migration recipe for an arbitrary application schema.

A real rebuild must preserve or intentionally change dependent object definitions, keys, sequences where applicable, indexes, triggers, and views. Follow the documented foreign-key-setting sequence outside transaction boundaries when required, and run the appropriate relational checks before completing the migration. Incorrectly renaming or dropping objects can break dependencies even when the final table name looks familiar.

sql A deliberately dependency-free rebuild

CREATE TABLE invoice(id INTEGER PRIMARY KEY,cents INTEGER);
INSERT INTO invoice VALUES(1,100),(2,200);
BEGIN;
CREATE TABLE invoice_new(id INTEGER PRIMARY KEY,cents INTEGER NOT NULL CHECK(cents>=0),currency TEXT NOT NULL);
INSERT INTO invoice_new SELECT id,cents,'USD' FROM invoice;
DROP TABLE invoice;
ALTER TABLE invoice_new RENAME TO invoice;
PRAGMA user_version=2;
COMMIT;
SELECT COUNT(*) AS rows,SUM(cents) AS total FROM invoice;
PRAGMA integrity_check;

Expected output

rows|total
2|300

integrity_check
ok

Both rows and the300-cent total survive the constrained rebuild; the isolated integrity check reports ok.

Plan coexistence and failure recovery

A new application and an older reader may access the same file during rollout. Decide which schema each can understand and whether writes remain compatible. A migration that succeeds locally can still strand older consumers through STRICT, JSONB, renamed columns, or changed constraints. Version gating must identify actual supported capabilities, not just a marketing application release.

For large backfills, define checkpoints, transaction sizes, restart identity, and partial-state visibility. Several committed batches are not one atomic migration unless the application explicitly understands the intermediate states. Test failure before copying, after copying, before rename, and after commit. Recovery should use a known-good copy or reviewed reverse plan, not ad hoc editing of sqlite_schema with writable_schema.

Verify data, dependencies, and version evidence

A migration test should start from the exact supported old schema with representative valid and invalid legacy data. Compare row identities, control totals, constraints, indexes, views, and application queries afterward. Run integrity and foreign-key checks with their separate meanings. Verify the version marker is advanced only when the intended changes and checks are complete.

Keep migration source and artifact identity recorded, and require repeat behavior to be explicit: reject an unexpected starting version or resume a documented interrupted state. Blindly ignoring every “already exists” error can conceal a partial or different schema. Reliable evolution is a checked state transition whose data and compatibility effects are explainable, not just a list of DDL statements that happened not to raise an error.

Common errors and fixes

An older application cannot open the migrated file.

Why it happens: Schema feature compatibility was omitted from rollout design.
How to fix it: Gate readers or migrate storage under a supported compatibility plan.
Prevent it next time: Test the oldest supported consumer.

A rebuilt table lost an important index or trigger.

Why it happens: Only rows and column names were copied.
How to fix it: Inventory and recreate intended dependent definitions.
Prevent it next time: Compare full schema behavior after migration.

user_version says complete but the schema is partial.

Why it happens: Metadata was advanced independently of migration validation.
How to fix it: Repair through a reviewed state-aware process and validate actual objects/data.
Prevent it next time: Commit marker and validated migration together.

Check your understanding

1. Why is adding currency DEFAULT USD not automatically correct for unknown historical rows?

Show worked solution

A default supplies a value; it does not establish the real currency of prior transactions. Validate source meaning, backfill from evidence, or preserve/reject unknowns under policy. The synthetic example assumes USD deliberately and does not authorize guessing in a real ledger.

2. What must be checked beyond row count after a table rebuild?