What is a database migration? What should you watch out for when adding a NOT NULL column to a table that already has data?
Suggested answer
A migration is a file (usually SQL) that describes one change to the schema, such as creating a table, adding a column or adding an index. The files are named in time order, committed to Git and run one after another, so the databases on dev machines, staging and production all go through exactly the same steps. It also means schema changes get reviewed in pull requests, just like code.
For the NOT NULL part, the interviewer wants to hear two things.
First, alter table posts add column tags text[] not null fails on a table that has data: the existing rows have no value for the new column, which means null, and that violates the constraint (PostgreSQL reports column "tags" of relation "posts" contains null values). On a dev machine with an empty table it runs fine, so the error only shows up in production. The fix is a default value: not null default '{}'. If there's no sensible default, split it into steps: add the column as nullable, fill in the data, then set not null.
Second, backward compatibility. During a deploy, the migration and the new code don't switch over at the same instant. There's usually a window where the old code runs against the new schema, so a migration must not break the old version: don't drop or rename columns the old code still uses, and give new columns a default so the old insert statements keep working.
Bonus points: say that a migration that has already run in production is never edited; if you need to change something, you write a new migration.