SQL Lab

SQL Lab explains how to run the book's examples. Each chapter's full scripts live next to the book together with the PostgreSQL version they were checked on, so you can create the schema, run the operations and see the constraint errors for yourself. The appendix covers starting a local PostgreSQL instance and how the scripts are organized.

What you need

The 52 pattern and anti-pattern chapters each have one script at book/assets/<slug>/<slug>.sql. Their headers record PostgreSQL 18.6 as the checked version. Use a PostgreSQL 18 server and its psql client: examples include uuidv7(), temporal keys, PostgreSQL ranges, PL/pgSQL and psql backslash commands. Other engines' implementation notes in the chapters are porting guidance, not claims that these files run unchanged there.

Use a disposable cluster with a superuser connection. Some examples create roles, change privileges and install the supplied ltree or btree_gist extensions. The tenant examples also create passwordless login roles, and Database per Tenant creates dt_a and dt_b and reconnects as its runtime roles. Those reconnects require the lab's local trust authentication. A fresh database alone does not isolate cluster-wide roles or those extra databases.

The commands below start from the repository root. Choose either server setup, then run one example or the complete suite. Keep server messages in English: several scripts compare the exact diagnostic as well as its SQLSTATE. Leave plpgsql.check_asserts enabled so the scripts' ASSERT checks execute.

A PostgreSQL 18 Docker container

The official PostgreSQL image provides the server and clients. This lab uses no published port, disables container networking and stores the cluster in temporary memory; local trust lets the tenant scripts reconnect without passwords. PostgreSQL 18 images use /var/lib/postgresql as the volume location.

docker run --detach --name ddp-sql-lab \
  --network none \
  --tmpfs /var/lib/postgresql \
  --env POSTGRES_HOST_AUTH_METHOD=trust \
  --mount "type=bind,source=$PWD,target=/book,readonly" \
  postgres:18

until docker exec ddp-sql-lab pg_isready -q -h 127.0.0.1 -U postgres; do
  sleep 1
done
docker exec ddp-sql-lab psql -X -U postgres -d postgres \
  -c 'SELECT version();'

If readiness never arrives, inspect docker logs ddp-sql-lab. The repository is mounted read-only at /book; its SQL scripts remain ordinary files, not initialization hooks. For example, run Surrogate Key in its own fresh database:

docker exec ddp-sql-lab createdb -U postgres --template=template0 lab_one
docker exec ddp-sql-lab psql -X -v ON_ERROR_STOP=1 -U postgres \
  -d lab_one -f /book/book/assets/surrogate-key/surrogate-key.sql
docker exec ddp-sql-lab dropdb -U postgres lab_one

When you finish all experiments, remove the container and its temporary cluster:

docker rm --force --volumes ddp-sql-lab

A local throwaway cluster

Install the full PostgreSQL 18 server distribution, including initdb, pg_ctl, postgres, clients and supplied extensions. A client-only installation cannot create this cluster. Put that distribution's bin directory first on PATH; for Homebrew on Apple Silicon the path is shown below. On another installation, replace it with your PostgreSQL 18 binary directory.

export PATH=/opt/homebrew/opt/postgresql@18/bin:$PATH
postgres --version
psql --version

lab_dir=$(mktemp -d /tmp/ddp.XXXXXX)
initdb -D "$lab_dir/data" -U postgres --locale=C --encoding=UTF8 \
  --auth-local=trust --auth-host=reject
pg_ctl -D "$lab_dir/data" -l "$lab_dir/server.log" \
  -o "-k $lab_dir -c listen_addresses=''" -w start

export PGHOST="$lab_dir" PGPORT=5432 PGUSER=postgres PGDATABASE=postgres
psql -X -c 'SELECT version();'

Run this as your ordinary operating-system user, not root. initdb creates the cluster and the named database superuser; pg_ctl starts it and waits for readiness. The short /tmp path keeps the Unix socket pathname short. An empty listen_addresses disables TCP; PGHOST selects the socket directory, so this does not use an existing server listening on TCP port 5432.

Keep the shell and lab_dir value until cleanup. After running the examples, stop the server before deleting its directory:

pg_ctl -D "$lab_dir/data" -m fast -w stop
rm -rf -- "$lab_dir"
unset PGHOST PGPORT PGUSER PGDATABASE

Run one script

With the local cluster running and the connection variables exported, create a database from template0, run one file, and drop the database:

createdb --maintenance-db=postgres --template=template0 lab_one
psql -X -v ON_ERROR_STOP=1 --dbname=lab_one \
  --file=book/assets/surrogate-key/surrogate-key.sql
dropdb --maintenance-db=postgres lab_one

psql -X ignores startup files, and ON_ERROR_STOP=1 makes unexpected errors stop the file with a nonzero exit status. Keep that option even though most scripts also set it themselves. Do not add --single-transaction: scripts own their transaction boundaries, some deliberately commit, and Database per Tenant issues CREATE DATABASE and reconnects.

Many examples roll their lab transaction back at the end. Others leave schemas and rows for inspection, so use a new database for each attempt rather than assuming a file is rerunnable. The tenant labs leave cluster-wide objects too; use the complete runner below for their cleanup or discard the entire cluster after running them manually. Sequential example statements demonstrate protocols and assertions; a passing file does not itself exercise every two-session race shown in a chapter.

Recognize intended failures

An ERROR line is sometimes the demonstration succeeding. Judge it by the nearby expectation check and the final process status, not by searching the output for the word ERROR.

For example, Surrogate Key's script uses this sequence inside its lab transaction:

SAVEPOINT expected_error;
\set ON_ERROR_STOP off
INSERT INTO customer (customer_number, display_name)
VALUES ('C-204', 'Duplicate');
\set rejected_state :SQLSTATE
\set ON_ERROR_STOP on
ROLLBACK TO SAVEPOINT expected_error;
SELECT :'rejected_state' = '23505' AS rejected \gset
\if :rejected
\else
    \quit 1
\endif

The saved state must be 23505 (unique violation). It is captured before ROLLBACK TO, because subsequent SQL changes :SQLSTATE. expected_error is a savepoint name used by several scripts, not a special PostgreSQL feature; other files use descriptive names such as rejected_operation. The rollback recovers the transaction so later assertions can run.

Exclusive Arc instead catches check_violation or foreign_key_violation in DO blocks and prints the expected SQLSTATE. Its deliberate RAISE EXCEPTION after an operation ensures that unexpected success is not silently accepted. Some handlers also compare SQLERRM. Delimited Lists defines the reusable temporary helper, while Association Table includes ASSERT checks of retained rows and cascade effects.

Successful checks can therefore produce raw errors, notices and ordinary query output in the same run. A nonzero psql status is a broken lab run even if earlier expected-error notices appeared. An unchanged script failing on a different diagnostic language or server version still needs investigation; do not suppress its assertions to obtain a pass.

Run all scripts

The POSIX shell runner uses set -eu and PostgreSQL clients from PATH. It takes host/socket, port, user and other connection defaults from the standard PG environment variables. PGDATABASE names the maintenance database, defaulting to postgres; each script gets an explicitly selected fresh database. The Database per Tenant example also reconnects to postgres, so keep that database available.

For the local cluster:

sh book/assets/sql-lab/run-all.sh

For the Docker setup, run the same file inside the container using its installed clients and local socket:

docker exec \
  --env PGHOST=/var/run/postgresql --env PGUSER=postgres \
  --env PGDATABASE=postgres \
  ddp-sql-lab sh /book/book/assets/sql-lab/run-all.sh

The runner discovers book/assets/*/*.sql in pathname order, independently of your working directory. For each file it creates a uniquely named database from template0, runs psql -X -v ON_ERROR_STOP=1, drops the database and prints PASS <slug>. It stops at the first nonzero script exit and prints FAIL <slug>; its exit trap attempts cleanup on failure or interruption. Cleanup failures also fail the run. With all current files passing, the final line is PASS: 52 scripts.

The runner also removes the tenant labs' auxiliary databases (dt_a, dt_b) and roles (ss_app, st_owner, st_app_a, st_app_b, dt_owner, dt_app_a, dt_app_b). Audit Log creates audit_example_app inside a transaction that rolls back. A preflight check rejects any of these existing names before running anything; it does not reuse or delete pre-existing lab objects. Run only one suite at a time in its disposable cluster. After an uncatchable termination or a cleanup failure, discard that cluster and start another.

The complete suite has been run successfully on a throwaway PostgreSQL 18.6 cluster: all 52 files passed with these checks enabled. The outbox lab assumes a broker acknowledgement in SQL rather than contacting a broker; queue and reservation examples advance test state instead of waiting for real worker deadlines.

Script catalog

The groups and chapter order below follow the book's Summary. Each file is self-contained for its documented lab setup; the preceding chapter is not a prerequisite.

Identity

Relationships

Reference and State

Hierarchies

Types and Attributes

Time and Deletion

Domain Structures

Reliable Writes

Tenant Isolation

Growth and Change

Anti-patterns