সরাসরি পর্বে যান
পর্ব ৩৩ · ৮২-এর মধ্যে · মডিউল ৫

Database testing — SQL দিয়ে data যাচাই, migration আর integrity

Database Testing: SQL Assertions, Migrations and Data Integrity

Taught in Bangla, this lesson tests what an API leaves in the database. Each rule becomes an SQL query returning the rows that break it: counts, totals, orphans and duplicates. It tests CHECK, UNIQUE and foreign key constraints, including SQLite's foreign keys being off by default, and migrations up, down and backfill. A real pytest run drives the Bazarghar API, then reads the test database, where an API that returned 200 had charged one order twice; a lost update is reproduced with a fixed interleaving.

এই পর্বে যা শিখবেন

  • Data layer-এ কী কী test করার আছে, তার একটি তালিকা বানানো: constraint, trigger, migration, query, transaction আর কাজের পরের integrity
  • নিয়মকে SQL assertion-এ রূপ দেওয়া: row count, যোগফল, orphan, duplicate আর মানের বৈধতা
  • API চালিয়ে তারপর test database-এ read-only connection খুলে pytest দিয়ে যাচাই করা, আর seeded bug-এ লাল হওয়া দেখা
  • Migration-এর up, down আর backfill-এর test লেখা, আর expand-contract পদ্ধতিতে নিরাপদ schema বদলের পরিকল্পনা করা
  • Lost update-এর মতো isolation-এর ভুলকে deterministic test-এ ধরা, আর data quality check দিয়ে ধারাবাহিক নজর রাখা

১ · Database testing কী যাচাই করে — খাতা আর ক্যাশবাক্স

পর্ব ২৮-এ আপনি দেখেছেন, integration test-এ database আসল রাখতে হয়। পর্ব ৩১-এ API-র উত্তর, status code আর schema মিলিয়েছেন। কিন্তু একটি প্রশ্ন বাকি: API যা বলল, database-এ কি সত্যিই তা-ই ঘটল? উত্তর আর জমা হওয়া data এক জিনিস নয়।

পাড়ার মুদি দোকানের কথা ভাবুন। দিনশেষে মালিক দুটি জিনিস মেলান: হিসাবের খাতা আর ক্যাশবাক্স। খাতায় লেখা আজ ৪২টি বিক্রি, মোট ১২,৪৫০ টাকা। ক্যাশবাক্সে কি ঠিক ১২,৪৫০? তাকের চালের বস্তা কি খাতার হিসাবমতো কমেছে? কোনো বিক্রির পাশে ক্রেতার নাম আছে, কিন্তু কী কিনেছেন লেখা নেই, এমন লাইন আছে কি? দোকানের কর্মচারী হাসিমুখে প্রত্যেক ক্রেতাকে “ধন্যবাদ” বলেছেন (এটি API-র 200), তবু খাতা না মেলালে বোঝা যায় না দিনটি আসলে ঠিক চলেছে কি না। Database testing হলো সেই খাতা মেলানো।

Database-এর দিকের test-কে কয়েকটি ভাগে ভাবা যায়। প্রতিটি ভাগের প্রশ্ন আলাদা:

লক্ষ্যপ্রশ্নবাজারঘরে উদাহরণ
Constraintভুল data ঢুকতে চাইলে database কি থামায়?স্টক শূন্যের নিচে নয়; একজন ক্রেতা একটি পণ্যে একটিই review (AC4)
Trigger আর stored procedureস্বয়ংক্রিয় কাজটি ঠিক সারিতে, ঠিক সময়ে চলে কি?অবস্থা বদলালে ইতিহাসের টেবিলে একটি সারি
MigrationSchema বদলের পরে পুরোনো data ঠিক আছে কি? ফিরিয়ে নেওয়া যায় কি?নতুন কলাম item_count, পুরোনো ৭টি order-এ সঠিক মান
Queryরিপোর্ট বা খোঁজার query সঠিক সারি আর সঠিক যোগফল দেয় কি? NULL, ফাঁকা আর বাংলা লেখায়?গড় rating ৪.২৫ থেকে “4.3”; খালি ফল
Transactionসব একসাথে জমা, নয়তো কিছুই না? দুটি অনুরোধ একসাথে এলে?Order, item, স্টক আর কার্ট এক transaction-এ; দুজন একসাথে শেষ দুধ কিনলে
কাজের পরের integrityএকগুচ্ছ API কাজের পরে সব নিয়ম একসাথে টেকে কি?প্রতিটি order-এর subtotal = item-গুলোর যোগফল; এক order-এ একটির বেশি charge নয়

নিচের ছবিতে বাজারঘরের database-এর মূল ছয়টি টেবিল আর তাদের মাঝের সম্পর্ক। প্রতিটি তীর দেখায় কোন টেবিলের সারি কোনটিকে নির্দেশ করে (wallet_charges ছাড়া বাকিগুলো schema-য় foreign key); লাল লেখাগুলো এমন নিয়ম, যার প্রতিটি থেকে অন্তত একটি test আসে:

বাজারঘরের database — টেবিল, সম্পর্ক আর নিয়ম

তীর = কোন সারি কাকে নির্দেশ করে; লাল = যে নিয়মের test লাগবে

বাজারঘরের database: products, users, orders, order_items, wallet_charges, reviews আর তাদের নিয়ম products stock >= 0 users Plus: 0 বা 1 orders subtotal−discount +delivery = total order_items Σ = subtotal orphan নয় wallet_charges এক order-এ ≤ ১টি charge reviews UNIQUE(product, user)
ছবি ৩৩.১ — কিছু নিয়ম database নিজেই প্রয়োগ করে (CHECK, UNIQUE, foreign key), কিছু শুধু app-এর কোডে থাকে (যোগফল, এক order-এ একটি charge). দ্বিতীয় দলের নিয়ম SQL assertion ছাড়া কেউ পাহারা দেয় না।

দুই দলের পার্থক্যটি গুরুত্বপূর্ণ। CHECK, UNIQUE আর foreign key-এর মতো নিয়ম schema-য় লেখা, ভাঙতে চাইলে database নিজেই ভুল দেয়। কিন্তু “subtotal = item-গুলোর যোগফল” বা “এক order-এ একটির বেশি charge নয়” কোথাও লেখা নেই; app-এর কোড ঠিক থাকলে টেকে, কোড ভুল করলে চুপচাপ ভাঙে। এই দ্বিতীয় দলের নিয়মকে বলে invariant: এমন শর্ত, যা যেকোনো কাজের পরে সবসময় সত্য থাকার কথা। Key আর constraint-এর মূল ধারণা DBMS ও SQL কোর্সের পর্ব ০৬-এ; এখানে আমরা দেখব কীভাবে এগুলো test-এ রূপ নেয়।

২ · SQL assertion — গোনা, যোগফল, orphan আর duplicate

Database-এর নিয়ম যাচাইয়ের সবচেয়ে কাজের কৌশলটি সহজ: প্রতিটি নিয়মকে একটি query বানান, যা নিয়ম ভাঙা সারিগুলো ফেরত দেয়। সুস্থ database-এ প্রতিটি query-র ফল খালি। এমন query-কে বলে SQL assertion. চারটি চেনা ধরন:

  • গোনা (row count): একটি order দিলে orders-এ ঠিক একটি নতুন সারি, ইতিহাসে ঠিক একটি placed. বেশি হলে duplicate, কম হলে কিছু হারিয়েছে।
  • যোগফল মেলানো: Order-এর subtotal = SUM(unit_price * qty); মোট = subtotal − ছাড় + delivery. দুই জায়গায় রাখা একই তথ্য মিলতেই হবে।
  • Orphan: এমন সারি, যার “মা” নেই, যেমন এমন order item, যার order মুছে গেছে (orphan row). উল্টোটাও খোঁজা দরকার: item ছাড়া order.
  • Duplicate: যা একবারই থাকার কথা, তা দুবার: একই ক্রেতার একই পণ্যে দুটি review, একই order-এ দুটি charge.

নিচের database-টি বাজারঘরের একটি “আমদানি করা” কপি, যার schema-য় কোনো constraint নেই, আর তাতে ইচ্ছা করে কয়েকটি data-ভুল বসানো (seeded). Query একটিই, ছয়টি নিয়ম UNION ALL দিয়ে জোড়া, যাতে ফল একটি টেবিলে আসে। LEFT JOIN আর NOT EXISTS-এর পরিচয় DBMS ও SQL কোর্সের পর্ব ১৯ আর পর্ব ২১-এ, GROUP BY … HAVING পর্ব ১৮-এ। চালিয়ে দেখুন, তারপর নমুনা ডেটাবেস খুলে মিলিয়ে নিন কোন সারি কেন ধরা পড়ল:

-- each part lists the rows that BREAK a rule; empty = healthy
SELECT 'order without items' AS problem, o.id AS row_id
  FROM orders o
 WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id)
UNION ALL
SELECT 'item without order', i.order_id
  FROM order_items i LEFT JOIN orders o ON o.id = i.order_id
 WHERE o.id IS NULL
UNION ALL
SELECT 'negative stock', id FROM products WHERE stock < 0
UNION ALL
SELECT 'subtotal != sum of items', o.id
  FROM orders o JOIN order_items i ON i.order_id = o.id
 GROUP BY o.id HAVING o.subtotal != SUM(i.unit_price * i.qty)
UNION ALL
SELECT 'duplicate review (AC4)', product_id || '/' || user_id
  FROM reviews GROUP BY product_id, user_id HAVING COUNT(*) > 1
UNION ALL
SELECT 'phone not 01[3-9]+8 digits', id FROM orders
 WHERE phone NOT GLOB '01[3-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

আসল আউটপুট (ব্রাউজারের SQLite):

problem                    | row_id
order without items        | 9
item without order         | 12
negative stock             | 11
subtotal != sum of items   | 10
duplicate review (AC4)     | 1/2
phone not 01[3-9]+8 digits | 11

ছয়টি নিয়ম, ছয়টি ভাঙা সারি। Order ৯-এর কোনো item নেই (একটি order, যা কিছুই বিক্রি করেনি, অথচ ৪৫০ টাকার বিল)। Item-এর টেবিলে order ১২-এর একটি সারি আছে, অথচ order ১২-ই নেই। দুধের স্টক −১: যা নেই তা বিক্রি হয়েছে। Order ১০-এর subtotal ১০১০, কিন্তু item-গুলোর যোগফল ৮৮০ + ১২১ = ১০০১; ছাড় আর মোট এখন ভুল ভিত্তির ওপর। ক্রেতা ২ (mitu) পণ্য ১-এ দুটি review দিয়েছেন, যা AC4 ভাঙে আর গড় rating-কেও বদলে দেয়। আর order ১১-এর phone বাংলা অঙ্কে লেখা, অথচ phone-এর নিয়ম শুধু ASCII অঙ্ক মানে (পর্ব ১৪)। লক্ষ করুন, শেষটি data-র বৈধতা, অন্যগুলো সম্পর্ক আর যোগফল; §৭-এ data quality-র এই ভাগগুলো আবার আসবে।

দুটি খুঁটিনাটি, যা বাস্তবে অনেক ভুল ঘটায়। প্রথম, “item ছাড়া order” খুঁজতে অনেকে লেখেন WHERE id NOT IN (SELECT order_id FROM order_items). Item-এর টেবিলে একটিও NULL order_id থাকলে এই query কোনো সারিই ফেরত দেয় না, কারণ SQL-এ x NOT IN (…, NULL) কখনো সত্য হয় না; অর্থাৎ test সবুজ, অথচ ভুল আছে (অনুশীলন ৩)। NOT EXISTS এই ফাঁদে পড়ে না। দ্বিতীয়, প্রতিটি নিয়মকে আলাদা নাম দিন (problem কলাম), যাতে লাল হলে সাথে সাথে বোঝা যায় কোন নিয়ম ভাঙল। নিচের stepper-এ ছয়টি check একে একে দেখুন:

Check ১ · item ছাড়া orderNOT EXISTS
orders:      8, 9, 10, 11
items আছে:   8, 10, 11, 12

item নেই →  9
Check ২ · order ছাড়া itemLEFT JOIN … IS NULL
item (12, MILK, 450, 1)
  LEFT JOIN orders ON id = 12
  → o.id = NULL

orphan →  12
Check ৩ · ঋণাত্মক স্টকstock < 0
3  OIL     28
7  ATTA    38
11 MILK    -1   ← ধরা পড়ল
12 EGG     45
Check ৪ · যোগফলGROUP BY … HAVING
order  subtotal  SUM(items)
8      2000      880×2+120×2 = 2000
10     1010      880+121 = 1001
11     155       155
Check ৫ · duplicate reviewCOUNT(*) > 1
product/user  COUNT
1/2           2   ← stars 4 আর 5
1/3           1
1/4           1
1/5           1
12/3, 12/4    1, 1
Check ৬ · phone-এর বৈধতাNOT GLOB
8   01712345678    ঠিক
9   01300000004    ঠিক
10  01800000002    ঠিক
11  ০১৫০০০০০০০৩    বাংলা অঙ্ক
  1. Item ছাড়া order. NOT EXISTS প্রতিটি order-এর জন্য খোঁজে, অন্তত একটি item আছে কি না। ৮, ১০ আর ১১-এর আছে; ৯-এর নেই। ফল: order ৯.
  2. Order ছাড়া item. LEFT JOIN প্রতিটি item রাখে; মিল না পেলে order-এর দিক NULL. Order ১২ নেই, তাই সেই item-টি orphan. ফল: ১২.
  3. ঋণাত্মক স্টক। সবচেয়ে সহজ check, শুধু একটি WHERE. দুধের স্টক −১। আসল schema-য় CHECK থাকলে এই সারি ঢুকতেই পারত না; §৩-এ সেটিরই test.
  4. যোগফল মেলানো। প্রতিটি order-এর item-গুলো যোগ করে subtotal-এর সাথে তুলনা। Order ৯-এর item নেই বলে JOIN-এ আসে না (সেটি check ১-এর কাজ)। Order ১০-এ ১০১০ বনাম ১০০১: ৯ টাকার অমিল। ফল: ১০.
  5. Duplicate review. পণ্য আর ক্রেতা ধরে গোনা; দুই বা বেশি হলে AC4 ভাঙা। পণ্য ১-এ ক্রেতা ২-এর দুটি review. ফল: 1/2.
  6. Phone-এর বৈধতা। GLOB-এর ধরন: ০১, তারপর ৩ থেকে ৯, তারপর আরও ৮টি অঙ্ক, মোট ১১টি। বাংলা অঙ্ক এই ধরনে মেলে না। ফল: order ১১.

৩ · Constraint আর trigger-কে test করা

“Database তো নিজেই নিয়ম প্রয়োগ করে, তার আবার test কী?” প্রশ্নটি স্বাভাবিক। উত্তর: constraint-ও কোড, কেউ লিখেছেন, কেউ migration-এ বদলেছেন, কেউ ভুলে বাদ দিয়েছেন। একটি constraint test-এ আপনি ইচ্ছা করে ভুল data ঢোকাতে চান আর আশা করেন database তা থামাবে: এটি database-এর স্তরে একটি negative test.

নিচের block-এ বাজারঘরের schema-র একটি ছোট রূপ আর তিনটি test: স্টক শূন্যের নিচে না নামা, AC4-এর UNIQUE, আর এমন order-এর item, যে order নেই। Fixture-টি দুইভাবে database খোলে: একবার PRAGMA foreign_keys = ON দিয়ে, একবার ছাড়া (params দিয়ে, পর্ব ২৩-এর মতো)। তাই তিনটি test ছয়বার চলে:

import os, sys, pytest
d = "/home/pyodide/p33a"; os.makedirs(d, exist_ok=True); os.chdir(d)
sys.modules.pop("test_constraints", None)
open("test_constraints.py", "w").write('''
import sqlite3, pytest
SCHEMA = """
CREATE TABLE products (id INTEGER PRIMARY KEY,
  stock INT NOT NULL CHECK (stock >= 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY);
CREATE TABLE order_items (order_id INT NOT NULL REFERENCES orders(id),
  product_id INT NOT NULL REFERENCES products(id), qty INT CHECK (qty >= 1));
CREATE TABLE reviews (product_id INT, user_id INT, stars INT,
  UNIQUE (product_id, user_id));
INSERT INTO products VALUES (11, 3); INSERT INTO orders VALUES (8);
INSERT INTO reviews VALUES (1, 2, 4);"""
@pytest.fixture(params=["fk_on", "fk_off"])
def db(request):
    conn = sqlite3.connect(":memory:")
    if request.param == "fk_on":
        conn.execute("PRAGMA foreign_keys = ON")   # SQLite: OFF by default!
    conn.executescript(SCHEMA)
    yield conn
    conn.close()
def test_stock_never_negative(db):            # milk: 3 left, sell 5
    with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
        db.execute("UPDATE products SET stock = stock - 5 WHERE id = 11")
def test_one_review_per_customer_product(db):  # AC4
    with pytest.raises(sqlite3.IntegrityError, match="UNIQUE"):
        db.execute("INSERT INTO reviews VALUES (1, 2, 5)")
def test_item_needs_an_existing_order(db):    # order 99 does not exist
    with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
        db.execute("INSERT INTO order_items VALUES (99, 11, 1)")''')
pytest.main(["-v", "--no-header", "--tb=line", "-p", "no:cacheprovider",
             "test_constraints.py"])

আসল আউটপুট (ব্রাউজারের Python, pytest 9.0.2):

============================= test session starts ==============================
collecting ... collected 6 items

test_constraints.py::test_stock_never_negative[fk_on] PASSED             [ 16%]
test_constraints.py::test_stock_never_negative[fk_off] PASSED            [ 33%]
test_constraints.py::test_one_review_per_customer_product[fk_on] PASSED  [ 50%]
test_constraints.py::test_one_review_per_customer_product[fk_off] PASSED [ 66%]
test_constraints.py::test_item_needs_an_existing_order[fk_on] PASSED     [ 83%]
test_constraints.py::test_item_needs_an_existing_order[fk_off] FAILED    [100%]

=================================== FAILURES ===================================
E   Failed: DID NOT RAISE <class 'sqlite3.IntegrityError'>
/home/pyodide/p33a/test_constraints.py:28: Failed: DID NOT RAISE <class 'sqlite3.IntegrityError'>
=========================== short test summary info ============================
FAILED test_constraints.py::test_item_needs_an_existing_order[fk_off] - Faile...
========================= 1 failed, 5 passed in 0.03s ==========================

পাঁচটি সবুজ, একটি লাল, আর লালটিই এই block-এর শিক্ষা। fk_on-এ order ৯৯-এর item ঢোকাতে গেলে SQLite FOREIGN KEY constraint failed দেয়, test সবুজ। fk_off-এ একই schema, একই REFERENCES, অথচ সারিটি নিঃশব্দে ঢুকে যায়: DID NOT RAISE. SQLite পুরোনো program-গুলোর সাথে সামঞ্জস্যের জন্য foreign key-এর যাচাই নিজে থেকে বন্ধ রাখে; প্রতিটি connection-এ আলাদা করে চালু করতে হয়। বাজারঘর app প্রতিটি connection-এ এটি চালু করে (তার db.py-তে PRAGMA foreign_keys = ON), আর এই পাতার SQL runner-ও তা-ই করে। কিন্তু কেউ একটি নতুন script বা admin tool লিখে pragma ভুলে গেলে §২-এর মতো orphan তৈরি হতে পারে। CHECK আর UNIQUE দুইভাবেই কাজ করেছে, কারণ এরা pragma-র ওপর নির্ভর করে না। Test-এর match="CHECK" অংশটিও জরুরি: শুধু “কোনো একটা ভুল এসেছে” নয়, ঠিক সেই নিয়মটিই থামিয়েছে, তা মেলানো।

Trigger আর stored procedure

Trigger হলো database-এর ভেতরের স্বয়ংক্রিয় কোড: কোনো সারি যোগ, বদল বা মোছার আগে বা পরে নিজে থেকে চলে (মূল ধারণা DBMS ও SQL কোর্সের পর্ব ২৯-এ)। বাজারঘর ইতিহাসের সারি app-এর কোডে লেখে, কিন্তু অনেক system এই কাজ trigger-এ রাখে। Trigger-এর test-এর ধরন একই: Arrange (জানা অবস্থা), Act (সারি বদলানো), Assert (trigger-এর ফল)। তিনটি প্রশ্ন সবসময় জিজ্ঞেস করুন: (ক) ঠিক যে ঘটনায় চলার কথা, শুধু তাতেই চলে কি (অবস্থা না বদলালে ইতিহাসে সারি যাওয়া উচিত নয়)? (খ) একসাথে অনেক সারি বদলালে প্রতিটির জন্য চলে, না একবার? (গ) Trigger ব্যর্থ হলে মূল কাজটিও rollback হয় কি? Stored procedure-এর test একটি function-এর unit test-এর মতোই, শুধু “function”-টি database-এর ভেতরে। এর জন্য আলাদা framework আছে, যেমন PostgreSQL-এর pgTAP বা SQL Server-এর tSQLt, তবে pytest থেকে procedure ডেকে ফল মেলানোও সমানভাবে চলে।

৪ · সত্যিকারের run — API চালিয়ে database যাচাই

এবার চালু app-এ। কৌশলটি: test HTTP দিয়ে app-কে দিয়ে কাজ করায় (ঠিক যেমন একজন ক্রেতা করেন), তারপর test database-এর ফাইলটি পড়ে দেখে ভেতরে কী জমা হলো। Database-এ লেখে শুধু app; test শুধু পড়ে।

কখনো production database নয়। এই ধরনের test সবসময় একটি আলাদা test database-এ চলে, seed থেকে শুরু করে, আর প্রতিটি test-এর আগে reset হয়। Production-এর database-এ test চালানো মানে আসল গ্রাহকের data দেখা (গোপনীয়তার ঝুঁকি), ভুল করে বদলে ফেলার ঝুঁকি, আর অন্য ব্যবহারকারীর কাজের সাথে মিশে অস্থির ফল। Test-এর connection-ও read-only খুলুন, যাতে ভুল করে লেখার পথই না থাকে।

বাজারঘর app নিজের lab-এ চালানো হলো, port ৮৭৮৬-এ, test mode চালু আর database ফাইল lab-এর ভেতরে:

$ BAZARGHAR_TEST_MODE=1 BAZARGHAR_DB=~/bazarghar-qa/lab.db \
    uv run bazarghar --port 8786 --test-mode --reset

আলাদা একটি uv project-এ pytest 9.2.0 (MIT) আর httpx 0.28.1 (BSD-3-Clause), Python 3.12; database পড়তে Python-এর নিজস্ব sqlite3 module. conftest.py-র নতুন অংশটি db fixture: একই ফাইল, কিন্তু mode=ro দিয়ে, অর্থাৎ read-only connection:

# tests/conftest.py (অংশ)
DB_PATH = os.environ["BAZARGHAR_DB"]     # the SAME file the test server uses

@pytest.fixture(autouse=True)
def fresh_seed(api):
    assert api.post("/api/test/reset").status_code == 200

@pytest.fixture
def db():
    """Read-only view of the test database: tests look, only the app writes."""
    conn = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)
    yield lambda sql, *args: conn.execute(sql, args).fetchall()
    conn.close()

এই connection দিয়ে কিছু লিখতে চাইলে কী হয়, সেটিও একবার চালিয়ে দেখা হয়েছে (SQLite 3.53.1): OperationalError: attempt to write a readonly database. প্রথম test-টি একটি checkout চালিয়ে পাঁচটি টেবিল মেলায়:

# tests/test_db_after_api.py (অংশ)
def test_checkout_writes_consistent_rows(api, db, login, buy):
    oid = buy(login("mitu"), [(OIL, 2), (ATTA, 2)])            # 2*880 + 2*120 = 2000
    assert db("SELECT subtotal, discount, delivery, total, status FROM orders "
              "WHERE id = ?", oid) == [(2000, 200, 0, 1800, "placed")]
    assert db("SELECT SUM(unit_price * qty) FROM order_items "
              "WHERE order_id = ?", oid) == [(2000,)]
    assert db("SELECT stock FROM products WHERE id IN (?, ?) ORDER BY id",
              OIL, ATTA) == [(28,), (38,)]
    assert db("SELECT COUNT(*) FROM cart_items WHERE user_id = 2") == [(0,)]
    assert db("SELECT status FROM order_history WHERE order_id = ?", oid) == [("placed",)]

def test_retry_after_timeout_charges_once(api, db, login, buy):
    mitu = login("mitu")
    oid = buy(mitu, [(OIL, 1), (9, 1)], method="mobile-wallet")   # 1001 taka
    api.put("/api/test/wallet", json={"mode": "timeout_once"})
    key = {"Idempotency-Key": "pay-8-try"} | mitu
    assert api.post(f"/api/orders/{oid}/pay", headers=key).status_code == 504
    assert api.post(f"/api/orders/{oid}/pay", headers=key).status_code == 200
    assert db("SELECT COUNT(*), SUM(amount) FROM wallet_charges "
              "WHERE order_id = ?", oid) == [(1, 961)]

আরও দুটি test: বাতিলে স্টক ফেরত আর ইতিহাসে cancelled; আর shipped order বাতিলের চেষ্টা 409 পেলে database-এ কিছুই বদলায়নি (অবস্থা, স্টক, ইতিহাসের সারির সংখ্যা আগে-পরে এক)। শেষ ফাইলটি §২-এর ধারণা: একগুচ্ছ invariant, প্রতিটি একটি SQL query, আর একটি fixture, যা আগে একটি ছোট “ব্যস্ত দিন” চালায়: mitu-র ২০০০ টাকার order আর পরিশোধ, রাফির wallet-এ timeout আর একই key দিয়ে আবার চেষ্টা, সুমির order ৬ বাতিল, admin-এর order ৭ pack:

# tests/test_invariants.py (অংশ)
INVARIANTS = {
    "orphan_items": "SELECT i.order_id FROM order_items i "
                    "LEFT JOIN orders o ON o.id = i.order_id WHERE o.id IS NULL",
    "money_adds_up": "SELECT id FROM orders "
                     "WHERE subtotal - discount + delivery != total",
    "delivery_rule": "SELECT o.id FROM orders o JOIN users u ON u.id = o.user_id "
                     "WHERE o.delivery != CASE WHEN o.district = 'Dhaka' THEN "
                     "(CASE WHEN o.subtotal >= 2000 THEN 0 ELSE 60 END) "
                     "WHEN u.plus = 1 THEN 60 ELSE 120 END",
    "one_charge_per_order": "SELECT order_id FROM wallet_charges "
                            "GROUP BY order_id HAVING COUNT(*) > 1",
    # … order_without_items, subtotal_matches_items, coupon_rule,
    #   history_matches_status
}

@pytest.mark.parametrize("name", INVARIANTS)
def test_invariant_holds(busy_day, db, name):
    assert db(INVARIANTS[name]) == []

সত্যিকারের run, কোনো bug ছাড়া (platform, path আর config-এর শুরুর লাইনগুলো বাদ):

$ uv run pytest -v --tb=short -p no:cacheprovider
collecting ... collected 12 items

tests/test_db_after_api.py::test_checkout_writes_consistent_rows PASSED  [  8%]
tests/test_db_after_api.py::test_cancel_restores_stock_and_logs_history PASSED [ 16%]
tests/test_db_after_api.py::test_rejected_cancel_leaves_db_untouched PASSED [ 25%]
tests/test_db_after_api.py::test_retry_after_timeout_charges_once PASSED [ 33%]
tests/test_invariants.py::test_invariant_holds[orphan_items] PASSED      [ 41%]
tests/test_invariants.py::test_invariant_holds[order_without_items] PASSED [ 50%]
tests/test_invariants.py::test_invariant_holds[subtotal_matches_items] PASSED [ 58%]
tests/test_invariants.py::test_invariant_holds[money_adds_up] PASSED     [ 66%]
tests/test_invariants.py::test_invariant_holds[coupon_rule] PASSED       [ 75%]
tests/test_invariants.py::test_invariant_holds[delivery_rule] PASSED     [ 83%]
tests/test_invariants.py::test_invariant_holds[one_charge_per_order] PASSED [ 91%]
tests/test_invariants.py::test_invariant_holds[history_matches_status] PASSED [100%]

============================= 12 passed in 2.24s ==============================

এবার server থামিয়ে তিনটি seeded defect একসাথে চালু করে আবার চালানো হলো: BAZARGHAR_BUGS=delivery_after_discount,double_charge_no_idempotency,cancel_after_shipped. Test-এর কোড একটুও বদলায়নি:

$ uv run pytest -v --tb=short -p no:cacheprovider
collecting ... collected 12 items

tests/test_db_after_api.py::test_checkout_writes_consistent_rows FAILED  [  8%]
tests/test_db_after_api.py::test_cancel_restores_stock_and_logs_history PASSED [ 16%]
tests/test_db_after_api.py::test_rejected_cancel_leaves_db_untouched FAILED [ 25%]
tests/test_db_after_api.py::test_retry_after_timeout_charges_once FAILED [ 33%]
tests/test_invariants.py::test_invariant_holds[orphan_items] PASSED      [ 41%]
tests/test_invariants.py::test_invariant_holds[order_without_items] PASSED [ 50%]
tests/test_invariants.py::test_invariant_holds[subtotal_matches_items] PASSED [ 58%]
tests/test_invariants.py::test_invariant_holds[money_adds_up] PASSED     [ 66%]
tests/test_invariants.py::test_invariant_holds[coupon_rule] PASSED       [ 75%]
tests/test_invariants.py::test_invariant_holds[delivery_rule] FAILED     [ 83%]
tests/test_invariants.py::test_invariant_holds[one_charge_per_order] FAILED [ 91%]
tests/test_invariants.py::test_invariant_holds[history_matches_status] PASSED [100%]

================================== FAILURES ===================================
____________________ test_checkout_writes_consistent_rows _____________________
tests\test_db_after_api.py:6: in test_checkout_writes_consistent_rows
    assert db("SELECT subtotal, discount, delivery, total, status FROM orders "
E   AssertionError: assert [(2000, 200, ...60, 'placed')] == [(2000, 200, ...00, 'placed')]
E
E     At index 0 diff: (2000, 200, 60, 1860, 'placed') != (2000, 200, 0, 1800, 'placed')
…
__________________ test_rejected_cancel_leaves_db_untouched ___________________
tests\test_db_after_api.py:32: in test_rejected_cancel_leaves_db_untouched
    assert r.status_code == 409
E   assert 200 == 409
E    +  where 200 = <Response [200 OK]>.status_code
____________________ test_retry_after_timeout_charges_once ____________________
tests\test_db_after_api.py:46: in test_retry_after_timeout_charges_once
    assert db("SELECT COUNT(*), SUM(amount) FROM wallet_charges "
E   AssertionError: assert [(2, 1922)] == [(1, 961)]
E
E     At index 0 diff: (2, 1922) != (1, 961)
…
_____________________ test_invariant_holds[delivery_rule] _____________________
tests\test_invariants.py:44: in test_invariant_holds
    assert db(INVARIANTS[name]) == []
E   AssertionError: assert [(8,)] == []
…
_________________ test_invariant_holds[one_charge_per_order] __________________
tests\test_invariants.py:44: in test_invariant_holds
    assert db(INVARIANTS[name]) == []
E   AssertionError: assert [(9,)] == []
…
========================= 5 failed, 7 passed in 2.21s =========================

পাঁচটি লাল, আর প্রতিটির গল্প আলাদা। প্রথম: database-এ order ৮-এর delivery ৬০, মোট ১৮৬০: ঢাকার ক্রেতা ২০০০ টাকার বাজারে ৬০ টাকা বেশি দিচ্ছেন। দ্বিতীয়: shipped order বাতিল হয়ে গেছে (200); test এখানে status code-এই থেমেছে, database পর্যন্ত যায়নি। তৃতীয়টি সবচেয়ে গুরুতর: API-র দিক থেকে সব স্বাভাবিক, প্রথমবার 504, দ্বিতীয়বার 200, ঠিক যেমন হওয়ার কথা। অথচ wallet-এর ledger-এ order-টির দুটি charge, মোট ১৯২২ টাকা, অর্থাৎ ক্রেতার কাছ থেকে ৯৬১ টাকা দুবার কাটা। Bug-টি Idempotency-Key উপেক্ষা করে, আর সেই ভুল শুধু database-এ দেখা যায়। পর্ব ২৯-এর idempotency-র আলোচনা এখানে টাকার অঙ্কে ধরা পড়ল।

Invariant-গুলোর ফলও শিক্ষণীয়। “ব্যস্ত দিন”-এ delivery_rule ধরেছে order ৮ (mitu-র ২০০০ টাকা), আর one_charge_per_order ধরেছে order ৯ (রাফির wallet). কিন্তু money_adds_up সবুজ: ৮-এর ২০০০ − ২০০ + ৬০ = ১৮৬০, হিসাব নিজের ভেতরে নিখুঁত, শুধু নিয়মটাই ভুল। সামঞ্জস্যের check (consistency) আর নিয়মের check (rule) আলাদা জিনিস; দুটিই রাখুন। আর cancel_after_shipped কোনো invariant ভাঙেনি, কারণ ব্যস্ত দিনে shipped order বাতিলের চেষ্টাই ছিল না: invariant শুধু সেই ভুল ধরে, যা আগের কাজগুলো সত্যিই ঘটিয়েছে। নির্দিষ্ট পথের test আর সাধারণ invariant একে অপরের পরিপূরক।

৫ · Migration test — up, down আর backfill

App বড় হলে schema বদলায়: নতুন কলাম, নতুন টেবিল, নাম বদল, ভাগ করা। প্রতিটি বদল একটি migration: একটি ছোট script, যার সংস্করণ নম্বর আছে আর যা database-কে এক অবস্থা থেকে পরের অবস্থায় নেয় (“up”); অনেক দল উল্টো পথের script-ও রাখে (“down”, down migration). Python-এ Alembic, Java-তে Flyway বা Liquibase এমন tool; ধারণা আর ফাঁদ DBMS ও SQL কোর্সের পর্ব ৫০ আর ফুল-স্ট্যাক ওয়েব ফ্রেমওয়ার্ক কোর্সের পর্ব ৪০-এ আছে। এখানে প্রশ্ন একটিই: migration-এর test কীভাবে লিখবেন?

Migration-এর ভুল সাধারণ bug-এর চেয়ে বিপজ্জনক, কারণ এটি একবারই চলে, আর চলে সব পুরোনো সারির ওপর। ভুল হলে হাজার সারির data নষ্ট, আর অনেক সময় ফেরার পথ নেই। ধরুন বাজারঘরে order-এর তালিকায় “কয়টি পণ্য” দেখাতে হবে, তাই migration ০০০৭ orders-এ item_count কলাম যোগ করবে। নতুন order-এর জন্য app মান লিখবে, কিন্তু পুরোনো ৭টি order-এর মান ভরতে হবে migration-এর ভেতরেই: এই কাজের নাম backfill. “কয়টি পণ্য” মানে কয়টি একক (qty-র যোগফল), কয়টি লাইন নয়; order ১-এ চাল ১টি আর ডাল ২টি, অর্থাৎ ৩টি।

নিচের test দুটি backfill-এর রূপ চালায়: ভুলটি COUNT(*) (লাইন গোনে), ঠিকটি SUM(qty). প্রত্যাশিত মান database-এর বাইরে, seed-এর Python dict থেকে আলাদাভাবে হিসাব করা, যাতে test আর migration একই ভুল না করে। তৃতীয় test up-এর পরে down চালিয়ে দেখে schema আগের মতো ফিরেছে আর কোনো সারি হারায়নি:

import os, sys, pytest
d = "/home/pyodide/p33b"; os.makedirs(d, exist_ok=True); os.chdir(d)
sys.modules.pop("test_migration", None)
open("test_migration.py", "w").write('''
import sqlite3, pytest
SEED = {1: [(1, 1), (2, 2)], 2: [(1, 1), (12, 1)], 3: [(1, 2), (12, 1)],
        4: [(1, 1)], 5: [(3, 1), (7, 1)], 6: [(3, 1), (9, 1)], 7: [(3, 2), (7, 2)]}
BACKFILL = {  # migration 0007: orders.item_count = units in the order
  "count_lines": "SELECT COUNT(*) FROM order_items i WHERE i.order_id = orders.id",
  "sum_qty": "SELECT SUM(qty) FROM order_items i WHERE i.order_id = orders.id"}
def up(c, backfill):
    c.execute("ALTER TABLE orders ADD COLUMN item_count INT NOT NULL DEFAULT 0")
    c.execute(f"UPDATE orders SET item_count = ({BACKFILL[backfill]})")
def down(c):
    c.execute("ALTER TABLE orders DROP COLUMN item_count")
cols = lambda c: [r[1] for r in c.execute("PRAGMA table_info(orders)")]
@pytest.fixture
def seeded():                         # a fresh copy of the seed, never a live DB
    c = sqlite3.connect(":memory:")
    c.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, district TEXT)")
    c.execute("CREATE TABLE order_items (order_id INT, product_id INT, qty INT)")
    for oid, items in SEED.items():
        c.execute("INSERT INTO orders (id) VALUES (?)", (oid,))
        c.executemany("INSERT INTO order_items VALUES (?, ?, ?)",
                      [(oid, p, q) for p, q in items])
    return c
@pytest.mark.parametrize("backfill", BACKFILL)
def test_up_backfills_units(seeded, backfill):
    up(seeded, backfill)
    got = dict(seeded.execute("SELECT id, item_count FROM orders"))
    assert got == {o: sum(q for _, q in it) for o, it in SEED.items()}
def test_down_restores_schema_and_rows(seeded):
    before = cols(seeded); up(seeded, "sum_qty"); down(seeded)
    assert cols(seeded) == before and len(list(seeded.execute("SELECT * FROM orders"))) == 7''')
pytest.main(["-q", "--no-header", "--tb=short", "-p", "no:cacheprovider", "test_migration.py"])

আসল আউটপুট (ব্রাউজারের Python):

F..                                                                      [100%]
=================================== FAILURES ===================================
_____________________ test_up_backfills_units[count_lines] _____________________
test_migration.py:28: in test_up_backfills_units
    assert got == {o: sum(q for _, q in it) for o, it in SEED.items()}
E   assert {1: 2, 2: 2, 3: 2, 4: 1, ...} == {1: 3, 2: 2, 3: 3, 4: 1, ...}
E     
E     Omitting 4 identical items, use -vv to show
E     Differing items:
E     {1: 2} != {1: 3}
E     {3: 2} != {3: 3}
E     {7: 2} != {7: 4}
E     Use -v to get more diff
=========================== short test summary info ============================
FAILED test_migration.py::test_up_backfills_units[count_lines] - assert {1: 2...
1 failed, 2 passed in 0.03s

count_lines-এর backfill লাল, আর pytest-এর dict তুলনা ঠিক তিনটি order দেখাচ্ছে: ১-এ ২ (প্রত্যাশা ৩), ৩-এ ২ (প্রত্যাশা ৩), ৭-এ ২ (প্রত্যাশা ৪)। বাকি চারটি মিলেছে (“Omitting 4 identical items”), কারণ সেগুলোর প্রতিটি লাইনে qty ১। এখানেই বিপদ: শুধু একটি-দুটি order দিয়ে test করলে, আর সেগুলোর qty ১ হলে, ভুল backfill-ও সবুজ। Test data-য় qty ২ থাকা জরুরি ছিল, ঠিক যেমন boundary-র test-এ সীমার মান (পর্ব ০৮)। sum_qty সবুজ, আর down-এর test নিশ্চিত করল কলাম সরে গিয়ে schema আগের দুটি কলামে ফিরেছে আর ৭টি সারিই আছে। (SQLite-এ DROP COLUMN এসেছে ৩.৩৫ সংস্করণে, ২০২১ সালে; তার আগে পুরো টেবিল নতুন করে বানাতে হতো।)

1ADD COLUMN item_count2SET = COUNT(*)  -- bug3test: vs SUM(qty)4SET = SUM(qty)  -- fix5down: DROP COLUMN
orders টেবিল
কলাম
order ১
order ৪
order ৭
  1. শুরু। Seed-এর একটি কপি: orders-এ দুটি কলাম আর ৭টি সারি। কখনো আসল database-এ নয়; প্রতিটি test নতুন কপি পায়।
  2. কলাম যোগ। ADD COLUMN … NOT NULL DEFAULT 0: সব পুরোনো সারিতে আপাতত ০। SQLite-এ NOT NULL কলাম যোগ করতে একটি default লাগে, নইলে ভুল দেয়; অন্য engine-এ বড় টেবিলে এই ধাপটি অনেকক্ষণ lock ধরে রাখতে পারে।
  3. ভুল backfill. COUNT(*) প্রতিটি order-এর লাইন গোনে: order ১-এ ২টি লাইন, তাই ২। Order ৪-এ একটিই লাইন, qty ১, তাই সেখানে ভুলটি লুকিয়ে থাকে।
  4. Test লাল। প্রত্যাশা seed থেকে হিসাব করা: order ১ = ১ + ২ = ৩, order ৭ = ২ + ২ = ৪. তিনটি order অমিল। Test-টি migration-এর SQL নয়, আলাদা পথে হিসাব করে বলেই ভুলটি ধরতে পারল।
  5. ঠিক backfill. SUM(qty): order ১ = ৩, order ৪ = ১, order ৭ = ৪. সাতটিই প্রত্যাশার সাথে মেলে।
  6. Down. DROP COLUMN-এর পরে কলামের তালিকা আবার id, district, আর সারি ৭টি। Up → down → আবার up চালানো গেলে CI-তে migration নিয়ে আস্থা বাড়ে।

Expand-contract — চালু system-এ নিরাপদ বদল

চালু app-এ একবারে কলামের নাম বদলানো বিপজ্জনক: নতুন কোড নতুন নাম খোঁজে, আর deploy-এর কয়েক মিনিট পুরোনো কোড চলতে থাকে পুরোনো নাম খুঁজে। তাই অনেক দল expand-contract (parallel change নামেও পরিচিত) পদ্ধতিতে বদল করে, ধাপে ধাপে, প্রতিটি ধাপের নিজের test-সহ:

  1. Expand: নতুন কলাম যোগ, পুরোনোটি রেখে। Test: পুরোনো কোড এখনো চলে (পুরোনো API test সবুজ)।
  2. দুই জায়গায় লেখা: App নতুন আর পুরোনো দুই কলামেই লেখে। Test: নতুন order-এ দুই কলাম মেলে (একটি invariant).
  3. Backfill: পুরোনো সারিতে নতুন কলাম ভরা, বড় টেবিলে ছোট ছোট ভাগে। Test: backfill-এর পরে অমিল সারির সংখ্যা শূন্য।
  4. পড়া নতুন কলাম থেকে: Test: সব API আর রিপোর্ট নতুন কলামে সবুজ।
  5. Contract: পুরোনো কলাম মুছে ফেলা, অন্তত একটি release পরে। Test: পুরোনো কলামের নাম কোডে কোথাও নেই।

প্রতিটি ধাপ আলাদাভাবে ফেরত নেওয়া যায়, আর প্রতিটির পরে §৪-এর invariant suite চালানো যায়। Migration-এর জন্য CI-তে তিনটি test রাখার চল আছে: খালি database-এ সব migration শুরু থেকে শেষ পর্যন্ত চলে কি; seed-এর কপিতে নতুন migration চলে আর data-র invariant টেকে কি; আর migration-এর পরে schema কি ORM-এর model-এর সাথে মেলে।

৬ · Transaction আর isolation — lost update ধরার test

একটি transaction-এর “সব, নয়তো কিছুই না” (atomicity) আচরণ পর্ব ২৮-এ দেখেছেন। এবার প্রশ্ন দুটি transaction একসাথে চললে। ধরুন দুধের শেষ ৩টি প্যাকেট আছে। mitu ২টি কিনছেন, একই মুহূর্তে joy ১টি। একটি সাধারণ কিন্তু ভুল কোড: আগে স্টক পড়া, তা থেকে বিয়োগ, তারপর নতুন মান লেখা। দুই অনুরোধ যদি দুজনেই আগে পড়ে, তারপর লেখে, তাহলে দ্বিতীয় লেখাটি প্রথমটিকে মুছে দেয়। এর নাম lost update: একটি বিক্রি হিসাব থেকে হারিয়ে গেল, অথচ দুজনেই দুধ পাবেন বলে নিশ্চিত।

Concurrency-র bug ধরার সমস্যা হলো এটি কদাচিৎ ঘটে, ঠিক সময়ে দুই অনুরোধ মিলে গেলে তবেই। Test-এ তাই ভাগ্যের ওপর না ছেড়ে interleaving, অর্থাৎ দুই কাজের ধাপগুলো কোন ক্রমে মিশবে, তা নিজে ঠিক করে দেওয়া হয়। নিচের block-এ প্রতিটি বিক্রির কোড একটি Python generator: প্রতিটি yield-এ থামে, আর test ঠিক করে কে কখন এগোবে। একই ফাইলে দুটি আলাদা connection, দুজন ক্রেতার মতো:

import os, sys, pytest
d = "/home/pyodide/p33c"; os.makedirs(d, exist_ok=True); os.chdir(d)
sys.modules.pop("test_lost_update", None)
open("test_lost_update.py", "w").write('''
import sqlite3, pytest
def read_modify_write(conn, qty):     # two separate statements
    stock = conn.execute("SELECT stock FROM products WHERE id = 11").fetchone()[0]
    yield f"read {stock}"
    conn.execute("UPDATE products SET stock = ? WHERE id = 11", (stock - qty,))
    yield f"wrote {stock - qty}"
def atomic(conn, qty):                # one statement, guarded
    yield "ready"
    cur = conn.execute("UPDATE products SET stock = stock - ? "
                       "WHERE id = 11 AND stock >= ?", (qty, qty))
    yield f"updated {cur.rowcount} row"
@pytest.mark.parametrize("sell", [read_modify_write, atomic])
def test_two_checkouts_sell_all_milk(tmp_path, sell):
    path = tmp_path / "shop.db"
    a, b = (sqlite3.connect(path, isolation_level=None) for _ in range(2))
    a.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, stock INT)")
    a.execute("INSERT INTO products VALUES (11, 3)")   # milk powder: 3 left
    ta, tb = sell(a, 2), sell(b, 1)                   # mitu buys 2, joy buys 1
    log = [next(ta), next(tb), next(ta), next(tb)]    # the interleaving
    print(log)
    final = a.execute("SELECT stock FROM products WHERE id = 11").fetchone()[0]
    assert final == 0, f"3 - 2 - 1 should be 0, got {final}"''')
pytest.main(["-q", "--no-header", "--tb=line", "-rA", "-p", "no:cacheprovider",
             "test_lost_update.py"])

আসল আউটপুট (ব্রাউজারের Python):

F.                                                                       [100%]
=================================== FAILURES ===================================
E   AssertionError: 3 - 2 - 1 should be 0, got 2
    assert 2 == 0
----------------------------- Captured stdout call -----------------------------
['read 3', 'read 3', 'wrote 1', 'wrote 2']
/home/pyodide/p33c/test_lost_update.py:23: AssertionError: 3 - 2 - 1 should be 0, got 2
==================================== PASSES ====================================
___________________ test_two_checkouts_sell_all_milk[atomic] ___________________
----------------------------- Captured stdout call -----------------------------
['ready', 'ready', 'updated 1 row', 'updated 1 row']
=========================== short test summary info ============================
PASSED test_lost_update.py::test_two_checkouts_sell_all_milk[atomic]
FAILED test_lost_update.py::test_two_checkouts_sell_all_milk[read_modify_write]
1 failed, 1 passed in 0.02s

read_modify_write-এর log: দুজনেই ৩ পড়েছেন, mitu লিখেছেন ৩ − ২ = ১, joy লিখেছেন ৩ − ১ = ২. শেষে স্টক ২, অথচ হওয়ার কথা ০: mitu-র বিক্রি হারিয়ে গেছে, আর বাজারঘর এখন এমন ২টি দুধ বিক্রি করবে, যা গুদামে নেই। atomic-এ বিয়োগটি database নিজে করে, এক statement-এ: stock = stock - ?, সাথে WHERE stock >= ? পাহারা। দুটি update-ই একটি করে সারি বদলেছে, শেষে ০। পাহারাটির আরেকটি কাজ আছে: স্টক কম থাকলে update কোনো সারি বদলায় না (rowcount ০), আর app তখন “স্টক নেই” বলতে পারে, স্টক ঋণাত্মক না করে। বাজারঘর app-এর checkout ঠিক এভাবেই স্টক কমায়, সাথে একটি write lock-ও রাখে।

1A: SELECT stock      → 32B: SELECT stock      → 33A: SET stock = 3-24B: SET stock = 3-15A: stock-2 if >=26B: stock-1 if >=1
অবস্থা
DB-তে stock
A (mitu, ২টি) যা জানে
B (joy, ১টি) যা জানে
বিক্রি হয়েছে
  1. শুরু। দুধের স্টক ৩। দুটি আলাদা connection: A (mitu, ২টি কিনবেন) আর B (joy, ১টি)।
  2. A পড়ে। A-র কাছে স্টক ৩. এখনো কিছু লেখেনি।
  3. B পড়ে। B-ও পড়ে ৩, কারণ A এখনো লেখেনি। এই মুহূর্তেই ভুলের বীজ: দুজনের হাতে একই পুরোনো মান।
  4. A লেখে। ৩ − ২ = ১. Database-এ এখন ১, বিক্রি ২টি।
  5. B লেখে। B নিজের জানা ৩ থেকে হিসাব করে: ৩ − ১ = ২, আর A-র লেখার ওপর লিখে দেয়। Database-এ ২, অথচ বিক্রি ৩টি: আসল স্টক হওয়ার কথা ০. Test লাল।
  6. Atomic, A. নতুন শুরু (স্টক ৩)। কেউ আগে পড়ে না; database নিজে stock - 2 করে, পাহারা stock >= 2 সত্য। স্টক ১।
  7. Atomic, B. Database বর্তমান মান ১ থেকে বিয়োগ করে: ০. কোনো বিক্রি হারায়নি, test সবুজ।

Isolation level আর তার anomaly

Database-গুলো একসাথে চলা transaction-কে কতটা আলাদা রাখবে, তা ঠিক হয় isolation level দিয়ে। SQL standard (১৯৯২) চারটি level আর তিনটি anomaly-র কথা বলে: dirty read (অন্যের না-commit-করা data পড়া), non-repeatable read (একই সারি দুবার পড়ে দুই মান), আর phantom read (একই শর্তে দুবার খুঁজে নতুন সারি)। পরে গবেষকেরা দেখান, lost update আর write skew-এর মতো anomaly-ও আছে, যা এই তালিকায় ধরা পড়ে না। পুরো আলোচনা DBMS ও SQL কোর্সের পর্ব ৪৭ আর পর্ব ৪৬-এ; tester হিসেবে আপনার কাজের তিনটি কথা:

  • Default জানুন। PostgreSQL-এর default Read Committed, MySQL-এর InnoDB-র Repeatable Read; একই কোড দুই engine-এ ভিন্ন আচরণ করতে পারে। Test-এর engine আর level production-এর মতো রাখুন।
  • সমাধানও test করুন। Atomic update, SELECT … FOR UPDATE-এর মতো row lock, বা optimistic locking (সারির একটি version নম্বর, লেখার সময় মিলিয়ে দেখা): প্রতিটির জন্য উপরের মতো interleaving-এর test, যেখানে দ্বিতীয় লেখক হয় অপেক্ষা করে, নয়তো ভুল পায়, কিন্তু কখনো নিঃশব্দে মুছে দেয় না।
  • App-এর স্তরেও lost update হয়। বাজারঘরের admin-এর স্টক পাতা পুরো মান লেখে (PUT … {"stock": 40}). দুজন admin একই পাতা খুলে দুই রকম বদল জমা দিলে শেষেরটি জেতে। এটি database-এর ভুল নয়, নকশার প্রশ্ন; exploratory test-এ (পর্ব ২০) দুটি browser tab খুলে খোঁজার মতো।

Application-এর কোডে transaction আর race condition-এর বিস্তারিত ফুল-স্ট্যাক ওয়েব ফ্রেমওয়ার্ক কোর্সের পর্ব ৪৩-এ; concurrency-র test-এর বড় আলোচনা আসবে পর্ব ৬২ (Concurrency আর async testing — race, সময় আর determinism)-এ।

৭ · Data quality check আর query-র test

§২-এর query-গুলো শুধু test suite-এ নয়, চালু system-এও কাজের। অনেক দল প্রতি রাতে এমন check চালায়, আর কোনো query খালি না এলে সতর্কবার্তা পাঠায়: পেমেন্টের ledger আর order-এর মোটের মিল (reconciliation), item ছাড়া order, ঋণাত্মক স্টক। এগুলোকে বলে data quality check. এর কয়েকটি প্রচলিত মাত্রা:

মাত্রাপ্রশ্নবাজারঘরে check
পূর্ণতা (completeness)যা থাকার কথা, আছে?প্রতিটি order-এ অন্তত একটি item; ঠিকানা ফাঁকা নয়
অনন্যতা (uniqueness)যা একবার থাকার কথা, একবারই?এক order-এ একটি charge; এক ক্রেতা-পণ্যে একটি review
বৈধতা (validity)মান নিয়মের ধরনে আছে?Phone-এর ধরন; stars ১–৫; district ১২টির একটি
সামঞ্জস্য (consistency)দুই জায়গার একই তথ্য মেলে?Subtotal = item-এর যোগফল; ইতিহাসের শেষ অবস্থা = order-এর অবস্থা
সময়োপযোগিতা (timeliness)Data যথেষ্ট নতুন?“paid” order ৪৮ ঘণ্টার বেশি pack হয়নি

বড় data-র জন্য এমন check লেখার tool আছে: Great Expectations আর dbt-র test (dbt-তে চারটি generic test আগে থেকে থাকে: unique, not_null, accepted_values, relationships); দুটিই Apache 2.0 licence-এর। ভেতরের ধারণা §২-এর query-ই। Data pipeline-এর test নিয়ে বিস্তারিত পর্ব ৭১ (Data pipeline আর ML model-এর testing)-এ, আর test data তৈরি ও masking পর্ব ৬৫-এ।

Query নিজেই যখন test-এর লক্ষ্য

রিপোর্ট, খোঁজা আর dashboard-এর query-ও কোড, তাই তারও test লাগে। ভালো query test-এর data ছোট, হাতে বানানো, আর প্রত্যাশিত ফল আগে থেকে হিসাব করা। কয়েকটি চেনা ফাঁদ, যার জন্য data-য় ইচ্ছা করে ঘর রাখুন: NULL (গড়ে NULL বাদ পড়ে, COUNT(col) তা গোনে না); ফাঁকা ফল (কোনো review না থাকলে গড় “০” নয়, “নেই”); JOIN-এ সারি দ্বিগুণ হওয়া (এক order-এর দুই item-এর সাথে join করে মোট যোগ করলে মোট দ্বিগুণ); ORDER BY ছাড়া ক্রম ধরে নেওয়া; পূর্ণসংখ্যার ভাগ (SQLite-এ 7 / 2 = ৩); আর বাংলা লেখায় LIKE-এর বড়-ছোট হাতের নিয়ম, যা বাংলায় খাটে না। বাজারঘরের গড় rating-এর নিয়ম (AC5: ৪.২৫ থেকে “4.3”, half up) একটি ভালো উদাহরণ: SQL-এর ROUND engine-ভেদে আলাদা আচরণ করতে পারে, তাই প্রত্যাশা লিখিত নিয়ম থেকে নিন (পর্ব ১৫), কোনো engine-এর ফল থেকে নয়।

আরও গভীরে: Concurrency-র anomaly নিয়ে সবচেয়ে প্রভাবশালী লেখাগুলোর একটি Hal Berenson, Phil Bernstein, Jim Gray ও সহকর্মীদের ১৯৯৫ সালের “A Critique of ANSI SQL Isolation Levels”, যা দেখায় standard-এর সংজ্ঞায় lost update আর write skew-এর মতো ঘটনা বাদ পড়েছে, আর snapshot isolation-কে আলাদা level হিসেবে বর্ণনা করে। পরে Jepsen নামের একটি স্বাধীন প্রকল্প বিভিন্ন database-এ এমন anomaly আসলেই ঘটে কি না, তা পরীক্ষা করে প্রকাশ করে আসছে; তাদের কাজ দেখায়, নথিতে লেখা isolation আর বাস্তব আচরণ সবসময় এক নয়। Migration-এর দিকে, Scott Ambler আর Pramod Sadalage-এর Refactoring Databases (২০০৬) চালু database-এ ছোট, test-করা, ধাপে ধাপে বদলের চর্চাকে একটি পদ্ধতি হিসেবে সাজায়, যা আজকের expand-contract-এর ভিত্তি।

পরীক্ষা ও ইন্টারভিউতে: বিশ্ববিদ্যালয়, চাকরি বা ইন্টারভিউর পরীক্ষায় এমন প্রশ্ন চেনা:

  • Database testing-এ কী কী যাচাই করেন? — Constraint, trigger, migration, query-র ফল, transaction আর concurrency, আর কাজের পরের data integrity (orphan, duplicate, যোগফল)।
  • Orphan record কীভাবে খুঁজবেন? — Child টেবিল থেকে parent-এ LEFT JOIN, তারপর parent-এর key IS NULL; বা NOT EXISTS. NOT IN NULL থাকলে ভুল ফল দেয়।
  • Lost update কী, কীভাবে ঠেকাবেন? — দুই transaction একই মান পড়ে আলাদা হিসাব লেখে, প্রথমটির লেখা হারায়; atomic update, row lock বা optimistic locking (version নম্বর)।
  • Migration-এর test কীভাবে করবেন? — Seed-এর কপিতে up চালিয়ে backfill-এর মান আলাদা পথে হিসাব করা প্রত্যাশার সাথে মেলানো, down চালিয়ে schema আর সারি ফেরা যাচাই, আর খালি database-এ সব migration শুরু থেকে চালানো।
  • API test থাকলে database test কেন? — উত্তর ঠিক থেকেও database-এ ভুল জমা হতে পারে (দুবার charge, ভুল delivery); পার্শ্বফল আর data-র নিয়ম শুধু database-এ দেখা যায়।

৮ · AI-এর যুগে database test

AI assistant SQL খুব দ্রুত লেখে, আর ঠিক এই কারণে তার ভুলগুলো চেনা দরকার। প্রথম, সে আপনার schema না দেখে কলাম আর টেবিলের নাম বানায় (order_total, customer_id), আর test প্রথম run-এই ভুল কারণে লাল হয়, বা আরও খারাপ, একটি ভুল টেবিলে খালি ফল পেয়ে সবুজ হয়। দ্বিতীয়, orphan খুঁজতে NOT IN, যা NULL-এ নিঃশব্দে কিছুই ফেরত দেয় না। তৃতীয়, প্রত্যাশিত মান database থেকেই নেওয়া (“এখন যা আছে তা-ই ঠিক”), ফলে ভুল backfill-ও সবুজ। চতুর্থ, শুধু happy path-এর data: qty সবসময় ১, কোনো NULL নেই, কোনো বাতিল order নেই। পঞ্চম, test-এর fixture-এ ঢালাও DROP TABLE বা DELETE, যা ভুল connection string পেলে আসল data মুছে দিতে পারে। আর শেষে, test না চালিয়েই “সব invariant সবুজ” দাবি।

AI-কে কাজ দিন schema আর নিয়ম হাতে দিয়ে, সীমা বেঁধে:

নিচে বাজারঘরের SQLite schema (CREATE TABLE-গুলো) আর ব্যবসার নিয়ম দিলাম। প্রতিটি নিয়মের জন্য একটি SQL query লেখো, যা নিয়ম ভাঙা সারিগুলো ফেরত দেয় (সুস্থ database-এ খালি)। নিয়ম: ১. শুধু schema-তে থাকা টেবিল আর কলাম ব্যবহার করবে; সন্দেহ হলে প্রশ্ন করো, নাম বানাবে না। ২. Orphan খুঁজতে NOT IN নয়, NOT EXISTS বা LEFT JOIN … IS NULL. ৩. প্রতিটি query-র সাথে একটি ছোট seed data দাও, যাতে নিয়মটি একবার ভাঙে, আর qty ২ বা বেশি, NULL আর বাতিল order-এর ঘর থাকে। ৪. প্রত্যাশিত ফল লিখিত নিয়ম থেকে হিসাব করবে, database-এর বর্তমান মান থেকে নয়। ৫. Test কোডে কোনো DROP, DELETE বা UPDATE থাকবে না; connection read-only (mode=ro). ৬. Query চালিয়েছ বলে দাবি করবে না; শুধু বলো কোন seed-এ কোন সারি আশা করি।

AI-এর লেখা database test হাতে পেলে — যাচাইয়ের চেকলিস্ট

ভাবনার প্রশ্ন

প্রতিটি প্রশ্ন নিয়ে নিজে কিছুক্ষণ ভাবুন, তারপর “→ উত্তর” চাপুন।

প্র ০১দোকানের খাতা মেলানোর পুরোনো রীতি, double-entry bookkeeping, database-এর invariant check-এর সাথে কীভাবে সম্পর্কিত?

Double-entry bookkeeping-এর মূল ভাবনা হলো প্রতিটি লেনদেন দুই জায়গায় লেখা: এক খাতে জমা, আরেক খাতে খরচ, আর দুই দিকের যোগফল সবসময় সমান। এই পদ্ধতি মধ্যযুগের ইতালীয় বণিকদের মধ্যে চালু ছিল; ১৪৯৪ সালে Luca Pacioli-র গণিতের বই Summa de arithmetica-য় এর একটি বিস্তারিত বিবরণ ছাপা হয়, আর সেই বই পদ্ধতিটিকে ইউরোপে ছড়াতে বড় ভূমিকা রাখে বলে সাধারণত ধরা হয়। পদ্ধতিটির শক্তি একটি সহজ invariant-এ: দুই দিক না মিললে কোথাও ভুল আছে। হিসাবরক্ষক ভুলটি কোথায় তা না জেনেও জানেন যে ভুল আছে।

এই পর্বের SQL assertion ঠিক এই কৌশল। Order-এর subtotal আর item-গুলোর যোগফল একই তথ্য দুই জায়গায়; মিল না হলে কোথাও ভুল। Wallet-এর ledger আর order-এর মোট আরেকটি জোড়া। §৪-এর সত্যিকারের run-এ API-র উত্তর ঠিক ছিল, কিন্তু ledger-এ order ৯-এর দুটি charge ছিল: ঠিক যেন ক্যাশবাক্সে খাতার চেয়ে বেশি টাকা ঢুকেছে, অর্থাৎ ক্রেতার কাছ থেকে বেশি নেওয়া হয়েছে।

ব্যাংক আর পেমেন্টের জগতে এই মেলানোর নাম reconciliation, আর এটি প্রতিদিনের রুটিন: নিজের ledger আর অন্য পক্ষের হিসাব মিলিয়ে অমিলগুলো আলাদা করে তদন্ত। Software-এর দিক থেকে শিক্ষাটি হলো, যে তথ্য দুই জায়গায় থাকে, তার মিল একটি বিনামূল্যের test. Database normalization (DBMS ও SQL কোর্সে) সাধারণত একই তথ্য দুবার রাখা এড়াতে বলে; কিন্তু যেখানে কারণে রাখা হয় (subtotal-এর মতো হিসাব জমা রাখা, যাতে পরে পণ্যের দাম বদলালেও পুরোনো বিল না বদলায়), সেখানে মিলের check আবশ্যিক।

আরেকটি দিক: হিসাবরক্ষকেরা মিলানো শুধু বছরে একবার করেন না, প্রতিদিন করেন, কারণ ভুল যত পুরোনো হয়, তার উৎস খোঁজা তত কঠিন। Data quality check-ও তাই রাতে বা প্রতি ঘণ্টায় চালানো ভালো, শুধু release-এর আগে নয়।

মূল উপলব্ধি: একই তথ্য দুই জায়গায় থাকলে তাদের মিল একটি invariant; পাঁচশো বছরের পুরোনো খাতা মেলানোর রীতিই আজকের SQL assertion.

প্র ০২SQLite foreign key-এর যাচাই নিজে থেকে বন্ধ রাখে কেন, আর “default” নিয়ে এটি tester-কে কী শেখায়?

SQLite বহু বছর REFERENCES লেখা মেনে নিত, কিন্তু প্রয়োগ করত না। প্রয়োগের ক্ষমতা আসে ৩.৬.১৯ সংস্করণে (২০০৯ সালে)। তখন একটি সমস্যা দেখা দিল: লক্ষ লক্ষ app-এর database-এ এমন schema আগে থেকেই আছে, যেখানে REFERENCES লেখা কিন্তু data হয়তো তা মানে না, কারণ এতদিন কেউ যাচাই করেনি। যাচাই হঠাৎ চালু করলে সেই app-গুলো আপডেটের পরে ভুল দিতে শুরু করত। তাই SQLite-এর নথি অনুযায়ী পুরোনো program-এর সাথে সামঞ্জস্যের জন্য foreign key-এর যাচাই নিজে থেকে বন্ধ, আর প্রতিটি connection-এ PRAGMA foreign_keys = ON দিয়ে চালু করতে হয়।

সিদ্ধান্তটি engineering-এর দিক থেকে যুক্তিসঙ্গত, কিন্তু এর একটি পার্শ্বফল আছে: নতুন developer schema দেখে ধরে নেন সম্পর্কগুলো পাহারায় আছে। §৩-এর block ঠিক এটি দেখায়: একই schema, একই REFERENCES, শুধু pragma-র পার্থক্যে একটি test সবুজ, আরেকটি লাল। আর বাস্তবে pragma ভুলে যাওয়া সহজ, কারণ এটি schema-র অংশ নয়, connection-এর; app-এর মূল কোডে থাকলেও একটি ছোট admin script বা data import tool নতুন connection খুলে তা বাদ দিতে পারে।

Tester-এর জন্য শিক্ষা সাধারণ: default-গুলো কখনো ধরে নেবেন না, test করুন। একই ধরনের উদাহরণ আরও আছে। SQLite সাধারণ টেবিলে কলামের ধরন কড়াভাবে মানে না (৩.৩৭ থেকে STRICT টেবিল আছে)। MySQL-এর পুরোনো সংস্করণে কিছু sql_mode বন্ধ থাকলে ভুল মান নিঃশব্দে কেটে-ছেঁটে জমা হতো। Database-এর isolation level-এর default engine-ভেদে আলাদা। প্রতিটি ক্ষেত্রে schema বা কোড পড়ে “নিরাপদ” মনে হয়, অথচ আসল আচরণ নির্ভর করে একটি সেটিং-এর ওপর।

তাই একটি ভালো database test suite-এ একটি ছোট “পরিবেশের test” রাখা চলে: প্রতিটি connection-এ foreign key চালু কি না (PRAGMA foreign_keys পড়ে দেখা), schema-য় প্রত্যাশিত constraint আছে কি না, isolation level কী। এগুলো এক সেকেন্ডের কম সময় নেয়, আর অনেক রহস্যময় bug আগেই ঠেকায়।

মূল উপলব্ধি: Schema-য় লেখা নিয়ম আর প্রয়োগ হওয়া নিয়ম এক নয়; constraint-এর অস্তিত্ব নয়, তার আচরণ test করুন।

প্র ০৩ANSI SQL-এর isolation level-এর সংজ্ঞা নিয়ে ১৯৯৫ সালের সমালোচনা কী বলেছিল, আর concurrency test করার সময় এটি কেন মনে রাখা দরকার?

SQL-এর ১৯৯২ সালের standard (SQL-92) চারটি isolation level সংজ্ঞায়িত করে: Read Uncommitted, Read Committed, Repeatable Read আর Serializable. সংজ্ঞাটি দেওয়া হয় তিনটি “ঘটনা” দিয়ে, কোন level-এ কোনটি ঘটতে পারে না: dirty read, non-repeatable read আর phantom. ১৯৯৫ সালে Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil আর Patrick O'Neil একটি প্রবন্ধে (“A Critique of ANSI SQL Isolation Levels”) দেখান যে এই সংজ্ঞা অস্পষ্ট আর অসম্পূর্ণ। Lost update-এর মতো anomaly তালিকায় নেই, আর তখনকার কিছু বাণিজ্যিক database যে snapshot isolation দিত, তা এই চার level-এর কোনোটিতে ঠিক বসে না। Snapshot isolation-এ lost update ঠেকে, কিন্তু write skew ঘটতে পারে: দুটি transaction দুটি আলাদা সারি বদলায়, প্রত্যেকে একটি শর্ত মেনে, অথচ একসাথে শর্তটি ভাঙে।

এর ব্যবহারিক ফল আজও আছে। দুটি database একই নামের level (“Repeatable Read”) দিতে পারে, কিন্তু ভেতরে আলাদা কৌশলে, তাই আলাদা anomaly ঠেকায়। PostgreSQL-এর Repeatable Read আসলে snapshot isolation, আর সেখানে lost update হওয়ার মতো ক্ষেত্রে দ্বিতীয় transaction একটি serialization ভুল পায়; অন্য engine-এ একই নামের level-এ আচরণ ভিন্ন হতে পারে। PostgreSQL ৯.১ থেকে Serializable level-এ “serializable snapshot isolation” নামে একটি কৌশল ব্যবহার করে, যা write skew-ও ধরে।

Tester-এর জন্য তিনটি শিক্ষা। প্রথম, level-এর নাম দেখে নিশ্চিত হবেন না; আপনার engine-এ, আপনার level-এ, আসল interleaving চালিয়ে দেখুন, ঠিক §৬-এর block-এর মতো। দ্বিতীয়, concurrency-র test-কে deterministic করুন: দুটি connection, নির্দিষ্ট ক্রম, প্রত্যাশিত ফল। এলোমেলো thread আর sleep দিয়ে “হয়তো একসাথে হবে” ধরনের test একশোবারে একবার লাল হয়, আর সেটি flaky বলে উপেক্ষিত হয়। তৃতীয়, isolation-এর ভরসায় না থেকে কোডের নকশা দেখুন: atomic update বা version-ভিত্তিক optimistic locking অনেক ক্ষেত্রে level-এর ওপর নির্ভরতা কমায়।

পরে Jepsen-এর মতো স্বাধীন প্রকল্পগুলো বিভিন্ন database-এর দাবি করা isolation আসলেই টেকে কি না পরীক্ষা করে অনেক অমিল প্রকাশ করেছে। এর মানে, নথি পড়া যথেষ্ট নয়; test-ই শেষ কথা।

মূল উপলব্ধি: Isolation level-এর নাম একটি প্রতিশ্রুতি, প্রমাণ নয়; নিজের engine-এ নির্দিষ্ট interleaving চালিয়ে anomaly আছে কি না দেখুন।

প্র ০৪App-এর কোডে validation থাকলে database-এ constraint কেন? “uniqueness validation”-এর race condition এ বিষয়ে কী শেখায়?

একটি দীর্ঘ বিতর্ক আছে: ব্যবসার নিয়ম কোথায় থাকবে, app-এর কোডে না database-এ? কিছু framework-এর সংস্কৃতিতে database-কে “বোবা ভান্ডার” ধরা হতো, আর সব যাচাই model-এর কোডে। কিন্তু একটি নিয়মের ক্ষেত্রে এই ভাবনা স্পষ্টভাবে ভাঙে: অনন্যতা। Ruby on Rails-এর নিজের নথিতে বলা আছে, model-এর uniqueness validation একা অনন্যতার নিশ্চয়তা দেয় না, কারণ দুটি অনুরোধ একসাথে “এমন সারি আছে কি?” জিজ্ঞেস করে দুজনেই “না” পেতে পারে, তারপর দুজনেই লেখে। নথিটি তাই database-এ একটি unique index রাখার পরামর্শ দেয়।

এটি §৬-এর lost update-এর আত্মীয়: check-then-act, অর্থাৎ আগে যাচাই, পরে কাজ, আর মাঝের ফাঁকে অন্য কেউ ঢুকে পড়ে। বাজারঘরের AC4 (একজন ক্রেতা একটি পণ্যে একটিই review) ঠিক এমন নিয়ম। App-এর কোড আগে দেখে review আছে কি না, আর এটি ব্যবহারকারীকে সুন্দর একটি 409 বার্তা দেয়; কিন্তু শেষ পাহারা database-এর UNIQUE (product_id, user_id). দুটি একসাথে রাখাই সঠিক নকশা: app-এর যাচাই ভালো অভিজ্ঞতার জন্য, database-এর constraint সত্যের জন্য।

আরও কারণ আছে। Database-এ শুধু একটি app লেখে না: admin script, data import, রাতের batch job, অন্য দলের service. প্রত্যেকে আলাদা কোড, আলাদা যাচাই, আর কেউ একজন ভুল করবেই। Constraint সবার জন্য একই নিয়ম প্রয়োগ করে। আর constraint-এর ভাঙা সাথে সাথে, ঠিক যে লেখাটি ভুল তার মুহূর্তে ধরা পড়ে; app-এর ভুল প্রায়ই সপ্তাহ পরে একটি রিপোর্টে দেখা যায়, যখন উৎস খোঁজা কঠিন।

Tester-এর দিক থেকে এর মানে দুই স্তরের test. App-এর স্তরে: দ্বিতীয় review-তে 409 আর সঠিক বার্তা। Database-এর স্তরে: app-কে পাশ কাটিয়ে সরাসরি দ্বিতীয় সারি ঢোকালে UNIQUE থামায় কি না (§৩-এর block). আর একটি concurrency test: দুটি review একসাথে পাঠালে ঠিক একটি জমা হয়, অন্যটি 409. এই তৃতীয়টি না থাকলে race-এর ফাঁক থেকে যায়।

মূল উপলব্ধি: App-এর validation ব্যবহারকারীর জন্য, database-এর constraint সত্যের জন্য; check-then-act-এর ফাঁক শুধু database-এর নিয়মই বন্ধ করে।

প্র ০৫অনেক দল down migration লেখা বন্ধ করে “শুধু সামনে” (forward-only) migration-এ গেছে। কেন, আর তাহলে migration-এর test-এর রূপ কী হয়?

Down migration-এর ধারণাটি আকর্ষণীয়: কিছু ভুল হলে এক command-এ আগের অবস্থায় ফেরা। Rails-এর মতো framework migration-কে শুরু থেকেই up আর down জোড়ায় ভেবেছে। কিন্তু বাস্তবে down প্রায়ই প্রতিশ্রুতির চেয়ে কম দেয়। একটি কলাম মোছার migration-এর down কলামটি ফেরত আনতে পারে, কিন্তু তার data নয়, কারণ data আর নেই। দুটি কলাম একটিতে মেলানোর পরে আবার ভাগ করা অনেক সময় অসম্ভব। আর up চলার পরে নতুন কোড যে নতুন data লিখেছে, down তার কী করবে, তা প্রায়ই অস্পষ্ট।

তাই অনেক দল একটি ভিন্ন নীতি নেয়: production-এ কখনো পেছনে নয়, শুধু সামনে। ভুল হলে একটি নতুন migration লিখে ঠিক করা হয়। এই নীতি কাজ করে তখনই, যখন প্রতিটি migration ছোট আর নিজে নিরাপদ, অর্থাৎ expand-contract-এর ধাপের মতো: কলাম যোগ (মোছা নয়), দুই জায়গায় লেখা, backfill, তারপর অনেক পরে, যখন নিশ্চিত, পুরোনো অংশ মোছা। প্রতিটি ধাপে পুরোনো আর নতুন কোড দুটিই চলতে পারে, তাই app-এর deploy ফেরত নেওয়া যায় database না ছুঁয়েই। Scott Ambler আর Pramod Sadalage-এর Refactoring Databases (২০০৬) এই “transition period”-এর ভাবনাকে বিস্তারিত সাজিয়েছিল।

Test-এর রূপও সেই অনুযায়ী বদলায়। Down-এর test (§৫-এর তৃতীয় test) তখনো কাজের, বিশেষ করে development-এ, যেখানে developer-রা migration আগে-পিছে করেন; কিন্তু মূল জোর পড়ে অন্য তিনটিতে: (ক) backfill-এর মান আলাদা পথে হিসাব করা প্রত্যাশার সাথে মেলা, বাস্তবসম্মত data-য় (qty ২+, NULL, বাংলা লেখা); (খ) প্রতিটি ধাপের পরে পুরোনো সংস্করণের app-এর test-ও সবুজ, যাতে deploy-এর মাঝের মিনিটগুলো নিরাপদ; (গ) বড় টেবিলে migration-এর সময় আর lock মাপা, কারণ test-এর ৭টি সারিতে যা মুহূর্তে শেষ, কোটি সারিতে তা ঘণ্টা নিতে পারে আর checkout আটকে দিতে পারে।

বাজারঘরের item_count-এর উদাহরণে forward-only পথ: migration ০০০৭ শুধু কলাম যোগ আর backfill, যা পুরোনো কোড উপেক্ষা করে। ভুল backfill ধরা পড়লে ০০০৮ নতুন করে সঠিক মান লেখে। কলামটি কখনো মোছার দরকার হলে তা আরেকটি আলাদা, পরের migration.

মূল উপলব্ধি: Down migration data ফেরত আনে না; ছোট, সামনে-চলা, প্রতিটি ধাপে পুরোনো-নতুন দুই কোডের সাথে চলতে পারা migration আর তার data-র test-ই আসল নিরাপত্তা।

অনুশীলন

কাগজ-কলমে করুন, তারপর উত্তর মিলিয়ে নিন। চাইলে উপরের SQL block-এ নিজের query চালিয়ে যাচাই করতে পারেন।

  1. Spec থেকে check (test design): বাজারঘরের review-এর নিয়ম: AC1 শুধু delivered order-এর পণ্যে review; AC2 stars ১ থেকে ৫; AC3 লেখা ১০ থেকে ৫০০ অক্ষর (code point); AC4 একজন ক্রেতা একটি পণ্যে একটিই review. টেবিল: reviews(id, product_id, user_id, order_id, stars, text), orders(id, user_id, status), order_items(order_id, product_id, qty). প্রতিটি নিয়মের জন্য একটি SQL assertion লিখুন (নিয়ম ভাঙা সারি ফেরত দেবে), আর বলুন কোনটি database-এর constraint দিয়েও পাহারা দেওয়া যায়।
    -- AC1: review of an order that is not delivered, or not the reviewer's own,
    --      or an order that does not contain the product
    SELECT r.id FROM reviews r JOIN orders o ON o.id = r.order_id
     WHERE o.status != 'delivered' OR o.user_id != r.user_id
        OR NOT EXISTS (SELECT 1 FROM order_items i
                       WHERE i.order_id = r.order_id AND i.product_id = r.product_id);
    -- AC2
    SELECT id FROM reviews WHERE stars NOT BETWEEN 1 AND 5 OR typeof(stars) != 'integer';
    -- AC3 (SQLite length() counts characters = code points for TEXT)
    SELECT id FROM reviews WHERE length(trim(text)) NOT BETWEEN 10 AND 500;
    -- AC4
    SELECT product_id, user_id FROM reviews
     GROUP BY product_id, user_id HAVING COUNT(*) > 1;

    Constraint দিয়ে পাহারা: AC2 (CHECK (stars BETWEEN 1 AND 5), বাজারঘরের schema-য় আছে) আর AC4 (UNIQUE (product_id, user_id), তা-ও আছে)। AC3-ও CHECK-এ লেখা যায়, তবে “trim-এর পরে” আর “code point” মিলিয়ে লেখা কঠিন, আর engine-ভেদে length-এর অর্থ আলাদা (কিছু engine byte গোনে), তাই সাধারণত app-এ থাকে আর এখানে data check. AC1 অন্য টেবিলের অবস্থার ওপর নির্ভর করে; সাধারণ CHECK তা পারে না (trigger পারে), তাই এটি data check হিসেবেই সবচেয়ে কাজের। লক্ষ করুন AC1-এর query-তে NOT EXISTS, NOT IN নয়। AC3-এর check একটি SQLite-এ ছোট data দিয়ে চালিয়ে মেলানো হয়েছে: ৯ অক্ষরের লেখা ধরা পড়ে, দুই পাশে space-সহ ১০ অক্ষর পড়ে না।

  2. ব্যর্থতা পড়ুন (read the failure): দুধের স্টক ৫. দুটি checkout একসাথে: A ২টি, B ৪টি কিনছে, দুটিই “পড়া, হিসাব, তারপর লেখা” কোডে, আর ক্রম A পড়ে, B পড়ে, A লেখে, B লেখে। (ক) শেষে database-এ স্টক কত? (খ) সঠিক আচরণ কী হওয়া উচিত ছিল? (গ) Atomic, পাহারা-সহ update (stock = stock - ? WHERE stock >= ?) একই ক্রমে চালালে প্রতিটি update কয়টি সারি বদলায়, আর শেষ স্টক কত?

    (ক) A আর B দুজনেই ৫ পড়ে। A লেখে ৫ − ২ = ৩, তারপর B লেখে ৫ − ৪ = ১. শেষ স্টক ১, অথচ বিক্রি হয়েছে ৬টি, যা গুদামে ছিলই না: lost update আর oversell একসাথে। (খ) মোট চাহিদা ৬, স্টক ৫; তাই একটি checkout সফল আর অন্যটি “স্টক নেই” পাওয়ার কথা। (গ) A-র update: স্টক ৫ ≥ ২, এক সারি বদলায়, স্টক ৩. B-র update: ৩ ≥ ৪ মিথ্যা, শূন্য সারি বদলায়; app rowcount ০ দেখে B-কে “স্টক নেই” বলে। শেষ স্টক ৩। সংখ্যাগুলো একটি scratch script-এ SQLite দিয়ে চালিয়ে মেলানো হয়েছে।

  3. Test-এর ভুল খুঁজুন (find the bug): একজন সহকর্মী item ছাড়া order খোঁজার এই test লিখেছেন, আর বলছেন, “মাসের পর মাস সবুজ, আমাদের data পরিষ্কার।” অথচ পরে একটি রিপোর্টে item ছাড়া order পাওয়া গেল। Item-এর টেবিলে একটি পুরোনো import-এর কারণে order_id ফাঁকা (NULL) একটি সারি আছে। দুটি সমস্যা খুঁজুন।
    def test_no_order_without_items():
        conn = sqlite3.connect("postgres-prod-replica.db")
        rows = conn.execute("SELECT id FROM orders WHERE id NOT IN "
                            "(SELECT order_id FROM order_items)").fetchall()
        assert rows == []

    সমস্যা ১, NOT IN আর NULL: Subquery-র ফলে একটি NULL থাকলে id NOT IN (…, NULL) কোনো সারির জন্যই সত্য হয় না (ফল “অজানা”), তাই query সবসময় খালি, test সবসময় সবুজ। একটি scratch SQLite-এ order ৮, ৯, ১০ আর item (৮), (১০), (NULL) দিয়ে চালিয়ে মেলানো: NOT IN দেয় খালি তালিকা, NOT EXISTS দেয় ৯. সমাধান: WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id), আর আলাদা একটি check, “order_id NULL এমন item”, যা নিজেই একটি data-ভুল। সমস্যা ২, ঠিকানা: নাম দেখে মনে হচ্ছে production-এর একটি কপি, আর connection read-only নয়। Test চলা উচিত test database-এ, seed থেকে; production-এর data-য় নজরের জন্য আলাদা, অনুমোদিত, read-only data quality job (masking-সহ) হওয়া উচিত। বাড়তি একটি পর্যবেক্ষণ: যে test কখনো লাল হয়নি, তাকে একবার ইচ্ছা করে লাল করে দেখুন (একটি item ছাড়া order বসিয়ে); তাহলেই এই ভুল বহু আগে ধরা পড়ত।

  4. Migration-এর পরিকল্পনা (ভেবে দেখুন): বাজারঘরে users.phone কলামের নাম বদলে users.mobile করতে হবে, আর সাথে নিয়ম: সব নম্বর ১১ অঙ্কের, 01 দিয়ে শুরু। কিছু পুরোনো সারিতে নম্বর +8801712345678 আকারে আছে। App চালু থাকবে, deploy-এর সময় কয়েক মিনিট পুরোনো আর নতুন কোড একসাথে চলে। ধাপগুলো আর প্রতিটি ধাপের test লিখুন।

    Expand-contract-এ: (১) Expand: mobile কলাম যোগ (NULL হতে পারে)। Test: পুরোনো app-এর সব test সবুজ। (২) দুই জায়গায় লেখা: নতুন app phone আর mobile দুটিতেই লেখে, mobile-এ normalize করা রূপে। Test: নতুন ব্যবহারকারী তৈরি করে দুটি কলাম পড়া; invariant “নতুন সারিতে mobile NULL নয়”। (৩) Backfill: পুরোনো সারিতে mobile ভরা, +880 বাদ দিয়ে সামনে 0. Test: seed-এর কপিতে, প্রত্যাশা আলাদা পথে হিসাব করা; data-য় +880…, স্বাভাবিক নম্বর আর দুই পাশে space-ওয়ালা নম্বর রাখা; backfill-এর পরে mobile NOT GLOB '01[3-9]…' এমন সারির সংখ্যা শূন্য, আর যেগুলো ঠিক করা যায়নি সেগুলোর আলাদা তালিকা (মানুষের হাতে দেখার জন্য, নিঃশব্দে বাদ নয়)। (৪) পড়া নতুন কলাম থেকে: Test: login, checkout আর রিপোর্ট নতুন কলামে সবুজ। (৫) Contract: এক release পরে phone মোছা; test: কোডে আর query-তে phone নামের কোনো ব্যবহার নেই, আর খালি database-এ সব migration শুরু থেকে চলে। প্রতিটি ধাপের পরে §৪-এর invariant suite. লক্ষ করুন orders.phone-এ ক্রেতার দেওয়া delivery-র নম্বর আলাদা তথ্য; সেটি এই migration-এর অংশ নয়, আর তা স্পষ্ট করে লেখা একটি ভালো পরিকল্পনার লক্ষণ।

নিজেকে যাচাই করুন

ভুল হলে কারণসহ ব্যাখ্যা পাবেন, আবার চেষ্টাও করতে পারবেন।

ক্রমচালু app-এ একটি কলামের নাম expand-contract পদ্ধতিতে বদলানোর ধাপগুলো সঠিক ক্রমে সাজান (▲▼ চেপে সরান)

  1. নতুন কলাম যোগ করা, পুরোনোটি রেখে
  2. App-কে দুই কলামেই লেখানো
  3. পুরোনো সারিতে নতুন কলাম ভরা (backfill) আর অমিল শূন্য কি না যাচাই
  4. সব পড়া নতুন কলাম থেকে করা
  5. অন্তত এক release পরে পুরোনো কলাম মুছে ফেলা

একটি SQL assertion লেখার প্রচলিত ধরন কোনটি?

  • নিয়ম মানা সব সারি গুনে একটি নির্দিষ্ট সংখ্যার সাথে মেলানো
  • নিয়ম ভাঙা সারি ফেরত দেয় এমন query, যার প্রত্যাশিত ফল খালি
  • পুরো টেবিল print করে চোখে দেখা
  • শুধু API-র উত্তরে status code মেলানো

orders-এ id ৮, ৯, ১০; order_items.order_id-এ মান ৮, ১০ আর একটি NULL. SELECT id FROM orders WHERE id NOT IN (SELECT order_id FROM order_items) কী ফেরত দেয়?

  • শুধু ৯
  • ৮, ৯ আর ১০
  • কোনো সারি নয় (খালি ফল)
  • একটি syntax ভুল

SQLite-এ একটি টেবিলে REFERENCES orders(id) লেখা আছে, কিন্তু connection-এ PRAGMA foreign_keys = ON চালানো হয়নি। অস্তিত্বহীন order ৯৯-এর item ঢোকালে কী হয়?

  • সারিটি নিঃশব্দে ঢুকে যায়
  • IntegrityError: FOREIGN KEY constraint failed
  • টেবিল তৈরির সময়ই ভুল আসে
  • SQLite নিজে order ৯৯ তৈরি করে নেয়

দুধের স্টক ৩। দুটি অনুরোধ “পড়া, হিসাব, তারপর লেখা” কোডে চলে: A ২টি, B ১টি কেনে; ক্রম A পড়ে, B পড়ে, A লেখে, B লেখে। শেষে database-এ স্টক কত, আর এই ভুলের নাম কী?

  • ০, কোনো ভুল নেই
  • ১, dirty read
  • ২, dirty read
  • ২, lost update

একটি migration পুরোনো order-এ item_count ভরছে। এর test-এ প্রত্যাশিত মান কোথা থেকে নেওয়া সবচেয়ে ভালো?

  • Migration-এর একই SQL দিয়ে আবার হিসাব করে
  • Seed data থেকে আলাদা পথে, নিয়ম অনুযায়ী হিসাব করে
  • Production database-এর বর্তমান মান থেকে
  • প্রথমবার migration চালিয়ে যা আসে, সেটি সংরক্ষণ করে

একটি checkout-এর পরে subtotal - discount + delivery = total check সবুজ, কিন্তু ঢাকার ২০০০ টাকার order-এ delivery ৬০ জমা হয়েছে, যেখানে নিয়মে ০. এ থেকে কী শিক্ষা?

  • Check-টির SQL-এ ভুল আছে
  • Delivery ৬০ হলে সমস্যা নেই, মোট মিলেছে
  • সামঞ্জস্যের check আর নিয়মের check আলাদা; দুটিই রাখা দরকার
  • Database-এর check দিয়ে এমন ভুল ধরা যায় না

Database test-এর connection নিয়ে কোন অভ্যাসটি সবচেয়ে নিরাপদ?

  • আলাদা test database, প্রতি test-এর আগে reset, আর test-এর connection read-only
  • Production database-এ, কারণ সেখানেই আসল data
  • Test database, কিন্তু test নিজে সরাসরি সারি লিখে অবস্থা বানায়, app-কে এড়িয়ে
  • সব developer মিলে একটি ভাগ করা test database, কখনো reset ছাড়া

শব্দকোষ

লেখার মধ্যে ডটেড দাগ দেওয়া শব্দে মাউস রাখুন বা চাপুন, অর্থ দেখাবে। এই পর্বের সব নতুন শব্দ এখানে একসাথে:

মনে রাখুন

  1. API-র উত্তর database-এর প্রমাণ নয়: কাজের পরে test database read-only খুলে সারি, যোগফল আর ইতিহাস মেলান; দুবার charge-এর মতো ভুল শুধু সেখানেই দেখা যায়।
  2. প্রতিটি নিয়ম একটি SQL assertion: ভাঙা সারি ফেরত দেয়, প্রত্যাশা খালি; গোনা, যোগফল, orphan (NOT EXISTS, NOT IN নয়) আর duplicate.
  3. Constraint-এর আচরণ test করুন, অস্তিত্ব নয়: SQLite-এ foreign key প্রতিটি connection-এ চালু করতে হয়; app-এর validation-এর পেছনে database-এর UNIQUE শেষ পাহারা।
  4. Migration seed-এর কপিতে: backfill-এর প্রত্যাশা আলাদা পথে হিসাব করা, qty ২-এর মতো ঘরসহ data; down আর expand-contract-এর প্রতিটি ধাপের test.
  5. Concurrency-র test deterministic: নির্দিষ্ট interleaving-এ lost update ধরুন; atomic update, row lock বা optimistic locking দিয়ে সারান, আর data quality check দিয়ে চালু system-এ নজর রাখুন।

পরের পর্বে: Wallet, SMS আর courier-এর মতো বাইরের নির্ভরতা test-এ কীভাবে আলাদা করবেন: stub, mock আর service virtualization, mock server, latency আর timeout-এর অনুকরণ। পর্ব ৩৪ (Service virtualization — mock server আর নির্ভরতা আলাদা করা).

আরও পড়ুন · ABCL TECH-এ আপনার পরবর্তী পদক্ষেপ

এই পর্বটি শেয়ার করুন:
এই পর্বে ভুল পেয়েছেন? জানান