অ্যাপ থেকে ডেটাবেস — driver, prepared statement আর connection pool

১ · তিন স্তরের পথ — browser, অ্যাপ সার্ভার আর ডেটাবেস

এ পর্যন্ত আপনি SQL লিখেছেন সরাসরি ডেটাবেসে — এই পাতার এডিটরে, psql-এ বা DB Browser-এ। কিন্তু দোকানের গ্রাহক Rahim Uddin কখনো SQL লেখেন না। তিনি ফোনে অ্যাপ খুলে “আমার অর্ডার” চাপেন, আর এক সেকেন্ডের মধ্যে তালিকা আসে। মাঝখানে কে SQL লিখল, কোথায় চালাল, ফল কীভাবে ফিরল — এই পর্ব সেই পথের গল্প। মডিউল ৮ শুরু হলো: এখান থেকে ডেটাবেসকে দেখবেন একটি বড় সিস্টেমের অংশ হিসেবে।

প্রায় সব আধুনিক অ্যাপ একটি three-tier নকশা মানে। প্রথম স্তর client: browser বা মোবাইল অ্যাপ, যা শুধু দেখায় আর অনুরোধ পাঠায়। দ্বিতীয় স্তর অ্যাপ সার্ভার: Python, Java, PHP বা Node.js-এ লেখা কোড, যা HTTP অনুরোধ নেয়, ব্যবসার নিয়ম চালায় আর ডেটাবেসকে SQL পাঠায়। তৃতীয় স্তর ডেটাবেস সার্ভার। অ্যাপ সার্ভার আর ডেটাবেসের সম্পর্কটি পর্ব ৩-এর client-server মডেল: অ্যাপটিই ডেটাবেসের client.

কেন browser সরাসরি ডেটাবেসে যায় না? পাঁচটি কারণ:

• চাবি লুকানো যায় না। browser-এর JavaScript যে কেউ DevTools খুলে পড়তে পারেন। ডেটাবেসের ব্যবহারকারী-নাম আর পাসওয়ার্ড সেখানে থাকলে তা সবার হাতে।
• অনুমতি অনেক মোটা দাগের। ডেটাবেসের GRANT বলে “এই ব্যবহারকারী orders টেবিল পড়তে পারে”। কিন্তু দরকার “Rahim শুধু নিজের অর্ডার দেখবেন”। এই নিয়মটি API-র কোডে থাকে: customer_id আসে লগইন session থেকে, URL বা ফর্ম থেকে নয়।
• যাচাই আর ব্যবসার নিয়ম। দাম কত, ছাড় প্রযোজ্য কি না, স্টক আছে কি না — এগুলো ব্যবহারকারীর পাঠানো সংখ্যায় বিশ্বাস করে ঠিক করা যায় না।
• নেটওয়ার্কের নিরাপত্তা। ডেটাবেসের port (PostgreSQL-এ ৫৪৩২, MySQL-এ ৩৩০৬) ইন্টারনেটে খোলা রাখা মানে সারা পৃথিবীর স্ক্যানারকে দরজা দেখানো। তা ছাড়া browser ডেটাবেসের নিজস্ব TCP protocol বলতেই পারে না; সে বোঝে HTTP আর WebSocket.
• সংযোগের সংখ্যা। লাখো browser মানে লাখো সংযোগ — কোনো ডেটাবেস সার্ভার তা সামলাতে পারে না। অ্যাপ সার্ভার কয়েকটি সংযোগ ভাগ করে চালায় (অংশ ৯)।

এই পর্বের লাইভ SQL চলে দোকানের ডেটাবেসে (db-shop): customers (customer_id, name, city, phone, joined_on — ৮ জন), categories (category_id, name — ৪টি শ্রেণি), products (product_id, name, category_id, price, stock — ১০টি পণ্য), orders (order_id, customer_id, order_date, status — ১০টি অর্ডার) আর order_items (order_id, product_id, quantity, unit_price — ১৬টি লাইন)। প্রতিবার চালালে ডেটাবেস নতুন করে তৈরি হয়, foreign key চালু থাকে। এডিটরগুলোতে থাকবে ঠিক সেই SQL, যা অ্যাপ সার্ভার পাঠায়। Rahim “আমার অর্ডার” চাপলে API এটি চালায় (Rahim-এর customer_id = 1, এসেছে তাঁর session থেকে):

-- GET /api/my-orders  (customer_id আসে session থেকে)
SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC;

২ · Driver আর protocol — ডেটাবেসের ভাষায় কথা বলা

আপনার Python কোড লেখে cur.execute("SELECT …"). PostgreSQL সার্ভার কিন্তু Python বোঝে না; সে বোঝে নেটওয়ার্কে আসা নির্দিষ্ট গঠনের বাইট। মাঝের অনুবাদকের নাম database driver: একটি লাইব্রেরি, যা ফাংশন কলকে ডেটাবেসের wire protocol-এর বার্তায় বদলায়, আর ফিরে আসা বাইটকে ভাষার int, str, None-এ ফেরায়।

• PostgreSQL: frontend/backend protocol-এর ৩ নম্বর সংস্করণ, PostgreSQL ৭.৪ (২০০৩) থেকে চলছে। প্রতিটি বার্তার শুরুতে এক অক্ষরের ধরন আর দৈর্ঘ্য: client পাঠায় Query, Parse, Bind, Execute; সার্ভার ফেরায় RowDescription, প্রতি সারিতে একটি DataRow, শেষে CommandComplete আর ReadyForQuery. অফিসিয়াল C লাইব্রেরি libpq; Python-এর psycopg এর ওপর চলে, অথচ Java-র pgjdbc আর Node.js-এর pg একই protocol নিজেরাই লিখেছে।
• MySQL: নিজস্ব client/server protocol, যার দুটি পথ — লেখা SQL পাঠানোর text protocol (COM_QUERY) আর prepared statement-এর binary protocol (COM_STMT_PREPARE, COM_STMT_EXECUTE). MariaDB-ও মূলত একই protocol বলে, তাই একই driver দুটিতে চলে।
• SQLite: কোনো protocol নেই, কারণ কোনো সার্ভার নেই। এটি একটি embedded database: লাইব্রেরিটি আপনার প্রক্রিয়ার ভেতরেই চলে, ডেটাবেস একটি ফাইল। Python-এর sqlite3 module-এ “কোয়েরি পাঠানো” আসলে একটি C ফাংশন ডাকা — মাইক্রোসেকেন্ডের কাজ। এ কারণেই SQLite-এ N+1 (অংশ ৭) PostgreSQL-এর তুলনায় অনেক কম ক্ষতি করে।

প্রতিটি ডেটাবেসের জন্য আলাদা API শিখতে না হয়, তাই এসেছে অভিন্ন interface. ১৯৯২ সালে Microsoft আনে ODBC; ১৯৯৭ সালে JDK ১.১-এর সাথে আসে JDBC. দুটির ধারণা এক: অ্যাপ একটি নির্দিষ্ট API ডাকে (JDBC-তে Connection, PreparedStatement, ResultSet), আর নিচের driver বদলালে অন্য ডেটাবেস। Python-এ একই কাজ করে DB-API (PEP 249): connect(), cursor(), execute(), fetchone(), commit(). একটি সতর্কতা: interface অভিন্ন হলেও SQL-এর উপভাষা আলাদাই থাকে (পর্ব ৩১)। driver বদলালেই PostgreSQL-এর কোড MySQL-এ চলবে না।

৩ · একটি সংযোগের দাম — TCP, TLS আর পরিচয় যাচাই

“ডেটাবেসে connect করা” এক লাইনের কোড, কিন্তু নেটওয়ার্কে কয়েক দফা কথাবার্তা। প্রতিটি দফায় বার্তা যায় আর উত্তর আসে — একটি round trip. অ্যাপ সার্ভার আর ডেটাবেস একই ডেটা সেন্টারে থাকলে একটি round trip সাধারণত এক মিলিসেকেন্ডের কম। দূরের অঞ্চলে থাকলে কয়েক দশক মিলিসেকেন্ড। নিচের হিসাবে ধরা হলো একটি round trip = ৩০ ms, আর PostgreSQL ১৪+ এর ডিফল্ট পাসওয়ার্ড-যাচাই।

সংযোগের সব তথ্য সাধারণত এক লাইনে লেখা হয় — connection string বা URL. এতে থাকে host, port, ডেটাবেস, ব্যবহারকারী, পাসওয়ার্ড আর বিকল্প। সবচেয়ে বড় নিয়ম: এই লাইন কোডে লিখবেন না। Git-এ একবার ঢুকলে history থেকে মোছা কঠিন, আর পাবলিক repository-তে ফাঁস হওয়া key স্বয়ংক্রিয় বট খুব অল্প সময়েই খুঁজে নেয়। এর বদলে environment variable ব্যবহার করুন:

# PostgreSQL — libpq, psycopg, node-postgres সবাই এই রূপ বোঝে
DATABASE_URL=postgresql://shop_app:CHANGE_ME@db.internal:5432/shop?sslmode=verify-full&application_name=shop-web
# MySQL (mysql2, অনেক ORM)
# DATABASE_URL=mysql://shop_app:CHANGE_ME@db.internal:3306/shop
# JDBC-র রূপ আলাদা; পাসওয়ার্ড সাধারণত আলাদা property-তে
# jdbc:postgresql://db.internal:5432/shop?sslmode=verify-full

কোডে শুধু নামটি থাকে: Python-এ os.environ["DATABASE_URL"], Node.js-এ process.env.DATABASE_URL, PHP-তে getenv('DATABASE_URL'). বড় সিস্টেমে পাসওয়ার্ড রাখা হয় secrets manager-এ — HashiCorp Vault, AWS Secrets Manager, Azure Key Vault বা Google Secret Manager. এরা কে কখন পড়ল তা লেখে, আর পাসওয়ার্ড নিয়মিত বদলাতে পারে। আরেকটি অভ্যাস: অ্যাপের ব্যবহারকারী (shop_app) কখনো superuser নয়; শুধু দরকারি টেবিলে দরকারি অনুমতি — least privilege. বিস্তারিত পর্ব ৫১-এ।

৪ · কোড থেকে কোয়েরি — cursor, fetch আর টাইপের অনুবাদ

সংযোগ খোলার পর কোয়েরি চালানোর ধাপগুলো প্রায় সব ভাষায় এক: একটি cursor (বা statement) তৈরি, execute, তারপর ফল পড়া। ফলের সারিগুলোকে বলে result set; cursor তার ওপর একটি আঙুল, যা জানে পরের সারি কোনটি। Python-এর DB-API-তে পড়ার তিনটি উপায়:

cur = con.execute("SELECT order_id, status FROM orders WHERE customer_id = ?", (1,))
first = cur.fetchone()          # একটি সারি, বা শেষ হলে None
rest  = cur.fetchall()          # বাকি সব সারি একটি list-এ
for order_id, status in con.execute("SELECT order_id, status FROM orders"):
    print(order_id, status)     # cursor নিজেই iterable — একটি একটি করে
while batch := cur.fetchmany(500):   # বড় ফল: ৫০০টি করে
    handle(batch)

fetchall() সহজ, কিন্তু দশ লাখ সারি হলে সব একসাথে অ্যাপের মেমরিতে। fetchmany() টুকরো টুকরো পড়ে। তবে একটি ফাঁদ আছে: PostgreSQL আর MySQL-এর বেশিরভাগ driver ডিফল্টে পুরো ফল আগেই নেটওয়ার্ক থেকে টেনে client-এর মেমরিতে রাখে; fetchmany শুধু সেখান থেকে ভাগ করে দেয়। সত্যিকারের অল্প-অল্প আনার জন্য লাগে server-side cursor: psycopg-এ নাম দেওয়া cursor (conn.cursor(name="big")), JDBC-তে setFetchSize(1000) — pgjdbc-তে এটি কাজ করে শুধু autocommit বন্ধ থাকলে।

সারি আসে tuple হিসেবে: (1009, '2025-10-08', 'pending'). কলামের নামে পড়তে চাইলে driver-কে বলুন সারি dict বা object হিসেবে দিতে — sqlite3-এ con.row_factory = sqlite3.Row, psycopg-এ row_factory=dict_row, PDO-তে PDO::FETCH_ASSOC. টেবিলের সারি থেকে ভাষার object বানানোর এই কাজটি বড় হয়ে গেলে তার নাম ORM — পরের পর্বের বিষয়।

টাইপের অনুবাদ — NULL, সময় আর টাকা

SQL-এর টাইপ আর ভাষার টাইপ এক নয়। তিনটি জায়গায় সবচেয়ে বেশি ভুল হয়।

সময়: একটি মুহূর্ত (অর্ডারের সময়, লগইনের সময়) রাখুন UTC-তে, আর দেখানোর সময় ব্যবহারকারীর সময় অঞ্চলে বদলান — বাংলাদেশে UTC+6. PostgreSQL-এ টাইপটি timestamptz; এটি ভেতরে UTC মুহূর্ত রাখে আর session-এর TimeZone অনুযায়ী দেখায়। MySQL-এর TIMESTAMP UTC-তে রাখে, DATETIME কোনো অঞ্চল জানে না। কোডে “naive” সময় (অঞ্চলহীন) এড়িয়ে চলুন — Python-এ datetime.now(timezone.utc), Java-য় Instant বা OffsetDateTime.

টাকা: floating-point সংখ্যা ০.১-কে নিখুঁতভাবে রাখতে পারে না। টাকা রাখুন DECIMAL/NUMERIC-এ, অথবা সবচেয়ে ছোট এককে পূর্ণসংখ্যা হিসেবে — ১২০০ টাকা = ১,২০,০০০ পয়সা। db-shop-এর price কলামটি REAL, শেখানোর সুবিধার জন্য; আসল দোকানে এটি হতো DECIMAL বা পয়সার INTEGER. নিচের এডিটরে NULL আর টাকার দুটি দিক দেখুন:

-- অ্যাপে এরা None / null হয়ে যাবে
SELECT customer_id, name, phone, city FROM customers
WHERE phone IS NULL OR city IS NULL;
-- float বনাম পয়সার পূর্ণসংখ্যা
SELECT 0.1 + 0.2 = 0.3 AS float_ok, 10 + 20 = 30 AS paisa_ok;
SELECT SUM(quantity * unit_price) AS total_taka
FROM order_items WHERE order_id = 1009;

৫ · Prepared statement আর parameter — কাঠামো একবার, মান আলাদা

সার্চ বক্সে গ্রাহক নাম লিখলেন, আর কোড SQL বানাল string জোড়া দিয়ে: "… WHERE name = '" + name + "'". যতক্ষণ নাম “Mitu Rani”, সব ঠিক। এবার একজন গ্রাহকের নাম Mitu D'Souza. নামের ভেতরের apostrophe SQL-এর উদ্ধৃতিচিহ্ন বন্ধ করে দেয়। জোড়া লাগানোর পর যে SQL তৈরি হলো, সেটিই চালিয়ে দেখুন:

-- string জোড়া দিয়ে বানানো SQL (ভুল পদ্ধতি)
SELECT customer_id, name FROM customers WHERE name = 'Mitu D'Souza';

সঠিক পথ হলো placeholder: SQL-এ মানের জায়গায় একটি চিহ্ন রাখুন, আর মানটি আলাদাভাবে দিন — parameter binding. Python-এ con.execute("SELECT … WHERE name = ?", (name,)). ডেটাবেস প্রথমে কাঠামো বোঝে, পরে মান পায় — মানটি যা-ই হোক, সেটি শুধুই ডেটা। এই কাঠামো-আগে-মান-পরে ব্যবস্থার নাম prepared statement. PostgreSQL-এর extended query protocol-এ এটি দেখতে এমন (driver আপনার হয়ে বার্তাগুলো পাঠায়):

দুটি লাভ তাই আলাদা করে মনে রাখুন। নিরাপত্তা: মান কখনো কোড হয় না — এটি প্রতিবার, প্রতিটি মানে সত্য। গতি: parse, আর কখনো plan-ও, একবারই — এটি নির্ভর করে driver আর ডেটাবেসের ওপর। গতির দিকে সূক্ষ্মতাও আছে: generic plan সব মানের জন্য ভালো নাও হতে পারে (পর্ব ২৮-এর plan cache আর parameter sniffing). placeholder-এর চিহ্ন driver-ভেদে আলাদা:

দুটি driver-এর বিশেষ আচরণ জেনে রাখুন। PHP-র pdo_mysql ডিফল্টে prepared statement “অনুকরণ” করে: PDO::ATTR_EMULATE_PREPARES চালু থাকলে driver নিজেই মান escape করে SQL-এ বসায়, সার্ভারে আসল prepare হয় না। সঠিক charset দেওয়া থাকলে এটি নিরাপদ বলে ধরা হয়, তবু অনেকে এটি বন্ধ রাখেন। Node.js-এর mysql2-এ query() client-এ escape করে, আর execute() সার্ভারে আসল prepared statement বানায়।

৬ · কোড থেকে transaction — commit, rollback, timeout আর retry

পর্ব ৪৪-এ আপনি BEGIN, COMMIT আর ROLLBACK লিখেছেন হাতে। কোড থেকে একই কাজ করতে প্রথম প্রশ্ন: driver-এর ডিফল্ট কী? উত্তর driver-ভেদে আলাদা, আর এখান থেকেই অনেক বাস্তব বাগ আসে।

• JDBC, PDO, Node.js pg, Go database/sql: ডিফল্টে autocommit — প্রতিটি স্টেটমেন্ট সাথে সাথে পাকা। একসাথে কয়েকটি লেখা চাইলে নিজে transaction শুরু করতে হয়: setAutoCommit(false), beginTransaction(), BEGIN বা db.BeginTx().
• psycopg (Python, PostgreSQL): উল্টো — প্রথম স্টেটমেন্টের সাথেই নিজে থেকে transaction শুরু হয়। commit() না ডাকলে সংযোগ বন্ধের সময় সব লেখা হারায়, আর মাঝের সময়টায় সংযোগটি “idle in transaction” অবস্থায় lock ধরে বসে থাকে (পর্ব ৪৪)।
• Python sqlite3 (ডিফল্ট মোড): SELECT-এ নয়, কিন্তু প্রথম INSERT/UPDATE/DELETE-এর আগে নিজে BEGIN পাঠায়; commit() আপনাকে ডাকতে হয়।

নিয়মটি সব ভাষায় এক: শুরু → কাজ → সব ঠিক হলে COMMIT → যেকোনো exception-এ ROLLBACK → শেষে সংযোগ ফেরত। এই কাঠামো হাতে try/except/finally দিয়ে লেখা যায়, কিন্তু ভোলার সুযোগ থাকে। তাই প্রায় সব ভাষায় একটি “নিশ্চিত-বন্ধ” ব্যবস্থা আছে — Python-এ context manager (with con:), Java-য় try-with-resources, Node.js-এ finally. আগে SQL-এ দেখুন অ্যাপটি ঠিক কী পাঠায়। Sabbir একটি Headphone আর একটি Power Bank অর্ডার করলেন; Power Bank-এর স্টক ০:

BEGIN;
INSERT INTO orders VALUES (1011, 3, '2025-10-20', 'pending');
INSERT INTO order_items VALUES (1011, 102, 1, 1200);
UPDATE products SET stock = stock - 1 WHERE product_id = 102 AND stock >= 1;
INSERT INTO order_items VALUES (1011, 103, 1, 1800);
UPDATE products SET stock = stock - 1 WHERE product_id = 103 AND stock >= 1;
-- অ্যাপ দেখল: শেষ UPDATE-এ ০টি সারি → ROLLBACK
ROLLBACK;
SELECT COUNT(*) AS orders FROM orders;
SELECT product_id, stock FROM products WHERE product_id IN (102, 103);

একই “অর্ডার দেওয়া” কাজ চারটি ভাষায়। Python-এর সংস্করণ এখানেই চালানো যায় (sqlite3, মেমরিতে ছোট একটি দোকান, দাম পয়সায়)। বাকি তিনটি একই কাজ PostgreSQL-এ — order_id সেখানে GENERATED ALWAYS AS IDENTITY, তাই RETURNING দিয়ে নতুন id ফেরত আসে।

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT, price_paisa INTEGER, stock INTEGER);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, total_paisa INTEGER);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER, unit_paisa INTEGER);
INSERT INTO products VALUES (102, 'Headphone', 120000, 40), (103, 'Power Bank', 180000, 0);""")
def place_order(con, customer_id, items):      # items = {product_id: quantity}
    with con:                                  # COMMIT at the end, ROLLBACK on any exception
        order_id = con.execute("INSERT INTO orders (customer_id, total_paisa) VALUES (?, 0)", (customer_id,)).lastrowid
        total = 0
        for pid, qty in items.items():
            cur = con.execute("UPDATE products SET stock = stock - ? WHERE product_id = ? AND stock >= ?", (qty, pid, qty))
            if cur.rowcount == 0: raise LookupError(f"out of stock: {pid}")
            (price,) = con.execute("SELECT price_paisa FROM products WHERE product_id = ?", (pid,)).fetchone()
            con.execute("INSERT INTO order_items VALUES (?, ?, ?, ?)", (order_id, pid, qty, price))
            total += price * qty
        con.execute("UPDATE orders SET total_paisa = ? WHERE order_id = ?", (total, order_id))
    return order_id, total
print("order 1:", place_order(con, 3, {102: 2}))
try: place_order(con, 5, {102: 1, 103: 1})
except LookupError as e: print("order 2 rolled back:", e)
print("orders, paisa:", con.execute("SELECT COUNT(*), SUM(total_paisa) FROM orders").fetchone(),
      "| Headphone stock:", con.execute("SELECT stock FROM products WHERE product_id = 102").fetchone()[0])

long placeOrder(DataSource ds, int customerId, Map<Integer, Integer> items) throws SQLException {
    try (Connection con = ds.getConnection()) {          // pool থেকে ধার, শেষে close() = ফেরত
        con.setAutoCommit(false);                        // transaction শুরু
        try (PreparedStatement order = con.prepareStatement(
                 "INSERT INTO orders (customer_id, order_date, status) VALUES (?, CURRENT_DATE, 'pending') RETURNING order_id");
             PreparedStatement upd = con.prepareStatement(
                 "UPDATE products SET stock = stock - ? WHERE product_id = ? AND stock >= ?");
             PreparedStatement item = con.prepareStatement(
                 "INSERT INTO order_items (order_id, product_id, quantity, unit_price) " +
                 "VALUES (?, ?, ?, (SELECT price FROM products WHERE product_id = ?))")) {
            order.setInt(1, customerId);
            long orderId;
            try (ResultSet rs = order.executeQuery()) { rs.next(); orderId = rs.getLong(1); }
            for (Map.Entry<Integer, Integer> e : items.entrySet()) {
                int pid = e.getKey(), qty = e.getValue();
                upd.setInt(1, qty); upd.setInt(2, pid); upd.setInt(3, qty);
                if (upd.executeUpdate() == 0) throw new SQLException("out of stock: " + pid);
                item.setLong(1, orderId); item.setInt(2, pid); item.setInt(3, qty); item.setInt(4, pid);
                item.executeUpdate();
            }
            con.commit();
            return orderId;
        } catch (SQLException ex) {
            con.rollback();                              // যেকোনো ত্রুটিতে পুরোটা বাতিল
            throw ex;
        }
    }
}

<?php
$pdo = new PDO(getenv('DB_DSN'), getenv('DB_USER'), getenv('DB_PASSWORD'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,   // PHP 8-এ এটিই ডিফল্ট
    PDO::ATTR_EMULATE_PREPARES => false,           // সার্ভারে আসল prepare
]);
function placeOrder(PDO $pdo, int $customerId, array $items): int {   // $items = [productId => qty]
    $pdo->beginTransaction();
    try {
        $st = $pdo->prepare("INSERT INTO orders (customer_id, order_date, status)
                             VALUES (?, CURRENT_DATE, 'pending') RETURNING order_id");
        $st->execute([$customerId]);
        $orderId = (int) $st->fetchColumn();
        $upd  = $pdo->prepare('UPDATE products SET stock = stock - ? WHERE product_id = ? AND stock >= ?');
        $item = $pdo->prepare('INSERT INTO order_items (order_id, product_id, quantity, unit_price)
                               VALUES (?, ?, ?, (SELECT price FROM products WHERE product_id = ?))');
        foreach ($items as $pid => $qty) {
            $upd->execute([$qty, $pid, $qty]);
            if ($upd->rowCount() === 0) throw new RuntimeException("out of stock: $pid");
            $item->execute([$orderId, $pid, $qty, $pid]);
        }
        $pdo->commit();
        return $orderId;
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

async function placeOrder(customerId, items) {        // items = [[productId, qty], ...]
  const client = await pool.connect();                // একটি নির্দিষ্ট সংযোগ ধার
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      "INSERT INTO orders (customer_id, order_date, status) VALUES ($1, CURRENT_DATE, 'pending') RETURNING order_id",
      [customerId]);
    const orderId = rows[0].order_id;
    for (const [pid, qty] of items) {
      const upd = await client.query(
        'UPDATE products SET stock = stock - $1 WHERE product_id = $2 AND stock >= $1', [qty, pid]);
      if (upd.rowCount === 0) throw new Error(`out of stock: ${pid}`);
      await client.query(
        'INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES ($1, $2, $3, (SELECT price FROM products WHERE product_id = $2))',
        [orderId, pid, qty]);
    }
    await client.query('COMMIT');
    return orderId;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();                                 // pool-এ ফেরত — না দিলে leak
  }
}

Timeout আর retry

কোড থেকে ডেটাবেস ডাকলে দুটি প্রশ্ন সবসময় ঠিক করে রাখুন: কতক্ষণ অপেক্ষা করব, আর ব্যর্থ হলে কী করব। অপেক্ষার সীমা কয়েক স্তরে: সংযোগ তৈরির timeout (libpq-তে connect_timeout), কোয়েরির statement timeout (PostgreSQL-এ statement_timeout, MySQL-এ SELECT-এর জন্য max_execution_time, JDBC-তে setQueryTimeout), lock-এর জন্য অপেক্ষার সীমা (lock_timeout, InnoDB-তে innodb_lock_wait_timeout), আর pool থেকে সংযোগ পাওয়ার অপেক্ষা। timeout না থাকলে একটি ধীর কোয়েরি অনুরোধটিকে আটকে রাখে, অনুরোধ সংযোগটিকে, আর কয়েক মিনিটে পুরো pool আটকে যায়।

ব্যর্থতার দুই জাত। স্থায়ী ত্রুটি — UNIQUE ভাঙা, CHECK ভাঙা, ভুল SQL — আবার চালালেও একই ফল, তাই retry অর্থহীন। সাময়িক ত্রুটি — deadlock (PostgreSQL-এ SQLSTATE 40P01, MySQL-এ ১২১৩), serialization failure (40001), ক্ষণিকের নেটওয়ার্ক বিচ্ছেদ — একটু পরে আবার করলে সাধারণত সফল। পর্ব ৪৭-এর retry loop-এর অ্যাপ-পাশের রূপ:

for attempt in 0 .. 4:
    try:
        BEGIN; …পুরো transaction…; COMMIT
        return ফল
    except error e:
        ROLLBACK
        if e.sqlstate not in {40001, 40P01} or attempt == 4:
            raise e                                  -- স্থায়ী ত্রুটি, বা চেষ্টা শেষ
        wait random(0, min(2 s, 50 ms × 2^attempt))  -- exponential backoff + full jitter

তিনটি নিয়ম। এক, retry করুন পুরো transaction, শুধু ব্যর্থ স্টেটমেন্ট নয় — ROLLBACK-এর পর আগের পড়া মানগুলো আর সত্য নাও হতে পারে। দুই, অপেক্ষা বাড়ান ধাপে ধাপে — exponential backoff — আর তাতে random অংশ (jitter) মেশান, যাতে একসাথে ব্যর্থ হওয়া একশো অনুরোধ একই মিলিসেকেন্ডে আবার আঘাত না করে। তিন, সংখ্যার সীমা রাখুন। আর COMMIT পাঠানোর পর সংযোগ কেটে গেলে আপনি জানেন না COMMIT হয়েছে কি না — সেখানে অন্ধ retry দ্বিগুণ অর্ডার বানাতে পারে; তাই পেমেন্ট আর অর্ডারে পর্ব ৪৪-এর idempotency key.

৭ · N+1 query — লুপের ভেতরে কোয়েরির ফাঁদ

অ্যাডমিন পাতায় দেখাতে হবে: সব delivered অর্ডার, প্রতিটির পাশে গ্রাহকের নাম। স্বাভাবিকভাবে লেখা কোড: প্রথমে অর্ডারগুলো আনা, তারপর লুপে প্রতিটি অর্ডারের গ্রাহককে আনা। পড়তে সহজ, চলেও ঠিক — আর এটিই N+1 query problem: ১টি কোয়েরি N-টি সারি আনে, তারপর প্রতিটির জন্য আরও ১টি। ORM-এ এটি আরও লুকিয়ে ঘটে: order.customer.name লিখলেই পেছনে একটি করে SELECT চলে যায় (পর্ব ৫০)।

তিনটি সংস্করণই নিজে চালিয়ে দেখুন। প্রথমটি — N+1 অ্যাপ যে ৬টি কোয়েরি পাঠায়, হুবহু:

SELECT order_id, customer_id FROM orders WHERE status = 'delivered';
-- লুপ: প্রতিটি অর্ডারের জন্য একটি করে
SELECT name FROM customers WHERE customer_id = 1;
SELECT name FROM customers WHERE customer_id = 2;
SELECT name FROM customers WHERE customer_id = 4;
SELECT name FROM customers WHERE customer_id = 2;
SELECT name FROM customers WHERE customer_id = 7;

-- সমাধান ১: একটি JOIN
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
ORDER BY o.order_id;

-- সমাধান ২: দুটি কোয়েরি, দ্বিতীয়টিতে IN-এর batch
SELECT order_id, customer_id FROM orders WHERE status = 'delivered';
SELECT customer_id, name FROM customers WHERE customer_id IN (1, 2, 4, 7);

এবার মাপ বাড়িয়ে গুনে দেখা যাক। নিচের প্রোগ্রামে ৫০ জন গ্রাহক আর ২০০টি অর্ডার। sqlite3-এর set_trace_callback প্রতিটি পাঠানো SQL একটি list-এ লিখে রাখে — অ্যাপের ভেতরে একটি ছোট “কোয়েরি গণনাকারী”:

import sqlite3
con = sqlite3.connect(":memory:", autocommit=True)
con.execute("CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT)")
con.execute("CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers)")
con.executemany("INSERT INTO customers VALUES (?, ?)", [(c, f"Customer {c}") for c in range(1, 51)])
con.executemany("INSERT INTO orders VALUES (?, ?)", [(o, o % 50 + 1) for o in range(1, 201)])
sent = []
con.set_trace_callback(sent.append)            # records every SQL statement the app sends
ORDERS = "SELECT order_id, customer_id FROM orders ORDER BY order_id"
def n_plus_one():
    return [(o, con.execute("SELECT name FROM customers WHERE customer_id = ?", (c,)).fetchone()[0])
            for o, c in con.execute(ORDERS).fetchall()]
def one_join():
    return con.execute("SELECT o.order_id, c.name FROM orders o JOIN customers c USING (customer_id) ORDER BY o.order_id").fetchall()
def in_batch():
    rows = con.execute(ORDERS).fetchall()
    ids = sorted({c for _, c in rows})
    marks = ", ".join("?" * len(ids))          # only "?" marks go into the SQL text; values are bound
    names = dict(con.execute(f"SELECT customer_id, name FROM customers WHERE customer_id IN ({marks})", ids))
    return [(o, names[c]) for o, c in rows]
results = []
for f in (n_plus_one, one_join, in_batch):
    sent.clear(); results.append(f())
    print(f"{f.__name__:10} queries sent: {len(sent):3}   rows: {len(results[-1])}")
print("same answer:", results[0] == results[1] == results[2]); con.close()

৮ · বড় কাজ কম যাত্রায় — batching, bulk insert আর keyset pagination

N+1-এর লেখার দিকের যমজ ভাই: CSV থেকে ১০,০০০টি পণ্য তুলতে লুপে ১০,০০০টি আলাদা INSERT, প্রতিটি autocommit-এ। এতে ১০,০০০টি round trip, আর ১০,০০০টি COMMIT — প্রতিটি COMMIT-এ log ডিস্কে নিশ্চিত করার খরচ (পর্ব ৪৮-এর fsync). সমাধান batching, তিন স্তরে:

• একটি transaction-এ মোড়ানো: শুধু এটুকুতেই COMMIT ১০,০০০ থেকে ১-এ নামে।
• একটি statement-এ অনেক সারি: executemany() (Python), addBatch()/executeBatch() (JDBC), অথবা নিজে বহু-সারির VALUES (…), (…), (…). driver-ভেদে এর প্রভাব আলাদা: pgjdbc-তে reWriteBatchedInserts=true আর MySQL Connector/J-তে rewriteBatchedStatements=true দিলে driver batch-কে বহু-সারির INSERT-এ বদলে দেয়; psycopg ৩ executemany-তে pipeline ব্যবহার করে round trip কমায়।
• বিশেষ bulk পথ: লাখো সারিতে PostgreSQL-এর COPY আর MySQL-এর LOAD DATA সাধারণ INSERT-এর চেয়ে অনেক দ্রুত (পর্ব ১৪-এর bulk load).

-- তিনটি সারি, একটি statement, একটি round trip
INSERT INTO products (product_id, name, category_id, price, stock) VALUES
  (111, 'Notebook',    2,  80, 200),
  (112, 'Lentils 1kg', 4, 140,  80),
  (113, 'USB Cable',   1, 250,   0);
SELECT COUNT(*) AS products, SUM(stock) AS total_stock FROM products;

পড়ার দিকেও একই কথা। “সব অর্ডার দেখাও” পাতায় সব সারি একবারে পাঠানো যায় না; পাতায় পাতায় ভাগ করতে হয় — pagination. সহজ পথ LIMIT 20 OFFSET 2000, কিন্তু পর্ব ১৫-এ দেখেছেন, OFFSET-এ ডেটাবেসকে আগের ২,০০০টি সারি পড়ে ফেলে দিতে হয় — ১০০তম পাতা প্রথম পাতার চেয়ে অনেক ধীর, আর মাঝে নতুন সারি ঢুকলে একটি সারি দুই পাতায় আসে বা হারিয়ে যায়। অ্যাপের জন্য ভালো পথ keyset pagination: “শেষ যে সারিটি দেখিয়েছি, তার পরের ২০টি দাও”।

-- পাতা ১: নতুন থেকে পুরোনো
SELECT order_id, order_date FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 4;
-- পাতা ২: অ্যাপ মনে রেখেছে শেষ সারি (2025-10-01, 1007)
SELECT order_id, order_date FROM orders
WHERE (order_date, order_id) < ('2025-10-01', 1007)
ORDER BY order_date DESC, order_id DESC
LIMIT 4;

৯ · Connection pool আর পর্যবেক্ষণ

অংশ ৩-এ দেখেছেন, একটি নতুন সংযোগে প্রায় ছয়টি round trip আর সার্ভারে একটি নতুন প্রক্রিয়া। প্রতি সেকেন্ডে কয়েকশো অনুরোধ আসলে এই খরচ অসহনীয়। সমাধান connection pool: অ্যাপ চালু হওয়ার সময় কয়েকটি সংযোগ খুলে রাখা, প্রতিটি অনুরোধ একটি ধার নেয় আর কাজ শেষে ফেরত দেয়। রেলস্টেশনের রিকশা-স্ট্যান্ডের মতো — যাত্রী এলে নতুন রিকশা বানানো হয় না, লাইনে দাঁড়ানো একটিতে ওঠেন; রিকশা ফিরে আবার লাইনে দাঁড়ায়। Java-য় HikariCP, Node.js-এ pg.Pool, Go-র database/sql নিজেই একটি pool, Python-এ psycopg_pool বা SQLAlchemy-র pool.

Pool কত বড় হবে?

স্বজ্ঞা বলে “বড় pool = বেশি গতি”, কিন্তু বাস্তবে উল্টো হতে পারে। ডেটাবেস সার্ভারে CPU core আর ডিস্ক সীমিত; ২০০টি সংযোগ একসাথে কাজ চাইলে তারা lock, CPU আর cache নিয়ে কাড়াকাড়ি করে, আর প্রত্যেকে ধীর হয়। HikariCP-র উইকিতে শুরুর বিন্দু হিসেবে একটি সূত্র দেওয়া আছে: সংযোগ ≈ (core সংখ্যা × ২) + কার্যকর ডিস্কের সংখ্যা — এটি চূড়ান্ত উত্তর নয়, মেপে ঠিক করার শুরু। আর হিসাবটি করুন পুরো সিস্টেমের: ৮টি অ্যাপ সার্ভার × pool ২০ = ১৬০টি সংযোগ, অথচ PostgreSQL-এর ডিফল্ট max_connections ১০০ (MySQL-এর ১৫১)। PostgreSQL প্রতিটি সংযোগে একটি আলাদা প্রক্রিয়া চালায়, তাই সংখ্যা বাড়ানোও সস্তা নয়।

db, err := sql.Open("pgx", os.Getenv("DATABASE_URL")) // সংযোগ নয়, pool তৈরি
if err != nil { log.Fatal(err) }
db.SetMaxOpenConns(10)                  // একসাথে সর্বোচ্চ ১০টি সংযোগ
db.SetMaxIdleConns(10)                  // খালি অবস্থায় রাখা যাবে ১০টি
db.SetConnMaxLifetime(30 * time.Minute) // পুরোনো সংযোগ মাঝে মাঝে বদলায়

ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
defer cancel()                          // ২ সেকেন্ডের বেশি অপেক্ষা নয়
var name string
err = db.QueryRowContext(ctx, "SELECT name FROM customers WHERE customer_id = $1", 7).Scan(&name)

অনেক অ্যাপ সার্ভার, serverless ফাংশন বা হাজারো client থাকলে প্রতিটির নিজের pool যথেষ্ট নয়; তখন ডেটাবেসের সামনে একটি আলাদা pooler বসে — PostgreSQL-এর জগতে সবচেয়ে পরিচিত PgBouncer. এর তিনটি মোড: session (client যতক্ষণ সংযুক্ত, ততক্ষণ একটি সার্ভার সংযোগ তার), transaction (শুধু একটি transaction-এর জন্য) আর statement (একটি স্টেটমেন্টের জন্য; বহু-স্টেটমেন্টের transaction চলে না)। সবচেয়ে বেশি সাশ্রয় transaction pooling-এ, কিন্তু তার দাম: পরের transaction অন্য সার্ভার সংযোগে যেতে পারে, তাই session-এর অবস্থা ভরসাযোগ্য নয় — session-স্তরের SET, temporary table, session-এর advisory lock, LISTEN, WITH HOLD cursor. protocol-স্তরের prepared statement-ও আগে এই মোডে চলত না; PgBouncer ১.২১ (২০২৩) থেকে এর সমর্থন এসেছে, তবে চালু করতে সেটিং লাগে।

পর্যবেক্ষণ — কোন কোয়েরি, কোথা থেকে, কত ধীর

অ্যাপ বড় হলে প্রশ্ন আসে: “গতকাল রাতে সাইট ধীর ছিল কেন?” উত্তরের জন্য লাগে observability. ডেটাবেসের দিকে slow query log — PostgreSQL-এ log_min_duration_statement (যেমন ২০০ ms-এর বেশি হলে লগে লেখা), MySQL-এ slow_query_log আর long_query_time — আর PostgreSQL-এর pg_stat_statements extension, যা প্রতিটি কোয়েরির ধরন কতবার চলল আর মোট কত সময় নিল তা জমায়। N+1 এখানে ধরা পড়ে একটি ছোট কোয়েরি হিসেবে, যা লাখবার চলেছে।

কিন্তু log-এ শুধু SQL দেখে বোঝা যায় না, কোন পাতা বা কোন কোড থেকে এল। তাই কোয়েরিতে একটি query tag জুড়ে দিন: সংযোগে application_name=shop-web, আর SQL-এর শুরুতে একটি comment — /* route='/api/my-orders' */ SELECT …. Google-এর sqlcommenter-এর মতো লাইব্রেরি ORM থেকে এটি স্বয়ংক্রিয়ভাবে যোগ করে। অ্যাপের দিকে মাপুন: প্রতিটি অনুরোধে কয়টি কোয়েরি, pool থেকে সংযোগ পেতে কত অপেক্ষা, কতগুলো সংযোগ ব্যস্ত। একটি সতর্কতা: log-এ parameter-এর মানসহ SQL লিখলে ফোন নম্বর বা অন্যান্য ব্যক্তিগত তথ্য log-এ জমে — দরকার না হলে মান বাদ দিন।

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

“অর্ডারের API লিখে দাও” — AI কয়েক সেকেন্ডে কাজ চলা কোড দেয়। পাঁচটি সারির ডেমোতে সব ঠিক দেখায়; সমস্যা ধরা পড়ে আসল মাপে, একসাথে অনেক অনুরোধে বা অদ্ভুত নামে। AI-এর লেখা ডেটাবেস কোডে প্রায়ই যা দেখা যায়:

AI-এর লেখা ডেটাবেস কোড চালু করার আগে — চেকলিস্ট

• প্রতিটি SQL-এ মান placeholder দিয়ে যায়; apostrophe-ওয়ালা নাম দিয়ে পরীক্ষা করেছি
• একটি অনুরোধে কয়টি কোয়েরি যায় গুনেছি — সংখ্যাটি ডেটার মাপের সাথে বাড়ে না
• সংযোগ pool থেকে আসে আর সব পথে (ত্রুটিসহ) ফেরত যায়
• transaction-এর শুরু-শেষ একই সংযোগে, ত্রুটিতে ROLLBACK, driver-এর autocommit ডিফল্ট জানি
• NULL, টাকা আর সময়ের টাইপ ভাষার দিকে ঠিকভাবে আসে (None, Decimal, UTC)
• পাসওয়ার্ড কোড বা Git-এ নেই; timeout আছে; শুধু সাময়িক ত্রুটিতে retry