Day 42 · The first backend

Week 6 Checkpoint: API + DB Mini-Service

You will be able to

Retrieve Week 6's systems concepts (SQL, indexes, processes, async, HTTP) from memory
Build a FastAPI notes service backed by SQLite with full CRUD and validation
Test every route and failure path with FastAPI's TestClient
EXPLAIN the service's hottest query and add the index it needs
Ship the week's work as one committed, tested, documented artifact

Today's ~115 minutes

Spaced-rep: full Week 6 deck, fumbles logged	12 min
Closed-book recall drills (schema, joins, async, HTTP)	18 min
Build milestone 1: service + storage, first commit	35 min
Build milestone 2: tests + index ritual, second commit	35 min
README, error log update, cumulative quiz	15 min

Builds on: Day 36 — SQL I — tables & queries · Day 38 — Database internals — indexes & transactions · Day 40 — Concurrency & async · Day 41 — HTTP & FastAPI

The analogy

🧸 ELI5⚙ Tech

All week you toured a restaurant's stations: the pantry with its labeled, contracted shelves (SQL), the index cards that find any ingredient in seconds (B-trees), the hotel-manager kitchen keeping cooks out of each other's pots (OS), the one clever chef juggling simmering pans (async), and the counter where orders are taken in a strict verb-and-status protocol (HTTP). Today you open the restaurant. Small menu — a notes service: write a note, find your notes, edit, delete — but every station wired together and actually serving.

This "boring CRUD app" is secretly the most-built object in software, and building it end-to-end forces the connections the week only implied: the request's JSON becomes a parameter-bound INSERT; the search endpoint becomes a WHERE that needs one of Day 38's indexes; the status code you return IS the contract with your caller. And because it is a review day, you build some of it with the book closed first — recalling the LEFT-join idiom or the 422-vs-404 decision from memory is today's spaced-repetition workout, hidden inside shipping something real.

Why this matters on the job

"Build a small CRUD API against a database, with tests" is a literal take-home interview task at many companies — today is a dress rehearsal with a rubric. It is also the load-bearing skeleton of your future: Day 45 hardens exactly this service with auth and layers, Day 49's URL shortener re-runs the pattern under design constraints, and the capstone's serving layer (Day 119 onward) is this shape with retrieval inside. The TestClient habit you start today — testing the API contract, not the implementation — is what makes every later refactor (Day 45's, Day 148's containerization) safe.

Guided practice

guided 1

Closed-book recall: wire the week together

18 min

Editor closed for each item; open only to check. Log misses in your error log with causes.

On paper, write the CREATE TABLE for notes (id, title, body, tag, created_at) with two constraints from memory, and the parameter-bound INSERT for it. Check against Day 36's rules — did you remember the ? placeholders and a CHECK?
On paper: the anti-join that finds tags never used by any note (two-table version), and where an index belongs if searches filter by tag. Check against Days 37–38.
Out loud, 60 seconds each: why a blocking call in an async handler freezes ALL requests (Day 40); what SIGTERM should make your uvicorn process do (Day 39); why DELETE returning 204 twice in a row is fine but POST retries are not (Day 41).
Run the week's flashcard deck. Under 80% → schedule the weak day's re-read before Day 43.

guided 2

Build milestone 1 — service + storage

35 min

Build notes_service/ in your practice repo: app.py, db.py, test_app.py. Milestones, each verified before moving on:

db.py: init_db(path) creates the notes table (constraints from your recall drill — now checked); get_conn dependency yields a per-request connection; CRUD functions (create_note, list_notes with optional tag filter + limit/offset, get_note, update_note, delete_note) — all SQL lives here, all writes inside with conn:.
app.py: pydantic NoteIn (title 1–120 chars, body non-empty, optional tag) and routes: POST /notes → 201, GET /notes?tag=&limit=&offset=, GET /notes/{id} → 200/404, PUT /notes/{id} → 200/404, DELETE /notes/{id} → 204/404. Handlers call db.py functions only — no SQL in routes.
Run with uvicorn; create, search, update, and delete notes through /docs. Watch the request log tell the story.
Commit at this milestone before testing begins — working service, then safety net, two commits.

🐍 python — editable, runs in your browser

# db.py -- the only file that speaks SQL
import sqlite3

def init_db(path="notes.db"):
    conn = sqlite3.connect(path)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS notes (
            id         INTEGER PRIMARY KEY,
            title      TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 120),
            body       TEXT NOT NULL CHECK (length(body) > 0),
            tag        TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    conn.commit()
    conn.close()

def create_note(conn, title, body, tag):
    with conn:                                    # atomic write
        cur = conn.execute(
            "INSERT INTO notes (title, body, tag) VALUES (?, ?, ?)",
            (title, body, tag),
        )
    return get_note(conn, cur.lastrowid)

def list_notes(conn, tag=None, limit=20, offset=0):
    sql, params = "SELECT * FROM notes", []
    if tag:
        sql += " WHERE tag = ?"
        params.append(tag)
    sql += " ORDER BY id DESC LIMIT ? OFFSET ?"
    params += [limit, offset]
    cols = ["id", "title", "body", "tag", "created_at"]
    return [dict(zip(cols, row)) for row in conn.execute(sql, params)]

def get_note(conn, note_id):
    row = conn.execute("SELECT * FROM notes WHERE id = ?", (note_id,)).fetchone()
    if row is None:
        return None
    cols = ["id", "title", "body", "tag", "created_at"]
    return dict(zip(cols, row))

def delete_note(conn, note_id):
    with conn:
        cur = conn.execute("DELETE FROM notes WHERE id = ?", (note_id,))
    return cur.rowcount > 0

Ctrl/⌘+Enter runs · Tab indents · numpy/pandas/sklearn auto-load on import (torch and network calls need a local run)

On your own

Build milestone 2 — the safety net and the index ritual

35 min

test_app.py with a fixture giving each test a TestClient wired to a fresh tmp_path database (override the get_conn dependency — look up app.dependency_overrides). Write the contract tests: create→201 with echoed fields; get missing→404; invalid body (empty title, 200-char title)→422; tag filter returns only matching; pagination slices correctly; update changes what it should; delete→204 then get→404; delete again→404. Aim for every route's happy AND failure path — about 10 tests.
Run pytest until green. Any bug you fix: add the regression test first (Day 19's habit).
The index ritual: seed 5,000 notes across 20 tags (script or fixture), EXPLAIN QUERY PLAN the tag search (SCAN), CREATE INDEX on tag, re-EXPLAIN (SEARCH), and record both plans plus timings in the README.
Commit. Two commits minimum today: service, then tests+index.

No hints beyond the milestones — the week taught every piece. Struggle first; your toolkit files second.

Ship before you stop

Ship it: the Week 6 artifact

Finish the notes service as a portfolio-grade mini-repo: working CRUD API (SQL confined to db.py), ~10 passing TestClient tests covering all routes' happy and failure paths, the tag index justified by recorded EXPLAIN plans, and a README containing: how to run (venv, uvicorn, pytest), the API table (route, method, statuses), your index before/after evidence, and a five-line "what I'd harden next" list (auth? rate limits? — foreshadowing Days 44–45). Update your error log with today's recall misses. This repo is the direct ancestor of Day 45's hardened service and Day 49's URL shortener.

Rubric — check what you completed (0/6)

All CRUD routes return specified statuses, verified by tests (not manually)Test suite ≥ 10 tests, isolated via fresh-database fixture, all green in one pytest runSQL exists only in db.py; handlers contain zero query stringsEXPLAIN plans before/after the tag index recorded in the README with timingsREADME complete enough that a stranger could run service and tests in 5 minutesTwo+ commits with meaningful messages; error log updated

Common mistakes & misconceptions

Testing by clicking /docs instead of writing TestClient tests. Manual checks vanish; the suite re-verifies the whole contract in seconds forever — and take-home graders read tests first.
Sharing one module-level sqlite3 connection across threaded requests — sqlite3 objects default to single-thread use and will raise (or corrupt ordering). Per-request connections via a dependency.
Letting tests share a database. Test A's leftover notes break test B only when order shifts — the classic flaky suite. Fresh tmp_path DB per test, via fixture.
Skipping the 404/422/204 failure-path tests because "the happy path works." The failure contract IS half your API; it is also where regressions hide.
Writing UPDATE without checking rowcount — updating a missing id "succeeds" silently and returns 200 for a note that does not exist. rowcount 0 → 404.
Indexing before EXPLAINing (or after guessing). The ritual is: read the plan, see SCAN, add the index the plan needs, confirm SEARCH. Evidence, then action.

Knowledge check

Q1. Your tag-search endpoint is slow with 50k notes. EXPLAIN QUERY PLAN says "SCAN notes". The fix and the proof?

Q2. PUT /notes/9999 (nonexistent) runs an UPDATE that matches zero rows. Correct API behavior?

Q3. Why must each test get a fresh database via fixture instead of sharing notes.db?

Go deeper — curated resources

docsFastAPI — Testing (official tutorial) ↗20 min docssqlite3 — official Python docs (transactions & placeholders refresher) ↗10 min docspytest — fixtures documentation ↗15 min

If you have a third hour

Preview: dependency injection as architecture ↗ — Your get_conn dependency is a first taste of DI — Day 45 generalizes it into the routes/services/repos layering. Skim FastAPI's Dependencies chapter with that lens.

Done means

Recall drills done closed-book; misses logged with causes
Service runs; all routes behave per the API table
pytest suite ≥ 10 tests, green, isolated by fixture
Index evidence (plans + timings) in the README; two+ commits pushed
Cumulative quiz ≥ 2/3

How this connects

← Back: Today compiled the whole week into one artifact: Day 36–37's SQL behind Day 41's routes, Day 38's EXPLAIN ritual on Day 40's threadpool-served handlers, tested with Day 18–19's pytest discipline, shipped with Day 20's git habits.

Forward →: Day 45 hardens exactly this service (auth, layers, middleware); Day 49's URL shortener re-runs the build under design constraints; Day 148 containerizes it; and the capstone's API layer (Day 119+) is this skeleton with retrieval and citations inside.

Unlocks: D45 Web Service Architecture