Node.js আর ডেটাবেস — SQL, parameterized query, transaction আর ORM

১ · মেমোরির array কেন যথেষ্ট নয় — ডেটাবেস কী দেয়

পর্ব ৫০-এ আপনি Express দিয়ে একটি দোকানের API বানিয়েছেন। পণ্য আর অর্ডার রাখা ছিল একটি সাধারণ array-তে: const products = [...]. ডেমোতে চমৎকার চলে। এবার বাস্তবের তিনটি ঘটনা ভাবুন। এক: রাত দুটোয় সার্ভার restart হলো (নতুন কোড deploy, বা বিদ্যুৎ গেল) — array-র সব অর্ডার মুছে গেল, কারণ সেগুলো ছিল শুধু RAM-এ। দুই: ঈদের ভিড়ে আপনি দুটি সার্ভার চালালেন — প্রত্যেকের নিজের array, তাই একজন ক্রেতার অর্ডার এক সার্ভারে আছে, অন্যটিতে নেই। তিন: পণ্য দশ লাখ হলো — প্রতিটি খোঁজায় পুরো array ঘুরে দেখা অনেক ধীর।

ডেটাবেস এই তিন সমস্যারই উত্তর। এটি একটি আলাদা প্রোগ্রাম (প্রায়ই আলাদা সার্ভারে), যার কাজ ডেটা স্থায়ীভাবে রাখা (disk-এ লেখা নিশ্চিত হলে তবেই “হয়েছে” বলে), অনেক অনুরোধকে একসাথে নিরাপদে পড়তে-লিখতে দেওয়া (transaction), আর দ্রুত খুঁজে দেওয়া (index). Node ডেটাবেসের সাথে কথা বলে একটি driver দিয়ে, নেটওয়ার্কের ওপর। কথা বলার দুই ধরন: নিজে SQL লেখা, অথবা ORM বা query builder-কে দিয়ে লেখানো। আর যে পথই নিন, একটি নিয়ম বদলায় না: ব্যবহারকারীর ইনপুট যায় parameter হিসেবে, query-র লেখায় জোড়া লেগে নয়।

দুই পরিবার — relational আর document

কোনটি বেছে নেবেন? প্রশ্নটি “কোনটি আধুনিক” নয় — “আমার ডেটার আকার আর নিয়ম কেমন”. ডেটায় যদি অনেক সম্পর্ক থাকে (ক্রেতা → অর্ডার → আইটেম → পণ্য), টাকার হিসাবে ভুল চলবে না, আর বিভিন্ন দিক থেকে রিপোর্ট লাগবে — relational. ডেটা যদি স্বাভাবিকভাবে একটি document-এ বসে (একটি ব্লগ পোস্ট তার ট্যাগসহ), আকার ঘন ঘন বদলায়, আর প্রায় সব প্রশ্ন “এই id-র পুরো জিনিসটি দাও” — document ভালো মানায়। Firebase-এর Firestore একটি document ডেটাবেস, যা ব্রাউজার থেকে সরাসরি পড়া যায় আর real-time আপডেট দেয়। এই পর্বের বেশিরভাগ উদাহরণ PostgreSQL দিয়ে, কারণ এর ধারণাগুলো প্রায় সব ডেটাবেসে কাজে লাগে; MongoDB আসবে অংশ ৮-এ।

২ · দশ মিনিটে SQL — টেবিল, key, CRUD, JOIN আর GROUP BY

SQL (Structured Query Language) ডেটাবেসকে “কীভাবে” নয়, “কী” চাই তা বলে: “ঢাকার ক্রেতাদের নাম দাও, নাম অনুযায়ী সাজিয়ে” — ডেটাবেস নিজে ঠিক করে কোন index ধরে, কোন ক্রমে খুঁজবে। পর্ব ১৬-এর filter/map/reduce মনে আছে? SQL-এর WHERE, SELECT আর GROUP BY প্রায় একই ভাবনা — শুধু কাজটি হয় ডেটাবেসের ভেতরে, ডেটা নেটওয়ার্ক দিয়ে Node-এ টেনে না এনে।

প্রতিটি টেবিলে একটি primary key থাকে — প্রতিটি সারির অনন্য পরিচয়, যেমন রোল নম্বর। অন্য টেবিলের সারিকে নির্দেশ করে foreign key: orders.customer_id বলে “এই অর্ডারটি কোন ক্রেতার”, আর ডেটাবেস নিশ্চিত করে যে এমন id-র ক্রেতা সত্যিই আছে। ডেটার চারটি মৌলিক কাজকে বলে CRUD — Create, Read, Update, Delete — SQL-এ INSERT, SELECT, UPDATE, DELETE:

-- Create: নতুন সারি
INSERT INTO customers (name, phone, district) VALUES ('রহিমা', '01711000000', 'ঢাকা');

-- Read: শর্ত, ক্রম আর সীমা
SELECT id, name FROM customers
WHERE district = 'ঢাকা'
ORDER BY name
LIMIT 20 OFFSET 0;          -- প্রথম পাতার ২০ জন

-- Update আর Delete: WHERE ভুলে গেলে সব সারি বদলাবে বা মুছবে!
UPDATE products SET stock = stock - 1 WHERE id = 7;
DELETE FROM carts WHERE updated_at < now() - interval '30 days';

-- JOIN: দুই টেবিল key ধরে মেলানো
SELECT o.id, c.name, o.total_paisa
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-10-01';

-- GROUP BY: জেলাভিত্তিক বিক্রি, বেশি থেকে কম
SELECT c.district, COUNT(*) AS orders, SUM(o.total_paisa) AS revenue_paisa
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY c.district
HAVING COUNT(*) >= 10
ORDER BY revenue_paisa DESC;

তিনটি জিনিস খেয়াল করুন। (১) JOIN-এর সাধারণ রূপ (INNER JOIN) শুধু মিল-থাকা সারি দেয়; যে ক্রেতার কোনো অর্ডার নেই, তাকেও তালিকায় চাইলে LEFT JOIN. (২) WHERE চলে দল বাঁধার আগে, HAVING চলে দল বাঁধার পরে। (৩) LIMIT … OFFSET ছোট তালিকায় ঠিক আছে, কিন্তু OFFSET ১০ লাখ হলে ডেটাবেসকে আগের ১০ লাখ সারি গুনে ফেলে দিতে হয়; বড় তালিকায় “শেষ দেখা id-র পর থেকে” (WHERE id < $1 ORDER BY id DESC LIMIT 20) — একে বলে keyset বা cursor pagination.

import { DatabaseSync } from "node:sqlite";      // Node 22.5+ (experimental)

const db = new DatabaseSync(":memory:");           // ফাইল চাইলে "shop.db"
db.exec(`
  CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT NOT NULL,
                         category TEXT, price_paisa INTEGER NOT NULL);
  INSERT INTO products (title, category, price_paisa) VALUES
    ('চাল ৫ কেজি', 'grocery', 42000), ('ডাল ১ কেজি', 'grocery', 14000),
    ('ছাতা', 'rain', 55000);
`);
const byCategory = db.prepare(
  "SELECT title, price_paisa FROM products WHERE category = ? ORDER BY price_paisa DESC");
console.log(byCategory.all("grocery"));
console.log(byCategory.all("x' OR '1'='1").length);
console.log(db.prepare("SELECT category, COUNT(*) AS n FROM products GROUP BY category").all());
// প্রত্যাশিত আউটপুট (Node 24-এ চালানো):
// [
//   [Object: null prototype] { title: 'চাল ৫ কেজি', price_paisa: 42000 },
//   [Object: null prototype] { title: 'ডাল ১ কেজি', price_paisa: 14000 }
// ]
// 0
// [
//   [Object: null prototype] { category: 'grocery', n: 2 },
//   [Object: null prototype] { category: 'rain', n: 1 }
// ]

? হলো SQLite-এর placeholder; দ্বিতীয় লাইনের অদ্ভুত লেখাটি একটি সাধারণ মান হিসেবেই তুলনা হলো, তাই 0 সারি — অংশ ৫-এ এর কারণ বিস্তারিত। সারিগুলো null prototype-এর object (পর্ব ২৫) — ডেটাবেসের কলামের নাম যেন Object.prototype-এর কোনো property-র সাথে না মেশে।

৩ · একটি ই-শপের schema — customers, products, orders আর order_items

কোড লেখার আগে টেবিলের নকশা — একে বলে schema. ভালো schema অর্ধেক বাগ শুরুতেই থামায়, কারণ ভুল ডেটা ডেটাবেস ঢুকতেই দেয় না। আমাদের দোকানের চারটি টেবিল:

CREATE TABLE customers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  phone       text NOT NULL UNIQUE,                  -- একই নম্বরে দুই অ্যাকাউন্ট নয়
  district    text,
  created_at  timestamptz NOT NULL DEFAULT now()     -- UTC-তে জমা থাকে
);

CREATE TABLE products (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title        text NOT NULL,
  category     text NOT NULL,
  price_paisa  integer NOT NULL CHECK (price_paisa >= 0),   -- টাকা নয়, পয়সা
  stock        integer NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers(id),
  status       text NOT NULL DEFAULT 'pending',
  total_paisa  integer NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id     bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id   bigint NOT NULL REFERENCES products(id),
  qty          integer NOT NULL CHECK (qty > 0),
  unit_paisa   integer NOT NULL,                     -- কেনার মুহূর্তের দাম
  PRIMARY KEY (order_id, product_id)
);

কেন order_items আলাদা টেবিল? একটি অর্ডারে অনেক পণ্য, আর একটি পণ্য অনেক অর্ডারে — এটি many-to-many সম্পর্ক। orders-এ product1, product2, product3 কলাম বানালে চতুর্থ পণ্যে আটকে যাবেন, আর “চাল কতবার বিক্রি হলো” প্রশ্নটি তিন কলামে খুঁজতে হবে। মাঝের টেবিলে প্রতিটি পণ্য-লাইন একটি সারি — যত খুশি পণ্য, আর রিপোর্ট সহজ। একই তথ্য এক জায়গায় রাখার এই নিয়মকে বলে normalization. কিন্তু unit_paisa ইচ্ছা করেই দ্বিতীয়বার রাখা: পণ্যের দাম কাল বদলাবে, কিন্তু গত মাসের রসিদে পুরোনো দামই থাকতে হবে।

কেন পয়সায়? পর্ব ১০-এ দেখেছেন, 0.1 + 0.2 হয় 0.30000000000000004 — floating-point দশমিককে হুবহু রাখতে পারে না। টাকার কলাম float বা real হলে হাজার অর্ডারের যোগফলে পয়সার গরমিল জমবে। তাই দুই পথ: পূর্ণসংখ্যা পয়সা (৪২০ টাকা = 42000), অথবা ডেটাবেসের নির্ভুল দশমিক ধরন numeric(12,2). একটি সূক্ষ্ম ফাঁদ: pg driver numeric আর bigint কলাম JavaScript-এ string হিসেবে দেয় (যেমন COUNT(*)-এর ফল '42', এমনকি bigint id-ও '7'), কারণ Number-এ সেগুলো নির্ভুল না-ও ধরতে পারে। '42' + 1 হয় '421' — পর্ব ৬-এর coercion-এর ফাঁদ এখানে আবার।

কেন timestamptz? পর্ব ২০-এর নিয়ম: সময় জমা রাখুন UTC-তে, দেখান ব্যবহারকারীর সময় অঞ্চলে। PostgreSQL-এর timestamptz ঢোকানো সময়কে UTC-তে রূপান্তর করে রাখে, আর pg সেটি JavaScript-এর Date হিসেবে দেয়। শুধু timestamp (সময় অঞ্চল ছাড়া) দিলে সার্ভারকে UTC-তে চলা cloud-এ সরালেই সময় ছয় ঘণ্টা সরে যাওয়ার ঝুঁকি।

আর constraint-গুলো — NOT NULL, UNIQUE, CHECK (stock >= 0), REFERENCES — হলো শেষ পাহারাদার। Node-এর যাচাই (পর্ব ৩৯-এর server-side validation) সুন্দর error message দেয়, কিন্তু কোনো বাগ, কোনো পুরোনো স্ক্রিপ্ট বা দুটি একসাথে চলা অনুরোধ সেটি পেরিয়ে গেলে ডেটাবেসের constraint তবু ভুল ডেটা ঢুকতে দেবে না।

৪ · Node থেকে সংযোগ — driver, Pool আর connection pooling

PostgreSQL-এর জন্য Node-এর সবচেয়ে প্রচলিত driver হলো pg (node-postgres): npm install pg. এটি দুটি জিনিস দেয়। Client = একটি সংযোগ — নিজে খুলবেন, নিজে বন্ধ করবেন; script বা migration-এর জন্য ঠিক আছে। Pool = কয়েকটি সংযোগের একটি দল, যেগুলো অনুরোধে অনুরোধে ভাগ করে ব্যবহার হয় — সার্ভারের জন্য এটিই সঠিক।

কেন pool? একটি নতুন সংযোগ খোলা সস্তা নয়: TCP সংযোগ, প্রায়ই TLS handshake, পাসওয়ার্ড যাচাই — আর PostgreSQL প্রতিটি সংযোগের জন্য সার্ভারে একটি আলাদা process চালায়, যা মেমোরি খায়। ডিফল্টে PostgreSQL সর্বোচ্চ ১০০টি সংযোগ নেয় (max_connections). প্রতি অনুরোধে নতুন সংযোগ খুললে প্রতিটি অনুরোধ কয়েক মিলিসেকেন্ড বাড়তি অপেক্ষা করে, আর ভিড়ের সময় ডেটাবেস “too many clients” বলে নতুন সংযোগ ফিরিয়ে দেয়। Pool সংযোগগুলো খোলা রেখে বারবার ব্যবহার করে, আর সর্বোচ্চ সংখ্যা বেঁধে দেয় — ডেটাবেসকে রক্ষা করে।

const pool = new Pool({ connectionString, max: 5 });
সংযোগ:  [ ] [ ] [ ] [ ] [ ]     ← এখনো একটিও খোলা নয় (pg দরকার হলে খোলে)
অপেক্ষার সারি: খালি

অনুরোধ ১–৫ এল → প্রত্যেকের জন্য নতুন সংযোগ খোলা (handshake + login)
সংযোগ:  [১] [২] [৩] [৪] [৫]   ← সব ব্যস্ত
অপেক্ষার সারি: খালি

অনুরোধ ৬–১০ এল → নতুন সংযোগ খোলা হবে না (max 5)
সংযোগ:  [১] [২] [৩] [৪] [৫]
অপেক্ষার সারি: ৬ → ৭ → ৮ → ৯ → ১০
// Node block হয়নি: এরা শুধু await করছে, অন্য কাজ চলছে

অনুরোধ ২-এর query শেষ → সংযোগ pool-এ ফেরত (release)
→ সারির প্রথম জন অনুরোধ ৬ সেটি সাথে সাথে পেল — নতুন handshake নেই
সংযোগ:  [১] [৬] [৩] [৪] [৫]
অপেক্ষার সারি: ৭ → ৮ → ৯ → ১০

১০টি অনুরোধ, খোলা হলো মাত্র ৫টি সংযোগ; সেগুলো এখন idle, পরের অনুরোধের অপেক্ষায়
pool ছাড়া: ১০টি সংযোগ খোলা-বন্ধ, ভিড়ে ১০০ ছাড়ালে "too many clients"
// ৪টি সার্ভার × max 5 = ডেটাবেসে সর্বোচ্চ ২০টি সংযোগ — হিসাব আপনার হাতে

এবার আসল কোড। Pool একবার বানিয়ে একটি মডিউল থেকে export করুন (পর্ব ৪৫), আর সংযোগের ঠিকানা নিন environment variable থেকে:

// db.js — পুরো অ্যাপে একটিই pool (npm install pg)
import pg from "pg";

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,   // postgres://shop_app:…@db.example:5432/shop
  max: 10,                                      // এই সার্ভার থেকে সর্বোচ্চ ১০টি সংযোগ
  idleTimeoutMillis: 30_000,                    // ৩০ সেকেন্ড অলস থাকলে বন্ধ
  connectionTimeoutMillis: 5_000,               // ৫ সেকেন্ডে সংযোগ না পেলে error
});

pool.on("error", (err) => console.error("idle client error", err));   // পটভূমির error

// ব্যবহার: await pool.query(text, values) → { rows, rowCount, … }
const { rows } = await pool.query("SELECT now() AS t");
console.log(rows[0].t instanceof Date);
// প্রত্যাশিত আউটপুট: true

প্রতিটি query একটি network round-trip, তাই pool.query একটি Promise ফেরত দেয় — await করুন (পর্ব ৩৪)। DATABASE_URL-এর মতো connection string-এ পাসওয়ার্ড থাকে, তাই এটি কোডে নয়, environment-এ: ডেভেলপমেন্টে .env ফাইলে (যা .gitignore-এ থাকবেই), আর Node 20.6 থেকে node --env-file=.env server.js দিয়ে আলাদা package ছাড়াই পড়া যায়; প্রোডাকশনে hosting-এর secret সেটিংসে। MySQL-এ mysql2 (placeholder ?), SQLite-এ better-sqlite3 (synchronous API) — ধারণা একই।

৫ · Parameterized query — SQL injection ঠেকানোর একমাত্র নির্ভরযোগ্য পথ

ধরুন GET /api/products?category=grocery. সবচেয়ে “স্বাভাবিক” মনে হওয়া কোডটি হলো template literal দিয়ে SQL বানানো: `SELECT … WHERE category = '${req.query.category}'`. সমস্যা হলো, তখন ব্যবহারকারীর পাঠানো লেখা আর আপনার লেখা SQL একই string-এ মিশে যায় — আর ডেটাবেস জানে না কোন অংশ আপনার, কোন অংশ বাইরের। ইনপুটে একটি ' থাকলেই string শেষ হয়ে যায়, আর তার পরের অক্ষরগুলো SQL-এর নির্দেশ হিসেবে পড়া হয়। পর্ব ৪৩-এর XSS-এর মতোই রোগ: ডেটা কোড হয়ে গেছে। নিচের ধাপগুলোতে শুধু তৈরি হওয়া SQL-এর লেখা দেখুন — কোনো ডেটাবেস নেই, কোনো আক্রমণ নেই, পাঠ্যবইয়ের চিরচেনা উদাহরণটি মাত্র।

const sql = "SELECT id, name FROM customers WHERE phone = '" + phone + "'";
// phone আসছে ব্যবহারকারীর কাছ থেকে — req.body.phone

phone = 01711000000
SELECT id, name FROM customers WHERE phone = '01711000000'
// ঠিকঠাক — একটি string, একটি শর্ত

phone = x' OR '1'='1
SELECT id, name FROM customers WHERE phone = 'x' OR '1'='1'
// প্রথম ' string বন্ধ করল; OR এখন SQL-এর শব্দ
// '1'='1' সবসময় সত্য → শর্তটি সব সারির জন্য সত্য

pool.query("SELECT id, name FROM customers WHERE phone = $1", [phone])
→ ডেটাবেসে যায় দুটি আলাদা জিনিস:
  লেখা:  SELECT … WHERE phone = $1      ← আগে parse হয়, কাঠামো স্থির
  মান:   [ "x' OR '1'='1" ]             ← পরে শুধু মান হিসেবে বসে

ডেটাবেস খোঁজে: phone কলামের মান হুবহু "x' OR '1'='1" — এমন নম্বর কারও নেই
→ 0টি সারি; WHERE-এর কাঠামো বদলায়নি
// ইনপুট যা-ই হোক, তা কেবলই একটি "ফোন নম্বর"

নিচের প্রোগ্রামটি একই জিনিস কোডে দেখায় — কোনো ডেটাবেস নয়, শুধু তৈরি হওয়া SQL-এর লেখা আর parameterized query-র object (pg ঠিক এই { text, values } আকারের object নেয়):

// ❌ লেখা জোড়া দিয়ে query — শুধু SQL লেখাটি দেখছি, কোনো ডেটাবেস নেই
const unsafeQuery = (phone) =>
  "SELECT id, name FROM customers WHERE phone = '" + phone + "'";

// ✅ parameterized query — SQL-এর লেখা আর মান আলাদা থাকে
const safeQuery = (phone) => ({
  text: "SELECT id, name FROM customers WHERE phone = $1",
  values: [phone],
});

for (const input of ["01711000000", "x' OR '1'='1"]) {
  console.log("ইনপুট:", input);
  console.log("  জোড়া:", unsafeQuery(input));
  console.log("  আলাদা:", JSON.stringify(safeQuery(input)));
}

// সার্ভার $1-কে শুধু একটি মান হিসেবে তুলনা করে — পুরো লেখাটিই একটি "ফোন নম্বর"
const rows = [{ id: 1, phone: "01711000000" }, { id: 2, phone: "01811000000" }];
const q = safeQuery("x' OR '1'='1");
console.log("parameterized-এ মিলল:", rows.filter((r) => r.phone === q.values[0]).length);
const where = unsafeQuery("x' OR '1'='1").split("WHERE ")[1];
console.log("জোড়া দেওয়া WHERE এখন:", where, "→ শর্ত", where.split(" OR ").length, "টি");

এবার আসল route. pg-তে placeholder $1, $2 …; একটি জিনিস placeholder দিয়ে হয় না — টেবিল বা কলামের নাম (যেমন ORDER BY-এর কলাম)। সেখানে একটি অনুমোদিত তালিকা (allowlist) থেকে বেছে নিন, ইনপুট সরাসরি নয়:

// GET /api/products?category=grocery&sort=price
const SORTS = { price: "price_paisa", title: "title", newest: "id DESC" };   // allowlist

app.get("/api/products", async (req, res, next) => {
  try {
    const category = String(req.query.category ?? "");
    const orderBy = SORTS[req.query.sort] ?? "id";          // অজানা মান → ডিফল্ট
    const { rows } = await pool.query(
      `SELECT id, title, price_paisa, stock FROM products
       WHERE category = $1 ORDER BY ${orderBy} LIMIT 50`,     // ${} শুধু allowlist-এর মান
      [category]                                             // ইনপুট parameter হিসেবে
    );
    res.json(rows);
  } catch (err) { next(err); }
});
// প্রত্যাশিত আউটপুট (GET /api/products?category=grocery&sort=price):
// [{"id":"2","title":"ডাল ১ কেজি","price_paisa":14000,"stock":1}, {"id":"1","title":"চাল ৫ কেজি",…}]
// id কেন string? bigint কলাম — pg সেটি string হিসেবে দেয় (অংশ ৩)

৬ · Transaction — অর্ডার হয় পুরোটা, নয়তো একটুও না

একটি অর্ডার নেওয়া মানে কয়েকটি লেখা: orders-এ একটি সারি, order_items-এ প্রতিটি পণ্যের সারি, আর products-এ স্টক কমানো। মাঝপথে কিছু ব্যর্থ হলে — দ্বিতীয় পণ্যের স্টক শেষ, সার্ভার crash, নেটওয়ার্ক বিচ্ছিন্ন — কী থাকবে? Transaction ছাড়া: একটি অর্ডার যার অর্ধেক আইটেম আছে, আর একটি পণ্যের স্টক কমেছে অথচ বিক্রি হয়নি। Transaction-এর প্রতিশ্রুতি চারটি, একসাথে ACID: Atomicity (সব নয়তো কিছুই না), Consistency (constraint কখনো ভাঙে না), Isolation (একসাথে চলা transaction একে অপরের অর্ধেক কাজ দেখে না), Durability (COMMIT সফল মানে বিদ্যুৎ গেলেও ডেটা থাকবে)।

1await client.query("BEGIN");2const o = await client.query("INSERT INTO orders … RETURNING id");3for (const it of cart) {4  await client.query("INSERT INTO order_items …");5  const r = await client.query("UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1", …);6  if (r.rowCount === 0) throw new OutOfStockError(it.productId);7}8await client.query("COMMIT");9} catch (e) { await client.query("ROLLBACK"); throw e; }

pg-তে transaction-এর সবচেয়ে জরুরি নিয়ম: সব query একই client-এ. pool.query প্রতিবার pool থেকে যেকোনো একটি সংযোগ নেয় — BEGIN এক সংযোগে, INSERT আরেকটিতে গেলে transaction-এর কোনো মানেই থাকে না। আর finally-তে release() ভুললে সংযোগটি আর pool-এ ফেরে না; কয়েকবার ভুল হলেই pool ফাঁকা, আর পুরো অ্যাপ অপেক্ষার সারিতে আটকে যায় (connection leak). পুরো ধাঁচটি:

// POST /api/orders  body: { customerId, items: [{ productId, qty }] }
export async function placeOrder(customerId, items) {
  const client = await pool.connect();               // pool থেকে একটি নির্দিষ্ট সংযোগ
  try {
    await client.query("BEGIN");
    const { rows: [order] } = await client.query(
      "INSERT INTO orders (customer_id, total_paisa) VALUES ($1, 0) RETURNING id", [customerId]);
    let total = 0;
    for (const { productId, qty } of items) {
      const r = await client.query(
        `UPDATE products SET stock = stock - $1
         WHERE id = $2 AND stock >= $1 RETURNING price_paisa`, [qty, productId]);
      if (r.rowCount === 0) throw new OutOfStockError(productId);   // → ROLLBACK
      const price = r.rows[0].price_paisa;
      await client.query(
        "INSERT INTO order_items (order_id, product_id, qty, unit_paisa) VALUES ($1, $2, $3, $4)",
        [order.id, productId, qty, price]);
      total += price * qty;
    }
    await client.query("UPDATE orders SET total_paisa = $1 WHERE id = $2", [total, order.id]);
    await client.query("COMMIT");
    return { id: order.id, totalPaisa: total };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;                                         // route-এর error handler 409/500 ঠিক করবে
  } finally {
    client.release();                                  // সংযোগ pool-এ ফেরত — সবসময়
  }
}
// প্রত্যাশিত আউটপুট: সফল হলে { id: '1', totalPaisa: 98000 }; স্টক কম হলে OutOfStockError, ডেটাবেস অপরিবর্তিত

খেয়াল করুন দাম নেওয়া হচ্ছে ডেটাবেস থেকে (RETURNING price_paisa), ক্রেতার পাঠানো body থেকে নয় — ব্রাউজারের পাঠানো দাম বিশ্বাস করা পর্ব ৩৯-এর parameter tampering-এর দরজা খুলে দেয়। এবার একই ধারণা মেমোরিতে চালিয়ে দেখুন: নিচে একটি ছোট্ট “ডেটাবেস”, যার begin একটি কপি রাখে, rollback সেই কপি ফিরিয়ে আনে:

const db = {                                   // মেমোরিতে তিনটি "টেবিল"; দাম পয়সায়
  products: new Map([[1, { title: "চাল ৫ কেজি", price: 42000, stock: 3 }],
                     [2, { title: "ডাল ১ কেজি", price: 14000, stock: 1 }]]), orders: [], orderItems: [] };
let backup = null;                             // আসল ডেটাবেস undo log বা MVCC দিয়ে এই কাজ করে
const begin = () => { backup = structuredClone(db); };
const commit = () => { backup = null; };
const rollback = () => { Object.assign(db, backup); backup = null; };

function placeOrder(customerId, cart) {
  begin();
  try {
    const order = { id: db.orders.length + 1, customerId, totalPaisa: 0 }; db.orders.push(order);
    for (const { productId, qty } of cart) {
      const p = db.products.get(productId);
      db.orderItems.push({ orderId: order.id, productId, qty, unitPaisa: p.price });
      if (p.stock < qty) throw new Error(`স্টক কম: ${p.title} (আছে ${p.stock}, চাই ${qty})`);
      p.stock -= qty; order.totalPaisa += p.price * qty;
    }
    commit();
    return `COMMIT — অর্ডার #${order.id}, মোট ${order.totalPaisa / 100} টাকা`;
  } catch (e) { rollback(); return `ROLLBACK — ${e.message}`; }
}
const state = () => `স্টক=[${[...db.products.values()].map((p) => p.stock)}] orders=${db.orders.length} items=${db.orderItems.length}`;
console.log(placeOrder(7, [{ productId: 1, qty: 2 }, { productId: 2, qty: 1 }]), "|", state());
console.log(placeOrder(8, [{ productId: 1, qty: 1 }, { productId: 2, qty: 1 }]), "|", state());

Isolation level — এক অনুচ্ছেদে

একসাথে চলা transaction কতটা আলাদা থাকবে, তা ঠিক করে isolation level. SQL মানদণ্ডে চারটি: Read Uncommitted, Read Committed (PostgreSQL-এর ডিফল্ট — প্রতিটি query শুধু commit হওয়া ডেটা দেখে), Repeatable Read (MySQL-এর InnoDB-র ডিফল্ট — পুরো transaction একই snapshot দেখে), আর Serializable (যেন transaction-গুলো একটির পর একটি চলেছে; ডেটাবেস কখনো একটিকে serialization error দিয়ে বাতিল করে, তখন আবার চেষ্টা)। যত কড়া, তত নিরাপদ, কিন্তু তত বেশি অপেক্ষা বা retry. বেশিরভাগ অ্যাপে Read Committed আর ওপরের মতো শর্তসহ UPDATE … WHERE stock >= $1 (বা দরকারে SELECT … FOR UPDATE দিয়ে সারি তালাবদ্ধ করা) যথেষ্ট। পর্ব ৩৬-এর race condition এখানে ডেটাবেসের স্তরে: “আগে পড়ি, তারপর Node-এ যাচাই করে লিখি” — এই দুই ধাপের ফাঁকে অন্য অনুরোধ ঢুকে পড়তে পারে।

৭ · গতি — index, EXPLAIN আর N+1 সমস্যা

Index — বইয়ের পেছনের সূচি

Index ছাড়া WHERE phone = '01711000000' খুঁজতে ডেটাবেসকে প্রতিটি সারি দেখতে হয় (sequential scan) — দশ লাখ সারিতে দশ লাখবার তুলনা। Index হলো আলাদা একটি সাজানো কাঠামো, সাধারণত B-tree: একটি চওড়া, কম উচ্চতার গাছ, যার প্রতিটি নোডে অনেকগুলো key সাজানো থাকে। দশ লাখ সারিতেও গাছের উচ্চতা মাত্র তিন-চার স্তর, তাই কয়েকটি disk page পড়েই ঠিক সারিতে পৌঁছানো যায় — সাজানো তালিকায় binary search-এর ভাবনা (প্রতি ধাপে বাকি অংশের বেশিরভাগ বাদ), disk-এর জন্য সাজানো। পর্ব ৪০-এ IndexedDB-র index একই কাজ করেছিল।

CREATE INDEX products_category_idx ON products (category);
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
-- প্রত্যাশিত আউটপুট (সংক্ষেপে; সংখ্যা ডেটাভেদে বদলাবে):
-- Limit  (actual time=0.03..0.05 rows=10)
--   ->  Index Scan using orders_customer_created_idx on orders  (rows=10)
--         Index Cond: (customer_id = 42)
-- index না থাকলে এখানে দেখা যেত: Seq Scan on orders … Filter: (customer_id = 42)

কখন index দেবেন? যে কলাম প্রায়ই WHERE, JOIN বা ORDER BY-এ আসে। Primary key আর UNIQUE কলামে PostgreSQL নিজেই index বানায়, কিন্তু foreign key কলামে নিজে থেকে বানায় না — orders.customer_id-এর index আপনাকে দিতে হবে (MySQL-এর InnoDB foreign key-তে index বাধ্যতামূলক করে)। কিন্তু index বিনামূল্যে নয়: প্রতিটি INSERT/UPDATE-এ সব index-ও আপডেট হয়, আর জায়গা লাগে। তাই অনুমানে নয়, প্রমাণে: ধীর query-তে EXPLAIN চালান (EXPLAIN ANALYZE query আসলেই চালিয়ে সময় দেখায়), “Seq Scan” কোথায় তা দেখুন, তারপর index দিন। কয়েকটি কলামের যৌথ index-এ কলামের ক্রম জরুরি: (customer_id, created_at) “এই ক্রেতার সাম্প্রতিক অর্ডার”-এ কাজে লাগে, শুধু created_at দিয়ে খোঁজায় তেমন নয়।

N+1 — লুপের ভেতরে লুকানো query

অ্যাডমিন পাতায় “সাম্প্রতিক ২০টি অর্ডার, ক্রেতার নামসহ” দেখাতে হবে। প্রথম যে কোডটি মাথায় আসে:

// ❌ N+1: ১টি query অর্ডারের জন্য, তারপর প্রতিটি অর্ডারের জন্য আরেকটি
const { rows: orders } = await pool.query(
  "SELECT id, customer_id, total_paisa FROM orders ORDER BY id DESC LIMIT 20");
for (const o of orders) {
  const { rows: [c] } = await pool.query("SELECT name FROM customers WHERE id = $1", [o.customer_id]);
  o.customerName = c.name;                    // ২০টি অর্ডার → মোট ২১টি round-trip
}

// ✅ সমাধান ১ — JOIN: ১টি query
const { rows } = await pool.query(
  `SELECT o.id, o.total_paisa, c.name AS customer_name
   FROM orders o JOIN customers c ON c.id = o.customer_id
   ORDER BY o.id DESC LIMIT 20`);

// ✅ সমাধান ২ — IN/ANY: ২টি query, যত অর্ডারই হোক
const ids = [...new Set(orders.map((o) => o.customer_id))];
const { rows: customers } = await pool.query(
  "SELECT id, name FROM customers WHERE id = ANY($1)", [ids]);   // pg array → $1
const byId = new Map(customers.map((c) => [c.id, c.name]));

N+1 ধরার উপায়: ডেভেলপমেন্টে প্রতিটি query log করুন (ORM-এ সাধারণত একটি setting), আর একটি পাতা খুললে কয়টি query হলো তা গুনুন। সংখ্যাটি ডেটার পরিমাণের সাথে বাড়লে — N+1. বারবার পড়া হয় কিন্তু কম বদলায় এমন ডেটার (যেমন ক্যাটাগরির তালিকা) জন্য এর ওপরে Redis-এর মতো cache বসানো যায়: আগে cache দেখো, না পেলে ডেটাবেস থেকে এনে কিছুক্ষণের TTL-সহ রাখো (cache-aside). তবে প্রথমে index আর ঠিক query, তারপর cache.

৮ · Migration, ORM আর MongoDB

Migration — schema-র version control

অ্যাপ বড় হয়, schema বদলায়: customers-এ email কলাম লাগবে, products-এ নতুন index. প্রোডাকশন ডেটাবেসে হাতে ALTER TABLE চালানো মানে কেউ জানে না কোন সার্ভারে কোন বদল হয়েছে। সমাধান migration: প্রতিটি বদল একটি সংখ্যা-দেওয়া ফাইল (0007_add_customer_email.sql), git-এ থাকে, আর একটি টুল ডেটাবেসের ভেতরে একটি টেবিলে লিখে রাখে কোনগুলো চলেছে — নতুনগুলো ক্রমানুসারে চালায়। পর্ব ৪০-এ IndexedDB-র version আর upgradeneeded-এ একই ভাবনা দেখেছেন। Node-এ প্রচলিত টুল: node-pg-migrate (SQL বা JavaScript-এ migration), Prisma Migrate (schema ফাইল থেকে SQL বানায়), Drizzle Kit. পেশাদার অভ্যাস: চলমান অ্যাপে বদল দুই ধাপে — আগে নতুন কলাম যোগ, কোড দুটোই সামলাক, তারপর পুরোনো কলাম সরান; হঠাৎ কলামের নাম বদল চলমান সার্ভার ভেঙে দেয়।

ORM আর query builder — সুবিধা আর তার দাম

ORM (Object-Relational Mapper) টেবিলকে class বা model হিসেবে দেখায়, আর query লেখে আপনার হয়ে; query builder SQL-এর কাছাকাছি থাকে, শুধু string-এর বদলে function-এর chain দিয়ে query বানায়। দুটোই ডিফল্টে parameterized query পাঠায়।

// schema.prisma (অংশ):
//   model Order { id Int @id @default(autoincrement())  customer Customer @relation(fields: [customerId], references: [id])
//                 customerId Int  totalPaisa Int  createdAt DateTime @default(now()) }
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

// N+1 ছাড়াই অর্ডার + ক্রেতা (include → ORM নিজে batch query করে)
const orders = await prisma.order.findMany({
  take: 20, orderBy: { id: "desc" }, include: { customer: { select: { name: true } } },
});

// transaction: হয় দুটোই, নয়তো কোনোটিই না
await prisma.$transaction(async (tx) => {
  const { count } = await tx.product.updateMany({
    where: { id: 2, stock: { gte: 1 } }, data: { stock: { decrement: 1 } } });
  if (count === 0) throw new Error("স্টক নেই");       // throw → Prisma ROLLBACK করে
  const p = await tx.product.findUniqueOrThrow({ where: { id: 2 } });
  await tx.order.create({ data: { customerId: 7, totalPaisa: p.pricePaisa } });
});
// প্রত্যাশিত আউটপুট: orders[0] → { id: 20, customerId: 7, totalPaisa: 98000, …, customer: { name: 'রহিমা' } }

ORM-এর দাম কোথায়? ORM SQL লুকায়, কিন্তু ডেটাবেস তো SQL-ই চালায় — তাই সমস্যা হলে আপনাকে নিচের স্তরে নামতে হয়। একে বলে leaky abstraction. orders.forEach(o => o.customer)-এর মতো নিরীহ লাইন lazy loading-এ N+1 হয়ে যেতে পারে; জটিল রিপোর্ট ORM-এ লিখতে গেলে কোড SQL-এর চেয়ে কঠিন হয়। তাই ভারসাম্য: সাধারণ CRUD-এ ORM-এর উৎপাদনশীলতা আর টাইপ-নিরাপত্তা, জটিল রিপোর্টে parameterized raw SQL (prisma.$queryRaw`… ${x}`-এর মতো tagged template নিরাপদ, কারণ এটি মানগুলোকে parameter বানায়; কিন্তু $queryRawUnsafe-এ জোড়া দেওয়া লেখা আবার injection). আর ORM ব্যবহার করলেও development-এ তার তৈরি SQL log করে দেখুন।

MongoDB — document, embedding আর referencing

MongoDB-তে টেবিলের বদলে collection, সারির বদলে document (BSON — JSON-এর মতো, তবে তারিখ, decimal আর binary ধরনসহ)। নকশার মূল প্রশ্ন: সম্পর্কিত ডেটা document-এর ভেতরে রাখবেন (embedding), নাকি আলাদা collection-এ রেখে id দিয়ে নির্দেশ করবেন (referencing)? নিয়মটি “একসাথে যা পড়া হয়, তা একসাথে রাখুন”: অর্ডারের আইটেম আর ডেলিভারি ঠিকানা অর্ডারের ভেতরেই — অর্ডার দেখলে সবই লাগে, আর এক document-এর ভেতরের বদল সবসময় atomic. কিন্তু ক্রেতা আলাদা collection-এ — তিনি অনেক অর্ডারে আছেন, আর তাঁর নাম বদলালে হাজার document বদলাতে চাইবেন না। আর যা সীমাহীন বাড়ে (একটি পণ্যের সব রিভিউ), তা embed করবেন না — একটি document-এর সর্বোচ্চ আকার ১৬ MB.

import { MongoClient } from "mongodb";            // official driver (npm install mongodb)
const client = new MongoClient(process.env.MONGODB_URI);   // client নিজেই একটি pool
const orders = client.db("shop").collection("orders");

await orders.insertOne({
  customerId: 7,                                          // referencing: ক্রেতা আলাদা collection-এ
  items: [{ productId: 1, title: "চাল ৫ কেজি", qty: 2, unitPaisa: 42000 }],   // embedding
  totalPaisa: 84000, createdAt: new Date(),
});
const list = await orders.find({ customerId: 7 }).sort({ createdAt: -1 }).limit(10).toArray();
console.log(list.length, list[0].items[0].title);
// প্রত্যাশিত আউটপুট: 1 চাল ৫ কেজি

// ⚠️ NoSQL-এও injection আছে: find({ phone: req.body.phone }) — body-তে যদি
// { "phone": { "$ne": null } } আসে, শর্তটি "phone ফাঁকা নয়" হয়ে যায়। ধরন যাচাই করুন: String(…)

Mongoose একটি জনপ্রিয় ODM — MongoDB-র ওপরে schema, যাচাই আর model যোগ করে (mongoose.model("Order", orderSchema)). MongoDB 4.0 (২০১৮) থেকে replica set-এ একাধিক document জুড়ে transaction-ও চলে, তবে নকশা এমন রাখাই ভালো যাতে বেশিরভাগ কাজ এক document-এ সারা যায়।

৯ · Express অ্যাপে ডেটাবেস — repository, 409, secret, backup আর টেস্ট

পর্ব ৫০-এর API-তে ডেটাবেস বসানোর পরিচ্ছন্ন পথ হলো একটি repository স্তর: route handler HTTP বোঝে (request, status code), repository ডেটাবেস বোঝে (SQL), আর দুটি আলাদা ফাইলে। তাহলে route-এ SQL ছড়িয়ে থাকে না, টেস্টে repository-কে নকল দিয়ে বদলানো যায়, আর কাল PostgreSQL থেকে অন্য কিছুতে গেলে বদলাতে হয় এক জায়গায়।

// repositories/customers.js — শুধু ডেটাবেসের কাজ
export class DuplicatePhoneError extends Error {}

export async function createCustomer({ name, phone }) {
  try {
    const { rows: [c] } = await pool.query(
      "INSERT INTO customers (name, phone) VALUES ($1, $2) RETURNING id, name, phone", [name, phone]);
    return c;
  } catch (err) {
    if (err.code === "23505") throw new DuplicatePhoneError(phone, { cause: err });  // unique_violation
    throw err;
  }
}

// routes/customers.js — শুধু HTTP-র কাজ
app.post("/api/customers", async (req, res, next) => {
  try {
    res.status(201).json(await createCustomer(req.body));
  } catch (err) {
    if (err instanceof DuplicatePhoneError)
      return res.status(409).json({ error: "এই ফোন নম্বরে আগেই অ্যাকাউন্ট আছে" });
    next(err);                                     // বাকি সব → 500, বিস্তারিত শুধু log-এ
  }
});
// প্রত্যাশিত আউটপুট: প্রথমবার 201 {"id":"1","name":"রহিমা","phone":"01711000000"}
//                   একই নম্বরে আবার 409 {"error":"এই ফোন নম্বরে আগেই অ্যাকাউন্ট আছে"}

লক্ষ করুন, কোডটি আগে “এই নম্বর আছে কি?” খুঁজে তারপর INSERT করে না। সেভাবে করলে দুটি একসাথে আসা অনুরোধ দুজনেই “নেই” দেখে দুজনেই ঢুকিয়ে দিতে পারে — race condition. এখানে সিদ্ধান্ত নেয় ডেটাবেসের UNIQUE constraint, যা দুই অনুরোধ একসাথে এলেও একটিকেই ঢুকতে দেয়; অন্যটি PostgreSQL-এর error code 23505 (unique_violation) পায়, যাকে আমরা 409 Conflict-এ রূপ দিই। আর ডেটাবেসের আসল error message (টেবিল, constraint-এর নাম) ব্যবহারকারীকে পাঠাবেন না — শুধু log-এ; বাইরে যায় সংক্ষিপ্ত বার্তা।

নিরাপত্তা আর রক্ষণাবেক্ষণের চেকলিস্ট

• Secret কোডে নয়: DATABASE_URL environment-এ; .env কখনো git-এ নয় (ভুলে commit হলে পাসওয়ার্ড বদলান — git-এর ইতিহাসে সেটি থেকে যায়)। প্রোডাকশনে ডেটাবেস সংযোগে TLS চালু রাখুন।
• ন্যূনতম অনুমতি (least privilege): অ্যাপ সংযোগ করে shop_app-এর মতো একটি ব্যবহারকারী দিয়ে, যার শুধু দরকারি টেবিলে SELECT/INSERT/UPDATE/DELETE আছে — superuser বা টেবিল-মোছার অনুমতি নয়। Migration চালায় আলাদা ব্যবহারকারী। ডেটাবেস ইন্টারনেটে খোলা নয়, শুধু অ্যাপ সার্ভার থেকে পৌঁছানো যায়।
• Backup — আর restore-এর মহড়া: নিয়মিত স্বয়ংক্রিয় backup (pg_dump বা সেবাদাতার snapshot), অন্য জায়গায় কপি, আর মাঝে মাঝে সত্যিই restore করে দেখা — যে backup কখনো restore হয়নি, সেটি শুধু একটি আশা।
• টেস্টে আসল ডেটাবেস: পর্ব ৪৮-এর ভাবনায় — repository-র টেস্ট চলুক একটি আলাদা টেস্ট ডেটাবেসে (Docker-এ PostgreSQL, বা Testcontainers-এর মতো টুল), প্রতিটি টেস্টের আগে migration আর পরিষ্কার ডেটা, দরকারে প্রতিটি টেস্ট একটি transaction-এ চালিয়ে শেষে rollback. নকল ডেটাবেস (mock) SQL-এর ভুল বা constraint ধরতে পারে না।

১০ · AI-এর যুগে — AI-এর লেখা ডেটাবেস-কোড যাচাই

“Express-এ একটি অর্ডার API বানাও, PostgreSQL দিয়ে” — AI কয়েক সেকেন্ডে চলমান কোড দেবে, আর প্রথম ডেমোতে সব ঠিক দেখাবে। ডেটাবেস-কোডের বাগ দেখা যায় ভিড়ে, অদ্ভুত ইনপুটে, মাঝপথে crash হলে। AI-এর কোডে যে ভুলগুলো বারবার দেখা যায়:

AI-এর লেখা ডেটাবেস-কোড — যাচাইয়ের চেকলিস্ট

• SQL-এর প্রতিটি লেখায় ${ আর + খুঁজেছি; ইনপুট সব $1… parameter-এ, নাম শুধু allowlist থেকে
• অ্যাপে একটিই pool; প্রতিটি pool.connect()-এর পরে finally { client.release() } আছে
• একাধিক লেখার প্রতিটি কাজ একটি transaction-এ, একই client-এ; error-এ ROLLBACK
• টাকা পয়সায় বা numeric-এ; pg-র string হয়ে আসা bigint/numeric/COUNT ঠিকভাবে রূপান্তর করা; সময় timestamptz
• Development-এ query log চালু করে একটি পাতার query গুনেছি — N+1 নেই; ধীর query-তে EXPLAIN দেখেছি
• Secret environment-এ, .env .gitignore-এ; অ্যাপের ডেটাবেস ব্যবহারকারী superuser নয়
• নিজে পরীক্ষা করেছি: উদ্ধৃতি-চিহ্নওয়ালা ইনপুট, একই ফোনে দুবার নিবন্ধন (409), শেষ পণ্যে একসাথে দুটি অর্ডার, আর মাঝপথে ইচ্ছা করে error — ডেটাবেসে অর্ধেক লেখা কিছু নেই