Lesson 5 / الدرس 5

Changing the shape of live data / تغيير شكل بيانات حيّة

Code can be replaced wholesale. A database cannot — it holds things you cannot recreate, and it has to change while people are using it. Migrations are how, and the ordering rules are what stop a deploy taking the site down.

الكود يمكن استبداله جملةً. أما قاعدة البيانات فلا — فهي تحمل أشياء لا تستطيع إعادة إنشائها، ولا بد أن تتغير والناس يستخدمونها. والترحيلات هي الكيفية، وقواعد الترتيب هي ما يمنع نشرًا من إسقاط الموقع.

A migration is a numbered file containing one change, applied once, in order, and recorded so it is never applied twice. The record is the whole idea — it is what lets a fresh machine, a colleague's laptop and the live server all reach the same schema by running the same command.

A numbered file, and a table that remembers

db/
  migrate.php          runs everything not yet recorded, in order
  schema.sql
  migrations/
    001-create-users.sql
    002-create-submissions.sql
    003-add-mark-to-submissions.sql

Numbers rather than dates in the filename, so the order is unambiguous at a glance. Once a migration has run anywhere but your own machine, it is immutable — editing it means the servers that already applied the old version never get the change, and no command will ever tell you.

-- The table whose only job is remembering what has already run.
CREATE TABLE migrations (
    name       VARCHAR(190) PRIMARY KEY,
    applied_at DATETIME NOT NULL
);

Four lines, and they are what turn a folder of SQL files into something reliable. Without the record, "has this one run here?" is answered by remembering, and a migration applied twice is usually worse than one never applied at all.

The rule that keeps the site up: expand, then contract

During a deploy the old code and the new schema exist at the same time — for a few seconds at best, and for as long as you leave it if you deploy gradually. So every change has to be made in a shape where both versions of the code work. That means renaming a column is not one step, it is four.

-- WRONG: one step, and the old code breaks the instant it runs.
ALTER TABLE users CHANGE name full_name VARCHAR(120);

-- RIGHT: four deploys, and the site never notices.

-- 1. EXPAND. Add the new column. Nothing reads it yet.
ALTER TABLE users ADD full_name VARCHAR(120) NULL;
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- 2. Deploy code that WRITES BOTH and reads the old one.
-- 3. Deploy code that reads the NEW one. Old rows are already filled.

-- 4. CONTRACT. Only now, and only once nothing references it:
ALTER TABLE users DROP COLUMN name;

It looks like a lot of ceremony for a rename, and it is — until the first time you do it in one step at four in the afternoon and every request fails for the ninety seconds the deploy takes. Each of those four steps is independently safe to stop at, which is the property that makes the whole thing recoverable.

على جدول كبير، تستطيع ALTER TABLE قفله دقائق. تحقق من عدد الصفوف قبل تشغيلها، وإن كان الجواب بالملايين فاعرف ماذا تفعل قاعدة بياناتك بشأن تغييرات المخطط الحيّة قبل النشر لا أثناءه.

Check yourself / اختبر نفسك

1. Why must a migration never be edited once it has run outside your own machine?

2. Why is renaming a column four deploys rather than one?

3. What makes a migration different from a code deploy in terms of risk?