Databases & SQL

The relational model

Suppose you keep your app's data in a JSON file. It works until two processes write at once (one overwrites the other), until the file is 2 GB and you need "all orders over $100 from last March" (you must load everything), or until a crash halfway through a write leaves the file corrupt. A database management system (DBMS) solves exactly these problems: concurrent access, fast queries over data larger than memory, and durability across crashes.

A relational database, proposed by Edgar Codd in 1970, stores data in tables (formally relations). Each table has named, typed columns (attributes) and any number of rows (tuples). The rows have no inherent order; you ask for an order when you query.

• A primary key (PK) is a column, or set of columns, whose value uniquely identifies each row. It can never be NULL and never repeats.
• A foreign key (FK) is a column whose values must match a primary key in another table. It is how rows in different tables refer to each other, the database equivalent of a pointer, but checked.
• A natural key is a real-world identifier (an ISBN, an email). A surrogate key is an invented one, usually an auto-incrementing integer or a UUID. Surrogate keys are the common default because real-world identifiers change (people change emails).
• SQL (Structured Query Language) is the declarative language for all of this: you describe what you want, and the database's query planner decides how to get it.

Python ships with sqlite3, a binding to SQLite, a complete relational database that lives in a single file (or in memory) with no server. It is the most widely deployed database in the world: it is in every phone and every browser. It runs right here in the page, too.

Each row comes back as a tuple. INTEGER PRIMARY KEY is special in SQLite: it becomes an alias for the internal row id and fills itself in automatically, which is why we never supplied id.

Designing tables: normalization to 3NF

The hardest part of databases is not SQL; it is deciding which tables to have. A bad design causes anomalies, bugs that come from storing the same fact in more than one place. Here is a typical spreadsheet someone exported from an online shop:

Three anomalies are hiding here:

• Update anomaly: Ada's email appears in several rows. Change one and forget another, and the data now disagrees with itself (row 3 already does).
• Insertion anomaly: you cannot record a new product (or a customer who has not ordered yet) without inventing an order.
• Deletion anomaly: delete Ben's only order and you lose the fact that Ben exists.

Normalization is a step-by-step method for removing these by splitting tables so that each fact lives in exactly one place. The first three normal forms cover almost all practical cases.

First normal form (1NF): one value per cell

Every column holds a single, atomic value, and there are no repeating groups. The items column breaks this: it packs a list into a string, so you cannot ask "how many pens did we sell?" without parsing text. Fix it by giving each order line its own row:

Second normal form (2NF): depend on the whole key

The key of that table is now the pair (order_id, product). 2NF says every non-key column must depend on the whole key, not part of it. But date and customer depend only on order_id, and unit_price depends only on product. Those are partial dependencies. Split them out: an orders table keyed by order_id, a products table keyed by product, and an order_items table keyed by (order_id, product_id) that keeps only qty.

Third normal form (3NF): nothing but the key

3NF removes transitive dependencies: a non-key column that depends on another non-key column. In orders, customer_email depends on customer, which depends on order_id. Move customer facts into a customers table and store only customer_id in orders. The classic summary: every non-key attribute must depend on "the key, the whole key, and nothing but the key".

erDiagram
  CUSTOMERS ||--o{ ORDERS : places
  ORDERS ||--|{ ORDER_ITEMS : contains
  PRODUCTS ||--o{ ORDER_ITEMS : "appears in"
  CUSTOMERS {
    INTEGER id PK
    TEXT name
    TEXT email UK
  }
  ORDERS {
    INTEGER id PK
    INTEGER customer_id FK
    TEXT ordered_on
  }
  PRODUCTS {
    INTEGER id PK
    TEXT name UK
    INTEGER price_cents
  }
  ORDER_ITEMS {
    INTEGER order_id PK, FK
    INTEGER product_id PK, FK
    INTEGER qty
  }
    

Read the crow's-foot notation as "one customer places zero or more orders; one order contains one or more items". Now every fact appears once: changing Ada's email is a single-row update, and products and customers exist on their own.

Sometimes teams denormalize on purpose, keeping a redundant copy (say, an order's total) to make a hot query fast. That is a performance trade-off made after measuring, and it comes with the duty to keep the copy in sync. Start normalized.

SQL from zero: CREATE, INSERT, SELECT

SQL statements fall into families. DDL (data definition) shapes the schema: CREATE TABLE, ALTER TABLE, DROP TABLE. DML (data manipulation) changes rows: INSERT, UPDATE, DELETE. And queries read rows: SELECT. Keywords are case-insensitive; writing them in capitals is just a convention that helps them stand out.

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT    NOT NULL UNIQUE,
    price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
    category    TEXT    NOT NULL DEFAULT 'misc'
);

Constraints are rules the database enforces for you, on every write, from every program: NOT NULL (a value is required), UNIQUE (no duplicates), CHECK (an arbitrary condition), DEFAULT (a value when none is given), PRIMARY KEY, and REFERENCES (a foreign key). Put rules in the schema whenever you can: application bugs come and go, but the constraint is always there.

A basic query has this shape. Only SELECT and FROM are required:

SELECT name, price_cents / 100.0 AS price   -- which columns (and computed expressions)
FROM products                                -- which table
WHERE category = 'office' AND price_cents < 500   -- which rows
ORDER BY price_cents DESC, name              -- in what order
LIMIT 3;                                     -- how many

NULL: the absence of a value

NULL means "unknown" or "missing". It is not zero and not the empty string, and it uses three-valued logic: any comparison with NULL is itself NULL (unknown), and WHERE keeps only rows where the condition is true. So WHERE x = NULL matches nothing. Use IS NULL and IS NOT NULL, and COALESCE(x, default) to substitute a value.

The third line surprises people: x != 1 is unknown for the two NULL rows, so only the row with 3 matches. COUNT(x) counts non-NULL values; COUNT(*) counts rows.

UPDATE and DELETE

UPDATE products SET price_cents = price_cents + 50 WHERE category = 'kitchen';
DELETE FROM products WHERE name = 'Sticker';

Aggregation: GROUP BY and HAVING

Aggregate functions collapse many rows into one value: COUNT, SUM, AVG, MIN, MAX. With GROUP BY, they collapse each group of rows that share the grouping columns, producing one output row per group. HAVING then filters groups, the way WHERE filters rows.

The key to understanding SQL is the logical order in which a query is evaluated, which is not the order it is written in:

1. FROM (and joins): build the working set of rows
2. WHERE: keep rows that match (no aggregates allowed here; groups do not exist yet)
3. GROUP BY: form groups
4. HAVING: keep groups that match (aggregates allowed)
5. SELECT: compute output columns and aliases
6. ORDER BY: sort (can use the aliases)
7. LIMIT/OFFSET: cut

JOINs: combining tables

Normalization splits data into tables; joins put it back together for a query. A join pairs rows from two tables according to a condition, usually "this foreign key equals that primary key".

• INNER JOIN keeps only pairs that match. A customer with no orders disappears; a customer with two orders appears twice.
• LEFT (OUTER) JOIN keeps every row of the left table. Where there is no match, the right table's columns are filled with NULL.
• RIGHT and FULL OUTER joins also exist (SQLite supports them since 3.39), and CROSS JOIN pairs every row with every row.

Venn diagrams are a popular but misleading picture of joins (they describe set operations on one kind of thing, and they hide the fact that one row can match many). Think in rows instead:

Order 104 points at customer 9, who does not exist. That is an orphan, and it was only possible because this demo table has no foreign key constraint. With customer_id INTEGER REFERENCES customers(id) and foreign keys switched on, the insert would have been rejected.

How does a database actually join?

Conceptually, the simplest strategy is a nested loop: for each left row, scan the right rows for matches. That is O(n·m). Real planners use an index on the join column (O(n log m)) or build a hash table of one side (a hash join, O(n + m), the same idea as the dictionary-based grouping you know from M23). Step through a left join written in plain Python:

Subqueries and CTEs

A subquery is a query nested inside another. It can produce a single value (a scalar subquery), a list for IN, or a yes/no for EXISTS. A correlated subquery refers to the outer query's current row, so conceptually it runs once per outer row.

-- scalar: products more expensive than average
SELECT name FROM products WHERE price_cents > (SELECT AVG(price_cents) FROM products);

-- IN: customers who bought a kettle
SELECT name FROM customers WHERE id IN (
    SELECT o.customer_id FROM orders o JOIN order_items i ON i.order_id = o.id
    JOIN products p ON p.id = i.product_id WHERE p.name = 'Kettle');

-- correlated EXISTS: customers with at least one order over 40
SELECT name FROM customers c WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 40);

A common table expression (CTE), introduced with WITH, names a subquery so you can read the query top to bottom, like assigning intermediate variables. CTEs are the best tool for keeping complex SQL readable.

Indexes and EXPLAIN QUERY PLAN

Without help, WHERE email = 'ada@example.com' must look at every row: a full table scan, O(n). An index is a separate, sorted data structure (a B-tree, a wide, shallow cousin of the binary search tree from M24) that maps column values to row locations, turning the lookup into O(log n). With a million rows, that is about 20 steps instead of a million. Explore how a search tree narrows the search at each level:

Primary keys and UNIQUE columns get an index automatically. You add others with CREATE INDEX. To see what the planner will do, prefix a query with EXPLAIN QUERY PLAN: SCAN means reading the whole table, SEARCH ... USING INDEX means a B-tree lookup.

Notice the last plan: SCAN users USING COVERING INDEX. Because age is not the leftmost column, SQLite cannot search the index; it still reads all of it, just the smaller index instead of the table (a covering index contains every column the query needs). That is cheaper than a table scan but still O(n).

Indexes are not free. Each one takes space and must be updated on every INSERT, UPDATE, and DELETE, so a write-heavy table with ten indexes gets slow. Index the columns you filter, join, and sort on frequently, and verify with the query plan rather than guessing.

sqlite3 from Python: parameters and row factories

The DB-API 2.0 (PEP 249) is the common interface for database drivers in Python, so what you learn with sqlite3 carries over to PostgreSQL drivers such as psycopg. The core objects are a connection (a session with the database) and a cursor (the result of a statement, which you iterate or call fetchone(), fetchmany(n), or fetchall() on). conn.execute(...) is a shortcut that creates a cursor for you.

Parameterized queries: never build SQL with f-strings

Pasting user input into SQL text lets the input become code. That is SQL injection, one of the most exploited vulnerabilities in history. The fix is parameters: put a placeholder in the SQL and pass the values separately. The driver sends them as data, so they can never change the statement's structure. sqlite3 supports ? (positional) and :name (named) placeholders.

Row factories: rows you can read by name

By default rows are tuples, so code says row[2] and breaks when someone reorders the columns. Set conn.row_factory to change what each row becomes. sqlite3.Row is a fast built-in that supports both index and name access; a custom factory can build dataclasses directly.

Transactions and ACID

A transaction groups statements into one unit that either happens completely or not at all. Moving $50 from Alice to Bob is two updates; a crash between them must never leave the money in neither account. Databases promise ACID:

In sqlite3, a connection used as a context manager manages this for you: with conn: commits when the block finishes normally and rolls back if an exception escapes. (It does not close the connection; use contextlib.closing(conn) or call conn.close() for that.)

Two more tools: SAVEPOINT name / ROLLBACK TO name undo part of a transaction, and PRAGMA journal_mode = WAL (write-ahead logging) lets SQLite readers proceed while one writer writes, which is the setting to use for a web app on SQLite.

Migrations, ORMs, pooling, and choosing a database

Migrations

Your schema will change: new columns, new tables, new indexes. A migration is a versioned, ordered script that moves a database from schema version N to N+1. The database records which version it is at (often in a one-row table), and on startup or deploy you apply every pending migration in order, each in a transaction. Never edit a migration that has already run somewhere; write a new one. You will implement exactly this pattern with a schema_version table in Project 6.

In the SQLAlchemy world the standard tool is Alembic. It generates migration files (with upgrade() and downgrade() functions) by comparing your models to the database, and tracks the current revision in an alembic_version table.

pixi add alembic
pixi run alembic init migrations
pixi run alembic revision --autogenerate -m "add recipes.servings"
pixi run alembic upgrade head      # apply all pending migrations
pixi run alembic downgrade -1      # undo the last one

ORMs: SQLAlchemy 2.0

An object-relational mapper (ORM) maps classes to tables and objects to rows, so you work with Python objects and the ORM writes the SQL. SQLAlchemy is the standard in Python; its 2.0 style uses typed Mapped[...] annotations and an explicit select() construct.

A sensible professional stance: know SQL well (an ORM never excuses you from it), use an ORM or SQLAlchemy Core for application CRUD if your team likes it, and drop to SQL for reporting. Always look at the SQL an ORM generates (echo=True) when performance matters.

Connection pooling

Opening a connection to a server database costs a network round trip, authentication, and memory on the server (a PostgreSQL connection is a whole process). A connection pool keeps a set of open connections and lends them out per request, then takes them back. SQLAlchemy's engine pools automatically (create_engine(url, pool_size=5, max_overflow=10, pool_pre_ping=True)); large deployments add an external pooler such as PgBouncer. Size pools deliberately: 50 app workers with 20 connections each is 1,000 connections, more than most databases want.

SQLite or PostgreSQL?

Practice

Each exercise receives an open sqlite3 connection that the tests create and fill. Write the SQL inside the function and return plain Python values.

import sqlite3

def adults(conn):
    rows = conn.execute("SELECT name FROM people WHERE age >= 18 ORDER BY name")
    return [name for (name,) in rows]

import sqlite3

def find_user(conn, email):
    cursor = conn.execute("SELECT id, email, name FROM users WHERE email = ?", (email,))
    row = cursor.fetchone()
    if row is None:
        return None
    names = [column[0] for column in cursor.description]
    return dict(zip(names, row))

import sqlite3

def big_regions(conn, min_total):
    sql = """
        SELECT region, SUM(amount) AS total
        FROM sales
        GROUP BY region
        HAVING SUM(amount) >= ?
        ORDER BY total DESC, region
    """
    return conn.execute(sql, (min_total,)).fetchall()

import sqlite3

def spend_report(conn):
    sql = """
        SELECT c.name, COUNT(o.id) AS n, COALESCE(SUM(o.total), 0) AS spent
        FROM customers AS c
        LEFT JOIN orders AS o ON o.customer_id = c.id
        GROUP BY c.id
        ORDER BY spent DESC, c.name
    """
    return conn.execute(sql).fetchall()

def no_orders(conn):
    sql = """
        SELECT c.name FROM customers AS c
        LEFT JOIN orders AS o ON o.customer_id = c.id
        WHERE o.id IS NULL
        ORDER BY c.name
    """
    return [name for (name,) in conn.execute(sql)]

import sqlite3

def transfer(conn, src, dst, amount):
    if amount <= 0:
        raise ValueError("amount must be positive")
    with conn:
        row = conn.execute("SELECT balance FROM accounts WHERE name = ?", (src,)).fetchone()
        if row is None:
            raise ValueError(f"unknown account {src!r}")
        if row[0] < amount:
            raise ValueError("insufficient funds")
        credited = conn.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, dst))
        if credited.rowcount != 1:
            raise ValueError(f"unknown account {dst!r}")
        conn.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, src))

Mini-project: a normalized recipe database

Quiz

Recap

• Relational databases store rows in tables; primary keys identify rows and foreign keys connect tables.
• Normalize to 3NF so each fact lives in one place: atomic values, no partial dependencies, no transitive dependencies. Denormalize only after measuring.
• SQL is declarative. Its logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
• NULL is unknown: use IS NULL and COALESCE, and remember COUNT(col) skips NULLs.
• Inner joins keep matching pairs; left joins keep every left row; LEFT JOIN ... WHERE right.id IS NULL finds what is missing.
• Indexes are B-trees that turn scans into searches at a cost to writes; check with EXPLAIN QUERY PLAN.
• Always use parameters, set PRAGMA foreign_keys = ON in SQLite, and wrap related writes in with conn: so they commit or roll back together.
• Migrations version your schema; ORMs such as SQLAlchemy trade control for convenience; pools reuse connections.

Going further

• sqlite3: DB-API 2.0 interface for SQLite databases (Python docs)
• SQL as understood by SQLite and EXPLAIN QUERY PLAN
• SQLAlchemy 2.0 Unified Tutorial
• Alembic tutorial
• The PostgreSQL tutorial
• Use The Index, Luke: a free book on indexing for developers
• Next in the course: M45 · Web & APIs, and later M61 · Analytical SQL & DuckDB