Start the SQL practice

Open a local database

This route uses Docker and PostgreSQL 18. Docker runs the database in a container, a separate environment on your computer; the image supplies PostgreSQL and its psql query client. Have Docker installed and running first. On macOS or Windows, open Docker Desktop and wait for its engine to start; the official installation guide covers getting Docker onto your machine. On Linux, use a running Docker Engine.

Run the following in your computer’s terminal. The first run downloads the PostgreSQL image. It starts a disposable PostgreSQL container called staycurrent-practice, with no port published to your computer. We’ll create the article databases inside it. The password belongs only to this new practice instance. The instance remains running until we stop it at the end.

docker run --name staycurrent-practice --rm -d -e POSTGRES_PASSWORD=practice-only postgres:18

Check that startup has finished:

docker exec staycurrent-practice pg_isready -h localhost -U postgres

Continue when it reports localhost:5432 - accepting connections. If it says “rejecting connections” or “no response,” wait briefly and run the check again. If Docker cannot connect to its daemon, its engine is not ready. If the name is already in use, you already have a container with that name; keep it if it is your current practice instance, or choose a new container name consistently in these commands.

Put the practice files where psql can read them

Download designing-data.sql first. It creates customers, products, orders and order lines, including Ada’s O12 and O13 purchases. The later articles have separate extensions:

• tables-and-json.sql follows the JSON example through its typed-capacity design.
• query-results.sql adds the query article’s customers, empty order and shipments.
• query-pagination.sql creates the independent pagination example and runs its change experiments.

In your terminal, change into the folder where your browser saved the files. For example, cd ~/Downloads works when your downloads are there. Then copy the files into the container. These commands use the suggested download filenames. If your browser saves a different name, use that actual filename on the left of the copy command; keep the destination path inside the container as shown.

docker cp designing-data.sql staycurrent-practice:/tmp/designing-data.sql
# Copy the other files when you reach their articles:
docker cp tables-and-json.sql staycurrent-practice:/tmp/tables-and-json.sql
docker cp query-results.sql staycurrent-practice:/tmp/query-results.sql
docker cp query-pagination.sql staycurrent-practice:/tmp/query-pagination.sql

The terminal can see your downloaded files, while psql will run inside the container. Copying them gives it the /tmp/… paths used below. The extension copies can wait until you need them.

Load the shop and check the first answer

Back in the terminal, create the database for Designing a schema and Constraints, then open psql:

docker exec staycurrent-practice createdb -U postgres shop_design
docker exec -it staycurrent-practice psql -X -U postgres -d shop_design

The prompt changes to shop_design=#. You are now talking to PostgreSQL. Enter the following there, rather than in your terminal:

\i /tmp/designing-data.sql
\pset null 'SQL NULL'
SELECT count(*) AS orders FROM orders;

\i reads a file as a SQL script, sending its statements in order. \pset null makes missing SQL values display as “SQL NULL”; it does not change the records. Commands beginning with a backslash belong to psql and do not need a semicolon. SQL statements such as SELECT do.

The file prints O12’s receipt: Blue mug, quantity 2, agreed unit price 18.00 and line total 36.00. The final count is 2 orders. These are the checkpoints before trying a variation. If a table already exists or another setup command fails, stop and check which database you opened; the setup is meant to run once in an empty database.

You can now paste individual SQL statements from the article. A statement rejected by a constraint is often the intended result. Outside a transaction, the next statement can still run. Inside a failed transaction, enter ROLLBACK; before continuing. When an example asks you to inspect a returned row before deciding whether to continue, do that check before pasting its next step.

Start fresh when the article starts again

Enter \q in psql to leave it. Once your computer’s terminal prompt returns, create another empty database in the same container and connect to it. For example, start Queries and joins with:

docker exec staycurrent-practice createdb -U postgres shop_queries
docker exec -it staycurrent-practice psql -X -U postgres -d shop_queries

Inside its new psql session, load the base and extension:

\i /tmp/designing-data.sql
\pset null 'SQL NULL'
\i /tmp/query-results.sql

The query extension creates its extra records and runs the worked queries. You can now rerun its SELECT queries or try a variation; skip its record-creation and INSERT setup blocks. Keep this psql session open while trying the filter comparison. Its temporary tables disappear when you reconnect. If you do reconnect, rerun only the filter-comparison setup in the article; do not reload the entire extension and duplicate its records.

\i /tmp/designing-data.sql
\pset null 'SQL NULL'
\i /tmp/tables-and-json.sql

\i /tmp/designing-data.sql
\pset null 'SQL NULL'
\i /tmp/query-pagination.sql

Keep script execution separate from sending an entire file as one server query. psql’s \i preserves these statement boundaries. Do not wrap the pagination file in an extra transaction: it deliberately begins and ends transactions of its own.

A second practice attempt can use a new database name such as shop_json_2, with the base loaded once. That is simpler than trying to undo each earlier change. For a two-session example, open two terminals with psql connected to the same practice database, and follow the article’s schedule; its illustrated timeline does not itself run SQL.

Finish the practice

Leave each psql session with \q, then run docker stop staycurrent-practice in your terminal. Because we started this disposable instance with --rm, stopping it removes the container and its practice databases. Downloaded SQL files stay on your computer. Start a new instance and reload the files for another session.