Change a shared database schema through intermediate states that both the old and new application can use. Add the new structure first, keep writes consistent during coexistence, migrate existing data, move reads, and remove the old structure only after its consumers and rollback dependencies are gone.
The difficult part is the period between releases. A backfill can finish successfully while an old process immediately makes its result stale. Expand and contract is a compatibility strategy; it does not establish zero downtime without testing the database, workload, and deployment procedure.
Use a rename that makes the coexistence problem visible
Consider a synthetic customer table with id and name. The application wants to call the same value display_name. This example deliberately preserves the meaning of the value. Splitting human names or converting ambiguous free text would require a separate data-quality decision.
Renaming the column in one migration breaks the old application's SELECT name query. Instead, add a nullable column:
ALTER TABLE customer ADD COLUMN display_name TEXT;
The existing application can continue reading and writing name. The new column is present, but its existence does not make it authoritative. Prisma's expand-and-contract explanation describes this transition through compatible structures, client migration, and eventual removal. The sequence below develops the write-ownership problem in a small executable example.
Define the application releases before deciding when the migration is finished:
| Release | Reads | Writes | Required schema |
|---|---|---|---|
| A: original | name |
name |
Original or expanded |
| B: bridge | name |
Both columns in one transaction | Expanded |
| C: switched | display_name |
Both columns in one transaction | Expanded, after catch-up |
| D: final | display_name |
display_name |
Expanded or contracted |
The bridge is doing important work. It permits A and B to coexist without asking B to trust a partially maintained field. C must wait until every A writer has stopped and the data is reconciled.
A completed backfill can still be stale
The first pass copies existing values:
UPDATE customer
SET display_name = name
WHERE display_name IS NULL;
Now an A process changes customer 1 from Aster to Aster revised. It writes only name. The row becomes:
name = Aster revised
display_name = Aster
A new reader that prefers the non-null new column would return the old value. “Fall back only when null” solves missing data, not stale data. Repeating the null-only backfill does not repair this row.
In this design, name remains authoritative until all A writers are retired. B writes both columns together. After confirming that no A application, scheduled job, importer, or maintenance script remains, run an authoritative catch-up:
UPDATE customer
SET display_name = name
WHERE display_name IS NULL OR display_name <> name;
The comparison is appropriate for this fixture because name is non-null and the intended mapping is exact equality. Other transformations need a different reconciliation predicate. A revision column, a change stream, or database-enforced synchronization may be needed when the application cannot retire old writers before switching reads. Each introduces its own ownership and failure modes.
Do not copy a value into application memory, wait, and then overwrite the destination without checking whether the source changed. A production backfill needs transaction and concurrency behavior chosen for its database. Batching also needs a durable progress record and a safe restart rule. The single-connection fixture below does not establish those properties.
What the local migration actually verified
We executed the example with Python 3.14.2 and SQLite 3.51.2 in an in-memory database. It created two customers, expanded the schema, ran the first backfill, and injected an old-style write. It then used a bridge-style update for the second customer and performed catch-up after the simulated retirement of old writers.
| Check | Observed result |
|---|---|
| Old write after initial backfill | New column remained stale: Aster |
| Bridge write to the second row | Both columns held Birch revised |
| Authoritative catch-up | Zero null or unequal new values |
| Repeating catch-up | Zero rows changed |
| New-column read | Returned Aster revised and Birch revised |
| Old-column read after contraction | Failed with no such column: name |
The last failure is expected evidence of the compatibility boundary. It shows why an old artifact is no longer a usable code-only rollback after dropping the column.
The fixture used real local SQL, but simulated release ordering with sequential statements. It did not run concurrent clients, a rolling deployment, PostgreSQL, replication, a large backfill, or a recovery exercise. SQLite's ALTER TABLE documentation also matters operationally: dropping a column rewrites table content and can fail when schema objects still reference it. Successful execution on two rows says nothing about production lock duration.
Move reads only after proving the invariant
Before C starts reading display_name, require evidence that every active writer maintains both representations and that the existing rows satisfy the mapping. A completed job record alone is weaker than checking the data it was meant to transform.
For the rename, inspect mismatch counts and selected rows, including rows updated during the migration window. Verify the application's actual serialization and query paths as well. A renamed database column can leak into an API accidentally even when the external field was supposed to remain stable. The contract-testing guide separates that consumer agreement from internal business checks.
Keep dual writes during C's rollback window. Returning to B is then straightforward for this identity mapping because B still reads the maintained old column. Returning to A needs more care: once A resumes old-only writes, the new column can drift again, so a later switch to C requires reconciliation again.
Contract only after choosing the new recovery boundary
D removes use of name, but the database column can remain temporarily. That provides a deployment window in which D and C coexist. Do not drop name while C still writes it: a write can fail even if every read already uses the new field.
Once only D consumers remain, decide whether the supported recovery artifact also works without name. If it does, contraction can proceed after the database-specific checks. If it does not, dropping the column closes that recovery route. Restoring a backup may discard writes made since the backup; a forward repair or a tested reverse migration may be more appropriate. The rollback workflow makes this distinction explicit.
The release record should name the last compatible application for each schema state, the evidence that old writers are gone, and the chosen recovery procedure. These facts make the migration reviewable long after the SQL file itself has become unremarkable.
Documentation and the bounded SQLite exercise were checked October 28, 2026. No production database or deployment was used.
