The Databases Picture Book
usersdrawer = table ordersdrawer = table productsdrawer = table One row id = 7 name = Ama email = ama@x.com plan = pro SQLSELECT … FROM …
PLATE 0 — Tables are drawers, rows are folders, and SQL is how you ask the clerk.
A picture book for grown-ups · with queries

Databases: SQL, PostgreSQL, and SQLite

Nine spreads. Each has a picture, the idea, and queries you can run today: in SQLite with zero setup, then in PostgreSQL for the real thing.

Say it like a pro

"Normalized schema with constraints, indexed for the queries we actually run, changed through migrations, and backed up with a restore we've tested."

Use the tabs or the ← → keys to turn pages.

SQLite your app+ SQLite library app.db (a file) PostgreSQL clients server Rule of thumb SQLite: apps, mobile, tools, prototypes, tests, small sites Postgres: many users writing at once, a team, a network service same SQL ideas in both · start small, move up when you outgrow it
PLATE 1 — SQLite is a file you open. PostgreSQL is a service you connect to.
Chapter 1 · Choosing & connecting

Two databases, one language

SQLitePostgreSQL
Setupnone; one fileinstall a server
Concurrencymany readers, one writermany readers and writers
Users / authnone (file perms)roles, grants
Typesflexible ("affinity")strict, rich (JSONB, arrays)
Best forembedded, localmulti-user production
SQLite · try it right now
$ sqlite3 demo.db
sqlite> .tables  .schema users  .headers on  .mode column  .quit
PostgreSQL · local server in Docker
$ docker run -d --name pg -e POSTGRES_PASSWORD=devpass -p 5432:5432 postgres:16
$ psql -h localhost -U postgres
postgres=# \l  \c mydb  \dt  \d users  \du  \q
Say it like a pro

"SQLite for embedded and tests, Postgres once we need concurrent writers and real access control."

idemailageplanteam_id 1ama@x.com31pro4 2kofi@x.com27freeNULL PRIMARYKEY UNIQUENOT NULL CHECK(age ≥ 0) DEFAULT'free' FOREIGNKEY Constraints make bad data impossible the database refuses a duplicate email, a negative age, a missing team NULL means "unknown", not zero and not empty string rules in the database protect you from every buggy app that writes to it
PLATE 2 — Types say what a column holds. Constraints say what it may never hold.
Chapter 2 · Tables, types & constraints

Define the shape, and let the database enforce it

PostgreSQL
CREATE TABLE teams (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  TEXT NOT NULL UNIQUE
);
CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  age        INT  CHECK (age >= 0),
  plan       TEXT NOT NULL DEFAULT 'free',
  team_id    BIGINT REFERENCES teams(id) ON DELETE SET NULL,
  settings   JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
SQLite (same idea, lighter syntax)
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,           -- auto-increments
  email      TEXT NOT NULL UNIQUE,
  age        INTEGER CHECK (age >= 0),
  plan       TEXT NOT NULL DEFAULT 'free',
  team_id    INTEGER REFERENCES teams(id),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;
PRAGMA foreign_keys = ON;                     -- off by default in SQLite!
money
integer cents or NUMERIC, never floating point
time
Postgres TIMESTAMPTZ; SQLite ISO-8601 text
Say it like a pro

"Primary keys, NOT NULL by default, unique and check constraints, and foreign keys on."

FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT you write SELECT first, but it runs fifth Reading a query from the inside out 1 · which table(s)? FROM 2 · which rows? WHERE (before grouping) 3 · bucket them? GROUP BY → aggregates 4 · which buckets? HAVING (after grouping) 5 · which columns, order, how many?
PLATE 3 — SQL runs in a different order than you write it.
Chapter 3 · Reading & writing data

CRUD, and the SELECT you'll write a thousand times

INSERT INTO users (email, age) VALUES ('ama@x.com', 31), ('kofi@x.com', 27);

SELECT id, email FROM users WHERE plan = 'pro' ORDER BY created_at DESC LIMIT 10;

UPDATE users SET plan = 'pro' WHERE email = 'ama@x.com';   -- always a WHERE!
DELETE FROM users WHERE id = 2;

SELECT plan, count(*) AS n, avg(age) AS avg_age
FROM users
WHERE age IS NOT NULL
GROUP BY plan
HAVING count(*) > 5
ORDER BY n DESC;

-- Postgres: insert-or-update. SQLite supports the same syntax.
INSERT INTO users (email, plan) VALUES ('ama@x.com', 'team')
ON CONFLICT (email) DO UPDATE SET plan = EXCLUDED.plan
RETURNING id, plan;
NULL
test with IS NULL, never = NULL
LIKE
name LIKE 'A%'; Postgres also has ILIKE (case-insensitive)
safety
run SELECT with the same WHERE first, or use a transaction
Say it like a pro

"I filter with WHERE, aggregate with GROUP BY, and I never run UPDATE or DELETE without checking the rows first."

usersorders INNER JOINonly matches usersorders LEFT JOINall users, orders if any usersordersLEFT JOIN result 1 Ama(1, $20)Ama · $20 2 Kofi(1, $35)Ama · $35 3 Esi(none)Esi · NULL LEFT keeps Esi; INNER would drop her
PLATE 4 — A join stitches rows from two tables where a key matches.
Chapter 4 · Joins & subqueries

Combine tables without copying data

-- who ordered what
SELECT u.email, o.id AS order_id, o.total_cents
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total_cents > 5000;

-- every user, even those with no orders
SELECT u.email, count(o.id) AS orders, coalesce(sum(o.total_cents),0) AS spent
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY spent DESC;

-- users who never ordered
SELECT email FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- readable steps with a CTE
WITH big AS (
  SELECT user_id, sum(total_cents) AS spent FROM orders GROUP BY user_id
)
SELECT u.email, big.spent FROM big JOIN users u ON u.id = big.user_id
WHERE big.spent > 100000;
window fn
row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) for "latest per user"
fan-out
joining two one-to-many tables multiplies rows; aggregate first
Say it like a pro

"LEFT JOIN to keep the unmatched side, CTEs for readability, and aggregate before joining many-to-many."

orders (messy) order | customer | addr | items 1 | Ama | 12 High St | pen,ink 2 | Ama | 12 High St | pad address typed twice → drifts customers orders order_items products Normal forms in one breath 1NF one value per cell (no "pen,ink" lists) 2NF every column depends on the whole key 3NF columns depend on the key, not on each other store each fact once · reference it by key denormalize on purpose, later, only for measured speed
PLATE 5 — Store each fact in exactly one place.
Chapter 5 · Schema design

Model the nouns, then the relationships

  1. List the things (users, products, orders) → one table each.
  2. Give each a primary key.
  3. One-to-many: put a foreign key on the "many" side (orders.user_id).
  4. Many-to-many: a join table (order_items(order_id, product_id, qty)).
  5. Add constraints and sensible ON DELETE behavior.
CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  qty        INT    NOT NULL CHECK (qty > 0),
  price_cents INT   NOT NULL,          -- price at time of sale
  PRIMARY KEY (order_id, product_id)
);
snapshot
copy the price into the order item: prices change, history shouldn't
soft delete
a deleted_at column instead of removing rows
JSONB
good for flexible extras; not a substitute for real columns
Say it like a pro

"Third normal form by default; denormalize only for a measured performance reason."

Seq scan reads every row · O(n) Index scan (B-tree) root A–M N–Z leaf leaf ✔ a few hops · O(log n) The trade-off faster reads on the indexed column(s) slower writes + more disk (every insert updates every index) index what you filter, join, and sort on · not everything
PLATE 6 — An index is the book's index: skip straight to the page.
Chapter 6 · Indexes & EXPLAIN

Make the slow query fast, and prove it

CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));
CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending';  -- partial
See what the planner does
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;
-- look for: Index Scan (good) vs Seq Scan on a big table (suspect)

-- SQLite
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;
-- SEARCH orders USING INDEX idx_orders_user  (good)  /  SCAN orders (bad)
composite
column order matters: (user_id, created_at) serves filter-by-user then sort
functions
WHERE lower(email) = … ignores a plain index; index the expression
ANALYZE
refreshes the planner's statistics
Say it like a pro

"EXPLAIN ANALYZE first, index the filter and join columns, then measure again."

BEGIN UPDATE accounts SET bal = bal − 100 WHERE id = 1 UPDATE accounts SET bal = bal + 100 WHERE id = 2 COMMIT ✔ both ROLLBACK ✘ neither Atomic Consistent Isolated Durable all or nothing · rules hold · others don't see halfway · survives a crash
PLATE 7 — A transaction is an all-or-nothing box around several changes.
Chapter 7 · Transactions & concurrency

Several changes, one atomic step

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;            -- or ROLLBACK; if anything looked wrong

-- Postgres: lock a row while you decide, so nobody changes it under you
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- ... check, then UPDATE ...
COMMIT;
Postgres isolationStops
READ COMMITTED (default)dirty reads
REPEATABLE READ+ non-repeatable reads
SERIALIZABLE+ anomalies; retry on conflict
MVCC
Postgres keeps row versions, so readers don't block writers
SQLite
one writer at a time; enable PRAGMA journal_mode=WAL; for concurrent reads
deadlock
two transactions waiting on each other; keep them short and lock in a consistent order
Say it like a pro

"Short transactions, consistent lock order, retry on serialization failure."

PoolerPgBouncer PostgreSQL Access one role per app/person grant only what's needed app role ≠ owner role TLS + pg_hba.conf rules Safety pg_dump on a schedule WAL archiving → PITR replica for failover/reads test a restore. Always.
PLATE 8 — Connections are expensive, access should be narrow, and backups are only real if restored.
Chapter 8 · Running PostgreSQL

Roles, backups, and keeping an eye on it

CREATE ROLE app LOGIN PASSWORD 'use-a-secret-manager';
CREATE DATABASE shop OWNER app;
GRANT CONNECT ON DATABASE shop TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
CREATE ROLE reporting LOGIN;  GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
$ pg_dump -Fc -h localhost -U postgres shop > shop.dump       # backup
$ createdb -U postgres shop_restore
$ pg_restore -U postgres -d shop_restore shop.dump           # restore test
$ psql -c "VACUUM (ANALYZE);" shop
-- what's running, and what's slow?
SELECT pid, state, now() - query_start AS running, left(query, 80)
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY running DESC;
SELECT pg_size_pretty(pg_database_size('shop'));
SELECT pg_cancel_backend(12345);   -- stop a runaway query
Say it like a pro

"Least-privilege roles, a connection pooler, nightly dumps plus WAL for point-in-time recovery, and a restore drill."

App codedriver / ORM SELECT … WHERE id = $1 params: ["42"] DB query text and data travel separately → no SQL injection Production habits migrations in version control (Alembic, Flyway, Prisma, Knex) connection pool · timeouts · retry on transient errors N+1 queries: fetch related rows in one JOIN, not in a loop paginate with keyset (WHERE id > last) rather than big OFFSET secrets in env vars · least-privilege app role · backups tested
PLATE 9 — Parameters keep data and code apart. That one habit stops SQL injection.
Chapter 9 · Using it from code

Parameters, migrations, and the N+1 trap

# Python + SQLite (built in)
import sqlite3
con = sqlite3.connect("app.db")
con.execute("PRAGMA foreign_keys = ON")
con.execute("INSERT INTO users (email) VALUES (?)", (email,))     # ? placeholders
row = con.execute("SELECT id, plan FROM users WHERE email = ?", (email,)).fetchone()
con.commit()

# Python + PostgreSQL (psycopg 3)
import psycopg
with psycopg.connect("postgresql://app:secret@localhost/shop") as conn:
    cur = conn.execute("SELECT id, plan FROM users WHERE email = %s", (email,))
    row = cur.fetchone()

# NEVER: f"SELECT * FROM users WHERE email = '{email}'"   ← SQL injection
A migration, in plain SQL
-- 0007_add_last_login.sql
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;
CREATE INDEX idx_users_last_login ON users (last_login);
N+1
1 query for users + N for each one's orders. Use a JOIN or WHERE user_id = ANY($1).
next steps
read the planner (EXPLAIN), then replication, partitioning, and full-text search; practice on pgexercises.com and sqlbolt.com
Say it like a pro

"Parameterized queries, migrations in git, a pool, and I check for N+1 before it ships."