The schema, and the migration that changes it

A table is a promise

A schema says what a table holds: a bookings table has an id, a user_id, a starts_at timestamp and a status text. Every row keeps that shape, and every piece of code that reads or writes the table relies on it. Change the shape and the code that relied on the old one breaks, which is why changing it is a formal act with a name.

In the tools this course assumes (Prisma, Drizzle, or a framework’s built-in ORM) the schema is a file in your repository. schema.prisma or schema.ts describes every table in code, and the database is made to match it. That file is the most important one in the project after the rules file, and the data-model lesson in the specification block is where its contents come from.

What a migration is

A migration is a file containing the SQL that moves the database from one shape to the next:

-- 0007_add_cancelled_at.sql
ALTER TABLE bookings ADD COLUMN cancelled_at TIMESTAMP;

Migrations are numbered, applied in order, and never edited once applied. The database keeps a ledger table (_prisma_migrations, __drizzle_migrations) recording which have run, so running the migration command twice does nothing the second time. That ledger is why a fresh database and a year-old one can both be brought to the same shape by the same command: each applies whatever it has not yet seen.

The ORM writes the file for you. Edit the schema, then:

npx prisma migrate dev --name add_cancelled_at    # Prisma: generate and apply locally
npx drizzle-kit generate && npx drizzle-kit migrate   # Drizzle: the same in two steps

Read the generated SQL before applying it. Most of the time it is three lines and obviously right. Occasionally it is a DROP COLUMN you did not intend, because you renamed a field in the schema and the tool saw a deletion and an addition: the same guessing problem git has with renames. A rename in production is done in two migrations: add the new column and copy the data, then drop the old one in a later release once nothing reads it.

Production is a deliberate step

Locally, migrate dev applies changes as you go. Production never runs that command. It runs the apply-only version (prisma migrate deploy, drizzle-kit migrate) as a step in the deployment, before the new code starts serving. Most hosts let you set this as a build command or a release command.

The failure mode from the earlier deploy lesson lives here: new code deployed, migration not run, and confident errors about a column that does not exist. Put the migration command in the deployment, not in your memory.

And never change the production database by hand in a table editor to “fix it quickly”. The ledger does not know, the next migration assumes a shape that is not there, and you have made the one kind of drift that is hard to diagnose.

The command that deletes everything

Every ORM has a reset: prisma migrate reset, drizzle-kit push with a force flag, db:reset. It drops every table and rebuilds from the migrations. On a local database with seed data it is convenient and harmless, and models learn it as the fix for any migration trouble, because in their training data it was, on somebody’s laptop.

Against production it is total data loss, and there is no confirmation beyond a yes-or-no prompt that an agent will answer. The protections, in order of strength: the agent never holds the production connection string; the reset commands are in the forbidden list in the rules file; and a backup that you have restored at least once exists before the first real user signs up. All three, because each fails on its own.

db push is not a migration

Prisma’s db push and Drizzle’s push skip the migration file and alter the database directly to match the schema. They are quick for prototyping and they leave no record, so a second environment cannot be brought to the same shape. Fine for the first week on a laptop. Once anything is deployed, switch to migrations and do not mix the two: a database pushed to one shape and then migrated from another produces exactly the “column already exists” error from the check below.

Reading the ledger

When a migration fails halfway, the ledger shows it as started and not finished, and the tool refuses to continue. The fix is to look at what actually got applied (is the column there or not) and either mark the migration as applied or roll it back, using the tool’s own commands for that (prisma migrate resolve is the Prisma one). It is a five-minute problem when you look, and a reset when you do not.