• v-b0
• About 75 minutes
• Hands-on: you run commands
• Introduces 12 new ideas

Baseline: your first backend

You will build the smallest backend that works: a database, and a REST API that is generated automatically from it. Then you will find out where it is weak, because the weak spots are what the rest of the course fixes.

1. The big picture

Every backend is, at its core, data behind an API. This variant builds exactly that and nothing more. It has two programs:

• PostgreSQL (often just "Postgres") is the database. It stores the data.
• PostgREST is the API. It looks at the structure of your database and creates web addresses for it automatically. You write no server code.

The traditional way to build an API is to write a program by hand that receives a request, runs some SQL, and formats the answer. PostgREST removes that program. It is only a translator between HTTP and SQL. A request for /candidates becomes a query on a table called candidates, and the rows that come back are sent as JSON.

2. Docker in five minutes

Installing a database directly on your computer is slow, and it leaves a mess behind. Docker avoids that. It lets you run a program in an isolated box that already contains everything the program needs. When you are done, you delete the box and your computer is as clean as before.

Compose files use a format called YAML. In YAML, the indentation is the structure. Use spaces, never tabs, and keep each level indented by exactly two spaces. Most beginner errors in this course come from a misaligned line.

3. Part 1: Start the database

You will build the stack in small pieces and run it after each piece. That way you always know which change caused which result. We start with the database alone.

Create the project folder

Make a new folder named exactly v-b0 and open a terminal inside it.

$ mkdir v-b0
$ cd v-b0

Write the compose file

Create a file named compose.yml with this content:

services:
  db:
    image: postgres:18-alpine
    environment:
      POSTGRES_DB: app
      POSTGRES_PASSWORD: devpassword
    ports:
      - "5432:5432"

Here is what each line does:

Start it

$ docker compose up -d
 Network v-b0_default  Created
 Container v-b0-db-1  Created
 Container v-b0-db-1  Started
$ docker compose ps

Your first run will also print some lines about pulling the image, with long ID codes. Those lines will not match anyone else's, which is normal.

Notice the names. Docker created a network called v-b0_default and a container called v-b0-db-1: the folder name, the service name, and an instance number. We did not ask for a network. Compose made one for us. We will come back to that in v-b1.

4. Part 2: Look inside with psql

A database is invisible until you ask it questions. The standard tool for this is psql, which already exists inside the Postgres container.

Run psql inside the container, as the postgres user, connected to the app database:

$ docker exec -it v-b0-db-1 psql -U postgres -d app
psql (18.6)
Type "help" for help.

app=#

Here is what each piece of the command means. docker exec runs a command inside a running container. -it makes it interactive, so you can type. -U postgres says which database user to log in as, and -d app says which database to open. The prompt app=# tells you that you are connected to the app database, and the # means you are a superuser.

Now try the three meta-commands you will use all through this course:

app=# \dt
Did not find any tables.

Leave psql open for the next part. (To leave it any time, type \q.)

5. Part 3: Add the API (and meet an error)

The database is running, but it is still empty: no tables, no data. We will add PostgREST now anyway. Instead of showing you a finished file, we will build it up the way a working engineer would, and in this part and the next you will meet the two errors that nearly everyone hits the first time. Learning to read these errors is a core skill.

First attempt: the bare minimum

Add a second service to compose.yml, below db and indented at the same level:

services:
  db:
    image: postgres:18-alpine
    environment:
      POSTGRES_DB: app
      POSTGRES_PASSWORD: devpassword
    ports:
      - "5432:5432"

  apiserver:
    image: postgrest/postgrest:v13.0.7
    ports:
      - "127.0.0.1:3000:3000"
    environment:
      PGRST_DB_URI: "postgres://postgres:devpassword@db:5432/app"
      PGRST_DB_SCHEMA: "public"
    depends_on:
      - db

The new lines mean the following.

Apply the change. Compose notices what changed and only starts the new service. Then call the API:

$ docker compose up -d
$ curl -i http://localhost:3000/candidates
HTTP/1.1 401 Unauthorized
{"code":"PGRST302","details":null,"hint":null,"message":"Anonymous access is disabled"}

Error 1: "Anonymous access is disabled"

An HTTP response starts with a status code, a three-digit number that tells the client how the request went. Codes starting with 2 mean success, 4 mean the client's request was wrong or not allowed, and 5 mean the server failed. 401 Unauthorized means "I do not know who you are, so I will not help". The PGRST302 code is PostgREST's own label for this error, which you can search for in its documentation.

This is PostgREST being careful. It will not let anyone in unless you explicitly say so. To allow anonymous visitors, you give it the name of a database role to act as when no identity is supplied. That will be a role called anonymous, which we create in the next part. It can do only what that role is granted.

Add one line to the environment: section of apiserver:

    environment:
      PGRST_DB_URI: "postgres://postgres:devpassword@db:5432/app"
      PGRST_DB_SCHEMA: "public"
      PGRST_DB_ANON_ROLE: "anonymous"

Changed settings only take effect when the container is created again. Running docker compose up -d again does this, because Compose notices that the service changed and replaces just that container.

$ docker compose up -d
$ curl -i http://localhost:3000/candidates
HTTP/1.1 404 Not Found
{"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'public.candidates' in the schema cache"}

This time the answer is different, and it is exactly what we should expect. PostgREST now knows who the visitor is (the anonymous role), so it goes looking for a table called candidates. There is no such table yet, so it reports 404 Not Found. The code PGRST205 means "table not found in the schema cache". We will explain the words "schema cache" shortly. For now, the next job is clear: create the table.

6. Part 4: Create a table (and meet a second error)

An empty database is not much use. Save the following as init.sql in your v-b0 folder. It is a SQL script: a list of commands that run one after another.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE ROLE anonymous NOLOGIN;

CREATE TABLE IF NOT EXISTS candidates (
    candidate_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name         VARCHAR(100) NOT NULL,
    created_at   TIMESTAMPTZ DEFAULT now(),
    updated_at   TIMESTAMPTZ DEFAULT now()
);

GRANT USAGE ON SCHEMA public TO anonymous;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO anonymous;

INSERT INTO candidates (name) VALUES
    ('Harry'), ('Hermione'), ('Luna'), ('Ron'), ('Neville');

This script has several new ideas. Take them one block at a time.

Run the script

The script is on your computer, but psql runs inside the container, so first copy the file in. Leave psql with \q, or open a second terminal, and run:

$ docker cp init.sql v-b0-db-1:tmp/init.sql
$ docker exec -it v-b0-db-1 psql -U postgres -d app

At the app=# prompt, use the \i meta-command ("include") to run the file:

app=# \i /tmp/init.sql
CREATE EXTENSION
CREATE ROLE
CREATE TABLE
GRANT
GRANT
INSERT 0 5

Each line is Postgres confirming one command. INSERT 0 5 means five rows were added. Now check your work with the commands you already know. The recording below shows what you should see.

Why is it still a 404?

Now call the API again, as you did before. You created the table and the data a moment ago, so you would expect five rows:

$ curl -i http://localhost:3000/candidates
HTTP/1.1 404 Not Found
{"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'public.candidates' in the schema cache"}

Nothing has changed in the answer, even though psql shows that the table exists. This is a classic moment of confusion, so slow down and ask: what does PostgREST know that I do not?

PostgREST started when the database was empty, so its photograph shows no tables. The general lesson is: whenever the structure of the database changes, PostgREST must be told to refresh its cache. In real projects this happens right after every change to the database structure.

PostgreSQL has a built-in messaging feature. A program can listen on a named channel, and anything can notify that channel. PostgREST listens on a channel called pgrst. Sending it the message reload schema makes it rebuild the cache. At the psql prompt:

app=# NOTIFY pgrst, 'reload schema';
NOTIFY

$ curl -i http://localhost:3000/candidates
HTTP/1.1 200 OK
Server: postgrest/13.0.7
Content-Range: 0-4/*
[{"candidate_id":"...","name":"Harry",...}, ...]

It works. 200 OK means success, and the body is a JSON list of your five rows. The Content-Range: 0-4/* header means "rows 0 to 4 are included".

PostgREST also describes itself. Fetch the root address, http://localhost:3000/, and you get an OpenAPI document: a machine-readable list of every endpoint it generated. Tools can read it to build documentation or client code for you.

7. Part 5: Make the setup automatic

Typing \i and NOTIFY by hand is fine for learning, but you would never rebuild a system this way. We want docker compose up to produce a working backend by itself, every time.

The Postgres image has a feature for exactly this. On its first start, it runs every .sql file it finds in a folder called /docker-entrypoint-initdb.d/. We will place our script there by mounting it: making a file from your computer appear inside the container.

Add the last line of this final init.sql. It sends the notification automatically:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE ROLE anonymous NOLOGIN;

CREATE TABLE IF NOT EXISTS candidates (
    candidate_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name         VARCHAR(100) NOT NULL,
    created_at   TIMESTAMPTZ DEFAULT now(),
    updated_at   TIMESTAMPTZ DEFAULT now()
);

GRANT USAGE ON SCHEMA public TO anonymous;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO anonymous;

INSERT INTO candidates (name) VALUES
    ('Harry'), ('Hermione'), ('Luna'), ('Ron'), ('Neville');

NOTIFY pgrst, 'reload schema';

Now replace your compose.yml with the final version. The only new part is the volumes entry under db:

services:
  db:
    image: postgres:18-alpine
    environment:
      POSTGRES_DB: app
      POSTGRES_PASSWORD: devpassword
    ports:
      - "5432:5432"
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql:ro

  apiserver:
    image: postgrest/postgrest:v13.0.7
    ports:
      - "127.0.0.1:3000:3000"
    environment:
      PGRST_DB_URI: "postgres://postgres:devpassword@db:5432/app"
      PGRST_DB_SCHEMA: "public"
      PGRST_DB_ANON_ROLE: "anonymous"
      PGRST_OPENAPI_SERVER_PROXY_URI: "http://127.0.0.1:3000"
    depends_on:
      - db

The mount line has three parts separated by colons: the file on your computer (./init.sql), where it appears in the container, and ro, which means read-only, so the container cannot change your file. PGRST_OPENAPI_SERVER_PROXY_URI only tells PostgREST what web address to print in its self-description.

Start fresh and watch it come up. Press play. The recording is slowed down so you can follow each line; in real life the whole thing takes about a second once the images are downloaded.

$ docker compose down -v
$ docker compose up -d

Now check that it works, with no manual steps:

8. Part 6: Break it on purpose

Now the most important part of this variant. You will add a record, shut the stack down the way people normally do, and watch your record vanish. We do this deliberately, because once you have felt the loss, the fix in v-b1 will make sense.

First, two ways to stop the stack

Compose has two common commands for stopping, and they do very different things:

Why did this happen? PostgreSQL keeps its files in a data directory inside the container. When Compose starts a container from an image that declares a data folder but is not told where to keep it, Docker quietly attaches a fresh, unnamed storage area to that container. docker compose down removes the container, and with it the link to that storage. The next up creates a brand new container with brand new, empty storage, so init.sql runs again and you get only the original five rows.

9. The seven weaknesses

None of these is an accident. The course method is break it, then fix it: you first build the naive version that most people really write, so that when you meet the fix, you understand why it exists. You have already met every item in this list. Here they are in one place, with the lesson that fixes each.

Weakness 7 is easy to miss because the two ports lines look similar. Compare "5432:5432" with "127.0.0.1:3000:3000" in your file. Only the second has an address restriction.

10. Troubleshooting

Most problems in this lesson fall into a short list. Find your symptom in the left column.

11. Challenge: add a second table

You have followed instructions for a while. Now do something with less help. This is the point of the exercise: it should be slightly hard, and that is where the learning happens.

How to check your own work. After a clean restart, this command should print exactly 4:

$ curl -s http://localhost:3000/houses | grep -o '"name"' | wc -l
4

In a later release of this course, a command named minbase check v-b0 will run checks like this one automatically on your live stack and report which requirements pass. For now, you are the grader.

-- Add this BEFORE the two GRANT lines:
CREATE TABLE IF NOT EXISTS houses (
    house_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name     VARCHAR(100) NOT NULL
);

-- Add this with the other INSERTs:
INSERT INTO houses (name) VALUES
    ('Gryffindor'), ('Hufflepuff'), ('Ravenclaw'), ('Slytherin');

12. Check what you learned

These questions test understanding, not memory. After each answer you will see an explanation, whether you were right or wrong. Read it either way.

Further reading

When a term is unclear, go to the source that owns it. These are the primary references for this lesson.

• PostgREST documentation: how a database schema becomes a REST API, including the schema cache and error codes.
• PostgreSQL: psql: every meta-command, and more.
• PostgreSQL: NOTIFY: the messaging feature used to refresh the cache.
• PostgreSQL: uuid-ossp: the extension that generates UUIDs.
• Docker Compose: services, networks and volumes.
• MDN: HTTP status codes: what 200, 201, 401 and 404 mean.