أساسيات PostgreSQL: الجداول والعلاقات والاستعلامات التي ستكتبها كل يوم

مقدمة

PostgreSQL قاعدة بيانات علائقية مفتوحة المصدر، موثوقة وغنية بالميزات، وتستخدمها منصات كبيرة — ومنها عرب كود. في هذا الدرس نبني مخطط منتدى صغير ونكتب الاستعلامات الأساسية بطريقة صحيحة من البداية.

أهداف التعلم

• إنشاء جداول بأنواع وقيود مناسبة.
• ربط الجداول بالمفاتيح الأجنبية.
• كتابة SELECT مع WHERE وJOIN وGROUP BY.
• استخدام الاستعلامات المُعلَّمة (Parameterized) لمنع حقن SQL.

1. إنشاء الجداول

CREATE TABLE users (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username    TEXT NOT NULL UNIQUE,
    email       TEXT UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE topics (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id   BIGINT NOT NULL REFERENCES users(id),
    title       TEXT NOT NULL CHECK (char_length(title) BETWEEN 8 AND 160),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    topic_id    BIGINT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    author_id   BIGINT NOT NULL REFERENCES users(id),
    body        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

• NOT NULL وUNIQUE وCHECK تجعل القاعدة نفسها تمنع البيانات الخاطئة، حتى لو أخطأ الكود.
• REFERENCES تضمن أن كل مشاركة تتبع موضوعاً موجوداً.
• TIMESTAMPTZ يخزن الوقت بمنطقة زمنية صحيحة؛ تجنب TIMESTAMP بلا منطقة.

2. الإدخال والاستعلام

INSERT INTO users (username, email) VALUES ('sara', 'sara@example.com') RETURNING id;

SELECT id, title, created_at
FROM topics
WHERE created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 20;

3. ربط الجداول بـ JOIN

-- كل موضوع مع اسم كاتبه
SELECT t.title, u.username
FROM topics t
JOIN users u ON u.id = t.author_id;

-- المواضيع مع عدد الردود، بما فيها التي بلا ردود
SELECT t.id, t.title, count(p.id) AS replies
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id
GROUP BY t.id, t.title
ORDER BY replies DESC;

LEFT JOIN يبقي المواضيع التي ليس لها ردود (عددها صفر)، بينما JOIN العادي يحذفها.

4. التجميع والتصفية بعده

-- الأعضاء الذين كتبوا أكثر من 10 مشاركات
SELECT author_id, count(*) AS total
FROM posts
GROUP BY author_id
HAVING count(*) > 10;

WHERE يصفي الصفوف قبل التجميع، وHAVING يصفي المجموعات بعده.

5. حقن SQL: لا تبنِ الاستعلامات بدمج النصوص

// خطير: المدخل "x' OR '1'='1" يغير معنى الاستعلام
db.query(`SELECT * FROM users WHERE username = '${input}'`);

// صحيح: القيمة تُرسل منفصلة عن نص الاستعلام
db.query('SELECT * FROM users WHERE username = $1', [input]);

مكتبات ORM مثل Prisma تستخدم الاستعلامات المعلّمة تلقائياً، لكن انتبه عند كتابة SQL خام بداخلها.

تمرين عملي

1. أنشئ الجداول الثلاثة على قاعدة محلية (يمكنك تشغيل PostgreSQL بـ Docker: docker run --rm -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:16).
2. أدخل 3 مستخدمين و5 مواضيع و20 رداً.
3. اكتب استعلاماً يعرض أنشط 3 أعضاء خلال آخر 30 يوماً مع عدد مشاركاتهم.
4. جرّب إدخال موضوع بعنوان من 3 أحرف ولاحظ رسالة القيد.

أخطاء شائعة

• SELECT * في الكود الإنتاجي: يجلب أعمدة لا تحتاجها ويتأثر بتغيير الجدول.
• نسيان ORDER BY مع LIMIT، فيصبح الترتيب غير محدد.
• تخزين التواريخ كنصوص.
• الاعتماد على الكود وحده للتحقق دون قيود في القاعدة.

الخلاصة

صمم جداول بقيود تمنع البيانات الخاطئة، اربطها بمفاتيح أجنبية، واستعلم بـ JOIN والتجميع بدقة، ومرر القيم دائماً كمعاملات منفصلة.

مصادر مقترحة

• توثيق PostgreSQL الرسمي: «Tutorial».
• موقع Use The Index, Luke (يمهد للدرس التالي عن الفهارس).
• OWASP: «SQL Injection Prevention Cheat Sheet».