SQL & Database Tutorial پروژه عملی پایان دوره پروژه عملی سطح: سخت

پروژه پایانی ۲ — API وبلاگ با دیتابیس و Prepared Statements

پروژه پایانی دوم: تلفیق دوره‌های SQL و بک‌اند — یک API وبلاگ کوچک با Node.js (یا PHP) که پست‌ها و کامنت‌ها را در SQLite می‌نویسد و می‌خواند؛ همه کوئری‌ها Prepared Statement با پارامتر.

راه‌حل قدم‌به‌قدم پروژه

  1. ۱

    هدف پروژه

    ساخت CRUD پست + کامنت روی SQLite با عملیات پارامتری و یک گزارش JOIN.

  2. ۲

    الزامات و موارد موردنیاز

    Node.js با better-sqlite3 (npm i better-sqlite3) یا PHP با PDO؛ آشنایی با درس‌های PDO و Express.

  3. ۳

    قدم ۱ — اسکیما و اتصال

    دو جدول: posts(id, title, body, created_at) و comments(id, post_id FK, author, body). اتصال را یک‌بار بسازید (db.ts یا db.js) و همه‌جا از آن import کنید.

  4. ۴

    قدم ۲ — لایه داده پارامتری

    توابع listPosts، getPost، createPost، addComment، listComments — همه با placeholder ?؛ حتی برای id و متن جستجو. تست حیاتی: تلاش برای تزریق ' OR 1=1 -- باید مثل متن معمولی رفتار شود نه دستور.

  5. ۵

    قدم ۳ — مسیرهای API

    GET /posts، GET /posts/:id (با کامنت‌هایش)، POST /posts، POST /posts/:id/comments و DELETE /posts/:id. کدهای وضعیت: 201 برای ساخت، 404 برای نبودن، 422 برای داده ناقص.

  6. ۶

    قدم ۴ — گزارش و صفحه‌بندی

    GET /stats با JOIN: تعداد کامنت هر پست و سه پست پرکامنت‌تر. صفحه‌بندی با LIMIT ? OFFSET ? و پاسخ meta شامل total.

  7. ۷

    قدم ۵ — تست و تحویل

    با curl همه مسیرها را تست کنید؛ خطای 404، تزریق، متن خالی و متن خیلی بلند را دستی امتحان کنید. فایل .db را به .gitignore ببرید و اسکریپت seed بسازید تا هم‌تیمی داده اولیه داشته باشد.

نکات مهم

  • ◆ قبل از خواندن راه‌حل، خودتان حداقل ۳۰ دقیقه تلاش کنید؛ یادگیری واقعی در همین تلاش اتفاق می‌افتد.
  • ◆ راه‌حل را بعد از دیدن، عمداً تغییر دهید و دوباره اجرا کنید تا با هر خط آن آشنا شوید.
  • ◆ اگر جایی گیر کردید، فقط بخش مشکل را جستجو کنید، نه کل پروژه را — دامنه جستجوی کوچک یادگیری را عمیق‌تر می‌کند.

کد کامل راه‌حل پروژه

// db.js (Node + better-sqlite3)
const Database = require("better-sqlite3");
const db = new Database("blog.db");
db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
  );
  CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author TEXT NOT NULL,
    body TEXT NOT NULL
  );
`);

module.exports = db;

// app.js
const db = require("./db");
const express = require("express");
const app = express();
app.use(express.json());

app.get("/posts", (req, res) => {
  const page = Math.max(1, Number(req.query.page) || 1);
  const limit = 10;
  const rows = db.prepare("SELECT id, title, created_at FROM posts ORDER BY id DESC LIMIT ? OFFSET ?")
    .all(limit, (page - 1) * limit);
  const total = db.prepare("SELECT COUNT(*) AS c FROM posts").get().c;
  res.json({ data: rows, meta: { page, total } });
});

app.get("/posts/:id", (req, res) => {
  const post = db.prepare("SELECT * FROM posts WHERE id = ?").get(Number(req.params.id));
  if (!post) return res.status(404).json({ error: "پست یافت نشد" });
  post.comments = db.prepare("SELECT author, body FROM comments WHERE post_id = ?").all(post.id);
  res.json(post);
});

app.post("/posts", (req, res) => {
  const title = String(req.body?.title || "").trim();
  const body = String(req.body?.body || "").trim();
  if (title.length < 3 || body.length < 10) return res.status(422).json({ error: "عنوان/متن کوتاه است" });
  const info = db.prepare("INSERT INTO posts (title, body) VALUES (?, ?)").run(title, body);
  res.status(201).json({ id: info.lastInsertRowid, title, body });
});

app.get("/stats", (req, res) => {
  const rows = db.prepare(`
    SELECT p.id, p.title, COUNT(c.id) AS comments
    FROM posts p LEFT JOIN comments c ON c.post_id = p.id
    GROUP BY p.id ORDER BY comments DESC LIMIT 3
  `).all();
  res.json(rows);
});

app.listen(3000, () => console.log("Blog API روی :3000"));

اجرای زنده کد JavaScript

editor.html
در حال آماده‌سازی زمین بازی…

کد را ویرایش کنید و «اجرای کد» را بزنید — خروجی واقعی مرورگر همین‌جا رندر می‌شود؛ مثل آزمایشگاه w3schools.

خروجی مورد انتظار

تست‌های مورد انتظار:
  POST پست موفق → 201 با id
  GET /posts → صفحه‌بندی‌شده با meta.total
  تلاش تزریق در title:  ' OR 1=1 --  → ذخیره مثل متن معمولی (بدون اثر)
  GET /posts/999 → 404 فارسی
  GET /stats → سه پست پرکامنت با شمارش

چالش اضافه (اختیاری)

نسخه PHP با PDO بنویسید و تفاوت‌های transaction و prepared بین دو دنیا را مستند کنید.

پرسش و پاسخ این درس (۰)

هنوز دیدگاهی ثبت نشده؛ اولین نفر باشید!

دیدگاه خود را بنویسید

کپچا

دیدگاه شما پس از تأیید مدیر نمایش داده می‌شود.