A schema changes many times. The same changes must reach every copy of the database: your laptop, a test server, production. Run by hand, a change is soon forgotten on one copy.

A migration has an up and a down

A migrationmigrationA numbered script that changes the schema, with a second script that undoes the change is a numbered pair of scripts:

• up makes one change to the schema.
• down undoes that change. After the down, the schema is the same as before the up.

migrations/
  001_create_suppliers.up.sql      CREATE TABLE suppliers (id integer NOT NULL, name text NOT NULL);
  001_create_suppliers.down.sql    DROP TABLE suppliers;
  002_add_supplier_email.up.sql    ALTER TABLE suppliers ADD COLUMN email text;
  002_add_supplier_email.down.sql  ALTER TABLE suppliers DROP COLUMN email;

A line that starts with -- is a comment. PostgreSQL ignores it. Migration files often start with a comment that says what they do:

-- 002: suppliers get an email address
ALTER TABLE suppliers ADD COLUMN email text;

Apply in order

A migration tool records the numbers that ran in a table of the database, often named schema_migrations. On each run, it applies only the new numbers, from the lowest to the highest.

To roll back, the tool runs the down scripts from the newest to the oldest.

Rules for good migrations

• One migration, one change. Its down undoes exactly that change.
• When an up makes several changes, its down undoes them in the reverse order.
• A down can lose data. The down of ADD COLUMN email removes every email. The down of DROP COLUMN can create the column again, but not its old values.
• Never change a migration that ran on another database. Write a new one.

Tools such as Flyway, Liquibase, Alembic and Prisma keep the files and the schema_migrations table for you. The files are the same plain SQL.