ORM ولا SQL: query builders و repositories و migrations و transactions

// data.js: data layer فوق pg: sql tagged template آمن، و db بالـ transactions والـ savepoints، و migrations بـ checksums و advisory lock، و repository بـ SQL واضح
// lab.js و test.js بيعملوا import من هنا، والـ schema في schema.js
import pg from "pg";
import { createHash } from "node:crypto";

export class DataError extends Error {
  constructor(code, message = code) {
    super(message);
    this.code = code;
  }
}

// ———— 1) sql ————
class Sql {
  constructor(parts, values) {
    this.parts = parts; // strings، وبينهم values أو Sql
    this.values = values;
  }
  toQuery(start = 1) {
    let text = "";
    const values = [];
    this.parts.forEach((p, i) => {
      text += p;
      if (i >= this.values.length) return;
      const v = this.values[i];
      if (v instanceof Sql) {
        const q = v.toQuery(start + values.length);
        text += q.text;
        values.push(...q.values);
      } else {
        values.push(v);
        text += `$${start + values.length - 1}`;
      }
    });
    return { text, values };
  }
  get text() { return this.toQuery().text; }
}

export function sql(strings, ...values) {
  return new Sql([...strings], values);
}
sql.id = (...names) => {
  for (const n of names) if (!/^[a-z_][a-z0-9_]*$/.test(n)) throw new DataError("BAD_IDENTIFIER", `Bad identifier: ${n}`);
  return new Sql([names.map((n) => `"${n}"`).join(".")], []);
};
sql.join = (items, sep = ", ") => {
  if (!items.length) return new Sql([""], []);
  return new Sql([...items.map((_, i) => (i ? sep : "")), ""], items);
};
sql.empty = new Sql([""], []);

// ———— 2) db ————
function api(run, stats) {
  const query = async (q) => {
    if (!(q instanceof Sql)) throw new DataError("NOT_SQL", "Use the sql tag");
    stats.queries++;
    return (await run(q.toQuery())).rows;
  };
  const maybeOne = async (q) => {
    const rows = await query(q);
    if (rows.length > 1) throw new DataError("TOO_MANY_ROWS");
    return rows[0] ?? null;
  };
  const one = async (q) => {
    const row = await maybeOne(q);
    if (!row) throw new DataError("NOT_FOUND");
    return row;
  };
  return { query, one, maybeOne };
}

export function createDb({ connectionString = process.env.DATABASE_URL, max = 10, onQuery } = {}) {
  const pool = new pg.Pool({ connectionString, max });
  const stats = { queries: 0 };
  const exec = (c) => (q) => { onQuery?.(q); return c.query(q); };

  function txApi(client, depth) {
    const t = api(exec(client), stats);
    t.tx = async (fn) => {
      const sp = `sp_${depth}`;
      await client.query(`SAVEPOINT ${sp}`);
      try {
        const r = await fn(txApi(client, depth + 1));
        await client.query(`RELEASE SAVEPOINT ${sp}`);
        return r;
      } catch (e) {
        await client.query(`ROLLBACK TO SAVEPOINT ${sp}`);
        throw e;
      }
    };
    return t;
  }

  const db = api(exec(pool), stats);
  db.stats = stats;
  db.tx = async (fn) => {
    const client = await pool.connect();
    try {
      await client.query("BEGI
…

$ node lab.js
— 1) migrations —
  أول مرة: [ '001_customers_products', '002_orders' ]
  أربع pods مع بعض: [[],[],[],[]]

— 2) N+1 —
  lazy loading: 21 queries في 6ms
  listOrders: 1 query في 2ms ← أول order: {"id":30,"customerId":1,"status":"pending","totalCents":15000,"createdAt":"2026-10-17T14:47:11.673Z","items":[{"sku":"MUG-1","qty":1,"priceCents":15000}]}

— 3) آخر قطعة، وخمسة بيشتروا مع بعض —
  read-modify-write: OK OK OK OK OK ← خمسة اشتروا قطعة واحدة
  placeOrder بـ FOR UPDATE: OUT_OF_STOCK OK OUT_OF_STOCK OUT_OF_STOCK OUT_OF_STOCK ، والـ stock: 0

— 4) الـ sort من الـ query —
  الـ string اللي كانت هتتحط في الـ SQL: SELECT * FROM orders ORDER BY total_cents; DROP TABLE orders
  search: BAD_SORT
  sort=total: 99000 , 50000 , 50000

— 5) lost update —
  التاني: CONFLICT ← بدل ما يمسح تعديل الأول

// orders.naive.js: ORM "سحري"، وكله كان شغّال على الـ laptop بعشر orders
const orders = await Order.findAll({ where: { customerId } });          // query
for (const o of orders) o.items = await o.getItems();                   // query لكل order ← N+1، ومحدش شايفه

app.get("/orders", async (req, res) => {
  // الـ ORM ما بيعرفش يعمل الـ sort ده، فـ raw query بـ string
  res.json(await sequelize.query(`SELECT * FROM orders ORDER BY ${req.query.sort}`));
});

app.post("/orders", async (req, res) => {
  const p = await Product.findByPk(req.body.sku);
  if (p.stock >= req.body.qty) {             // اتنين قروا stock واحد في نفس الوقت
    p.stock -= req.body.qty;
    await p.save();                          // من غير transaction ولا lock ← الاتنين اشتروا آخر قطعة
  }
  await Order.create({ customerId, total: req.body.total }); // الـ total من الـ client
});

// customer.save() بيكتب كل الـ columns، فتعديل حد تاني في نفس الوقت بيضيع (lost update)
// sequelize.sync({ alter: true }) وقت الـ start في الإنتاج ← الـ schema بتتغير لوحدها، ومن غير history

// schema.js: الـ migrations والـ seed. الـ test والـ lab بيستخدموهم، وانت ما تعدّلش فيهم
export const MIGRATIONS = [
  {
    id: "001_customers_products",
    up: `CREATE TABLE customers (id serial PRIMARY KEY, name text NOT NULL, email text NOT NULL UNIQUE, version int NOT NULL DEFAULT 1);
         CREATE TABLE products (sku text PRIMARY KEY, name text NOT NULL, price_cents int NOT NULL CHECK (price_cents >= 0), stock int NOT NULL CHECK (stock >= 0));`,
  },
  {
    id: "002_orders",
    up: `CREATE TABLE orders (id serial PRIMARY KEY, customer_id int NOT NULL REFERENCES customers(id), status text NOT NULL DEFAULT 'pending',
           total_cents bigint NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
         CREATE TABLE order_items (order_id int NOT NULL REFERENCES orders(id), sku text NOT NULL REFERENCES products(sku), qty int NOT NULL CHECK (qty > 0),
           price_cents int NOT NULL, PRIMARY KEY (order_id, sku));
         CREATE INDEX orders_customer_created ON orders (customer_id, created_at DESC);`,
  },
];

export async function seed(db, sql) {
  await db.query(sql`INSERT INTO customers (name, email) VALUES ('سارة', '[email protected]'), ('عمر', '[email protected]'), ('منى', '[email protected]')`);
  await db.query(sql`INSERT INTO products (sku, name, price_cents, stock) VALUES
    ('MUG-1', 'مج', 15000, 50), ('TSHIRT-M', 'تيشيرت', 35000, 20), ('CAP-1', 'كاب', 12000, 3), ('LAST-1', 'آخر قطعة', 99000, 1)`);
}

// lab.js: نفس الشغل بطريقة الـ ORM الساذجة، وبالـ data layer
import { sql, createDb, migrate, createRepo } from "./data.js";
import { MIGRATIONS, seed } from "./schema.js";

const db = createDb();
await db.query(sql`DROP SCHEMA public CASCADE`);
await db.query(sql`CREATE SCHEMA public`);
console.log("— 1) migrations —");
console.log("  أول مرة:", await migrate(db, MIGRATIONS));
console.log("  أربع pods مع بعض:", JSON.stringify(await Promise.all([1, 2, 3, 4].map(() => migrate(db, MIGRATIONS)))));
await seed(db, sql);
const repo = createRepo(db);
for (let i = 0; i < 30; i++) await repo.placeOrder({ customerId: 1, items: [{ sku: "MUG-1", qty: 1 }, ...(i % 3 ? [] : [{ sku: "TSHIRT-M", qty: 1 }])] });

console.log("\n— 2) N+1 —");
let q0 = db.stats.queries, t0 = Date.now();
const orders = await db.query(sql`SELECT * FROM orders WHERE customer_id = ${1} ORDER BY created_at DESC LIMIT ${20}`);
for (const o of orders) o.items = await db.query(sql`SELECT * FROM order_items WHERE order_id = ${o.id}`);
console.log(`  lazy loading: ${db.stats.queries - q0} queries في ${Date.now() - t0}ms`);
q0 = db.stats.queries; t0 = Date.now();
const list = await repo.listOrders({ customerId: 1, limit: 20 });
console.log(`  listOrders: ${db.stats.queries - q0} query في ${Date.now() - t0}ms ← أول order:`, JSON.stringify(list[0]));

console.log("\n— 3) آخر قطعة، وخمسة بيشتروا مع بعض —");
const naiveBuy = async () => {
  const p = await db.one(sql`SELECT stock FROM products WHERE sku = ${"LAST-1"}`);
  await new Promise((r) => setTimeout(r, 20)); // الـ request بيعمل حاجات تانية
  if (p.stock >= 1) { await db.query(sql`UPDATE products SET stock = ${p.stock - 1} WHERE sku = ${"LAST-1"}`); return "OK"; }
  return "OUT_OF_STOCK";
};
console.log("  read-modify-write:", (await Promise.all([1, 2, 3, 4, 5].map(naiveBuy))).join(" "), "← خمسة اشتروا قطعة واحدة");
await db.query(sql`UPDATE products SET stock = 1 WHERE sku = ${"LAST-1"}`);
const tx = await Promise.all([1, 2, 3, 4, 5].map((c) => repo.placeOrder({ customerId: (c % 3) + 1, items: [{ sku: "LAST-1", qty: 1 }] }).then(() => "OK", (e) => e.code)));
console.log("  placeOrder بـ FOR UPDATE:", tx.join(" "), "، والـ stock:", (await db.one(sql`SELECT stock FROM products WHERE sku = ${"LAST-1"}`)).stock);

console.log("\n— 4) الـ sort من الـ query —");
const evil = "total_cents; DROP TABLE orders";
console.log("  الـ string اللي كانت هتتحط في الـ SQL:", `SELECT * FROM orders ORDER BY ${evil}`);
try { await repo.search({ sort: evil }); } catch (e) { console.log("  search:", e.code); }
const top = await repo.search({ sort: "total", dir: "desc", limit: 3 });
console.log("  sort=total:", top.map((o) => o.totalCents).join(" , "));

console.log("\n— 5) lost update —");
await repo.renameCustomer(2, "عمر خالد", 1);
try { await repo.renameCustomer(2, "عمر من tab قديم", 1); } catch (e) { console.log("  التاني:", e.code, "← بدل ما يمسح تعديل الأول"); }
await db.close();