
Why not change the database by hand
- Every environment has to get the same change: your laptop, the test server, production.
- The change has to be reviewed like code.
- Six months later, someone has to be able to see what was changed and why.
What a migration looks like
-- 0042_add_idempotency_key_to_orders.sql
ALTER TABLE orders ADD COLUMN idempotency_key TEXT;
CREATE UNIQUE INDEX orders_idempotency_key ON orders (idempotency_key);The migration tool keeps a list of which migrations have already run, and runs only the new ones, in order.
The part people forget: the rows that already exist
A new column on an empty table is easy. On a table with ten million rows, the same change raises questions. What value do the old rows get? Can the column be required if the old rows have nothing in it? How long does the change lock the table?
The order of deploys
During a deploy, old code and new code run side by side for a while. A migration that is safe for only one of them breaks the other. So a change that removes or renames something is done in steps:
- Add the new column. Old code ignores it.
- Deploy code that writes to both columns and reads the new one.
- Copy the old data across.
- Deploy code that no longer uses the old column.
- Remove the old column.
It is more steps, and none of them can take the site down.
Questions you will hear in a meeting
- Is this migration safe to run while the old code is still serving requests?
- How long will it lock the table?
- Can we roll it back, and what happens to data written in between?
- Who runs it, and when?
In an interview
When you add a field in a system design round or a take-home review, say how the existing rows are handled. Mentioning the migration without being asked is a small sign of real experience.