Chapter 11 · Reps

Persistence I — SQLite and the Relational Model — Reps

← Back to Chapter 11

Chapter 11 — Reps

Conditioning, not grading. SQL and Python sqlite3 reps this week.

Ground rules:

  1. Type every line yourself. No copy-paste of SQL, especially the queries. The placeholder discipline only becomes muscle memory if your fingers build it.
  2. Run everything against a real file. Use the sqlite3 command-line shell for the SQL reps and Python for the rest. Watch each statement do what you predicted. When it doesn’t, find out why before moving on.
  3. AI/agents are ON for Phase 2 — but these reps are hand-built unless a rep explicitly says otherwise. You are building the muscle that lets you review the database code an agent writes for the project. You cannot review a CRUD layer you have never typed.
  4. Commit your writes. Every INSERT/UPDATE/DELETE in Python needs a con.commit() or it didn’t happen. Burn this in now.

Everything here uses Python’s standard-library sqlite3 (no install) and the sqlite3 command-line shell. If you don’t have the shell, every SQL rep can be run from Python via con.executescript(...) instead. Toolchain setup is in Appendix A.

Reps 1–3: Raw SQL in the Shell

Rep 1 — Your First Table

Open a fresh database in the shell:

sqlite3 practice.db

At the sqlite> prompt, type these one at a time and watch each one:

CREATE TABLE verses (id INTEGER PRIMARY KEY, reference TEXT NOT NULL, text TEXT NOT NULL);
INSERT INTO verses (reference, text) VALUES ('John 3:16', 'For God so loved the world...');
INSERT INTO verses (reference, text) VALUES ('Psalm 23:1', 'The LORD is my shepherd; I shall not want.');
INSERT INTO verses (reference, text) VALUES ('Romans 8:28', 'And we know that for those who love God all things work together for good...');
SELECT * FROM verses;

Confirm three rows come back, each with an auto-assigned id you never typed. Then run .schema verses and read the table definition the database stored. Type .quit to exit, then re-open practice.db and SELECT * FROM verses again. The rows are still there. That is persistence. Sit with it for a second — this is the thing your APIs were missing.

Rep 2 — WHERE and ORDER BY

Still in practice.db, add two more verses, then practice filtering and sorting:

SELECT reference FROM verses ORDER BY reference;
SELECT reference FROM verses ORDER BY id DESC;
SELECT * FROM verses WHERE reference = 'John 3:16';
SELECT * FROM verses WHERE id > 2;
SELECT reference FROM verses WHERE reference LIKE 'Psalm%';

For each query, predict the result before you press Enter, then confirm. The LIKE 'Psalm%' one is new — % matches any run of characters, so it finds every reference that starts with “Psalm.” Write down in one sentence what ORDER BY does when two rows would tie.

Rep 3 — UPDATE and DELETE (carefully)

UPDATE verses SET text = 'For God so loved the world, that he gave his only Son...' WHERE reference = 'John 3:16';
SELECT text FROM verses WHERE reference = 'John 3:16';
DELETE FROM verses WHERE reference = 'Romans 8:28';
SELECT COUNT(*) FROM verses;

Now the lesson that has to land in your hands, not just your head. Type this — and read it before you run it:

-- DO NOT RUN THIS against data you care about:
-- DELETE FROM verses;

Leave it commented. Write one sentence explaining what it would have done and why a missing WHERE is the most dangerous habit in SQL. Then delete practice.db from disk and confirm — one file, gone, whole database gone. That is what “the database is a single file” means, in both directions.

Reps 4–5: Python sqlite3

Rep 4 — CRUD From Python

Write crud.py. No framework, just sqlite3. Do all five operations in order against a file called reps.db:

  1. Connect, set con.row_factory = sqlite3.Row, and create the verses table (id, reference, text, translation with a default of 'ESV').
  2. INSERT three verses using con.execute(..., (?, ?)) with placeholders. Commit.
  3. SELECT them all and print id and reference for each, sorted by reference.
  4. UPDATE one verse’s text by id. Commit. SELECT it back and confirm the change.
  5. DELETE one verse by id. Commit. Print the final COUNT(*).

Run it twice. The second run should fail on CREATE TABLE (the table already exists) — fix it by using CREATE TABLE IF NOT EXISTS, then notice that the second run now has six verses, because the first run’s rows persisted. Add a cleanup line or a fresh-file step so reruns start clean, and write one sentence about why “it persisted between runs” is the entire point.

Rep 5 — lastrowid and fetchone

Extend crud.py (or write crud2.py):

  1. After an INSERT, capture cur = con.execute(...) and print cur.lastrowid. Confirm it matches the id the database assigned.
  2. Write a function get_verse(con, verse_id) that runs a parameterized SELECT ... WHERE id = ? and returns con.execute(...).fetchone() — a single row or None.
  3. Call it with a real id (get a row) and a nonexistent id like 9999 (get None). Print both results.

This is the exact pattern your API’s POST (needs lastrowid for the 201) and GET /verses/{id} (needs fetchone and a None check for the 404) will use. You’re building the project’s parts.

Reps 6–7: Wiring SQLite Into the API

Rep 6 — Persist One Route

Open your Week 10 FastAPI app (or start from code/api_sqlite_starter.py). Replace just the POST /verses route so it inserts into SQLite with a parameterized query, commits, and returns VerseOut(id=cur.lastrowid, ...).

Then prove persistence the only way that counts:

  1. Start the server. curl -X POST localhost:8000/verses -H 'content-type: application/json' -d '{"reference":"Psalm 46:1","text":"God is our refuge and strength..."}'
  2. Confirm you get back a 201 with an id.
  3. Stop the server (Ctrl-C). Start it again.
  4. curl localhost:8000/verses (once you’ve wired GET too, Rep 7) — the verse you POSTed before the restart is still there.

Write one sentence in your notes: what exactly survived the restart, and where did it survive? (The process’s RAM was wiped. The answer is the file.)

Rep 7 — The Full Read Layer

Wire the two read routes against SQLite:

  1. GET /verses — SELECT id, reference, text FROM verses ORDER BY id, return a list of VerseOut.
  2. GET /verses/{verse_id} — parameterized SELECT ... WHERE id = ?, fetchone(), and raise HTTPException(404, ...) if it’s None.

Confirm GET /verses/9999 returns a clean 404, not a 500 and not an empty 200. Then add PUT and DELETE, both parameterized. By the end you have the entire Normal-tier CRUD layer running against a file. Restart the server between every test and confirm nothing is lost.

Reps 8–9: Relationships and JOINs

Rep 8 — A Second Table

Back in the shell or in Python, against a fresh file:

CREATE TABLE collections (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE verses (
    id INTEGER PRIMARY KEY,
    reference TEXT NOT NULL,
    text TEXT NOT NULL,
    collection_id INTEGER REFERENCES collections(id)
);
INSERT INTO collections (name) VALUES ('Romans Road'), ('Comfort');
INSERT INTO verses (reference, text, collection_id) VALUES ('Romans 3:23', '...sinned...', 1);
INSERT INTO verses (reference, text, collection_id) VALUES ('John 3:16', '...so loved...', 2);

Now insert a verse pointing at a collection that doesn’t exist (collection_id = 99). It will succeed — because foreign keys are off by default. Then start a fresh Python connection, run con.execute("PRAGMA foreign_keys = ON;") first, and try the bad insert again. It now raises IntegrityError. Write one sentence on why the PRAGMA is not optional.

Rep 9 — Your First JOIN

Against the two tables from Rep 8 (with the bad row removed), write and run:

SELECT v.reference, c.name AS collection
FROM verses AS v
JOIN collections AS c ON v.collection_id = c.id
ORDER BY c.name, v.reference;

Predict the output before you run it. Then answer in writing: if “Romans Road” were stored as a text column on every verse instead of in its own table, what would you have to do to rename the collection to “The Romans Road”? What does the JOIN-based design save you? (This is the no-duplicated-data argument in your own words.)

Reps 10–11: Injection and the Limit

Rep 10 — Run the Injection Both Ways

Run code/injection_demo.py. Watch the unsafe version leak the whole table and the safe version return nothing. Then, by hand, write your own tiny version: a users table with two rows, and a lookup(name) function written the unsafe way (f-string). Pass it ' OR '1'='1 (with a leading quote) and confirm you can read both rows. Then rewrite lookup with a ? placeholder and confirm the same input now returns nothing.

Write the attacker’s input string into your notes and, beside it, the full SQL the unsafe version actually sent. Seeing the assembled malicious query in your own handwriting is the rep.

Rep 11 — Make It Say “database is locked”

Cause SQLite’s defining limit on purpose. Write contention.py:

  1. Create a file database with one table and one row.
  2. Define a worker that connects (with a short timeout, e.g. sqlite3.connect(path, timeout=0.1)), runs BEGIN IMMEDIATE, does an UPDATE, sleeps 0.3 seconds while holding the write lock, then commits.
  3. Launch five of these workers as threads at once. Catch sqlite3.OperationalError in each and count how many fail.

You should see several workers fail with database is locked — they couldn’t get the write lock before their timeout because another worker was holding it. Write two sentences: what caused the error, and why this exact behavior is the constraint that pushes a high-write-concurrency app off SQLite and toward Postgres. (You’ll expand this into the Project 11 Hard-tier memo.)

Done? One Last Thing.

From scratch, no looking — build a complete, persistent, parameterized CRUD store in a single Python file, store.py:

  1. A verses table (id, reference NOT NULL, text NOT NULL, translation default 'ESV', and a UNIQUE(reference, translation) constraint).
  2. Four functions, all parameterized: add(con, reference, text) returning the new id, get(con, id) returning a row or None, all_verses(con) returning a sorted list, and delete(con, id).
  3. add must catch sqlite3.IntegrityError (a duplicate reference) and raise a clear ValueError("verse already exists") instead — the seed of the 409 you’ll build in the project.
  4. A main() that adds three verses, prints them, attempts a duplicate (and reports the clean error), deletes one, and prints the final count.

Run it. Then run it again without deleting the file and confirm the duplicate-detection fires on the rows from the first run — proof the constraint is enforced by the database, across process lifetimes, not by your Python. If you can write this cold — schema, parameterized CRUD, constraint handled, persistence proven — you have the week’s muscle.

Up next: Project 11 — Project 11: Persist It in SQLite.