web dev//SQL//schema migration

2026-03-15

A schema migration is a versioned, ordered change to the structure of a database (adding a table, a column, an index or a constraint), written as a file that lives in the repository and is applied the same way to every environment. It is how a team keeps the development, preview and production databases structurally identical while their data stays different.

The schema is the shape of the database, its tables, columns, types and relations; the data is what fills it. A migration changes only the first. If the users table needs an email, a migration file says ALTER TABLE users ADD COLUMN email TEXT; in DDL, the migration tool records that it ran, and every other environment runs the same file in the same order. Nobody edits a production table by hand.

Migrations are ordered history. Each file builds on the previous one, the tool keeps a table of which have run, and a fresh database is built by replaying them all from the first. Supabase, Prisma, Django and Rails all work this way under different names.

Code and schema must change in a safe order. A deploy is not atomic: for a while old code runs against the new schema, or new code against the old one. The safe recipe is expand and contract: first add what the new code needs (a nullable column) without removing anything, then deploy the code that uses it, then backfill the data, and only later drop what the old code needed.

Destructive steps are the risky ones. Dropping or renaming a column breaks any running code that still reads it and loses data that no rollback restores, so they come last and after a backup.

Applying a migration is a decision of its own. Many teams run it manually, or as a deliberate pipeline step, before the code that depends on it, instead of letting every push touch the production database.

Migrations move structure between environments; data never travels with them.

That is what lets development hold test users and production real ones under the same tables.

The environments a migration moves through are described in deployment environment.