Database schema migrations

Why version the schema

Code is versioned in git, but the database schema (tables, columns, indexes) often changes by hand through psql. Then nobody knows which schema is on which server, a new developer can't bring up the database from scratch, and rolling back is impossible.

Migrations solve this. Every schema change is written as a separate numbered file in the repository, next to the code. The schema travels to the server the same way the code does: through git and the pipeline.

Migration: a numbered file

A migration describes one schema change and has a sequence number or a timestamp. A tool (Flyway, Liquibase, Alembic, golang-migrate and similar ones) keeps a tracking table in the database itself and uses it to know what's already applied. Flyway and Liquibase record every applied migration there (flyway_schema_history, DATABASECHANGELOG). Alembic and golang-migrate store only the current version number (alembic_version, schema_migrations).

migrations/
  0001_create_users.up.sql
  0002_add_email_index.up.sql
  0003_add_active_column.up.sql

-- 0003_add_active_column.up.sql
ALTER TABLE users ADD COLUMN active boolean NOT NULL DEFAULT true;

Running the tool applies every migration that hasn't been applied yet, in order. This is what the teaching tool from this lesson's labs prints; real tools have their own format, but the idea is the same:

applied 0002_add_email_index
applied 0003_add_active_column
database is at version 3

The database remembers it's at version 3, and running the tool again does nothing. It's the same idempotency as in earlier tracks: applying it twice is safe.

In PostgreSQL, schema changes run inside transactions. That's why tools usually run each migration in a transaction: if something fails halfway through the file, the whole migration rolls back, and the database stays at the previous version instead of a half-finished state.

up and down

A migration has two sides. up applies the change, down rolls it back. If up adds a column, down removes it.

-- up
ALTER TABLE users ADD COLUMN phone text;
-- down
ALTER TABLE users DROP COLUMN phone;

down is useful for rolling back in development, but rolling back a schema in production is risky. A down that drops a column destroys the data in it. That's why production usually only rolls forward, and a mistake gets fixed with a new migration.

For the same reason, you don't edit a migration that's already been applied. The database already ran the old text, and it's applied in that old form on other servers and for your teammates too, so editing it there changes nothing. Flyway and Liquibase store a checksum for every file and refuse to run when it doesn't match. A fix is written as the next migration.

Migrations and zero-downtime deploys

This is where migrations meet the rolling deploy from the deployment strategies lesson. During a rollout, the old and new versions of the code run at the same time, so the schema has to suit both. Breaking changes are dangerous: dropping or renaming a column that the old code still reads will bring it down.

ERROR:  column "login" does not exist

That's the error the old version gets if a migration renamed login to username.

The safe approach: change the schema in several steps (expand/contract).

1. expand: add the new thing without breaking the old one (a new column that allows NULL).
2. Roll out code that writes to both the old and the new.
3. Migrate the data, switch reads to the new one.
4. contract: in the next release, drop the old one once nothing uses it.

This way the schema stays compatible with both versions of the code at every point, and the rollout goes through without downtime.

Common mistakes

• Editing an applied migration. On your machine the database is already at the new version, and the edit won't apply. Other servers end up with a different schema. Fix it with a new migration.
• Changing the schema by hand, bypassing migrations. The column exists on this server but not in the repository. The next migration that adds it fails with already exists, and a new server comes up without it.
• NOT NULL without DEFAULT on a table that has data. The command ALTER TABLE users ADD COLUMN phone text NOT NULL fails on a non-empty table with column "phone" of relation "users" contains null values. Either let the column allow NULL, or give it a DEFAULT. With a constant DEFAULT, PostgreSQL 11+ adds the column without rewriting the table.
• Renaming or dropping a column in the same release as the code. Old copies of the app crash until the rollout finishes. Do it through expand/contract instead.
• Two migrations with the same number from different branches. The tool either refuses or applies only one of them. Check that the number is free before merging.
• CREATE INDEX CONCURRENTLY inside a transaction. PostgreSQL responds with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. In the tool, such a migration is marked to run without a transaction.

What you'll need for the tasks

• Migration file names: NNNN_name.up.sql and NNNN_name.down.sql, the next number in order.
• ALTER TABLE <table> ADD COLUMN <column> <type> and ALTER TABLE <table> DROP COLUMN <column>.
• The timestamptz type: date and time with a time zone.
• UPDATE <table> SET <column> = <other column> to copy data.
• Look at a table's structure: docker exec -u postgres <container> psql -d app -c '\d users'.
• The teaching tool: bash migrate.sh status, bash migrate.sh up, bash migrate.sh down.

The next lesson covers connection pools: how to avoid hitting the database's connection limit.