Migrations من غير downtime: expand و backfill و contract

-- 1) CREATE INDEX العادي بيقفل الكتابة على الجدول لحد ما يخلص
--    CONCURRENTLY ما بيقفلش، بس ما ينفعش جُوّه transaction
BEGIN;
CREATE INDEX CONCURRENTLY users_email ON users (email);
ROLLBACK;
CREATE INDEX CONCURRENTLY users_email ON users (email);

-- 2) NOT NULL مباشرة: بيقفل ويقرا الجدول كله، وبيقع لو فيه NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- الطريقة الآمنة: CHECK … NOT VALID (فورًا، للصفوف الجديدة بس)، وبعدين VALIDATE (من غير قفل الكتابة)
ALTER TABLE users ADD CONSTRAINT users_email_not_null CHECK (email IS NOT NULL) NOT VALID;
INSERT INTO users (fullname) VALUES ('بدون إيميل');
UPDATE users SET email = 'missing-' || id || '@x.com' WHERE email IS NULL;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;
SELECT conname, convalidated FROM pg_constraint WHERE conname = 'users_email_not_null';

$ psql -q -f seed.sql -f migration.sql
psql:migration.sql:4: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
psql:migration.sql:9: ERROR:  column "email" of relation "users" contains null values
psql:migration.sql:13: ERROR:  new row for relation "users" violates check constraint "users_email_not_null"
DETAIL:  Failing row contains (100001, بدون إيميل, null).
       conname        | convalidated 
----------------------+--------------
 users_email_not_null | t
(1 row)

-- عايزين نغيّر fullname لـ name، والـ app شغال، ونسختين منه شغالين (قديمة وجديدة) وقت الـ deploy

-- EXPAND (migration 1): زوّد الجديد، ومن غير ما تلمس القديم
ALTER TABLE users ADD COLUMN name text;

-- (الـ app الجديد بيكتب في الاتنين، والقديم لسه بيكتب في fullname)
-- trigger مؤقت يخلي الاتنين متطابقين أيًا كان مين بيكتب
CREATE FUNCTION users_sync_name() RETURNS trigger AS $$
BEGIN
  NEW.name := COALESCE(NEW.name, NEW.fullname);
  NEW.fullname := COALESCE(NEW.fullname, NEW.name);
  RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER users_sync_name BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION users_sync_name();

-- BACKFILL على دفعات صغيرة (مش UPDATE واحد على 100 ألف صف)
DO $$
DECLARE n int;
BEGIN
  LOOP
    UPDATE users SET name = fullname
    WHERE id IN (SELECT id FROM users WHERE name IS NULL LIMIT 20000);
    GET DIAGNOSTICS n = ROW_COUNT;
    RAISE NOTICE 'batch: % rows', n;
    EXIT WHEN n = 0;
    COMMIT; -- كل دفعة transaction لوحدها
  END LOOP;
END $$;

-- app قديم لسه بيكتب fullname بس
INSERT INTO users (fullname, email) VALUES ('من النسخة القديمة', '[email protected]');
SELECT count(*) FILTER (WHERE name IS NULL) AS missing FROM users;

-- CONTRACT (migration 3، بعد ما كل النسخ بقت جديدة): شيل القديم
DROP TRIGGER users_sync_name ON users;
DROP FUNCTION users_sync_name();
ALTER TABLE users DROP COLUMN fullname;
\d users

$ PGOPTIONS='-c client_min_messages=notice' psql -q -f seed.sql -f migration.sql
psql:migration.sql:29: NOTICE:  batch: 20000 rows
psql:migration.sql:29: NOTICE:  batch: 20000 rows
psql:migration.sql:29: NOTICE:  batch: 20000 rows
psql:migration.sql:29: NOTICE:  batch: 20000 rows
psql:migration.sql:29: NOTICE:  batch: 20000 rows
psql:migration.sql:29: NOTICE:  batch: 0 rows
 missing 
---------
       0
(1 row)

                         Table "public.users"
 Column |  Type  | Collation | Nullable |           Default            
--------+--------+-----------+----------+------------------------------
 id     | bigint |           | not null | generated always as identity
 email  | text   |           |          | 
 name   | text   |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

import pg from "pg";
import fs from "node:fs";
import crypto from "node:crypto";

const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
await client.query("DROP TABLE IF EXISTS schema_migrations, courses CASCADE");


async function migrate() {
  await client.query(`CREATE TABLE IF NOT EXISTS schema_migrations (
    name text PRIMARY KEY, checksum text NOT NULL, applied_at timestamptz NOT NULL DEFAULT now())`);
  await client.query("SELECT pg_advisory_lock(42)");
  try {
    const { rows } = await client.query("SELECT name, checksum FROM schema_migrations");
    const done = new Map(rows.map((r) => [r.name, r.checksum]));
    let applied = 0;
    for (const name of fs.readdirSync("migrations").sort()) {
      const sql = fs.readFileSync(`migrations/${name}`, "utf8");
      const sum = crypto.createHash("sha256").update(sql).digest("hex");
      if (done.has(name)) {
        if (done.get(name) !== sum) throw new Error(`${name} اتعدّل بعد ما اتطبّق`);
        continue;
      }
      const tx = !sql.startsWith("-- no-transaction");
      try {
        if (tx) await client.query("BEGIN");
        await client.query(sql);
        await client.query("INSERT INTO schema_migrations (name, checksum) VALUES ($1, $2)", [name, sum]);
        if (tx) await client.query("COMMIT");
      } catch (err) {
        if (tx) await client.query("ROLLBACK");
        throw err;
      }
      console.log("applied", name);
      applied++;
    }
    if (!applied) console.log("nothing to do");
  } finally {
    await client.query("SELECT pg_advisory_unlock(42)");
  }
}

await migrate();
await migrate();
await client.end();

$ node main.js
applied 001_courses.sql
applied 002_price.sql
applied 003_title_index.sql
nothing to do

CREATE TABLE courses (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL);

ALTER TABLE courses ADD COLUMN price_cents int NOT NULL DEFAULT 0;

-- no-transaction
CREATE INDEX CONCURRENTLY courses_title ON courses (title);