Running the Database Locally

Purpose of this page

Get a local PostgreSQL database running with the current schema, and know exactly what to do if a database from before the authentication update is already sitting on your machine.

Codey typing on a laptop — one command, one database

Before you start it prerequisites

Docker Desktop (or Docker Engine + the Compose plugin) installed and running.
A .env file at the project root, copied from .env.example. Compose requires JWT_SECRET_KEY and refuses to start without it. The DB_* values matter only if you run the API directly with Python — the containerized API gets its database settings from the compose file itself.
Port 5432 free on your machine — stop any other local PostgreSQL install first, or it will conflict. Starting the whole stack also needs 5000 and 8888.

Walking through the db service one of three

docker-compose.yml defines three services — ui, api, and db — and starts them together. The other two are covered in the Docker tutorial; this page stays on the database.

docker-compose.yml (the db service)  db:
    image: postgres:16
    restart: unless-stopped
    environment:
      POSTGRES_DB: metals
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - pgdata_metals:/var/lib/postgresql/data
      - ./sql/metals-db.sql:/docker-entrypoint-initdb.d/metals-db.sql:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d metals"]
      interval: 5s
      timeout: 5s
      retries: 10
      start_period: 10s

volumes:
  pgdata_metals:

image: postgres:16the database engine

The official PostgreSQL image, version 16 — the same major version the manual and Terraform deployment paths create in Azure, so behavior matches production.

environmentfirst-boot configuration

These three variables are read by the official Postgres image only the first time it starts with an empty data directory: they create the metals database and a postgres superuser with password postgres. Your local .env file's DB_USER / DB_PASSWORD need to match these exact values, since that's what the container will actually accept.

ports: "5432:5432"reaching it from your machine

Maps the container's PostgreSQL port to the same port on your computer, so tools like psql, DBeaver, or the API itself can connect to localhost:5432 as if Postgres were installed natively.

volumes: pgdata_metalswhere the data actually lives

A named Docker volume that stores the database files outside the container, so your data survives docker compose down, container rebuilds, and image updates. This is also exactly the piece you have to delete to force a full reset — see below.

healthcheckwhat the API waits for

pg_isready asks PostgreSQL whether it's actually accepting connections. Compose runs it every five seconds and won't start the API until it succeeds — because “the container has started” and “the database will accept a connection” are very different things, especially on the first run when the whole schema is being loaded. start_period: 10s is a grace window where early failures don't count against the retries.

the metals-db.sql mountthe auto-init hook

Bind-mounting sql/metals-db.sql into /docker-entrypoint-initdb.d/ is a feature built into the official Postgres image: on startup, if (and only if) the data directory is completely empty, it runs every .sql file found in that folder, in order. That single run is what creates every table and loads every seed row — there's no separate migration step to remember.

Starting it up day-to-day commands

Run these from the project root, next to docker-compose.yml.

Codey holding up a sticky note — same three commands, every time

Command	What it does
`docker compose up -d db`	Starts the db service in the background. The first time, this also creates the volume and runs metals-db.sql.
`docker compose logs -f db`	Streams the container's logs — useful the first time, to watch the schema and seed data load.
`docker compose down`	Stops and removes the container, but keeps the pgdata_metals volume, so your data is still there next time you run up.

Have an old database already running? read this before you debug anything else

If you ran docker compose up -d db on this project before the authentication update, your pgdata_metals volume already has data in it — which means the “only runs on an empty data directory” rule from above now works against you. Postgres will start up fine, but it will silently skip metals-db.sql entirely, so the new users, roles, and user_roles tables will simply not exist. The API will fail with errors like relation "users" does not exist as soon as you try to register or log in.

Codey holding a bug-hunting net — old volume, new script — they don't mix

Stop the container and delete its volume:
```
docker compose down -v
```
Start it again from scratch:
```
docker compose up -d --build
```
Because the volume is now gone, Postgres treats this as a brand-new database and runs metals-db.sql again in full — creating every table, including the three new ones, and reloading all seed data.

The -v flag deletes the volume, permanently. Any data you added by hand (extra elements, alloys you created through the API, etc.) will be gone. That's expected and fine for a local development database — never run down -v against a shared or production database.

The two seed accounts how to actually log in

metals-db.sql creates exactly two login accounts so you can start using the Metals API immediately, without registering a new one first. Both use the same password.

Codey giving a thumbs up — ready to log in, out of the box

Username	Password	Role	Can do
`admin`	`password`	Admin	Everything — including creating, updating, and deleting elements, alloys, alloy elements, and coins.
`customer`	`password`	Customer	Read-only — can view the catalog, but write attempts get a 403 Forbidden.

Common errors and how to fix them

What you see	Likely cause	How to fix it
Port 5432 already in use / bind: address already in use	Another PostgreSQL instance (local install, another project's container) is already using the port.	Stop the other instance, or change the left-hand side of the ports mapping (e.g. "5433:5432") and update DB_PORT in .env to match.
password authentication failed for user	.env's DB_USER / DB_PASSWORD don't match the environment block in docker-compose.yml.	Set both to postgres / postgres, or change the compose file's environment block to match your .env — just make sure they agree.
relation "users" does not exist (or similarly, "roles", "elements")	You have an old volume from before the schema change, so the init script never ran against it.	Follow the reset steps above: docker compose down -v, then docker compose up -d --build.
API can't connect at all / connection refused	The container isn't running yet, or hasn't finished starting.	Run docker compose ps to check its status, and docker compose logs -f db to watch it start; give it a few seconds after up -d.