Postgres for local dev: one container, one Makefile

#docker #postgres #devops #database

Most local database setups fail in one of two ways. Either everyone shares a staging database and steps on each other's data, or the repo ships a 200-line compose file with six services that nobody fully understands. You need neither. One Postgres container, plain SQL migrations, and a reset command that runs in seconds will carry you a long way.

The container

# docker-compose.yml
services:
  db:
    image: postgres:17
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: app_dev
    volumes:
      - pgdata:/var/lib/postgresql/data
    command: >
      postgres
      -c fsync=off
      -c synchronous_commit=off
      -c full_page_writes=off
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d app_dev"]
      interval: 2s
      retries: 15

volumes:
  pgdata:

Pin the major version to whatever you run in production. postgres:latest is how you discover a version mismatch at deploy time. (If you pin 18 or later, the image expects the volume mounted at /var/lib/postgresql instead.)

The three -c flags trade durability for speed. Your dev data is disposable, so take the trade, and never copy those lines into a production config.

# .env
DATABASE_URL=postgres://app:app@localhost:5432/app_dev

This one variable is the only thing your app knows about the database.

Migrations are SQL files

migrations/
  001_users.sql
  002_sessions.sql
  003_add_user_timezone.sql
seed.sql

They're numbered, append-only, and written in plain SQL. No ORM-generated diffs, no DSL to learn. When you need to know what the schema looks like, you read the files.

The reset command

include .env
export

ADMIN_URL = postgres://app:app@localhost:5432/postgres

db-up:
    docker compose up -d --wait db

db-reset: db-up
    psql $(ADMIN_URL) -c "DROP DATABASE IF EXISTS app_dev WITH (FORCE)"
    psql $(ADMIN_URL) -c "CREATE DATABASE app_dev"
    for f in migrations/*.sql; do \
      psql $(DATABASE_URL) -v ON_ERROR_STOP=1 -q -f $$f || exit 1; \
    done
    psql $(DATABASE_URL) -v ON_ERROR_STOP=1 -q -f seed.sql

db-shell:
    psql $(DATABASE_URL)

make db-reset is the primitive everything else rests on. If it's fast, nobody hoards a precious local database they're afraid to touch. After a bad migration, a branch switch, or a weird state, you reset and move on.

It also means every migration gets replayed from zero on every reset, so your migration history is continuously tested instead of only tested in production.

Tests: template databases

Don't run tests against app_dev, and don't truncate tables between tests. Postgres can clone a database at the file level:

-- once per test run, after migrating app_template
CREATE DATABASE test_a1b2c3 TEMPLATE app_template;

-- when the test finishes
DROP DATABASE test_a1b2c3;

Cloning a small schema takes tens of milliseconds. Each test, or each test worker, gets a real, isolated, fully migrated database, which means no mocks and no shared state. It also removes "passes alone, fails in the suite." The one catch is that nothing can be connected to the template while it's being cloned, so migrate it and then leave it alone.

Where this breaks

This setup is right for projects that:

Use stock Postgres or extensions with a published image
Have a seed dataset small enough to rebuild in seconds
Run on a single database

It gets less comfortable when:

You need extensions. Swap the image (pgvector/pgvector, postgis/postgis) instead of building your own until you truly have to.
Performance bugs hide behind tiny data. A query that's instant on 50 seed rows can be a sequential scan on 5 million. Keep a script that generates volume with generate_series, and check EXPLAIN ANALYZE against it before shipping anything query-heavy.
Your production is managed Postgres. Hosted providers restrict superuser and some extensions. If your migrations assume superuser locally, they'll fail on deploy, so run them as a non-superuser role in dev too.
You run several projects. They'll all want port 5432, so give each project its own host port in compose and its own DATABASE_URL.

Each of these changes a line or two, and the shape of the setup stays the same.

Wrap-up

Local database setups fail when they're slow or fragile, because people stop resetting them, and then every machine drifts into its own private schema. A fast reset and a versioned container prevent that, and template databases keep tests isolated.

For a batteries-included setup with Postgres, auth, and realtime prebundled, tinbase.dev is worth a look. And if the thing you're building on top of that database is a mobile app, RapidNative generates a full-stack React Native app from a prompt. Otherwise, the compose file and Makefile above will have you running in ten minutes.

Make the reset cheap. Everything else follows.

DEV Community