Chapter 11 — Persistence I: SQLite and the Relational Model

“Memory is the treasury and guardian of all things.” — attributed to Cicero, on why a record outlives the mind that made it

“Then those who feared the LORD spoke with one another. The LORD paid attention and heard them, and a book of remembrance was written before him of those who feared the LORD and esteemed his name.” — Malachi 3:16 (ESV)

Why This Matters

Here is a fact you have been quietly living with for two chapters and probably haven’t grieved yet.

Every memory verse you POSTed to your Week 9 Node server died when you hit Ctrl-C. Every prayer request you added to your Week 10 FastAPI server is gone the moment the process exits. You built two real internet-aware servers — and both of them have the memory of a goldfish. Restart, and the slate is blank.

That is not a bug you introduced. It is the nature of the tool you used: a Python dict, a Node array, an in-memory store. RAM is fast and RAM is forgetful. The instant the process ends — a deploy, a crash, a power blip, a kill — every byte you held in memory is reclaimed by the operating system and handed to the next program. The data structures you built by hand in Phase 1 all lived in RAM. They were the right tool for what Phase 1 was teaching. They are the wrong tool the moment a human expects the program to remember something between Tuesday and Wednesday.

A database is how a program remembers.

That sentence is the whole chapter. Everything else — tables, rows, SQL, primary keys, the file on disk — is mechanism in service of that one idea: a place to put data that survives the death of the process that wrote it. When the ministry’s volunteer adds a verse on Tuesday and the server restarts overnight for a deploy, the verse is still there Wednesday morning. The program forgot — the process is brand new, its RAM is empty — but the record remembered.

This week’s Christian question is how do we faithfully keep a record? It is a stewardship question, and it is an old one. In Malachi, when the faithful spoke together, “a book of remembrance was written before him.” The Lord does not need a notebook; the book of remembrance is a picture of something taken seriously enough to be written down and kept — not trusted to memory, which fails, but committed to a record, which endures. We are about to learn the engineer’s version of that discipline. A database is a book of remembrance, and keeping one faithfully — accurately, durably, without corrupting the previous good copy — is real stewardship of data that belongs to real people.

And here is the right-tool thread, because this is still that book: there are many ways to keep a record, and they cost different things. This week you meet the simplest one that is still real — SQLite. A whole relational database in a single file, no server, no configuration. The perfect first database and, for a huge class of real problems, the perfect last one too. Next week, when you meet PostgreSQL, the question will not be “which is better.” It will be “which does this problem deserve” — and you will only be able to answer that because this week you felt exactly where SQLite stops being enough.

11.1 — The Problem, Stated Precisely

Your Week 10 FastAPI app held its verses like this:

# The in-memory store. Dies on restart.
verses: dict[int, dict] = {}
next_id = 1

That dict lives in the process’s heap. It is fast — O(1) lookups, you built one by hand in Week 5 and you know exactly why. But its lifetime is bounded by the lifetime of the process. Three ways it disappears, all routine:

1. A deploy. You ship new code; the old process stops and a new one starts. Empty dict.
2. A crash. An unhandled exception kills the worker; the supervisor restarts it. Empty dict.
3. A restart. The machine reboots for an OS update. Empty dict.

None of these are exotic. All of them happen weekly to real servers. So the requirement — “the data must survive a restart” — is not a nice-to-have. It is the line between a toy and a tool.

To survive a restart, data has to leave RAM and land on something that persists: disk. You learned the file primitives back in Coding 2 (Chapter 6 — read, write, the atomic temp-file-plus-rename pattern). So why not just write the verses to a JSON file and read them back on startup?

You could. For a few dozen rows you should. But the moment the data grows or more than one thing touches it, a flat file forces you to write — by hand — answers to questions a database already answered:

• How do I find verse #17 without loading and scanning the whole file? (Indexing.)
• How do I add one verse without rewriting the entire file? (Incremental writes.)
• How do I stop two writers from clobbering each other? (Concurrency control.)
• How do I guarantee a half-finished write never leaves a corrupt file? (Atomicity, durability.)
• How do I enforce “no two verses with the same reference”? (Constraints.)

A database is the tool that has already solved every one of those, correctly, by people who spent careers on it. Reaching for one is not over-engineering. It is declining to re-implement, badly, a solved problem. That is the right-tool instinct.

Coach’s Note — You spent eight weeks building structures by hand precisely so that you would respect the ones you don’t build by hand. A database’s B-tree index is the tree you built in Week 6. Its hash-based lookups are the hash table you built in Week 5. Its query planner chooses between them the way you learned to choose in the Week 6 memo. You are not leaving Phase 1 behind. You are about to watch a database make the exact tradeoffs you trained your hands to feel.

11.2 — The Relational Model: Tables, Rows, Columns

A relational database stores data in tables. A table is — and this should feel familiar — a grid. Like a spreadsheet, like a CSV, like the rows-and-columns shape you parsed in Coding 2’s catechism pipeline. The vocabulary:

Here is the verses table we’ll build, drawn as a grid:

Three things make this structured in a way your JSON blob was not:

Columns are typed. id holds an integer. reference holds text. The database will reject a row that tries to put text where an integer belongs. That is a guarantee your dict never gave you — a verse with id: "banana" was perfectly legal in Python and would blow up somewhere downstream, far from where the bad data entered. The schema catches it at the door. This is the Coding 2 lesson — “validate at the boundary” — enforced by the database itself.

Every row has the same columns. No verse can secretly have an extra field; none can be missing one. The shape is the same for all rows, declared once. (Hold that thought — Week 13’s MongoDB throws this rule out on purpose, and you’ll learn exactly when that’s the right call.)

One column is the primary key. The id. It uniquely identifies a row. No two rows may share it. It is how you say “that verse, the specific one” — the equivalent of a key in the hash map you built, except the database maintains it for you and guarantees its uniqueness. “Called by name” (Week 5) at the level of a whole record.

Coach’s Note — Why structure at all? Because structure is enforced specification. In Coding 2 you wrote specs in Pydantic models and Javadoc and prayed the data honored them. A schema is a spec the database refuses to let anyone violate. The cost is rigidity — you have to decide the shape up front. The payoff is that bad data cannot get in. Whether that trade is worth it is the relational-vs-document question of Week 13. This week, assume it is, because for a list of verses it plainly is.

11.3 — SQL: Talking to the Database

You talk to a relational database in SQL — Structured Query Language. It is a small, declarative language: you describe what you want, and the database figures out how to get it. You will learn five verbs this week. They are the database’s version of CRUD — the Create/Read/Update/Delete you’ve been doing in your APIs since Week 9.

CREATE TABLE — declaring the shape

CREATE TABLE verses (
    id          INTEGER PRIMARY KEY,
    reference   TEXT    NOT NULL,
    text        TEXT    NOT NULL,
    translation TEXT    NOT NULL DEFAULT 'ESV'
);

Read it like the spec it is. Four columns. id is an integer and the primary key. reference and text are text and may not be NULL (the database will reject a row missing either). translation is text, may not be null, and defaults to 'ESV' if you don’t supply it — our textbook’s standing rule, now enforced in the schema itself.

INTEGER PRIMARY KEY is special in SQLite: it makes id an alias for the table’s built-in rowid, which means the database assigns it automatically if you don’t. You insert a verse without an id; SQLite hands you back the next integer. You never manage ids by hand.

INSERT — adding a row

INSERT INTO verses (reference, text) VALUES ('John 3:16', 'For God so loved the world...');

We don’t supply id (auto-assigned) or translation (defaults to 'ESV'). The database fills both.

SELECT — the verb you’ll use most

SELECT id, reference FROM verses;                          -- two columns, all rows
SELECT * FROM verses;                                      -- every column, all rows
SELECT * FROM verses WHERE translation = 'ESV';            -- only matching rows
SELECT * FROM verses WHERE id = 3;                         -- one specific row
SELECT * FROM verses ORDER BY reference;                   -- sorted, ascending
SELECT * FROM verses ORDER BY id DESC;                     -- sorted, descending
SELECT * FROM verses WHERE translation = 'ESV' ORDER BY reference;  -- both

WHERE filters. ORDER BY sorts. That covers the overwhelming majority of reads you will ever write. Notice you describe the result you want — “the ESV verses, sorted by reference” — and say nothing about how to scan, filter, or sort. The database’s query planner decides that, and it is very good at it.

UPDATE — changing rows

UPDATE verses SET text = 'For God so loved the world, that he gave his only Son...'
WHERE id = 3;

Coach’s Note — The most dangerous SQL statement a junior ever runs is an UPDATE or DELETE without a WHERE clause. DELETE FROM verses; deletes every row in the table, no confirmation, no undo. UPDATE verses SET text = ''; blanks them all. The WHERE is not optional in practice — it is the difference between editing one verse and erasing the book of remembrance. Type the WHERE first, then the SET. Make it a habit before you ever point this at real data.

DELETE — removing rows

DELETE FROM verses WHERE id = 3;

That’s the whole CRUD vocabulary. Five verbs. You now read SQL.

11.4 — SQLite Specifically: One File, Zero Config

There are many SQL databases. This week’s is SQLite, and you need to understand exactly what makes it different, because that difference is the entire reason it is the right first database — and, for a great many real apps, the right only database.

Most databases — PostgreSQL next week, MySQL, SQL Server — are client/server. A separate database server process runs continuously, listening on a network port, and your program is a client that connects to it over the network (even when “the network” is just localhost). You install it, configure it, start it, manage users, manage ports. It is a whole second program to operate.

SQLite is serverless. There is no server process. There is no port. There is no configuration. The entire database — every table, every row, every index — lives in a single ordinary file on disk (verses.db). Your program reads and writes that file directly through a library linked into your process. The “database” is a file and a function library, full stop.

What that buys you:

• Zero setup. pip install nothing — Python ships sqlite3 in the standard library. No server to start. It just works.
• One file to back up, copy, email, or commit. Your whole database is verses.db. Copy the file, copy the database. Delete the file, the database is gone.
• It is everywhere. SQLite is almost certainly the most widely deployed database engine on Earth — it is inside your phone, your browser, your operating system, most applications you use. It is not a toy. It is battle-tested production software that happens to be tiny.
• Real SQL. Everything in §11.3 works against it. The SQL you learn here transfers almost entirely to Postgres next week.

This is the MVP tool incarnate — minimum viable persistence. When you need a program to remember things and you are one process, or a handful of low-traffic requests, SQLite is not the cheap option you settle for. It is frequently the correct option you’d be foolish to over-build past. “One file, one writer at a time” is exactly right for an embedded app, a desktop tool, a prototype, a single-server ministry app serving a congregation. The architect’s move is to know that — and to know its edges, which §11.8 will name precisely.

Coach’s Note — Reaching for a heavyweight client/server database when SQLite would do is its own architectural mistake — the over-engineering kind, the kind that costs a week of ops work to serve fifty users. Next week’s chapter is literally titled around justifying the upgrade. The right tool is sometimes the small one. An architect is as suspicious of too much machine as of too little.

11.5 — Wiring SQLite into Python: Connections and Cursors

Python’s sqlite3 module is your driver — the library that turns your SQL strings into operations on the file. Two objects matter.

A Connection is your handle to the database file. You get one with connect().

A Cursor is a handle to the results of a statement — you iterate it to read rows back. In sqlite3 you often don’t create a cursor explicitly; connection.execute(...) returns one.

Here is the whole CRUD cycle in Python. (This is the heart of code/crud_demo.py, which you should run.)

import sqlite3

con = sqlite3.connect("verses.db")   # opens (or creates) the file
con.row_factory = sqlite3.Row        # rows act like dicts: row["reference"]

# CREATE the schema
con.executescript("""
    CREATE TABLE IF NOT EXISTS verses (
        id          INTEGER PRIMARY KEY,
        reference   TEXT NOT NULL,
        text        TEXT NOT NULL,
        translation TEXT NOT NULL DEFAULT 'ESV'
    );
""")

# INSERT — note the ? placeholders and the tuple of values
con.execute(
    "INSERT INTO verses (reference, text) VALUES (?, ?)",
    ("Psalm 46:1", "God is our refuge and strength, a very present help in trouble."),
)
con.commit()                         # writes are not durable until you commit

# SELECT — execute returns a cursor; iterate it for rows
for row in con.execute("SELECT id, reference FROM verses ORDER BY reference"):
    print(row["id"], row["reference"])

con.close()

Three things to lock in:

con.commit() makes writes durable. sqlite3 opens a transaction implicitly; your INSERT/UPDATE/DELETE are not actually written to the file until you commit(). Forget it and your changes vanish when the connection closes — a maddening “it worked but didn’t save” bug. (It’s in Common Bugs for a reason.)

con.executescript(...) runs multiple statements at once (good for schema setup). con.execute(...) runs one, and returns a cursor you can iterate or call .fetchone() / .fetchall() on.

cursor.lastrowid gives you the id the database just auto-assigned on an INSERT — exactly what your API needs to return in a 201 Created.

Now the payoff. Replacing your Week 10 in-memory store is this small a change. The route doesn’t change shape — only the storage line does:

# BEFORE (Week 10) — dies on restart:
@app.post("/litman-books/verses", status_code=201)
def create_verse(v: VerseIn) -> VerseOut:
    global next_id
    verses[next_id] = v.model_dump()
    next_id += 1
    return VerseOut(id=next_id - 1, **v.model_dump())

# AFTER (Week 11) — survives restart:
@app.post("/litman-books/verses", status_code=201)
def create_verse(v: VerseIn) -> VerseOut:
    with get_db() as con:
        cur = con.execute(
            "INSERT INTO verses (reference, text) VALUES (?, ?)",
            (v.reference, v.text),
        )
        con.commit()
        return VerseOut(id=cur.lastrowid, **v.model_dump())

The Pydantic models, the routes, the status codes — all the Week 10 work — stay. You swapped the storage, not the interface. That separation is itself an architectural win: the API’s promise to the world didn’t change when its memory got an upgrade. (Start from code/api_sqlite_starter.py, which has this half-wired and TODOs for the rest.)

11.6 — Parameterized Queries and the SQL Injection Lesson

This is the most important section in the chapter. Read it twice.

Look again at the INSERT above. The SQL has ? placeholders, and the actual values travel separately, as a tuple. That is a parameterized query, and it is the only acceptable way to put a value into a SQL statement. Ever.

Here is the temptation, and why it will get someone hurt. Suppose a user looks up a verse by reference, and you build the query with an f-string because it’s the obvious Python move:

# NEVER DO THIS.
reference = request_value            # comes from the user, the internet, a stranger
sql = f"SELECT * FROM verses WHERE reference = '{reference}'"
con.execute(sql)

For a polite user typing John 3:16, this works. But the user is not always polite. The user might type:

nobody' OR '1'='1

Now look at the string your f-string actually builds:

SELECT * FROM verses WHERE reference = 'nobody' OR '1'='1'

'nobody' matches no verse — but '1'='1' is always true, so the WHERE clause is satisfied for every row in the table. You asked for one verse by reference; the attacker walked out with the whole table. And that is the gentle version. With a users table holding password hashes or tokens, the same trick dumps your secrets. With a semicolon they can append ; DROP TABLE verses; -- and delete your data. This attack is called SQL injection, and it has been at or near the top of the OWASP list of web vulnerabilities for as long as the list has existed. It is not exotic. It is the single most common way real databases get breached, and it is trivial to fall into.

The cause is precise: you let user input become part of the SQL code. The string the user typed was interpreted as SQL syntax instead of treated as a plain value.

The fix is equally precise: never build SQL by string concatenation or f-strings. Use ? placeholders and pass values separately.

# The right way. The ? is a slot the driver fills with DATA, never code.
con.execute("SELECT * FROM verses WHERE reference = ?", (reference,))

With the placeholder, the driver sends the query and the value to the database separately. The database compiles the query first — its structure is fixed before your value is anywhere near it — and then looks for a verse whose reference is literally the string nobody' OR '1'='1. There is no such verse. The query returns nothing. The injection is inert because the malicious text was never code — it was always just data.

code/injection_demo.py runs both versions against the same attack and prints the result. Run it. Watch the unsafe version leak every secret in the table and the safe version shrug the attack off. Here is its actual output:

UNSAFE lookup with attacker input:
  SQL sent: SELECT name, secret FROM users WHERE name = 'nobody' OR '1'='1'
    LEAKED -> maya: maya-token-001
    LEAKED -> marcus: marcus-token-002
    LEAKED -> admin: admin-token-ROOT
  (3 rows leaked — the entire table.)

SAFE lookup with the same attacker input:
  (0 rows returned — the attack did nothing.)

Coach’s Note — This is the chapter’s hill to die on. Parameterize everything a user can influence — and in a web app, that’s almost every value. The ? is not a convenience or a style preference; it is a wall between data and code, and the wall is the entire security model. An agent will sometimes generate the f-string version because it’s what it saw most in training data. You are the reviewer who catches it. The Coding 1 reading muscle — read what the machine actually wrote — is now load-bearing for security. A single concatenated SQL string in code you approved is a breach with your name on the commit.

A note on ? versus the variations you’ll see: SQLite uses ? (qmark style). Postgres drivers next week use %s or $1. The placeholder character differs by database. The principle — values go through placeholders, never into the string — is universal and never changes.

11.7 — A First Relationship and a Simple JOIN

So far, one table. Real data has relationships, and the relational model is named for exactly this. Here is the smallest meaningful one: a verse belongs to a collection — “Romans Road,” “Comfort,” “Memory Work for Lent.”

The naive instinct is to add a collection_name column to every verse:

But now “Romans Road” is duplicated on every verse in it. Rename the collection and you must update every row. Misspell it on one row and you have two collections that look like one. Duplicated data is data that drifts — the same corruption-over-copies problem you guarded against in Coding 2’s catechism round-trip. The relational answer is to give collections their own table and have each verse point at one:

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)   -- the relationship
);

collection_id is a foreign key: it holds the id of a row in collections. The collection’s name is stored once, in one place. Verses point at it by id. (You learned pointers in Coding 1 — a foreign key is a pointer that lives in a database instead of in memory. Same idea: a value that refers to something stored elsewhere.)

To read a verse together with its collection’s name — data that now lives in two tables — you JOIN them:

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;

Read it slowly. “From the verses table (call it v), joined to the collections table (call it c) wherever a verse’s collection_id matches a collection’s id, give me each verse’s reference and its collection’s name.” The ON clause is the matching rule — it’s how the database knows which collection row goes with which verse. Run code/crud_demo.py and you’ll see it produce:

  Comfort      John 3:16
  Romans Road  Romans 6:23

That is a JOIN: the database stitched two tables back into one result on a relationship you declared. This is a first taste — Weeks 12 and 13 go deep on what relationships cost and when a different model (documents) serves better. For now, internalize the move: store each fact once, point at it with a key, and JOIN to reassemble. Don’t repeat data; relate it.

Coach’s Note — Turn foreign keys on. SQLite, for backward-compatibility reasons, ships with foreign-key enforcement OFF by default. You must run PRAGMA foreign_keys = ON; on every connection or the database will cheerfully let you insert a verse pointing at a collection that doesn’t exist. The constraint you wrote is only enforced if you enable it. It’s in Common Bugs; it bites everyone once.

11.8 — The Honest Limits of SQLite (and the Door to Postgres)

An architect who only knows a tool’s strengths is dangerous. Here is exactly where SQLite stops being the right tool, stated precisely so that next week’s migration is a decision and not a reflex.

SQLite’s defining constraint: one writer at a time. When a process writes, it takes a lock on the database file, and every other writer must wait. Readers can proceed concurrently (in WAL mode, even during a write), but writes are serialized — strictly one at a time across the whole file. If a second writer can’t get the lock before a timeout, SQLite raises:

sqlite3.OperationalError: database is locked

For a single-process app, or a handful of low-traffic requests, you will never see this — the writes are so brief that the lock is held for microseconds. But put a real concurrent write load on it — many users POSTing at once, multiple server workers all writing — and writers start colliding, queueing, and timing out. You can demonstrate this in a dozen lines: spin up several threads that each open a write transaction and hold it briefly, and watch the losers get database is locked. (Project 11’s Hard tier asks you to build exactly that demonstration and report the numbers — because a limit you’ve measured is a limit you actually understand.)

So the rule of thumb, the right-tool boundary for the whole relational arc:

The single constraint that pushes you off SQLite is almost always concurrent writers. The moment your design has more than one process that needs to write to the same data at the same time, “one writer at a time, one file, one machine” stops fitting, and you reach for PostgreSQL — a client/server database built from the ground up for many simultaneous writers (it uses MVCC, multi-version concurrency control, so writers don’t block each other the way SQLite’s file lock does). That is next week.

But hear the thesis clearly: you do not migrate to Postgres because it is “more serious.” You migrate because you hit a constraint SQLite cannot satisfy — and you should be able to name that exact constraint before you migrate. Reaching for the bigger database without that named reason is over-engineering, and it costs real time and complexity to serve users SQLite would have served fine. Most apps in the world should be on SQLite and aren’t, because their builders reached for the heavy tool out of habit. Don’t be that builder. Know the edge, and migrate when you reach it — not before.

Coach’s Note — “MVP and the right tool” is the soul of Phase 2. SQLite is the MVP database. The skill this week is not just using it — it’s knowing precisely when its single defining limit becomes your problem, so that the Week 12 migration is something you can defend in a sentence: “We have N concurrent writers; SQLite serializes writes; that’s our bottleneck; Postgres’s MVCC removes it.” That sentence is the architecture. The code is downstream of it.

11.9 — Common Bugs

Bug: Your INSERT “worked” but the data is gone after restart. Example: You called con.execute("INSERT ...") and then con.close() with no con.commit() in between. Fix: sqlite3 runs your writes inside an implicit transaction. Nothing is durable until con.commit(). Commit after every write (or group of writes you want to land together). If you’d rather not think about it, open with sqlite3.connect(path, isolation_level=None) for autocommit — but know that you’ve given up control over transaction boundaries.

Bug: A foreign key points at a row that doesn’t exist, and the database let it. Example: You inserted a verse with collection_id = 99 when no collection has id 99, and SQLite accepted it. Fix: SQLite ships with foreign-key enforcement OFF. Run con.execute("PRAGMA foreign_keys = ON;") on every connection. The REFERENCES clause is only enforced when this is on.

Bug: You built SQL with an f-string and “it works on your machine.” Example: con.execute(f"SELECT * FROM verses WHERE id = {verse_id}"). Fix: Parameterize: con.execute("SELECT * FROM verses WHERE id = ?", (verse_id,)). It works on your machine because you type polite input. It is an injection hole the instant a stranger can influence that value. There are no exceptions to this rule. None.

Bug: You passed a single value as the parameter and got ValueError: parameters are of unsupported type or a silent mismatch. Example: con.execute("... WHERE id = ?", verse_id) — passing the bare int instead of a tuple. Fix: The parameters argument must be a sequence. A single parameter still needs a one-element tuple: (verse_id,). Note the trailing comma — (verse_id) is just verse_id in parentheses, not a tuple.

Bug: A DELETE or UPDATE changed far more rows than you meant. Example: con.execute("DELETE FROM verses") — no WHERE, so it emptied the table. Fix: A WHERE-less UPDATE/DELETE hits every row. Write the WHERE clause first, before the SET or as your first instinct on DELETE. Test destructive statements against a throwaway copy of the file before you ever run them against real data.

Bug: A duplicate INSERT crashed the API with a 500 instead of a clean error. Example: You have UNIQUE (reference, translation) and a user POSTs a verse that already exists. sqlite3 raises IntegrityError, which bubbles up as an unhandled 500. Fix: Wrap the INSERT in try/except sqlite3.IntegrityError and translate it into a proper 409 Conflict at the API layer (Project 11, Medium tier). A constraint violation is expected input, not a server error — handle it like the 404 you already handle.

11.10 — Reps

Open the exercises for the full set. This week’s reps build from raw SQL in the sqlite3 shell up to a CRUD layer you wire into the API by hand. AI/agents are ON for Phase 2 — but the reps are hand-built unless a rep says otherwise, because you cannot review a database layer you’ve never written.

A preview:

• Rep 1 — Create a table, insert three rows, and SELECT them back, entirely in the sqlite3 shell.
• Rep 6 — Replace one route of your Week 10 API with a parameterized SQLite call and prove the data survives a restart.
• Rep 9 — Add a second table and write a JOIN.
• Rep 10 — Run the injection both ways: write the same lookup unsafely and safely; prove the attack works, then prove the placeholder kills it.
• Rep 11 — Make SQLite say database is locked on purpose, and explain in two sentences what caused it.

Do every one. The keyboard is the gym, and SQL fluency is a muscle you build by typing real queries against a real file.

11.11 — This Week’s Project

You’re ready for Project 11 — Persist It in SQLite, in Project 11.

You will rip the in-memory store out of your Week 10 API and replace it with SQLite, so the data survives a restart. You will design the schema yourself — that is the human’s job, and an agent may not do it for you. The Normal tier is the parameterized CRUD layer and a database that remembers. The Medium tier adds a second related table, a JOIN, and a uniqueness constraint handled as a clean 409 Conflict. The Hard tier is the architect’s deliverable: a memo on SQLite’s real limits, backed by your own write-contention measurement, naming the exact constraint that would push the app to Postgres.

This is Phase 2, so agentic AI is ON and an agent-log.txt is REQUIRED — every task you delegated, what the agent built, and where you intervened. The agent can write the route handlers. The schema — the shape of the data, the keys, the constraints — is yours, because the shape of the data is the architecture.

11.12 — Coach’s Final Word for Week 11

Your servers learned to remember this week.

That is a bigger deal than it sounds. Until now, everything you built in Phase 2 was a beautiful goldfish — fluent, correct, and amnesiac. Now there is a file on disk that outlives the process, and a volunteer’s Tuesday verse is there on Wednesday. You crossed the line from toy to tool.

You also learned the cheapest real tool that exists for the job, and — just as important — exactly where it stops. SQLite is not a compromise you’ll apologize for later. It is the right answer for an enormous swath of real software, and the architect’s skill is knowing both that and the single constraint — concurrent writers — that would make you reach for something bigger. Next week you reach. But only because you’ll be able to say why.

And you learned the security lesson that has its own name and its own permanent spot on every list of how systems get breached. Parameterize everything. The ? is a wall between data and code. Build that wall every single time, and review every machine-generated query for the f-string that skips it.

If you found the SQL easy: good, it’s meant to be. The hard part was never the syntax. The hard part is the judgment in §11.8 — knowing the limit before you hit it. That’s the part an agent can’t do for you.

See you on Monday.

Up next: Work every rep in the exercises, then build Project 11 — Project 11: Persist It in SQLite. After that, Chapter 12 — Persistence II: PostgreSQL and scaling up, where you’ll meet a database built for the one thing SQLite can’t do.