Lesson 4 — Beginner

Data & Persistence Basics

Abhi tak tumhari service ka data memory mein tha — restart maaro aur gayab. Persistence ka matlab hai data ka hona application restarts se pare. Is lesson mein relational modeling (tables, keys), PostgreSQL ke saath SQL (CRUD, joins, aggregates), indexes aur query performance, app se DB connect, aur credentials ko code se bahar rakhna dekhoge.

Persistence aur relational data kya hai

Socho: list of dicts bhi data rakh leta hai, lekin restart par gayab, aur jab do cheezein aapas mein judi hon (order kis customer ka hai) toh dict list kaafi nahi padti. Table + keys se data ek jagah rehta hai, juda bhi rehta hai, aur bade par bhi tez dhoondha jaata hai.

Modeling — tables, keys, relationships

CREATE TABLE rooms (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL UNIQUE,
    capacity   INTEGER NOT NULL CHECK (capacity > 0)
);

CREATE TABLE bookings (
    id         BIGSERIAL PRIMARY KEY,
    room_id    BIGINT NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    guest      TEXT NOT NULL,
    starts_at  TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_bookings_room_start ON bookings (room_id, starts_at);

• Primary key: har row ka unique id — kabhi NULL nahi, kabhi repeat nahi. Human-readable naam kabhi key mat banao (naam badalta hai, id nahi).
• Foreign key (REFERENCES): booking ka room_id dusri table ki row ka reference hai. ON DELETE CASCADE = room delete toh uski bookings bhi jaayen — referential integrity.
• NOT NULL, UNIQUE, CHECK: rules jo data pehle se hi tootne na de. DB mein hi rokna application code mein check karne se sasta aur pakka hai.
• Basic normalization: ek hi info (customer ka email) ek jagah rakho, doosri jagah sirf reference. Duplicate likhoge toh update mein ek jagah bhool jaoge.

SQL — CRUD, filters, joins, aggregates

-- CRUD (chhote aur seedhe)
INSERT INTO rooms (name, capacity) VALUES ('Boardroom', 12) RETURNING id;
SELECT id, name, capacity FROM rooms WHERE capacity >= 8 ORDER BY capacity DESC;
UPDATE rooms SET capacity = 14 WHERE id = 3;
DELETE FROM rooms WHERE id = 3;

-- Filter + join + group + aggregate
SELECT r.name, count(b.id) AS bookings
FROM rooms r
LEFT JOIN bookings b ON b.room_id = r.id
WHERE r.capacity > 4
GROUP BY r.name
ORDER BY bookings DESC;

-- Pagination
SELECT * FROM bookings ORDER BY starts_at LIMIT 20 OFFSET 40;

• Read ka pattern: SELECT columns FROM table WHERE filter GROUP BY ... HAVING ... ORDER BY ... LIMIT. Yahi skeleton har query mein hota hai.
• Joins: ON se batata hai kaunsi columns judenge. INNER = sirf match wale rows, LEFT = baayen table ki saari rows (match na ho toh NULL).
• Aggregates: count, sum, avg, min, max — GROUP BY ke saath har group ka. HAVING group par filter lagata hai (WHERE row par).
• Filter SQL mein hi karo — saari rows laakar Python mein dhoondhna har query par pura data network par bhejne jaisa hai.

Indexes aur query performance

-- Plan dekho: query kaise chalegi
EXPLAIN SELECT guest FROM bookings WHERE room_id = 42;
--  -> Seq Scan on bookings   (poori table padhi — badi table par slow)

CREATE INDEX idx_bookings_room ON bookings (room_id);

EXPLAIN SELECT guest FROM bookings WHERE room_id = 42;
--  -> Index Scan using idx_bookings_room   (seedha lookup)

• Index ka kaam: column ka alag sorted directory banata hai — jaise book ka index page vs poora kitab padhna. Lookup seedha ho jaata hai.
• Kis column pe? WHERE, JOIN aur ORDER BY mein aane wale columns (foreign keys aksar). Hamari DDL mein (room_id, starts_at) ka composite index isi liye tha.
• Cost bhi hai: har index INSERT/UPDATE ke waqt update hota hai aur storage lete hain — har column par index mat banao.
• EXPLAIN hi asli jawab hai jab "query slow hai" aaye. Table badi ho jaaye tabhi Seq Scan dikhta hai — chhoti table pe sab fast lagta hai.

DB ko app se jodo — driver, pools, credentials

import os

import psycopg
from fastapi import FastAPI

app = FastAPI()

# Asli project mein pool lifespan mein banao (FastAPI lesson jaisa)
def get_conn():
    return psycopg.connect(os.environ["DATABASE_URL"])

@app.post("/rooms", status_code=201)
def create_room(name: str, capacity: int):
    with get_conn() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "INSERT INTO rooms (name, capacity) VALUES (%s, %s) RETURNING id",
                (name, capacity),          # values alag tuple se — injection safe
            )
            room_id = cur.fetchone()[0]
    return {"id": room_id, "name": name, "capacity": capacity}

# .env (local — .gitignore mein, commit kabhi nahi)
DATABASE_URL=postgresql://app:localpass@localhost:5432/bookings

# .env.example (commit hota hai — sirf key names ka sample)
DATABASE_URL=

# Code mein:
# url = os.environ["DATABASE_URL"] — missing ho toh startup par hi clear error

• Driver: psycopg (ya async stack ke liye asyncpg). SQL aata hai toh ORM kabhi bhi seekh sakte ho — pehle SQL hi samjho.
• Parameterized queries: %s placeholders + tuple. Values ko string mein jodna = SQL injection (agle level mein detail).
• Connection lifecycle: connection banana mehnga hai — per-request naye ki jagah pool (lifespan mein), aur context manager se close guarantee.
• Credentials code se bahar: DATABASE_URL env var se. .env local, .env.example commit — repo mein secret gaya toh rotate karna padega.

Exercise

Common mistakes

• Credentials code ya repo mein likhna: DATABASE_URL/keys source mein likhi toh wo history mein hamesha ke liye reh jaayengi. Fix: env vars, .env gitignored, .env.example commit, leak ho toh rotate.
• SQL ko string concatenation se banana: f"SELECT ... WHERE name = {name}" injection ke liye khula darwaza hai. Fix: %s placeholders + parameterized tuple (ya ORM bound params).
• Har request pe naya connection: Connection banane ka cost har request par, aur burst par exhaustion. Fix: pool lifespan mein, per-request checkout, context manager se release.
• Filter/sorting Python mein karna: Saari rows laakar list mein dhoondhna — data badhne par har query heavy. Fix: WHERE, ORDER BY, LIMIT SQL mein; DB ko kaam karne do.

-- Customer ek jagah, order mein sirf uska id
INSERT INTO customers (email) VALUES ('ravi@example.com') RETURNING id;  -- 7
INSERT INTO orders (customer_id, item) VALUES (7, 'gpu-hours');

-- Email badalna ho toh ek jagah update — har order apne aap sahi rehta hai
UPDATE customers SET email = 'ravi@office.com' WHERE id = 7;