Overview

Data lives in tables (relations) of rows and typed columns, and every fact is stored exactly once, related to other facts by matching key values rather than by memory addresses. An in-memory structure connects records by pointers that only make sense while the process is alive; a relational table's foreign-key values stay meaningful on disk, across processes, and across however many years the data outlives the program that first wrote it.

It designates that column as the row's unique identifier, and in SQLite specifically, a single-column INTEGER PRIMARY KEY becomes an alias for the table's internal rowid -- inserting NULL (or omitting the column) makes SQLite auto-assign the next integer value for you, so you never hand-manage ids.

A foreign key constrains a column to only hold values that already exist as a primary key in another table (or be NULL), and, when enforced, drives ON DELETE actions like CASCADE or RESTRICT. SQLite disables foreign key enforcement by default for backward compatibility with older schemas -- you must run PRAGMA foreign_keys = ON on every connection where you want orphan rows rejected.

NOT NULL forbids a missing value; UNIQUE forbids a duplicate value across the whole column; CHECK forbids any value that fails a boolean expression you supply (e.g. CHECK(price >= 0)); and DEFAULT supplies a value automatically when an INSERT omits that column.

It keeps every fact stored in exactly one place by splitting repeating groups and transitive dependencies into their own tables, which prevents update anomalies -- e.g. a fact changing in one row but not the other rows that duplicated it. The cost is more joins: recombining normalized data at query time is more verbose than reading one wide, denormalized table.

SQLite assigns each column a preferred storage class (INTEGER/TEXT/REAL/BLOB/NUMERIC) based on its declared type, but it does not rigidly reject a value of a different class the way, say, a strictly typed engine would -- the declared type guides storage, it does not enforce it. typeof(column) reveals what storage class a given value actually landed as.

CREATE TABLE declares a new relation: its name, its column list, and every constraint on it. .schema <table> (a CLI dot-command) echoes back the literal CREATE TABLE text SQLite stored in its internal sqlite_master table, comments included -- proof of what the engine actually has, not what you remember writing.

The SELECT column list performs projection -- choosing which columns come back, never changing the row count. WHERE performs selection -- choosing which rows come back, using comparisons, AND/OR, LIKE, IN, CASE, or a subquery, never changing which columns are visible.

ORDER BY controls the sequence rows come back in; LIMIT caps how many rows come back; OFFSET skips a number of rows before that cap starts counting. ORDER BY <col> LIMIT 10 OFFSET 10 sorts first, then skips the first 10 (page 1) and returns the next 10 (page 2) -- ordering must come first or "page 2" is meaningless.

INSERT INTO table(columns) VALUES(...) adds one or (with comma-separated tuples) many new rows. Appending ON CONFLICT(col) DO UPDATE SET ... turns the same statement into an upsert -- insert if the conflicting key is new, update in place if it already exists, instead of raising a constraint error.

The SET clause applies to every single row in the table, not just the one you had in mind -- there is no implicit "current row" the way a cursor-based language might assume. This is the single most dangerous omission in this whole topic (Kata 1).

DELETE FROM table with no WHERE clause removes every row in the table, not one -- the exact same missing-filter danger as an unscoped UPDATE, just irreversible instead of merely wrong.

An inner join returns only the rows where the join condition finds a match on both sides -- a row from either table with zero matches on the other side is silently dropped from the result entirely, never appearing with NULLs or otherwise (Kata 3).

A LEFT JOIN guarantees every row from the left table appears at least once in the result, even when it has zero matches on the right -- unmatched right-side columns are filled with NULL instead of the row being dropped. That NULL-filling is also what makes LEFT JOIN ... WHERE right.id IS NULL an anti-join: it isolates exactly the left rows with no match at all.

GROUP BY <col> collapses every row sharing the same value of <col> into a single summary row per distinct value. Any of COUNT, SUM, AVG, MIN, MAX, or GROUP_CONCAT can then summarize the rows inside each group.

WHERE filters individual rows before GROUP BY collapses them into groups -- it has no access to aggregate values like count(*). HAVING filters the already-formed groups, after aggregation has run, so it can test conditions like HAVING count(*) > 1 that only make sense once rows are already summarized.

NULL means "unknown," and under SQL's three-valued logic, comparing anything to an unknown value -- including NULL = NULL -- yields NULL (neither true nor false), which WHERE treats as a non-match. The only correct test for "is this value unknown" is IS NULL (or IS NOT NULL for the opposite); COALESCE(col, fallback) substitutes a default when you want a NULL to read as a concrete value instead.

A transaction (BEGIN ... COMMIT/ROLLBACK) makes every write inside it atomic as a group -- either all of them apply, or none do; there is no partially applied state a reader can ever observe. SAVEPOINT creates a named point inside a still-open transaction that ROLLBACK TO can undo back to, without ending the outer transaction or discarding writes made before the savepoint.

sqlite3.connect(path) opens (or creates) the database file and returns a Connection; .cursor() gets a cursor to run SQL through; .execute(sql, params) runs a statement; .commit() makes any pending writes durable and .close() releases the connection -- skipping .commit() before .close() silently discards uncommitted writes (Kata 6).

A ? (or :name) placeholder tells the driver "this is a single opaque value, never SQL syntax" -- the driver sends the query text and the value separately to the engine, so nothing the value contains (quotes, OR, ;, anything) can change the shape of the statement being executed. String interpolation, by contrast, makes the value part of the SQL text itself before the engine ever sees it, so a crafted value can add real clauses (Kata 4).

fetchone() returns the next single row (or None once exhausted), letting you stream through a large result without holding it all in memory; fetchall() returns every remaining row as a list in one call. Setting row_factory = sqlite3.Row changes how a row itself is accessed -- from a plain positional tuple (row[0]) to name-based access (row["name"]) as well, without giving up tuple-style indexing.

Adding a column with a DEFAULT value means every existing row instantly gets that default for the new column -- no existing row becomes invalid or needs a separate backfill just to satisfy a NOT NULL constraint on the new column. PRAGMA user_version is a single integer stored in the database file itself that a migration script can read and bump, so a deploy script can tell "has this migration already been applied to this exact file" and skip re-running it.

It is issuing one query to fetch N parent rows, then one additional query per parent inside a loop to fetch each parent's children -- N+1 total round trips for what should be a small, fixed number. The two standard fixes are replacing the loop with a single JOIN query, or batching the child fetch into one WHERE id IN (?, ?, ...) query (Kata 7).

Any four of: .tables (lists table names), .schema (echoes stored CREATE TABLE text), .mode (sets output format, e.g. column or csv), .output (redirects query results to a file), .headers on (prints a column-name header row), or < file.sql (feeding an entire script to the CLI non-interactively) -- there is no GUI anywhere in this topic, every operation goes through the CLI plus PRAGMA checks.

Use both: WHERE in_stock = 1 ... GROUP BY author_id HAVING sum(price) > 20. WHERE excludes the out-of-print rows before aggregation ever runs, so they never contribute to the sum in the first place; HAVING then filters the resulting per-author sums afterward. Using HAVING alone to exclude out-of-print rows would still let their revenue count toward each sum first, since HAVING only filters already-aggregated groups, not the raw rows that fed them.

A LEFT JOIN from author to book, not an inner join. An inner join drops the zero-book side entirely; LEFT JOIN keeps every row from the left table (author) regardless of whether it has any matches (Kata 3).

WHERE published_year IS NULL, not = NULL. Under three-valued logic, = NULL never evaluates to true no matter what is stored -- IS NULL is the dedicated operator for testing "unknown" (Kata 2).

A parameterized query using a ? or :name placeholder, never f-string/.format/% interpolation. The driver binds the value as an opaque parameter, never as SQL text, so nothing the input contains can change the query's shape (Kata 4).

This is the N+1 query problem. Replace the per-author loop with one JOIN query, or one batched WHERE author_id IN (?, ?, ...) query -- either collapses 51 round trips into 1 (Kata 7).

INSERT ... ON CONFLICT(id) DO UPDATE SET ... -- an upsert. The same statement inserts on a new key and updates on a colliding one, with no separate "does this row already exist?" query needed first.

SAVEPOINT before the risky sub-task, then ROLLBACK TO that savepoint on failure. The outer transaction stays open the whole time; only the work done since the savepoint is undone, and the first sub-task's already-applied writes are untouched.

PRAGMA foreign_keys = ON was never set for that connection. SQLite disables foreign key enforcement by default, so a REFERENCES clause is purely documentation until you explicitly turn enforcement on (Kata 5).

Split the repeating group into its own table (a child or junction table holding one author per row) -- 1NF normalization. A single column packing multiple values together is exactly the repeating-group shape 1NF exists to eliminate.

Set conn.row_factory = sqlite3.Row and access columns by name (row["title"]) instead of position. Name-based access is immune to a SELECT list being reordered or extended later.

cur.executemany("INSERT ... VALUES (?)", rows) instead of looping over cur.execute(...) per row -- one Python-level call driving the same 500 inserts, with far less per-row call overhead.

PRAGMA user_version tracks a single integer inside the database file itself. Read it before migrating, bump it after, and skip the migration entirely if the stored version already reflects it -- the migration becomes idempotent across retries.

-- THE FIX: WHERE id = 2 (co-11) scopes the write to exactly the row the promo
-- applies to -- UPDATE without WHERE has no concept of "the row I meant."
UPDATE book
SET
  price = 0
WHERE
  id = 2;
 
SELECT
  id,
  title,
  price
FROM
  book
ORDER BY
  id;
 
-- => only row 2's price changed

Root cause: UPDATE table SET ... applies its SET clause to every row currently in the table unless a WHERE clause narrows it -- there is no implicit "current row" the way a cursor-based or spreadsheet-style tool might assume. Omitting WHERE is not a smaller version of the bug; it is the single most common way to silently destroy production data with one keystroke's difference from the safe version.

Run: sqlite3 app.db < kata.sql

Output:

id  title                 price
--  --------------------  -----
1   Refactoring           45.0
2   Domain-Driven Design  0.0
3   Clean Code            35.0

-- THE FIX: IS NULL (co-17), not = NULL, is the correct test for "value unknown".
SELECT
  id,
  title
FROM
  book
WHERE
  published_year IS NULL;
 
-- => matches the one row whose published_year was never recorded

Root cause: NULL means "unknown" under SQL's three-valued logic -- comparing anything to NULL, including with =, yields NULL, and WHERE discards any row whose condition is not literally true. IS NULL is a distinct operator built specifically to test for "unknown," which is why it is the only correct way to find NULL values.

Run: sqlite3 app.db < kata.sql

Output:

id  title
--  -------------
2   Unknown Draft

-- THE FIX: LEFT JOIN (co-14) keeps every author row, even ones with zero matching
-- books -- unmatched book columns come back as NULL instead of the row vanishing.
SELECT
  author.name,
  book.title
FROM
  author
  LEFT JOIN book ON book.author_id = author.id
ORDER BY
  author.name;
 
-- => all three authors appear; Grace Hopper and Margaret Hamilton show a NULL title

Root cause: An inner join (JOIN) only returns rows where both sides find a match -- an author with zero books contributes zero rows to an inner join's result and simply disappears from the output, with no error or warning. LEFT JOIN guarantees every row from the left table (author) appears at least once, substituting NULL for any unmatched right-side (book) column.

Run: sqlite3 app.db < kata.sql

Output:

name               title
-----------------  ------------------------------
Ada Lovelace       Notes on the Analytical Engine
Grace Hopper       NULL
Margaret Hamilton  NULL

def find_author_by_name(conn: sqlite3.Connection, name: str) -> list[tuple[int, str]]:
    cur = conn.cursor()
    # THE FIX: ? is a placeholder (co-20), not a splice point -- the driver binds
    # `name` as one opaque value, so it can never change the shape of the query.
    cur.execute("SELECT id, name FROM author WHERE name = ?", (name,))
    return cur.fetchall()
 
 
malicious_input: str = "nobody' OR '1'='1"
print(find_author_by_name(conn, malicious_input))  # => Output: [] (correctly zero matches)

Root cause: Building f"... WHERE name = '{name}'" makes the caller's input part of the SQL text itself -- a value crafted with an embedded ' OR '1'='1 does not just fail to match, it adds a real OR clause that makes the whole condition true for every row. A ? placeholder sends the value to the engine as a bound parameter, entirely separate from the query text, so no character sequence inside it can ever be interpreted as SQL syntax.

Run: python3 kata.py

Output:

[]

-- THE FIX: turn on enforcement (co-03) for this connection BEFORE any writes happen.
PRAGMA foreign_keys = ON;
 
-- author_id 99 still does not exist -- this insert is now rejected outright.
INSERT INTO
  book (title, author_id)
VALUES
  ('Ghost Reference', 99);
 
-- => the statement fails; zero rows are inserted (SQLite statements fail atomically)

Root cause: SQLite disables foreign key enforcement by default for backward compatibility with database files created before FK support existed -- a REFERENCES clause in CREATE TABLE is purely documentation until PRAGMA foreign_keys = ON is set on that specific connection. With it set, the same orphan insert that silently succeeded before now fails outright, and because SQLite statements fail atomically, the failed INSERT touches zero rows -- there is no partial write to clean up.

Run: sqlite3 app.db < kata.sql

Output:

Runtime error near line 15: FOREIGN KEY constraint failed (19)

The script does not halt -- sqlite3's non-interactive default does not stop on the first error, so the trailing SELECT id, title, author_id FROM book; still runs. It prints nothing because the rejected INSERT never touched book, so the query legitimately returns zero rows, and .headers on/.mode column print nothing at all for an empty result set (the same mechanism Kata 2 uses above). That is exactly the point: the orphan row was never created to select.

conn1.execute("INSERT INTO author(name) VALUES ('Ada Lovelace')")
# THE FIX: commit() (co-18/co-19) BEFORE close() -- the write is durable before
# the connection that made it ever goes away.
conn1.commit()
conn1.close()
 
conn2: sqlite3.Connection = sqlite3.connect(DB_PATH)
rows: list[tuple[int, str]] = conn2.execute("SELECT id, name FROM author").fetchall()
print(rows)  # => Output: [(1, 'Ada Lovelace')] -- the row survived the connection swap

Root cause: sqlite3's default isolation_level opens an implicit transaction before the first data-modifying statement, and that transaction stays open -- uncommitted -- until .commit() is called explicitly. .close() does not commit a pending transaction on your behalf; it simply ends the connection, and the uncommitted INSERT never reached durable storage, so a fresh connection to the same file sees a table that is still empty.

Run: python3 kata.py

Output:

[(1, 'Ada Lovelace')]

def books_by_author(conn: sqlite3.Connection) -> dict[str, list[str]]:
    query_count: int = 0
    result: dict[str, list[str]] = {}
    cur = conn.cursor()
    # THE FIX: one LEFT JOIN (co-13/co-14/co-23) fetches every author-book pair
    # (or lone author) in a single round trip, instead of one query per author.
    cur.execute(
        "SELECT author.name, book.title FROM author "
        "LEFT JOIN book ON book.author_id = author.id "
        "ORDER BY author.name"
    )
    rows: list[tuple[str, str | None]] = cur.fetchall()
    query_count += 1  # => exactly ONE query, regardless of how many authors exist
    for name, title in rows:
        result.setdefault(name, [])
        if title is not None:
            result[name].append(title)
    print(f"queries issued: {query_count}")  # => Output line 1: queries issued: 1
    return result

Root cause: The buggy version issues one SELECT to list authors, then a second SELECT per author inside the loop -- N+1 total round trips for N authors, each one paying its own overhead even against a local file. Replacing the loop with one LEFT JOIN (kept instead of an inner join specifically so authors with zero books still appear) fetches every author-book pair the query needs in exactly one round trip, regardless of how many authors exist.

Run: python3 kata.py

Output:

queries issued: 1
{'Ada Lovelace': ['Notes'], 'Alan Turing': ['On Computable Numbers'], 'Grace Hopper': ['COBOL Manual', 'Compiler Notes']}

-- THE FIX: group_concat(title, sep) (co-15) is a genuine aggregate -- it names
-- EVERY title in the group explicitly, instead of SQLite silently picking one.
SELECT
  author_id,
  group_concat (title, '; ') AS titles,
  count(*) AS book_count,
  sum(price) AS total_price
FROM
  book
GROUP BY
  author_id;
 
-- => author_id 1's group now correctly shows BOTH of its titles, not just one

Root cause: SQLite's GROUP BY is lenient about non-aggregated, non-grouping columns in the SELECT list -- rather than raising an error the way a stricter engine's default mode would, it silently picks one arbitrary row's value for that column per group. title is neither wrapped in an aggregate function nor listed in GROUP BY, so author_id 1's group (with two distinct titles) reports back only one of them, and which one is an implementation detail, not a guarantee. Wrapping title in a real aggregate (group_concat) makes every title in the group explicit instead of leaving one silently dropped.

Run: sqlite3 app.db < kata.sql

Output:

author_id   titles                                                                  book_count    total_price
----------  ----------------------------------------------------------------------  ------------  ------------
1           Notes on the Analytical Engine; Sketch of the Analytical Engine         2             30.0
2           COBOL Manual                                                            1             9.5

SQL asks you to state the policy -- the shape of the result you want ("every book with its author's name, ordered by price") -- and leaves the mechanism -- which index to scan, which join algorithm to use, in what order to visit the tables -- entirely to the query planner. If SQL were imperative, every query would also have to encode a specific execution strategy, and that strategy would need rewriting by hand every time the data grew, an index appeared, or the storage engine changed. Declarative SQL means the exact same SELECT ... JOIN ... WHERE ... text can get faster over years of engine improvements and schema changes without a single line of application code changing -- the mechanism is free to evolve because it was never specified in the first place. That separation is mechanism-vs-policy in its purest form.

SQLite predates its own foreign-key support (added years after the file format was already widely deployed), and turning enforcement on by default for every existing database file would have silently started rejecting writes that had worked for years -- a breaking change hidden inside a routine version upgrade. Making enforcement an explicit per-connection PRAGMA foreign_keys = ON keeps old schemas and scripts working exactly as they always did, while letting any new code opt into the stricter guarantee deliberately. The cost, as Kata 5 shows directly, is that forgetting the PRAGMA is silent -- an orphaned row inserts without complaint -- so every connection that cares about referential integrity has to remember to ask for it explicitly.

NULL represents "unknown," not "empty" or "zero" -- and comparing any value to something genuinely unknown can only honestly produce "unknown" as the answer, never a hard true or false. SQL encodes that honesty directly into three-valued logic: every comparison involving NULL evaluates to NULL, and WHERE only keeps rows whose condition evaluates to true, so a NULL result is silently treated the same as false. Raising an error instead would treat every accidental = NULL as a hard failure, which sounds safer, but it would also make NULL impossible to combine into larger boolean expressions (WHERE a = 1 AND b = NULL) without every such expression crashing the whole query -- three-valued logic lets NULL propagate through arbitrarily complex conditions predictably, at the cost of = NULL being a trap for anyone who reaches for the wrong operator (Kata 2).

A wide, denormalized table duplicates every fact wherever it is needed for convenient reading, but that duplication means a single real-world fact -- an author's name, say -- now lives in as many rows as that author has books. Every UPDATE to that fact must remember to touch every duplicate copy, and any UPDATE that misses even one copy leaves the database internally inconsistent with no error raised anywhere. Normalization moves each fact back to exactly one row in exactly one table, so an UPDATE to that fact is a single-row write with no possibility of a stale duplicate elsewhere -- the price is that reading the combined view back out now requires a JOIN to recombine what normalization split apart.

The entire reason to group writes into a transaction in the first place is that some of them are only individually meaningful in combination -- a debit from one account and a credit to another only make sense together; a debit with no matching credit is simply money disappearing. If a failure partway through could leave some writes applied and others not, every reader querying the database in that window would see a state that never should have been observable at all, and no caller could ever safely assume "if I see any of this transaction's writes, I see all of them." Atomic rollback is what makes a transaction's boundary a real boundary -- readers only ever see the state from before the transaction started, or the complete state after it commits, with nothing in between.

Escaping tries to neutralize specific characters (quotes, semicolons) after the fact, inside a string that is still ultimately going to be parsed as SQL syntax -- it is a defense that has to anticipate every dangerous character an attacker might use, and history is full of escaping schemes that missed an edge case. A parameterized query sidesteps the whole category of problem: the SQL text and the value are sent to the engine as two entirely separate pieces of information from the start, and the value is never parsed as SQL syntax at all, no matter what characters it contains. There is no escaping step to get wrong, because there is no point at which the value and the query text are ever combined into one string in the first place (Kata 4).