ORMs, Drivers, Migrations & Polyglot Persistence

ORM, driver, migration ও polyglot persistence — কোর্সের শেষ অধ্যায়

Read: ~50 min Intermediate 12 practice problems Course finale

1. From SQL on a Whiteboard to a Real Application

Across the last 51 modules we treated the database almost as a place where queries are typed by humans. In production, of course, queries come from code — Python, JavaScript, Java, Go — and the line between "the app" and "the database" is mediated by three layers: a driver, sometimes an ORM, and a migration tool that evolves the schema safely over time.

এই ৫১ module-এ আমরা database-কে যেন একটি জায়গা ভেবেছি যেখানে মানুষ সরাসরি SQL টাইপ করে। বাস্তব production-এ কিন্তু query আসে কোড থেকে — Python, Node, Java, Go। App ও DB-র মাঝে তিনটি স্তর থাকে: driver (DB-এর সাথে raw কথা বলা), ORM (object-style API), এবং migration tool (schema নিরাপদে evolve করা)।

And finally — the last big lesson — most real systems do not use one database. A modern food-delivery app uses Postgres for orders, Redis for live driver locations, Elasticsearch for restaurant search, S3 for menu images and BigQuery for analytics. We end the course with that polyglot persistence story.

2. Native Drivers — The Lowest Layer

A driver is the library that knows the DB's wire protocol. It opens TCP connections, marshals data types, and lets your code send raw SQL or commands. Every other higher-level tool sits on top of a driver.

Database	Language	Recommended driver
PostgreSQL	Python	`psycopg3` (modern), `psycopg2` (legacy), `asyncpg` (async)
PostgreSQL	Node.js	`pg` (node-postgres) or `postgres` (porsager)
PostgreSQL	Go	`pgx`
PostgreSQL	Java/Kotlin	JDBC `org.postgresql:postgresql`
MySQL / MariaDB	Python	`mysql-connector-python`, `PyMySQL`
MySQL	Node.js	`mysql2`
MongoDB	Python / Node	`pymongo` / `mongodb`
Redis	Python / Node	`redis-py` / `ioredis`
SQLite	built-in	Python's `sqlite3`, Node's `better-sqlite3`

Driver হলো DB-এর "ভাষা জানা" library। উচ্চ-স্তরের সব tool — ORM, framework, এমনকি GUI client — সবই driver-এর উপরে দাঁড়িয়ে। Driver direct ব্যবহার করলে সবচেয়ে দ্রুত এবং সবচেয়ে কম "magic" — কিন্তু পরিশ্রমও বেশি।

raw_psycopg.py

# Raw psycopg3 — no ORM. Parameterised queries (NEVER string-format SQL).
import psycopg

with psycopg.connect("postgresql://user:pwd@localhost/shop") as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, name, price FROM products WHERE category = %s LIMIT %s",
            ("mobile", 10),
        )
        for row in cur.fetchall():
            print(row)

Never concatenate user input into SQL cur.execute(f"SELECT * FROM x WHERE id = {user_input}") is a SQL-injection vulnerability. Always use parameter placeholders (%s, $1, ?) — the driver handles escaping for you.

3. ORMs — Objects Instead of Rows

An Object-Relational Mapper lets you treat database rows as objects in your programming language. Instead of writing SQL, you call methods like user.orders.create(...) or Post.objects.filter(author=u). The ORM generates the SQL, runs it, and gives you back typed objects.

ORM	Language	Style
SQLAlchemy	Python	Two layers: Core (SQL builder) + ORM (objects). The most powerful Python ORM.
Django ORM	Python	Active-Record style, tightly tied to Django models & migrations.
Prisma	TypeScript / Node	Type-safe schema-first ORM with a generated client and migration engine.
Drizzle ORM	TypeScript	SQL-like, edge-friendly, no runtime overhead.
TypeORM / Mongoose	Node	Decorator-style; Mongoose is for MongoDB.
Hibernate / JPA	Java/Kotlin	The dominant JVM ORM, deeply configurable.
GORM	Go	Ergonomic, struct-tag driven.
Active Record	Ruby (Rails)	The pattern that named the style. Convention over configuration.
Ecto	Elixir	Composable query DSL with explicit changesets.

✅ ORM strengths

Type safety — your editor knows column names.
Less boilerplate, faster CRUD.
Migration tooling usually bundled.
Cross-database portability (the same code on Postgres or MySQL).

⚠️ ORM costs

N+1 queries — the silent killer (next section).
Hides what SQL is actually generated; learning to EXPLAIN is still mandatory.
Complex joins / window functions are often clearer in raw SQL.
Adds a learning curve and a runtime dependency.

ORM-এর মূল লাভ — কম boilerplate, type-safety, migration বিল্ট-ইন। কিন্তু ORM-ই আপনার শিখে আসা SQL ভুলে যাওয়ার অজুহাত নয় — production-এ slow query debug করতে গেলে আপনাকে ORM-এর তৈরি raw SQL ও EXPLAIN পড়তেই হবে।

4. The N+1 Query Problem

The most famous performance bug in ORM-land. You write innocent-looking code:

n_plus_one_bad.py

# Django ORM — looks fine. Actually fires 1 + N queries.
authors = Author.objects.all()         # 1 query
for a in authors:
    print(a.name, a.book_set.count())  # 1 extra query PER author

With 100 authors that is 101 queries. With 10 000, the page times out. The fix is eager loading — tell the ORM "fetch the related rows in the same trip":

n_plus_one_good.py

# Django: prefetch_related (or select_related for FK forward direction)
authors = Author.objects.prefetch_related("books").all()  # 2 queries total

# SQLAlchemy: joinedload / selectinload
from sqlalchemy.orm import selectinload
authors = session.scalars(
    select(Author).options(selectinload(Author.books))
).all()

# Prisma: include
const authors = await prisma.author.findMany({ include: { books: true } });

N+1 কী? ১টি query author-দের আনতে, তারপর প্রতিটি author-এর জন্য আলাদা ১টি query — মোট 1+N query। সমাধান: eager loading — Django-তে select_related/prefetch_related, SQLAlchemy-তে joinedload, Prisma-তে include। সব production app-এর সবচেয়ে common performance bug এটিই।

Detection Tools like Django Debug Toolbar, django-silk, sqlalchemy.engine echo, Prisma's query log, and APMs (Datadog, New Relic) surface N+1 patterns automatically. In CI, libraries like nplusone can fail the test suite when one is detected.

5. Connection Pooling & PgBouncer

Opening a Postgres connection costs ~10 ms and roughly 10 MB of server RAM. Web apps that open a fresh connection per request will saturate the database long before the CPU is full. The answer is connection pooling — keep a small pool of warm connections and hand them out to requests.

In-app pools: SQLAlchemy's QueuePool, HikariCP (Java), pg.Pool (Node).
External pools: PgBouncer (the de-facto standard for Postgres), Pgpool-II, AWS RDS Proxy, Supabase Pooler.
Serverless & edge: short-lived functions can't keep connections alive — use a pooler that maps thousands of clients to a small number of real DB connections.

Figure 52.1 — A pooler turns thousands of fleeting client connections into a handful of long-lived DB sessions.

Connection pool ছাড়া পরিণতি: ১০০০ concurrent user মানে ১০০০ Postgres connection, ১০ GB RAM শুধু idle connection-এ — DB crash অনিবার্য। PgBouncer (বা RDS Proxy) ১০০০-কে ২০-৫০-এ namap করে — DB-র দিকে limited connection, app-এর দিকে যত খুশি।

6. Schema Migrations — Evolving the Database Safely

Every long-lived application changes its schema dozens of times per year — new columns, renamed tables, new indexes. A migration tool records each change as a versioned, code-reviewed file, and applies them in the same order to dev, staging and production.

Tool	Ecosystem	Style
Flyway	JVM	Plain SQL files (`V001__init.sql`, `V002__add_users.sql`) versioned forward-only.
Liquibase	JVM	XML/YAML/JSON or SQL changelogs, supports rollback.
Alembic	SQLAlchemy / Python	Auto-generates migration scripts by diffing models.
Django migrations	Django / Python	Auto-generated from model changes, app-aware.
Prisma Migrate	Node / TS	Schema-first — write the `schema.prisma` & the tool emits SQL.
Drizzle Kit	Node / TS	SQL-first migrations from Drizzle schema.
Active Record migrations	Ruby / Rails	`rails generate migration AddEmailToUsers email:string`.
Atlas	Multi-language	HCL-defined schemas, declarative + versioned, works across DBs.

The three migration commandments

Forward-only: in production, never edit a migration after it has been applied. Add a new one instead.
Idempotent: running a migration twice should be safe (or refuse cleanly).
Reversible during dev, expand-then-contract in prod: deploy a column-add migration before the code that uses it; backfill; deploy the code; drop the old column in a later release.

Migration বাস্তব team-এ schema পরিবর্তনের একমাত্র নিরাপদ উপায়। প্রতিটি change একটি code-reviewed file হয়ে git-এ যায়, একই ক্রমে dev → staging → prod-এ apply হয়। শৃঙ্খলা না থাকলে dev-এ যা চলে তা prod-এ ভেঙে পড়ে।

migrate.ts

// Prisma Migrate workflow
// 1. Edit schema.prisma:
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  fullName  String
  // NEW field below — added in v2 release
  phone     String?
  createdAt DateTime @default(now())
}

// 2. Generate & apply the migration:
//    $ npx prisma migrate dev --name add_phone_to_user
// Prisma writes prisma/migrations/20260510_add_phone_to_user/migration.sql
// and runs it on dev. Same file is checked into git and applied to prod by:
//    $ npx prisma migrate deploy

migration_versions.sql

-- Inspect a migration history table — every framework keeps one of these
SELECT version, applied_at
FROM     schema_migrations
ORDER BY applied_at DESC;

7. Polyglot Persistence — Right Tool for the Job

Imagine a Bangladeshi food-delivery app — call it "PathaoEats". It serves several million users, has hundreds of thousands of menu items, and tracks thousands of riders moving in real-time. No single database is great at all of this. The pragmatic answer is to use several, each chosen for what it does best — the practice known as polyglot persistence.

Figure 52.2 — A real polyglot architecture: each store earns its place by doing one job exceptionally well.

Need	Store	Why
Users, orders, payments — ACID truth	PostgreSQL	Transactions, foreign keys, audit trail. Boring & correct.
Live rider GPS pings (every 3 s)	Redis (geo + TTL)	In-memory writes, geo-radius queries, expiring keys.
Restaurant + dish search ("biryani near me")	Elasticsearch / OpenSearch	Full-text, fuzzy match, Bangla + English, geo filters.
Menu photos, invoice PDFs	S3 (object storage)	Cheap per GB, served via CDN; not a job for a database.
"Find dishes similar to what I liked"	pgvector (Module 50)	Embedding-based recommendations.
Daily revenue cohorts, retention curves	BigQuery / ClickHouse	Columnar; scans years of data in seconds.
Sync between systems (CDC)	Kafka / Debezium	Stream changes from Postgres to search & warehouse.

Polyglot persistence-এর মূল নীতি: এক DB সব কাজে best হয় না। Postgres-কে রাখুন "source of truth" হিসেবে — সেখানে user, order, payment-এর ACID record থাকবে। বাকি specialised store (Redis, Elasticsearch, S3, BigQuery, pgvector) Postgres থেকে CDC বা event-driven পদ্ধতিতে data পাবে। ফলে প্রতিটি system নিজের কাজে দ্রুততম থাকে এবং Postgres-এ শুধু সঠিক transactional load পড়ে।

The hidden cost More stores = more failure modes, more learning curves, more on-call surfaces. Add a second database only when a measurable bottleneck demands it. "Three databases" is a milestone, not a starting point.

8. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Driver	Library that speaks the DB wire protocol from your language.	আপনার ভাষা থেকে DB-র protocol-এ কথা বলা library।
ORM	Maps DB rows to language objects and generates SQL for you.	Row-কে object আকারে দেখায়, SQL স্বয়ংক্রিয় তৈরি করে।
N+1 problem	One initial query plus N follow-ups — a hidden performance killer.	১+N query সমস্যা — performance-এর নীরব ঘাতক।
Eager loading	Fetch related rows in the same trip to avoid N+1.	সম্পর্কিত row একসাথে এনে N+1 এড়ানো।
Connection pool	A reusable set of warm DB connections.	আগে থেকেই খোলা connection-এর reusable set।
Migration	A versioned, code-reviewed schema change file.	Schema পরিবর্তনের একটি versioned, reviewed file।
Polyglot persistence	Using multiple databases, each for what it does best.	একাধিক DB ব্যবহার — প্রত্যেকটি যেটিতে ভালো সে কাজে।
CDC	Change Data Capture — streaming row-level changes to other systems.	একটি DB-র পরিবর্তনগুলো অন্য system-এ stream করা।

9. Practice Problems

Twelve final problems that touch every layer — drivers, ORMs, N+1, pooling, migrations and polyglot architecture. The last few are open-ended design questions; defend your reasoning.

শেষ অধ্যায়ের ১২টি problem — driver, ORM, N+1, pooling, migration এবং polyglot architecture সব ছুঁয়ে যায়। শেষেরগুলো design question — উত্তরের সাথে কেন বেছে নিচ্ছেন তার যুক্তি দিন।

In one sentence, define a database driver.

এক বাক্যে driver-এর সংজ্ঞা দিন।

✨ Show Answer

Answer: A driver is a library that speaks a database's wire protocol from inside your programming language, allowing your code to send queries and receive results.

Driver হলো এমন library যা আপনার programming language থেকে DB-এর wire protocol-এ কথা বলে।
Why is cur.execute(f"SELECT * FROM users WHERE name='{name}'") dangerous?

উপরের কোডটি কেন বিপজ্জনক?

✨ Show Answer

Answer: It concatenates user input directly into SQL, opening a SQL injection hole. A malicious name like "' OR '1'='1" would return every row, or worse, drop tables. The fix is parameterised queries: cur.execute("SELECT * FROM users WHERE name = %s", (name,)).

User input সরাসরি SQL-এ যোগ করায় SQL injection ঘটে। সমাধান — placeholder ব্যবহার করে parameterised query।

Spot the N+1 problem and rewrite using SQLAlchemy's selectinload.

N+1 problem ঠিক করে SQLAlchemy selectinload-এ পুনর্লিখন করুন।

✨ Show Answer

ans3.py

from sqlalchemy import select
from sqlalchemy.orm import selectinload

stmt = (select(Customer)
        .options(selectinload(Customer.orders))
        .where(Customer.country == "BD"))

for c in session.scalars(stmt):
    print(c.name, len(c.orders))   # 2 queries total — not 1+N

Your Lambda function opens a fresh Postgres connection on every invocation and you are running out of connections. Two fixes?

প্রতি Lambda invocation নতুন connection খুলছে — দুটি সমাধান বলুন।

✨ Show Answer

Answer: (1) Put a connection pooler in front — AWS RDS Proxy, PgBouncer, Neon's built-in pooler, or Supabase Pooler — so thousands of Lambdas multiplex into a small pool of real connections. (2) Use a serverless-friendly driver such as Neon's HTTP driver or Cloudflare's Hyperdrive that does not require a long-lived TCP connection.

(১) Pooler (RDS Proxy / PgBouncer) ব্যবহার করুন। (২) Serverless-অনুকূল HTTP driver (Neon, Hyperdrive) ব্যবহার করুন।
List the migration history shown in §6 in reverse-chronological order. Why is such a table essential?

§৬-এর migration table reverse-chronological order-এ দেখান এবং এর প্রয়োজনীয়তা ব্যাখ্যা করুন।
✨ Show Answer
ans5.sql
```
SELECT version, applied_at
FROM     schema_migrations
ORDER BY applied_at DESC;
```
Why essential: the table is the single source of truth for "which migrations have already run on this database". Without it, the migration tool cannot know what to apply next, would re-run old migrations, and dev/staging/prod would drift apart silently.

এই table-ই বলে কোন migration ইতিমধ্যে run হয়েছে; এটি ছাড়া tool বুঝতে পারে না পরবর্তীতে কোনটি apply করতে হবে।
Explain "expand-then-contract" migrations in two sentences.

Expand-then-contract migration দুই বাক্যে বুঝিয়ে বলুন।

✨ Show Answer

Answer: First "expand" — add the new column or table without removing the old one, deploy code that writes to both. After all old data has been backfilled and no code uses the old shape, "contract" — drop the old column in a later release. This avoids any moment when running code disagrees with the schema.

প্রথমে expand (নতুন column/table যোগ + পুরোনোও থাকে), তারপর backfill, তারপর পরের release-এ contract (পুরোনো column drop)। ফলে code ও schema কখনো একসাথে desync হয় না।
Write the Prisma schema for a User with id, email, fullName and an optional phone, then the CLI command to apply it.

উপরের requirement অনুযায়ী Prisma schema ও apply করার command লিখুন।
✨ Show Answer
schema.prisma
```
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  fullName  String
  phone     String?
  createdAt DateTime @default(now())
}
```
apply.sh
```
npx prisma migrate dev --name init_user
# In production:
npx prisma migrate deploy
```
Design: which datastores would you use for each part of the PathaoEats app: live rider tracking, restaurant search, order history, menu images, monthly cohort report?

PathaoEats-এর পাঁচটি কাজের জন্য কোন datastore বেছে নেবেন?
✨ Show Answer
Answer:
- Live rider tracking — Redis (GEO commands, TTL, in-memory).
- Restaurant search — Elasticsearch / OpenSearch (full-text + geo + Bangla analyzer).
- Order history (truth) — PostgreSQL with strict ACID.
- Menu images — Amazon S3 served through CloudFront/Cloudflare CDN.
- Monthly cohort report — BigQuery (or ClickHouse), populated via CDC from Postgres.
Live rider — Redis; restaurant search — Elasticsearch; order — Postgres; image — S3; analytics — BigQuery।
Why is "PostgreSQL only, until you really need more" usually the right starting advice?

"শুরুতে শুধু Postgres" — পরামর্শটি কেন প্রায়ই সঠিক?

✨ Show Answer

Answer: Postgres handles relational data, JSON, full-text search, geospatial (PostGIS), vectors (pgvector), pub/sub (LISTEN/NOTIFY) and even queues — all under one ACID engine, one backup, one set of credentials. Adding a second store doubles the operational surface (monitoring, backups, failure modes, hires, on-call). Defer that complexity until a real bottleneck makes the cost-benefit obvious.

Postgres একাই relational, JSON, full-text, geo, vector, pub/sub সব করতে পারে। দ্বিতীয় DB যোগ করা মানেই দ্বিগুণ ops। স্পষ্ট bottleneck না দেখা পর্যন্ত complexity বাড়াবেন না।
Two ways an ORM can mislead a developer about performance.

ORM developer-কে performance সম্পর্কে যে দুটি কারণে বিভ্রান্ত করতে পারে।

✨ Show Answer

Answer: (1) It hides the SQL it generates, so a one-line method call may quietly fan out to dozens of queries (the N+1 trap). (2) It reports "the query took 5 ms" but does not show that the index is being scanned, only used partially, or that a JOIN is producing a Cartesian explosion before LIMIT — visible only in EXPLAIN ANALYZE.

(১) ORM SQL লুকায় — N+1-সহ অনেক বেশি query চলে যেতে পারে। (২) Index scan, JOIN explosion ইত্যাদি ORM-এর timing report-এ দেখা যায় না, EXPLAIN ANALYZE ছাড়া বোঝা কঠিন।
A startup wants to add Redis caching in front of Postgres for hot product reads. List two correctness pitfalls and one mitigation each.

Postgres-এর সামনে Redis cache বসালে দুটি correctness সমস্যা ও তাদের সমাধান বলুন।
✨ Show Answer
Answer:
- Stale cache after a write — when product price updates in Postgres, Redis still serves the old price. Mitigation: cache-aside with explicit invalidation on writes, or short TTLs.
- Cache stampede — when a popular key expires, hundreds of requests miss simultaneously and stampede the DB. Mitigation: a single-flight lock (one fetch, others wait) or pre-warming popular keys.
(১) Stale cache — TTL/invalidation দিয়ে। (২) Cache stampede — single-flight lock বা pre-warming দিয়ে।
Looking back at the whole 52-module course, write 3-5 sentences on what surprised you the most.

পুরো ৫২ module-এর কোর্সে যা সবচেয়ে চমকপ্রদ লেগেছে — ৩-৫ বাক্যে লিখুন।

✨ Show Answer (sample reflection)

Sample reflection: The most striking realisation is that "the database" is not a single thing — it is a 50-year-old layered idea, from B-trees on disk pages to ACID, MVCC, query planners, replication, embeddings and now globally consistent SQL. The second is that boring Postgres can take you remarkably far before any specialised store earns its place. And the third is that real database engineering is less about clever SQL and more about discipline: indexes that match queries, migrations that ship safely, schemas that survive change.

এই কোর্সের সবচেয়ে বড় শিক্ষা — "database" আসলে অনেক স্তরের ৫০ বছরের ধারণা; আর "boring Postgres" বাস্তবে কতদূর যেতে পারে তা চমকপ্রদ। আসল database engineering মানে discipline — সঠিক index, নিরাপদ migration, পরিবর্তনের সাথে টিকে থাকা schema।

Course Finale — You Did It 🎉

From the first lecture on "what is a database?" through the relational model, ER diagrams, SQL, normalisation, indexes, transactions, MVCC, replication, sharding, NoSQL, big-data engines, time-series, graphs, search, vectors, the cloud, and finally how real applications wire these engines together — you have walked the full breadth of database management systems. There are very few software engineers anywhere who have a complete picture of this stack. You now do.

This module wraps up the practical glue: drivers let your code speak to a DB, ORMs turn rows into objects (with the famous N+1 tax), connection pools & PgBouncer keep the DB sane under load, migrations evolve the schema with discipline, and polyglot persistence assembles several stores into a single, scalable product — because no one database is best at everything, and that is fine.

অভিনন্দন — আপনি ৫২টি module পার করে এসেছেন। "Database কী?" থেকে শুরু করে relational model, SQL, normalisation, transaction, replication, sharding, NoSQL, big data, time-series, graph, search, vector, cloud — এবং আজকের শেষ পাঠ: driver, ORM, migration ও polyglot persistence। এই কোর্সে আপনি যেটা শিখলেন সেটা শুধু "SQL সিনট্যাক্স" নয় — কিভাবে data কাঠামোগতভাবে চিন্তা করতে হয়, কিভাবে একটি system বছরের পর বছর সঠিক, দ্রুত, এবং scalable রাখা যায়। এই দৃষ্টিভঙ্গিই আপনাকে অন্য সাধারণ developer থেকে আলাদা করবে।

What next? Take a real project — your final-year thesis, your startup, an open-source issue — and apply five things you learnt here: write a proper ER diagram, normalise to 3NF, add the right indexes, wrap critical paths in transactions, and run the first migration with a tool. That single project will teach more than the next ten tutorials.

ABCL TECH-এর পক্ষ থেকে আপনাকে অভিনন্দন। আপনার পরের প্রজেক্টে এই জ্ঞান কাজে লাগুক — এবং ভবিষ্যতে আপনি যখন কোনো জুনিয়র developer-কে শেখাবেন, এই কোর্সটি তাঁকে recommend করবেন। শুভ কামনা ।

Next → Back to the syllabus — pick a phase to revisit, or build something real with what you have learnt.

Prev: Cloud Databases Back to Syllabus (সিলেবাস)