🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

🔌 धडा 06 — Integration tests: memory मधला खरा database

📍 तुम्ही इथे आहात: 12 पैकी धडा 06 · मागे: lesson-05-test-doubles · पुढे: lesson-07-contract-tests

📦 या ब्रँचमध्ये काय आहे

धडे 01–05, आणि integration tests: तुमचा code एका खऱ्या dependency सोबत — इथे प्रत्येक test साठी memory मध्ये नव्याने तयार केलेला खरा SQLite database. exam/grades.py मधला GradeStore गुण save करतो आणि SQL मध्ये class average मोजतो; त्याच्या पहिल्या version मध्ये असा bug आहे जो Python fake कधीच दाखवू शकला नसता. exam/suites/test_store_sqlite.py आणि exam/demo.py मधले integration().

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

कतरिनाला maths मध्ये 68 मिळाले. दीपिकाला 67. Class average आहे 67.5.

धडा 05 च्या पद्धतीने, दीपिकाने आपले average एका खोट्या marks book वर test केले: एक Python dict. त्याने सांगितले 67.5. ✅

मग ऐश्वर्याने तोच प्रश्न खऱ्या marks book वर चालवला — SQL database. त्याने सांगितले 67. ❌

का? दीपिकाचा SQL म्हणत होता "गुणांची बेरीज करा, किती आहेत त्याने भागा". SQLite मध्ये, पूर्ण संख्येला पूर्ण संख्येने भागले की पूर्ण संख्याच मिळते. 135 ÷ 2 = 67. अर्धा भाग टाकून दिला जातो.

खोट्या marks book ने बेरीज Python मध्ये केली, जिथे 135 / 2 म्हणजे 67.5. म्हणून खोट्या book मध्ये हा bug येऊच शकत नव्हता. फक्त खऱ्यामध्येच येऊ शकत होता.

आणि आणखी एक: खरे book सांगते की प्रत्येक विद्यार्थिनीचा प्रत्येक विषयात एकच mark असतो. कतरिनाचे maths दोनदा save करणे नाकारले जाते. खोट्या book ने ते गुपचूप overwrite केले. 📕

🗺️ आकृती

flowchart LR
    t["🧪 test: Katrina 68, Dipika 67<br/>expected average 67.5"]
    t --> fake["🧸 Python fake store<br/>135 / 2 = 67.5 ✅"]
    t --> real["🗄️ real SQLite :memory:<br/>SUM/COUNT = 67 ❌"]
    real --> fix["AVG(marks) = 67.5 ✅"]
    real --> pk["save Katrina twice<br/>IntegrityError"]
    fake --> ow["save twice<br/>silently overwritten"]

🗺️ काढलेली आवृत्ती + एक lab: https://school-edh.pages.dev/testing/lesson-diagrams.html#l06

❓ काय

Integration test — तुमचा code एखाद्या खऱ्या गोष्टीसोबत चालतो का ते तपासणाऱ्या tests: database, file system, message queue, framework. Unit test पेक्षा हळू, पण जोडणीच्या जागांबद्दल कितीतरी जास्त प्रामाणिक.
Narrow विरुद्ध broad integration — narrow: तुमचा code आणि एक खरी dependency (हा धडा). Broad: एकाच वेळी अनेक खऱ्या services — end-to-end च्या जवळ (धडा 08).
In-memory database — SQLite चे ":memory:" RAM मध्ये पूर्ण database तयार करते, जो connection बंद झाल्यावर नाहीसा होतो. Laptop वर प्रत्येक test साठी नवा database तयार करायला एका millisecond पेक्षा खूपच कमी वेळ लागतो, म्हणून प्रत्येक test ला स्वच्छ data मिळतो.
SQL मधले integer division — SQLite मध्ये (आणि PostgreSQL मध्ये), INTEGER / INTEGER चे उत्तर INTEGER येते: 7/2 म्हणजे 3. AVG() floating-point निकाल देते; 7.0/2 सुद्धा.
Constraint — database पाळायला लावतो असा नियम: PRIMARY KEY (student, subject) त्याच विद्यार्थिनी आणि विषयासाठी दुसरी row sqlite3.IntegrityError ने नाकारतो.
Type affinity — SQLite चे columns ढिले typed असतात: INTEGER column मध्ये घातलेला text '71' integer 71 मध्ये बदलला जातो, आणि आकड्यासारखा न दिसणारा text ('absent') सरळ text म्हणूनच साठवला जातो. PostgreSQL integer column साठी 'absent' error देऊन नाकारते. SQLite वरची test म्हणजे PostgreSQL वरची test नव्हे.
Testcontainers — in-memory पर्याय पुरेसा जवळचा नसेल तेव्हा, test run च्या कालावधीपुरता Docker container मध्ये खरा database (किंवा queue) सुरू करणारी library.

🤔 का

कारण अनेक bugs तुमचा code आणि खरी गोष्ट यांच्या मध्ये राहतात: तुम्हाला वाटते त्यापेक्षा वेगळा अर्थ असलेला SQL, fake मध्ये कधीच नसलेली constraint, बदलला जाणारा type, commit न झालेला transaction. Fake म्हणजे dependency बद्दलचा तुमचा विश्वास; integration test तो विश्वास तपासते.

🔧 कसे (या repo मध्ये)

exam/grades.py मधले GradeStore(":memory:") standard library च्या sqlite3 module ने SQLite connection उघडते आणि PRIMARY KEY (student, subject) असलेले marks table तयार करते. class_average_v1() SUM(marks) / COUNT(*) चालवते; class_average() AVG(marks) चालवते. Unittest file setUp मध्ये नवा store तयार करते आणि tearDown मध्ये बंद करते — धडा 04 ची नवी उत्तरपत्रिका, आता पूर्ण database.

🧪 करून पाहा

python3 exam/demo.py integration
python3 - <<'EOF'
import sqlite3
from exam.grades import GradeStore
s = GradeStore()
for who, m in (("Katrina", 68), ("Dipika", 67), ("Aishwarya", 70)): s.save(who, "maths", m)
print("3 pupils: v1", s.class_average_v1("maths"), "· fixed", round(s.class_average("maths"), 2))
print("SQLite says 7/2 =", s.db.execute("SELECT 7/2, 7.0/2, typeof(7/2)").fetchone())
print("empty subject: v1", s.class_average_v1("art"), "· fixed", s.class_average("art"))
s.db.execute("INSERT INTO marks VALUES ('Dipika', 'science', '71')")      # text that looks like a number
s.db.execute("INSERT INTO marks VALUES ('Katrina', 'science', 'absent')")  # text that does not
print("stored types:", s.db.execute("SELECT student, typeof(marks) FROM marks WHERE subject='science' ORDER BY student").fetchall())
EOF
python3 -m unittest exam.suites.test_store_sqlite 2>&1 | tail -1

✅ तपासा — तुम्हाला काय दिसायला हवे

integration छापते:

── Katrina 68 and Dipika 67 in maths; the class average should be 67.5
   unit test with a Python fake store → 67.5 ✅ (Python's / keeps the half)
   integration test, REAL SQLite in memory, v1 SQL SUM/COUNT → 67 ❌ (INTEGER / INTEGER drops it)
   fixed SQL AVG(marks) → 67.5 ✅
   save Katrina's maths twice: fake silently overwrites · SQLite → IntegrityError (the PRIMARY KEY the fake never had)
── exam/suites/test_store_sqlite.py: 3 tests · 0 failing · a fresh :memory: database per test

तुमचा snippet छापतो:

3 pupils: v1 68 · fixed 68.33
SQLite says 7/2 = (3, 3.5, 'integer')
empty subject: v1 None · fixed None
stored types: [('Dipika', 'integer'), ('Katrina', 'text')]

आणि unittest शेवटी छापते:

OK

🏁 तुम्ही आत्ताच काय सिद्ध केले

V1 SQL बहुतेक classes साठी चुकीचा आहे (तीन विद्यार्थिनींसाठी 68.33 ऐवजी 68) आणि fake वापरणाऱ्या unit test ला तो दिसू शकला नाही, कारण fake ने भागाकार Python मध्ये केला. खऱ्या database ने आणखी दोन वर्तने दाखवली ज्यांची स्वतंत्र test व्हायला हवी: रिकामा विषय 0 नव्हे तर None देतो (म्हणून बोलवणाऱ्याने "अजून गुण नाहीत" हाताळायला हवे), आणि SQLite ने text '71' ला आकड्यात बदलले पण INTEGER म्हणून declare केलेल्या column मध्ये 'absent' हा शब्द तसाच ठेवला — PostgreSQL ने ती row नाकारली असती. तुम्ही कोणत्या engine वर test करता त्यावर तुम्हाला काय सापडू शकते ते ठरते.

⚠️ नेहमीच्या चुका

SQL फक्त fake किंवा database driver च्या mock मधूनच test करणे
संपूर्ण suite साठी एकच shared database, म्हणजे tests एकमेकांच्या rows पाहतात
SQLite वर test करणे आणि PostgreSQL वर चालवणे — वेगळे types, वेगळा SQL
logic च्या प्रत्येक शाखेसाठी integration tests (त्या वेगवान unit tests मध्ये ठेवा)
रिकामा case, duplicate case आणि constraint errors test करायला विसरणे

🏭 प्रत्यक्ष वापरात

खऱ्या project वर — Testcontainers सह प्रत्येक test run साठी खरा PostgreSQL (pip install testcontainers[postgres] psycopg; Docker लागतो):

from testcontainers.postgres import PostgresContainer
import psycopg

def test_class_average_on_postgres():
    with PostgresContainer("postgres:16") as pg:
        url = pg.get_connection_url(driver=None)
        with psycopg.connect(url) as conn:
            conn.execute("CREATE TABLE marks (student text, subject text, marks int, PRIMARY KEY (student, subject))")
            conn.execute("INSERT INTO marks VALUES ('Katrina', 'maths', 68), ('Dipika', 'maths', 67)")
            assert conn.execute("SELECT SUM(marks) / COUNT(*) FROM marks").fetchone()[0] == 67      # int / int
            assert float(conn.execute("SELECT AVG(marks) FROM marks").fetchone()[0]) == 67.5

Container सुरू व्हायला काही सेकंद लागतात, म्हणून एका session साठी एकच share करा आणि प्रत्येक test ला शेवटी rolled back होणारा transaction द्या — नवा server न लावता नवा data. हाच pattern Java, Go, .NET आणि Node.js साठीही आहे (Testcontainers कडे प्रत्येकासाठी libraries आहेत), आणि GitHub Actions सारख्या CI services database service container म्हणून चालवू शकतात.

🏭 Production मध्ये हे का महत्त्वाचे: काहीतरी मोजणाऱ्या प्रत्येक query साठी (एक average, एकूण बेरीज, rank), production मध्ये तुम्ही चालवता त्याच database engine आणि version वर किमान एक integration test ठेवा.

⏭️ पुढे

Attendance service दुसऱ्या team ची आहे. ती तुम्ही तुमच्या tests मध्ये चालवू शकत नाही, आणि तुमचा stub खोटे बोलत असू शकतो. Contract tests दोन्ही बाजूंना उत्तर कसे दिसते यावर सहमत करतात.

git checkout lesson-07-contract-tests

🔌 Lesson 06 — Integration tests: a real database in memory

📍 You are here: Lesson 06 of 12 · Previous: lesson-05-test-doubles · Next: lesson-07-contract-tests

📦 What's in this branch

Lessons 01–05, plus integration tests: your code together with a real dependency — here a real SQLite database, created fresh in memory for every test. GradeStore in exam/grades.py saves marks and computes a class average in SQL; its first version has a bug that a Python fake could never show. exam/suites/test_store_sqlite.py and integration() in exam/demo.py.

🧒 Explain like I'm 5

Katrina got 68 in maths. Dipika got 67. The class average is 67.5.

In lesson 05's style, Dipika tested her average with a pretend marks book: a Python dict. It said 67.5. ✅

Then Aishwarya ran the same question against the real marks book — the SQL database. It said 67. ❌

Why? Dipika's SQL said "add up the marks, divide by how many". In SQLite, a whole number divided by a whole number gives a whole number. 135 ÷ 2 = 67. The half is thrown away.

The pretend marks book did the sum in Python, where 135 / 2 is 67.5. So the pretend book could not have this bug. Only the real one could.

And one more: the real book says each pupil has one mark per subject. Saving Katrina's maths twice is refused. The pretend book quietly overwrote it. 📕

🗺️ Diagram

flowchart LR
    t["🧪 test: Katrina 68, Dipika 67<br/>expected average 67.5"]
    t --> fake["🧸 Python fake store<br/>135 / 2 = 67.5 ✅"]
    t --> real["🗄️ real SQLite :memory:<br/>SUM/COUNT = 67 ❌"]
    real --> fix["AVG(marks) = 67.5 ✅"]
    real --> pk["save Katrina twice<br/>IntegrityError"]
    fake --> ow["save twice<br/>silently overwritten"]

🗺️ Drawn version + a lab: https://school-edh.pages.dev/testing/lesson-diagrams.html#l06

❓ What

Integration test — tests that your code works with something real: a database, a file system, a message queue, a framework. Slower than a unit test, much more honest about the seams.
Narrow vs broad integration — narrow: your code and one real dependency (this lesson). Broad: many real services at once — close to end-to-end (lesson 08).
In-memory database — SQLite's ":memory:" creates a full database in RAM that disappears when the connection closes. A fresh one per test costs well under a millisecond on a laptop, so every test gets clean data.
Integer division in SQL — in SQLite (and in PostgreSQL), INTEGER / INTEGER gives an INTEGER: 7/2 is 3. AVG() returns a floating-point result; so does 7.0/2.
Constraint — a rule the database enforces: PRIMARY KEY (student, subject) refuses a second row for the same pupil and subject with sqlite3.IntegrityError.
Type affinity — SQLite's columns are loosely typed: text '71' inserted into an INTEGER column is converted to the integer 71, and text that does not look like a number ('absent') is simply stored as text. PostgreSQL refuses 'absent' for an integer column with an error. A test on SQLite is not a test on PostgreSQL.
Testcontainers — a library that starts a real database (or queue) in a Docker container for the length of a test run, when an in-memory stand-in is not close enough.

🤔 Why

Because many bugs live between your code and the real thing: SQL that means something different from what you think, a constraint the fake never had, a type that is converted, a transaction that is not committed. A fake is your belief about the dependency; an integration test checks the belief.

🔧 How (in this repo)

GradeStore(":memory:") in exam/grades.py opens a SQLite connection with the standard-library sqlite3 module and creates a marks table with a PRIMARY KEY (student, subject). class_average_v1() runs SUM(marks) / COUNT(*); class_average() runs AVG(marks). The unittest file creates a new store in setUp and closes it in tearDown — lesson 04's fresh answer sheet, now a whole database.

🧪 Try it

python3 exam/demo.py integration
python3 - <<'EOF'
import sqlite3
from exam.grades import GradeStore
s = GradeStore()
for who, m in (("Katrina", 68), ("Dipika", 67), ("Aishwarya", 70)): s.save(who, "maths", m)
print("3 pupils: v1", s.class_average_v1("maths"), "· fixed", round(s.class_average("maths"), 2))
print("SQLite says 7/2 =", s.db.execute("SELECT 7/2, 7.0/2, typeof(7/2)").fetchone())
print("empty subject: v1", s.class_average_v1("art"), "· fixed", s.class_average("art"))
s.db.execute("INSERT INTO marks VALUES ('Dipika', 'science', '71')")      # text that looks like a number
s.db.execute("INSERT INTO marks VALUES ('Katrina', 'science', 'absent')")  # text that does not
print("stored types:", s.db.execute("SELECT student, typeof(marks) FROM marks WHERE subject='science' ORDER BY student").fetchall())
EOF
python3 -m unittest exam.suites.test_store_sqlite 2>&1 | tail -1

✅ Verify — what you should see

integration prints:

── Katrina 68 and Dipika 67 in maths; the class average should be 67.5
   unit test with a Python fake store → 67.5 ✅ (Python's / keeps the half)
   integration test, REAL SQLite in memory, v1 SQL SUM/COUNT → 67 ❌ (INTEGER / INTEGER drops it)
   fixed SQL AVG(marks) → 67.5 ✅
   save Katrina's maths twice: fake silently overwrites · SQLite → IntegrityError (the PRIMARY KEY the fake never had)
── exam/suites/test_store_sqlite.py: 3 tests · 0 failing · a fresh :memory: database per test

Your snippet prints:

3 pupils: v1 68 · fixed 68.33
SQLite says 7/2 = (3, 3.5, 'integer')
empty subject: v1 None · fixed None
stored types: [('Dipika', 'integer'), ('Katrina', 'text')]

and unittest ends with:

OK

🏁 What you just proved

The v1 SQL is wrong for most classes (68 instead of 68.33 for three pupils) and a unit test with a fake could not see it, because the fake did the division in Python. The real database also showed two behaviours worth a test of their own: an empty subject gives None, not 0 (so the caller must handle "no marks yet"), and SQLite turned the text '71' into a number but kept the word 'absent' in a column declared INTEGER — PostgreSQL would have refused that row. The engine you test on shapes what you can find.

⚠️ Common mistakes

testing SQL only through a fake or a mock of the database driver
one shared database for the whole suite, so tests see each other's rows
testing on SQLite and running on PostgreSQL — different types, different SQL
integration tests for every branch of logic (put those in fast unit tests)
forgetting to test the empty case, the duplicate case and the constraint errors

🏭 In production

On a real project — a real PostgreSQL per test run with Testcontainers (pip install testcontainers[postgres] psycopg; needs Docker):

from testcontainers.postgres import PostgresContainer
import psycopg

def test_class_average_on_postgres():
    with PostgresContainer("postgres:16") as pg:
        url = pg.get_connection_url(driver=None)
        with psycopg.connect(url) as conn:
            conn.execute("CREATE TABLE marks (student text, subject text, marks int, PRIMARY KEY (student, subject))")
            conn.execute("INSERT INTO marks VALUES ('Katrina', 'maths', 68), ('Dipika', 'maths', 67)")
            assert conn.execute("SELECT SUM(marks) / COUNT(*) FROM marks").fetchone()[0] == 67      # int / int
            assert float(conn.execute("SELECT AVG(marks) FROM marks").fetchone()[0]) == 67.5

Starting a container takes seconds, so share one per session and give each test a transaction that is rolled back at the end — fresh data without a fresh server. The same pattern exists for Java, Go, .NET and Node.js (Testcontainers has libraries for each), and CI services such as GitHub Actions can run a database as a service container.

🏭 Why this matters in production: for every query that computes something (an average, a total, a rank), keep at least one integration test against the same database engine and version you run in production.

⏭️ Next

The attendance service belongs to another team. You cannot run it in your tests, and your stub may be lying. Contract tests make both sides agree on what the answer looks like.

git checkout lesson-07-contract-tests

← Previoustest doubles Next →contract tests

This page is the lesson's README from the lesson-06-integration branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.