Part 10: Postgres in a container

SQLite from Part 8: Real persistence with SQLite is perfect for local work, but it's a single file on disk, not the kind of database a deployed app runs against.

On a real server we use Postgres. The easiest way to run it locally is to use Docker, so we'll do it here.

Run Postgres in Docker

First, start a Postgres container:

docker run -id \
  --name snake-db \
  -e POSTGRES_USER=snakearena \
  -e POSTGRES_PASSWORD=snakearena \
  -e POSTGRES_DB=snakearena \
  -p 5432:5432 \
  -v snake_pgdata:/var/lib/postgresql/data \
  postgres:16-alpine

The -v snake_pgdata:/var/lib/postgresql/data volume lives outside the container, so stopping or removing the container keeps every account and score.

Add Postgres support

We keep SQLite for local tests and add Postgres alongside it.

Ask the assistant:

I want this app to use Postgres, not only SQLite

Add the Postgres driver as a dependency so SQLAlchemy can connect to it, and run
it against the Postgres container above

The backend already chooses its database from SNAKE_ROYALE_DATABASE_URL and goes through SQLAlchemy. So this is a small change. The assistant adds the psycopg driver and the same code now talks to Postgres.

Point the backend at the container and start it:

export SNAKE_ROYALE_DATABASE_URL="postgresql://snakearena:snakearena@localhost:5432/snakearena"
make backend

It creates its tables in Postgres on first run. Sign up and submit a score, then stop and restart the database container - the named volume keeps the data, so it's still there.

One complication shows up if you run the app in Docker against this Postgres, where localhost inside the container isn't your host. You'd reach the database through host.docker.internal (with --add-host=host.docker.internal:host-gateway on Linux). Docker Compose removes that hassle in the next part by putting both containers on one network.

Stop the database when you're done:

docker stop snake-db

Commit the Postgres switch:

git add .
git commit -m "Switch from SQLite to Postgres"

In Part 11: Docker Compose we wrap the app and Postgres into a single Compose file so one command brings the whole stack up.