How to Set Up a Local Database, Step by Step (PostgreSQL and MariaDB with Docker)

· Canvasly Web Studio · Development · 11 min read

A terminal starting, seeding and resetting a local PostgreSQL database with one command

In our Local development setup guide we said every layer of your environment should be written down, so a new machine needs one command. This tutorial does exactly that for the layer that causes the most trouble: the database. By the end you'll have a PostgreSQL database that starts in seconds, is reachable only from your own computer, comes with realistic test data, and can be backed up, restored or wiped clean with a single command. A MariaDB + WordPress version follows, for headless WordPress work.

Your database should be the easiest part of your setup: started in seconds, safe from the network, and reset in one command.

  • Portable: the same PostgreSQL version on every machine.
  • Isolated: reachable only from your own computer.
  • Disposable: wipe it and get fresh test data in one command.

What we’re building

Your app and tools talk to a database container on 127.0.0.1, with data kept in a Docker volume

What you'll end up with:

my-project/
├── compose.yaml          # the database, defined once
├── .env.example          # settings everyone shares (committed)
├── .env                  # your copy of the settings (not committed)
├── .gitignore
├── Makefile              # db-up, db-shell, db-reset, db-dump …
├── db/
│   └── init/             # runs once, on a fresh database
│       ├── 01-extensions.sql
│       ├── 02-schema.sql
│       └── 03-seed.sql
└── backups/              # your dumps (not committed)

Step 1: Install Docker

You need Docker with Docker Compose v2 (the docker compose command, with a space).

Check that it works:

docker --version
docker compose version

You don't need to install PostgreSQL on your computer at all: that's the point.

Step 2: Create the project folder and settings

mkdir my-project && cd my-project
mkdir -p db/init backups

Create .env.example. It lists every setting, with safe development values. It's committed, so everyone starts from the same file:

# .env.example — copy to .env; development values only, never real secrets
POSTGRES_USER=app
POSTGRES_PASSWORD=app_dev_only
POSTGRES_DB=app
DB_PORT=5432
DATABASE_URL=postgres://app:app_dev_only@127.0.0.1:5432/app

Make your own copy, and keep it, and your backups, out of Git:

cp .env.example .env
printf '.env\nbackups/\n' >> .gitignore

Docker Compose reads .env automatically, and your app can read DATABASE_URL from it.

Step 3: Describe the database in compose.yaml

Create compose.yaml:

services:
  db:
    image: postgres:16                     # pin the major version you run in production
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    command: ["postgres", "-c", "shared_preload_libraries=pg_stat_statements"]
    ports:
      - "127.0.0.1:${DB_PORT:-5432}:5432"  # reachable from this computer only
    volumes:
      - db-data:/var/lib/postgresql/data   # your data survives restarts
      - ./db/init:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB"]
      interval: 2s
      timeout: 3s
      retries: 30

volumes:
  db-data:

What each part does, and which practice from the guide it follows:

Step 4: Add the schema and seed data

Files in db/init run in alphabetical order on a fresh database.

db/init/01-extensions.sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

db/init/02-schema.sql

CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text        NOT NULL,
  email      text        NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint        NOT NULL REFERENCES customers (id),
  total       numeric(10,2) NOT NULL,
  status      text          NOT NULL,
  created_at  timestamptz   NOT NULL DEFAULT now()
);

CREATE INDEX orders_status_created_idx ON orders (status, created_at);

db/init/03-seed.sql: realistic, but entirely made up:

INSERT INTO customers (name, email)
SELECT 'Customer ' || g, 'customer' || g || '@example.test'
FROM generate_series(1, 25) AS g;

INSERT INTO orders (customer_id, total, status, created_at)
SELECT (random() * 24)::int + 1,
       round((random() * 200 + 10)::numeric, 2),
       (ARRAY['paid', 'pending', 'refunded'])[1 + floor(random() * 3)::int],
       now() - random() * interval '90 days'
FROM generate_series(1, 200);

Using an ORM or a migration tool? (Prisma, Django, Rails, Laravel, Flyway…) Then let it own the schema: keep only 01-extensions.sql in db/init, and run your migrations and seeders after the database starts. The rest of this tutorial works the same.

Step 5: Start the database

docker compose up -d --wait
docker compose ps

--wait returns only when the health check passes, so the database is ready to use. docker compose ps should show db as healthy.

Step 6: Connect to it

From the terminal (no local PostgreSQL install needed):

docker compose exec db sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB"'
SELECT count(*) FROM customers;          -- 25
SELECT status, count(*) FROM orders GROUP BY status;
\q

From a desktop tool (TablePlus, DBeaver, pgAdmin, or your editor's database panel): connect to host 127.0.0.1, port 5432, with the user, password and database name from .env.

From your app: use DATABASE_URL from .env.

Step 7: One command for everything

Typing long Docker commands gets old fast. Put them in a Makefile, so every task is one short word:

# Makefile — make sure the indented lines start with a TAB, not spaces
.PHONY: db-up db-down db-logs db-shell db-reset db-dump db-restore db-stats

db-up:      ## start the database and wait until it's ready
    cp -n .env.example .env 2>/dev/null || true
    docker compose up -d --wait

db-down:    ## stop it (data is kept)
    docker compose down

db-logs:    ## follow the database log
    docker compose logs -f db

db-shell:   ## open psql
    docker compose exec db sh -c 'psql -U "$$POSTGRES_USER" -d "$$POSTGRES_DB"'

db-reset:   ## wipe everything and start fresh (re-runs db/init)
    docker compose down -v
    docker compose up -d --wait

db-dump:    ## save a backup to backups/
    docker compose exec -T db sh -c 'pg_dump -U "$$POSTGRES_USER" -d "$$POSTGRES_DB" --format=custom' > backups/app-$$(date +%Y%m%d-%H%M).dump

db-restore: ## restore one: make db-restore FILE=backups/app-....dump
    docker compose exec -T db sh -c 'pg_restore -U "$$POSTGRES_USER" -d "$$POSTGRES_DB" --clean --if-exists --no-owner' < $(FILE)

db-stats:   ## the 10 queries with the most total time
    docker compose exec db sh -c 'psql -U "$$POSTGRES_USER" -d "$$POSTGRES_DB" -c "SELECT calls, round(total_exec_time::numeric,1) AS total_ms, left(query, 80) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"'

Now a new team member's instructions are just:

git clone … && cd my-project
make db-up
make db-shell

If a task needs a long command, it belongs in the Makefile. Then the README is just: clone, make db-up, start coding.

One command for everything

Step 8: Back up, restore and reset

Working with a copy of production data

Sometimes you need real volumes to reproduce a bug. Use an anonymised copy, never raw customer data:

  1. Take the dump on a secure server or a staging copy, not on your laptop.
  2. Restore it into a temporary database there, and anonymise it:
UPDATE customers
SET name  = 'Customer ' || id,
    email = 'customer' || id || '@example.test';
-- …and the same for addresses, phone numbers, payment details and free-text notes
  1. Dump the anonymised copy, bring that file home, and make db-restore FILE=….

Variant: MariaDB + WordPress (for headless WordPress)

Working on a WordPress site, or on a headless front end for one? Here's the same approach with MariaDB, WordPress and Mailpit to catch emails.

.env.example

MARIADB_ROOT_PASSWORD=root_dev_only
MARIADB_PASSWORD=wp_dev_only

compose.yaml

services:
  mariadb:
    image: mariadb:11
    environment:
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      MARIADB_DATABASE: wordpress
      MARIADB_USER: wordpress
      MARIADB_PASSWORD: ${MARIADB_PASSWORD}
    volumes:
      - wp-db:/var/lib/mysql
    ports:
      - "127.0.0.1:3306:3306"
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 5s
      timeout: 5s
      retries: 20

  wordpress:
    image: wordpress:6                       # match the version you run in production
    depends_on:
      mariadb:
        condition: service_healthy
    environment:
      WORDPRESS_DB_HOST: mariadb
      WORDPRESS_DB_USER: wordpress
      WORDPRESS_DB_PASSWORD: ${MARIADB_PASSWORD}
      WORDPRESS_DB_NAME: wordpress
    ports:
      - "127.0.0.1:8080:80"
    volumes:
      - wp-files:/var/www/html

  mail:
    image: axllent/mailpit
    ports:
      - "127.0.0.1:8025:8025"                 # inbox in your browser
      - "127.0.0.1:1025:1025"                 # SMTP

volumes:
  wp-db:
  wp-files:
cp .env.example .env
docker compose up -d --wait

Open http://localhost:8080 and finish the WordPress installer. Useful commands for this variant:

# Back up the WordPress database
docker compose exec -T mariadb sh -c 'mariadb-dump -u root -p"$MARIADB_ROOT_PASSWORD" wordpress' > backups/wp.sql

# Restore it
docker compose exec -T mariadb sh -c 'mariadb -u root -p"$MARIADB_ROOT_PASSWORD" wordpress' < backups/wp.sql

WordPress sends email with PHP's mail function by default. To see every message in Mailpit (http://localhost:8025), install an SMTP plugin and point it at host mail, port 1025.

Design the headless front end with Canvasly

With WordPress running locally, you can build its fast front end without touching production:

  1. In WordPress, go to Settings → Permalinks and choose Post name.
  2. Open the Canvasly Web Studio desktop app and choose Connect WordPress Site…, then enter http://localhost:8080. The desktop app accepts local addresses for exactly this kind of work.
  3. Design the post, page, blog and shop templates, bind them with Bind to WordPress, and export a Next.js or Astro front end.
  4. Run the export next to your local WordPress with npm install and npm run dev.

The full walk-through is in How to set up headless WordPress with Next.js or Astro.

Never put real customer data on a laptop. Seed data for daily work, and an anonymised copy when you truly need production volumes.

Data safety

Troubleshooting

"Port is already allocated" or "address already in use". Another database already uses the port, often a PostgreSQL or MySQL installed with Homebrew or an installer. Stop it, or set DB_PORT=5433 in .env (and in DATABASE_URL), then make db-up.

My init scripts didn't run. They only run when the database is created for the first time. Run make db-reset to start from an empty volume.

I changed the password in .env and now I can't log in. The password is also set only on first creation. Change it back, or run make db-reset.

"Connection refused" right after starting. The database wasn't ready yet. Always start with docker compose up -d --wait, which waits for the health check.

It's slow on macOS or Windows. Keep the data in a named volume (as above), not a folder shared with your computer. On Windows, keep the project inside WSL 2.

I want to see what the database is doing. make db-logs follows the log, and make db-stats shows the queries that take the most time.

Checklist: did we follow the guide?

Practice from the setup guide Where it is in this tutorial
Pinned versions postgres:16, mariadb:11, wordpress:6
Services in containers compose.yaml, with nothing installed on your computer
Isolated Ports bound to 127.0.0.1, one project per folder
Safe configuration .env.example committed, .env and backups ignored
Realistic data Seed scripts, and anonymised production copies only
One command for everything make db-up, db-shell, db-reset, db-dump, db-restore
Reset any time make db-reset
Fast Named volumes, WSL 2 on Windows, health checks instead of guessing

Conclusion

A local database shouldn't be a weekend project or a source of "works on my machine" bugs. With one compose.yaml, one .env.example and a few Makefile tasks, it becomes portable (the same everywhere), fast (seconds to start, seconds to reset) and isolated (your data, your machine, nobody else's). Commit the files, and the next person to join your project is one make db-up away from working.

Building the website side too? Try Canvasly Web Studio free for 7 days at canvasly.pro.

Related reading: Local development setup guide · Database performance tuning 101 · Headless CMS vs. monolithic CMS