Lesson 08

Migration backfills

Split schema changes, backfills, and stricter constraints into safe migration steps.

migrations
backfill
constraints

Good Code

20260525090000_add_display_name.sql

ALTER TABLE users
ADD COLUMN display_name TEXT;

UPDATE users
SET display_name = COALESCE(NULLIF(name, ''), email)
WHERE display_name IS NULL;

ALTER TABLE users
ALTER COLUMN display_name SET NOT NULL;

Bad Code

20260525090000_add_display_name.sql

ALTER TABLE users
ADD COLUMN display_name TEXT NOT NULL;

UPDATE users
SET display_name = name;

Review Notes

What to review

Good Code

The good version adds the column, fills existing rows, then tightens the constraint after data is ready.

Bad Code

The bad version adds a required column before existing rows have a value. It can fail immediately or force unsafe defaults.

Takeaways

A safe migration respects the data that already exists.