الـ N+1 Queries والـ DataLoader: صفحة واحدة بمِيّتين query

// bench.js: الـ feed بياخد كام query وكام ms؟
const B = "http://localhost:3000";
await fetch(`${B}/feed?limit=1`); // warm-up
for (const limit of [10, 50, 100]) {
  const s = performance.now();
  const r = await fetch(`${B}/feed?limit=${limit}`);
  const feed = await r.json();
  console.log(`feed بـ ${String(limit).padStart(3)} post ← ${r.headers.get("x-queries").padStart(3)} query في ${(performance.now() - s).toFixed(0).padStart(4)}ms`);
}
const r = await (await fetch(`${B}/feed?limit=100`)).json();
console.log("أول post:", JSON.stringify(r[0]), "| post سبعة:", JSON.stringify(r.find((p) => p.id === 7)));
const s = performance.now();
const one = await fetch(`${B}/feed-sql?limit=100`);
if (one.ok) console.log(`feed-sql بـ 100 post ← ${one.headers.get("x-queries")} query في ${(performance.now() - s).toFixed(0)}ms`);

$ node server.js & S=$!; sleep 1.5; node bench.js; kill $S
on 3000
feed بـ  10 post ←  21 query في   56ms
feed بـ  50 post ← 101 query في  258ms
feed بـ 100 post ← 201 query في  505ms
أول post: {"id":100,"title":"post 100","author":"author 1","comments":0} | post سبعة: {"id":7,"title":"post 7","author":null,"comments":23}

// loaders.js: DataLoader لكل request، و feed بـ query واحدة
import DataLoader from "dataloader";

// DataLoader لازم يرجّع النتايج بنفس ترتيب الـ keys وبنفس عددها
export function byKeys(keys, rows, keyOf, fallback = null) {
  const m = new Map(rows.map((r) => [keyOf(r), r]));
  return keys.map((k) => m.get(k) ?? fallback);
}

export function createLoaders(query) {
  return {
    author: new DataLoader(async (ids) => {
      const { rows } = await query("SELECT id, name FROM authors WHERE id = ANY($1::int[])", [ids]);
      return byKeys(ids, rows, (r) => r.id);
    }),
    commentCount: new DataLoader(async (ids) => {
      const { rows } = await query("SELECT post_id, count(*)::int AS n FROM comments WHERE post_id = ANY($1::int[]) GROUP BY post_id", [ids]);
      return byKeys(ids, rows, (r) => r.post_id).map((r) => r?.n ?? 0); // post من غير comments مش هيرجع في الـ GROUP BY
    }),
  };
}

// loaders جديدة لكل request: الـ cache بتاعهم يعيش طول الـ request بس
export const withLoaders = (query) => (req, res, next) => { req.loaders = createLoaders(query); next(); };

// أو من غير DataLoader خالص: query واحدة بـ JOIN
export async function feedOneQuery(query, limit) {
  const { rows } = await query(
    `SELECT p.id, p.title, a.name AS author, COALESCE(c.n, 0)::int AS comments
       FROM (SELECT * FROM posts ORDER BY id DESC LIMIT $1) p
       LEFT JOIN authors a ON a.id = p.author_id
       LEFT JOIN (SELECT post_id, count(*) AS n FROM comments GROUP BY post_id) c ON c.post_id = p.id
      ORDER BY p.id DESC`,
    [limit],
  );
  return rows;
}

$ node server.js & S=$!; sleep 1.5; node bench.js; kill $S
on 3000
feed بـ  10 post ←   3 query في   12ms
feed بـ  50 post ←   3 query في   10ms
feed بـ 100 post ←   3 query في   11ms
أول post: {"id":100,"title":"post 100","author":"author 1","comments":0} | post سبعة: {"id":7,"title":"post 7","author":null,"comments":23}
feed-sql بـ 100 post ← 1 query في 6ms

// server.js: نفس الـ feed، بس بـ DataLoader، و feed-sql بـ query واحدة
import express from "express";
import { query, seed, stats } from "./db.js";
import { withLoaders, feedOneQuery } from "./loaders.js";

const app = express();
await seed();
app.use(withLoaders(query));
const counted = (handler) => async (req, res) => {
  const before = stats.queries;
  const body = await handler(req);
  res.set("X-Queries", String(stats.queries - before)).json(body);
};
const limitOf = (req) => Math.min(Number(req.query.limit ?? 50), 100);

app.get("/feed", counted(async (req) => {
  const { rows: posts } = await query("SELECT id, author_id, title FROM posts ORDER BY id DESC LIMIT $1", [limitOf(req)]);
  // load كتير في نفس الـ tick ← DataLoader بيجمعهم في query واحدة لكل نوع
  return Promise.all(posts.map(async (p) => {
    const [author, comments] = await Promise.all([req.loaders.author.load(p.author_id), req.loaders.commentCount.load(p.id)]);
    return { id: p.id, title: p.title, author: author?.name ?? null, comments };
  }));
}));
app.get("/feed-sql", counted((req) => feedOneQuery(query, limitOf(req))));

app.listen(3000, () => console.log("on 3000"));

// db.js: pool بيعدّ الـ queries، و data للتجربة
import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
export const stats = { queries: 0 };
// الـ database هنا على نفس الـ machine. في الحقيقة كل query بتروح وترجع على الـ network
// فبنضيف اتنين ms لكل query، زي database في availability zone تانية
const RTT = Number(process.env.DB_RTT_MS ?? 2);
export async function query(sql, params) {
  stats.queries++;
  await new Promise((r) => setTimeout(r, RTT));
  return pool.query(sql, params);
}

export async function seed() {
  await pool.query(`
    DROP TABLE IF EXISTS comments, posts, authors;
    CREATE TABLE authors (id int PRIMARY KEY, name text NOT NULL);
    CREATE TABLE posts (id int PRIMARY KEY, author_id int, title text NOT NULL);
    CREATE TABLE comments (id serial PRIMARY KEY, post_id int NOT NULL REFERENCES posts, body text);
    CREATE INDEX ON comments (post_id);
    INSERT INTO authors SELECT i, 'author ' || i FROM generate_series(1, 20) i;
    INSERT INTO posts SELECT i, CASE WHEN i = 7 THEN 999 ELSE 1 + i % 20 END, 'post ' || i FROM generate_series(1, 100) i;
    INSERT INTO comments (post_id, body) SELECT 1 + i % 90, 'c' || i FROM generate_series(1, 2000) i;
  `);
}
export const end = () => pool.end();

import pg from "pg";
import { byKeys, createLoaders, feedOneQuery } from "./loaders.js";

let ok = 0, all = 0;
const check = (name, cond, extra = "") => { all++; if (cond) ok++; console.log(cond ? "✅" : "❌", name, extra); };
const B = "http://localhost:3000";

check("byKeys: بترتيب الـ keys، واللي مش موجود ← fallback", JSON.stringify(byKeys([3, 1, 9], [{ id: 1 }, { id: 3 }], (r) => r.id)) === '[{"id":3},{"id":1},null]');
check("byKeys: fallback متغيّر", byKeys(["x"], [], (r) => r.k, 0)[0] === 0);

const calls = [];
const fake = async (sql, [ids]) => {
  calls.push({ sql, ids: [...ids] });
  if (sql.includes("authors")) return { rows: ids.filter((i) => i !== 999).reverse().map((id) => ({ id, name: `a${id}` })) }; // بترتيب مقلوب
  return { rows: ids.filter((i) => i % 2).map((post_id) => ({ post_id, n: post_id * 10 })) };
};
const L = createLoaders(fake);
const got = await Promise.all([L.author.load(1), L.author.load(2), L.author.load(999), L.author.load(1)]);
check("author: أربع loads في نفس الـ tick ← query واحدة", calls.length === 1 && calls[0].sql.includes("ANY"));
check("author: الـ ids من غير تكرار", JSON.stringify(calls[0].ids) === "[1,2,999]");
check("author: الترتيب صح رغم إن الـ rows مقلوبة، و 999 ← null", got[0].name === "a1" && got[1].name === "a2" && got[2] === null && got[3].name === "a1");
await L.author.load(2);
check("author: نفس الـ key تاني في نفس الـ request ← من الـ cache", calls.length === 1);
const cc = await Promise.all([1, 2, 3].map((i) => L.commentCount.load(i)));
check("commentCount: query واحدة بـ GROUP BY، واللي مالوش comments ← صفر", calls.length === 2 && calls[1].sql.includes("GROUP BY") && JSON.stringify(cc) === "[10,0,30]");
await createLoaders(fake).author.load(2);
check("loaders جديدة ← cache جديد (مفيش cache بين الـ requests)", calls.length === 3);

const get = async (p) => { const s = performance.now(); const r = await fetch(B + p); return { q: Number(r.headers.get("x-queries")), body: await r.json(), ms: performance.now() - s }; };
const f50 = await get("/feed?limit=50");
check("server: feed بخمسين post ← تلات queries", f50.q === 3, `${f50.q}`);
check("server: خمسين post مترتبين من الأجدد", f50.body.length === 50 && f50.body[0].id === 100 && f50.body[49].id === 51);
const f100 = await get("/feed?limit=100");
check("server: feed بمية post ← لسه تلاتة", f100.q === 3);
check("server: post سبعة author بتاعه مش موجود ← null، و comments صح", (() => { const p = f100.body.find((x) => x.id === 7); return p.author === null && p.comments === 23; })());
check("server: feed بمية أسرع من مية ms", f100.ms < 100, `${f100.ms.toFixed(0)}ms`);
const db = new pg.Client({ connectionString: process.env.DATABASE_URL });
await db.connect();
const { rows: [truth] } = await db.query("SELECT count(*)::int AS n FROM comments WHERE post_id = 50");
check("server: عدد الـ comments زي الـ database", f100.body.find((x) => x.id === 50).comments === truth.n);
const sql = await get("/feed-sql?limit=100");
check("server: feed-sql ← query واحدة ونف
…