07 · Relations, joins and all-or-nothing changes

A list of dictionaries is enough for a small calculation. It becomes fragile when multiple collections must agree: events refer to jobs, job names should be unique, and an accepted event should have an audit entry. A relational schema makes those rules inspectable. A transaction groups changes that must succeed or fail together.

The following examples use SQLite through Python's standard library. Each creates only an in-memory database and closes it. They do not start a server, modify a disk database, or demonstrate recovery after a process crash. SQLite behavior is not a substitute for learning another database's concurrency and type rules.

State the grain before writing the query

A job row describes one named job. An event row describes one observation with a unique event ID. The ID is not the job name: the same job can have many observations. A foreign key requires an event's job to exist. A primary key prevents duplicate event identities. NOT NULL and CHECK express different conditions: a check expression that evaluates to NULL does not reject a row in SQLite, so use both when both are required. See the official table-constraint reference.

import sqlite3
 
con = sqlite3.connect(":memory:", isolation_level=None)
try:
    con.execute("PRAGMA foreign_keys = ON")
    assert con.execute("PRAGMA foreign_keys").fetchone() == (1,)
    con.executescript("""
        CREATE TABLE jobs(name TEXT PRIMARY KEY NOT NULL);
        CREATE TABLE events(
            id TEXT PRIMARY KEY NOT NULL,
            job TEXT NOT NULL REFERENCES jobs(name),
            duration_ms INTEGER NOT NULL
                CHECK(typeof(duration_ms) = 'integer' AND duration_ms >= 0)
        );
    """)
    con.executemany("INSERT INTO jobs VALUES (?)", [("ingest",), ("publish",)])
    con.executemany("INSERT INTO events VALUES (?, ?, ?)", [
        ("e1", "ingest", 20), ("e2", "ingest", 30)
    ])
    rows = con.execute("""
        SELECT j.name, COUNT(e.id), COALESCE(SUM(e.duration_ms), 0)
        FROM jobs AS j LEFT JOIN events AS e ON e.job = j.name
        GROUP BY j.name ORDER BY j.name
    """).fetchall()
    assert rows == [("ingest", 2, 50), ("publish", 0, 0)]
 
    for row in [("e1", "ingest", 9), ("e3", "missing", 9), ("e4", "ingest", -1)]:
        try:
            con.execute("INSERT INTO events VALUES (?, ?, ?)", row)
        except sqlite3.IntegrityError:
            pass
        else:
            raise AssertionError(f"constraint did not reject {row!r}")
    assert con.execute("SELECT COUNT(*) FROM events").fetchone() == (2,)
finally:
    con.close()

The left join preserves publish even though it has no matching event. Its joined event columns are NULL. COUNT(e.id) counts non-NULL event IDs and yields zero; COUNT(*) would count the placeholder row and yield one. The sum of no matching durations is NULL, so COALESCE applies the report's explicit zero policy. ORDER BY is necessary to promise the returned order; insertion order is not a query contract. The SQLite aggregate reference specifies the different COUNT forms and the NULL result of SUM over no non-NULL inputs.

The SQLite SELECT documentation describes join construction, outer-join NULL rows, filtering and grouping. SQL evaluation is declarative: this conceptual order helps reason about results, but it is not a claim that the engine physically materializes each intermediate table.

If you add WHERE e.duration_ms > 25, publish disappears because its NULL-extended row does not satisfy the predicate. If you mean “all jobs, counting only long events,” put the event predicate in the join's ON clause. If you join events to a second one-to-many table, each event may appear multiple times, inflating the sum. Write the grain of the intermediate result before aggregating it; aggregate each child relation first when appropriate.

SQLite applies type affinity, so the typeof check here validates the stored integer, not the caller's original Python type. A Python boolean can arrive as integer one. Application boundary validation remains necessary when the input contract distinguishes them.

Bind data, never assemble it into SQL syntax

Question-mark placeholders bind values. The Python sqlite3 reference recommends them instead of string formatting to avoid SQL injection. They do not parameterize table names, sort direction or arbitrary SQL structure; choose those from a closed allowlist if dynamic structure is needed.

import sqlite3
 
con = sqlite3.connect(":memory:")
try:
    con.execute("CREATE TABLE labels(name TEXT NOT NULL)")
    label = "x'); DROP TABLE labels; --"
    con.execute("INSERT INTO labels VALUES (?)", (label,))
    assert con.execute("SELECT name FROM labels WHERE name = ?", (label,)).fetchall() == [(label,)]
    assert con.execute("SELECT COUNT(*) FROM labels").fetchone() == (1,)
finally:
    con.close()

The suspicious string is stored as a string. This does not test every injection surface or authorize a real attack. It demonstrates the intended data-versus-syntax boundary on a disposable database. Replacing quote characters is not an equivalent general defense.

Atomicity is a group property

Suppose each accepted event must have one audit record. If those writes are separately committed, a failure between them leaves inconsistent state. Put both in the same transaction. Explicitly roll back on failure; a statement error alone does not always roll back every previous statement in a transaction.

import sqlite3
 
con = sqlite3.connect(":memory:", isolation_level=None)
try:
    con.executescript("""
        CREATE TABLE accepted(id TEXT PRIMARY KEY NOT NULL);
        CREATE TABLE audit(id TEXT PRIMARY KEY NOT NULL);
    """)
    con.execute("BEGIN IMMEDIATE")
    try:
        con.execute("INSERT INTO accepted VALUES (?)", ("e1",))
        raise RuntimeError("injected failure before audit")
        # The failure above represents losing control before the second write.
    except RuntimeError:
        con.execute("ROLLBACK")
    assert con.execute("SELECT COUNT(*) FROM accepted").fetchone() == (0,)
    assert not con.in_transaction
 
    con.execute("BEGIN IMMEDIATE")
    try:
        con.execute("INSERT INTO accepted VALUES (?)", ("e1",))
        con.execute("INSERT INTO audit VALUES (?)", ("e1",))
        con.execute("COMMIT")
    except Exception:
        if con.in_transaction:
            con.execute("ROLLBACK")
        raise
    assert con.execute("SELECT * FROM accepted").fetchall() == [("e1",)]
    assert con.execute("SELECT * FROM audit").fetchall() == [("e1",)]
finally:
    con.close()

These examples choose isolation_level=None and explicit SQL transaction control under Python's legacy transaction-control mode, which was the default in the tested Python 3.12.3 environment. Newer Python documentation recommends the separate autocommit API; a future default change requires revisiting this setup. Do not mix these examples casually with a caller's already-open transaction: SQLite BEGIN transactions do not nest; use a deliberate savepoint or caller-owned transaction contract.

The SQLite transaction reference states that multiple readers are supported but only one writer can hold a write transaction at a time. BEGIN IMMEDIATE attempts to acquire that write transaction immediately and can fail with SQLITE_BUSY. It is not a guarantee of infinite availability. A production caller needs bounded retries and a clear error path. Database atomicity also does not automatically include an email sent or a file written outside the database.

Exercises

1. Modify the report to include only durations above 25 while retaining jobs with no qualifying events.
2. If each event has two tag rows, why does joining tags before summing double every duration?
3. Does the rollback test prove durability across power failure?

Exit artifact: schema, grain definitions, parameterized queries and a rollback test. Next: Retries and the uncertain outcome.