Local Databases (WSL + Docker) Guide

Technology: local-database · Category: database · Last reviewed: 2026-08-23

Source: https://tech-stack.codeamanilabs.org/guide/local-database

Insight:

This is the local-dev mirror of the Supabase/Neon production databases — the same Postgres major version, the same extensions, the same RLS policies, running as a throwaway container inside WSL Ubuntu. The trade-off is honest: you get an offline, zero-cost, reset-in-two-seconds database, but you do not get Supabase's Auth/Storage/Realtime or Neon's branching, so anything that depends on those still needs a cloud branch. The rule that makes it safe: a local container is a disposable copy of production's shape, never a place to relax production's rules — POSTGRES_HOST_AUTH_METHOD=trust and 127.0.0.1-only port binds are the two knobs that decide whether "just for dev" stays just for dev.

██╗      ██████╗  ██████╗ █████╗ ██╗
██║     ██╔═══██╗██╔════╝██╔══██╗██║
██║     ██║   ██║██║     ███████║██║
██║     ██║   ██║██║     ██╔══██║██║
███████╗╚██████╔╝╚██████╗██║  ██║███████╗
╚══════╝ ╚═════╝  ╚═════╝╚═╝  ╚═╝╚══════╝

██████╗  █████╗ ████████╗ █████╗ ██████╗  █████╗ ███████╗███████╗
██╔══██╗██╔══██╗╚══██╔══╝██╔══██╗██╔══██╗██╔══██╗██╔════╝██╔════╝
██║  ██║███████║   ██║   ███████║██████╔╝███████║███████╗█████╗
██║  ██║██╔══██║   ██║   ██╔══██║██╔══██╗██╔══██║╚════██║██╔══╝
██████╔╝██║  ██║   ██║   ██║  ██║██████╔╝██║  ██║███████║███████╗
╚═════╝ ╚═╝  ╚═╝   ╚═╝   ╚═╝  ╚═╝╚═════╝ ╚═╝  ╚═╝╚══════╝╚══════╝

Local Databases (WSL + Docker) Guide

Focus: Running Postgres, MySQL/MariaDB, Redis, MongoDB and pgvector as containers inside WSL Ubuntu — pinned docker run one-liners, one compose.yaml for the whole local stack with named volumes, connecting from the Windows host and from Next.js, and clean teardown. Grounded in docs.docker.com and the official image docs; tags checked against Docker Hub on 2026-08-23.

Overview

codeAmani ships production data to [[supabase]] (Postgres + RLS) or [[neon]] (serverless Postgres with branching). Neither is a good place to run a migration you're not sure about at 2am on a hotel Wi-Fi. A local containerized database is the mirror: same engine, same major version, same extensions, zero network, zero bill, and docker compose down -v to start over.

The whole thing rides on the stack the [[docker]] guide already sets up — Docker Desktop on the WSL 2 backend, or Docker Engine installed directly in your Ubuntu distro. Either way the daemon runs against a real Linux kernel, so postgres:18 locally is byte-for-byte the postgres:18 that Supabase and Neon run.

flowchart LR
  W["Windows 11 host<br/>Next.js dev · psql · TablePlus"]
  subgraph WSL["WSL 2 · Ubuntu"]
    D["Docker Engine<br/>(Desktop integration or apt)"]
    subgraph NET["compose network 'app-net'"]
      PG["postgres:18.6-trixie<br/>:5432"]
      RD["redis:8.10.1-alpine<br/>:6379"]
      MG["mongo:8.0.29-noble<br/>:27017"]
    end
    V[("named volumes<br/>pgdata · redisdata · mongodata")]
  end
  W -->|"127.0.0.1:5432<br/>WSL localhost forwarding"| PG
  D --- NET
  PG --- V
  RD --- V
  MG --- V
  PG -.->|"same engine, same<br/>schema, same RLS"| PROD["Supabase / Neon<br/>production"]

Official Documentation

1. Get a Docker daemon inside WSL Ubuntu

Two supported paths. Pick one — do not run both.

Path A — Docker Desktop with WSL integration (recommended on Windows)

Install Docker Desktop, then Settings → Resources → WSL integration → enable your Ubuntu distro. The docker and docker compose CLIs then work from inside Ubuntu with no daemon of your own, and published ports land on Windows localhost automatically. See the [[docker]] guide for the full walkthrough.

Path B — Docker Engine straight into Ubuntu (no Desktop)

Run this inside the WSL Ubuntu shell. These are the current commands from docs.docker.com/engine/install/ubuntu/:

# 1. Docker's official GPG key
sudo apt update
sudo apt install ca-certificates curl
sudo install -m 0755 -d /etc/apt/keyrings
sudo curl -fsSL https://download.docker.com/linux/ubuntu/gpg -o /etc/apt/keyrings/docker.asc
sudo chmod a+r /etc/apt/keyrings/docker.asc

# 2. Add the repository to Apt sources
sudo tee /etc/apt/sources.list.d/docker.sources <<EOF
Types: deb
URIs: https://download.docker.com/linux/ubuntu
Suites: $(. /etc/os-release && echo "${UBUNTU_CODENAME:-$VERSION_CODENAME}")
Components: stable
Architectures: $(dpkg --print-architecture)
Signed-By: /etc/apt/keyrings/docker.asc
EOF
sudo apt update

# 3. Install engine + CLI + compose plugin
sudo apt install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

# 4. Run docker without sudo (log out / `wsl --shutdown` to pick up the group)
sudo usermod -aG docker $USER

# 5. Verify
docker run --rm hello-world
docker compose version

WSL doesn't boot systemd unless you ask it to. Enable it once so the daemon starts with the distro:

# /etc/wsl.conf inside Ubuntu
sudo tee /etc/wsl.conf <<'EOF'
[boot]
systemd=true
EOF
# then from PowerShell on the host:
#   wsl --shutdown

Without systemd, start the daemon by hand each session with sudo service docker start.

Filesystem rule (same as [[docker]]): keep the project on the Linux filesystem (~/code/...), never /mnt/c/.... Bind-mounting a database data directory from /mnt/c crosses the 9p boundary on every fsync and will make Postgres crawl. Named volumes (below) sidestep this entirely — they live inside the WSL VM's ext4 disk.

2. Environment variables

One .env.local per project. These are local-only development credentials — they still never get committed, because the habit is what protects the production string that eventually sits in the same file.

# .env.local — local containers only
POSTGRES_USER=app
POSTGRES_PASSWORD=devpassword
POSTGRES_DB=appdb
DATABASE_URL=postgresql://app:devpassword@localhost:5432/appdb

REDIS_URL=redis://localhost:6379
MONGODB_URI=mongodb://root:devpassword@localhost:27017/appdb?authSource=admin
MYSQL_ROOT_PASSWORD=devpassword
MYSQL_URL=mysql://app:devpassword@localhost:3306/appdb

3. docker run one-liners (pinned tags)

Every tag below was resolved against Docker Hub on 2026-08-23. Pin the minor version — latest moves under you and a Postgres major bump silently invalidates the data directory.

Postgres 18

docker run -d --name pg \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=devpassword \
  -e POSTGRES_DB=appdb \
  -p 127.0.0.1:5432:5432 \
  -v pgdata:/var/lib/postgresql \
  postgres:18.6-trixie

⚠️ Postgres 18 changed the volume target. For 18 and above the image sets PGDATA=/var/lib/postgresql/18/docker and declares its VOLUME at /var/lib/postgresql. For 17 and below you must still mount at /var/lib/postgresql/data — mounting 17 at /var/lib/postgresql silently writes to an anonymous volume and your data vanishes on re-create. Copy the right line for your major version.

# Postgres 17 and below — note the /data suffix
docker run -d --name pg17 \
  -e POSTGRES_PASSWORD=devpassword \
  -p 127.0.0.1:5432:5432 \
  -v pg17data:/var/lib/postgresql/data \
  postgres:17.11-trixie

Postgres + pgvector

pgvector/pgvector is the official Postgres image with the extension already compiled in — same env vars, same volume rules, same major-version tag scheme. Use it whenever the project touches embeddings (see [[pgvector]]).

docker run -d --name pgv \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=devpassword \
  -e POSTGRES_DB=appdb \
  -p 127.0.0.1:5432:5432 \
  -v pgvdata:/var/lib/postgresql \
  pgvector/pgvector:0.8.6-pg18-trixie

The extension ships in the image but is not enabled in your database until you say so — exactly like Supabase and Neon:

docker exec -it pgv psql -U app -d appdb -c "CREATE EXTENSION IF NOT EXISTS vector;"
docker exec -it pgv psql -U app -d appdb -c "SELECT extversion FROM pg_extension WHERE extname='vector';"

MySQL 9.7 (LTS)

docker run -d --name mysql \
  -e MYSQL_ROOT_PASSWORD=devpassword \
  -e MYSQL_DATABASE=appdb \
  -e MYSQL_USER=app \
  -e MYSQL_PASSWORD=devpassword \
  -p 127.0.0.1:3306:3306 \
  -v mysqldata:/var/lib/mysql \
  mysql:9.7.2

MySQL switched Innovation releases to calendar versioning starting at 26.7.0 (YY.M.P), and mysql:latest now follows that Innovation track. mysql:lts currently resolves to 9.7.2; 8.4 is the previous LTS line. Pin an LTS unless you specifically want Innovation features.

MariaDB 12.3 (LTS)

docker run -d --name mariadb \
  -e MARIADB_ROOT_PASSWORD=devpassword \
  -e MARIADB_DATABASE=appdb \
  -e MARIADB_USER=app \
  -e MARIADB_PASSWORD=devpassword \
  -p 127.0.0.1:3306:3306 \
  -v mariadbdata:/var/lib/mysql \
  mariadb:12.3.2

MariaDB's env vars are MARIADB_* (the MYSQL_* spellings are legacy aliases), and the data directory is still /var/lib/mysql.

Redis 8

Redis runs without persistence and without a password by default. Turn on snapshots explicitly or your local cache evaporates on restart:

docker run -d --name redis \
  -p 127.0.0.1:6379:6379 \
  -v redisdata:/data \
  redis:8.10.1-alpine \
  redis-server --save 60 1 --appendonly yes --loglevel warning

--save 60 1 snapshots if ≥1 write happened in the last 60s; --appendonly yes adds the AOF log. Both write to the VOLUME /data. This is the local stand-in for [[upstash]] Redis — see the [[caching]] guide for what belongs in it.

MongoDB 8.0

docker run -d --name mongo \
  -e MONGO_INITDB_ROOT_USERNAME=root \
  -e MONGO_INITDB_ROOT_PASSWORD=devpassword \
  -e MONGO_INITDB_DATABASE=appdb \
  -p 127.0.0.1:27017:27017 \
  -v mongodata:/data/db \
  mongo:8.0.29-noble

Transactions and change streams require a replica set, even locally — and Prisma's Mongo connector refuses to write without one. A single-node replica set is enough:

docker run -d --name mongo -p 127.0.0.1:27017:27017 -v mongodata:/data/db \
  mongo:8.0.29-noble --replSet rs0 --bind_ip_all
docker exec -it mongo mongosh --eval 'rs.initiate({_id:"rs0",members:[{_id:0,host:"localhost:27017"}]})'

Then use mongodb://localhost:27017/appdb?replicaSet=rs0&directConnection=true. See the [[mongodb]] guide for driver-side detail.

Why 127.0.0.1:PORT:PORT and not -p PORT:PORT

-p 5432:5432 binds 0.0.0.0 inside the WSL VM. With WSL's mirrored networking mode, or a netsh portproxy, or a corporate Wi-Fi that treats the host as LAN-reachable, that is a Postgres with a dev password answering the network. -p 127.0.0.1:5432:5432 binds loopback only and costs nothing. Make it the default.

4. The whole local stack — compose.yaml

One file, one command, named volumes for persistence, healthchecks so nothing races the database. Drop it at the repo root.

# compose.yaml — local dev only. No `version:` key: it is obsolete
# and Compose warns if you include it.
name: appdb-local

services:
  postgres:
    image: pgvector/pgvector:0.8.6-pg18-trixie
    restart: unless-stopped
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: devpassword
      POSTGRES_DB: appdb
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql          # pg18+ target — NOT /data
      - ./db/init:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d appdb"]
      interval: 5s
      timeout: 5s
      retries: 10
      start_period: 20s

  redis:
    image: redis:8.10.1-alpine
    restart: unless-stopped
    command: ["redis-server", "--save", "60", "1", "--appendonly", "yes", "--loglevel", "warning"]
    ports:
      - "127.0.0.1:6379:6379"
    volumes:
      - redisdata:/data
    healthcheck:
      test: ["CMD", "redis-cli", "ping"]
      interval: 5s
      timeout: 3s
      retries: 10

  mongo:
    image: mongo:8.0.29-noble
    restart: unless-stopped
    environment:
      MONGO_INITDB_ROOT_USERNAME: root
      MONGO_INITDB_ROOT_PASSWORD: devpassword
      MONGO_INITDB_DATABASE: appdb
    ports:
      - "127.0.0.1:27017:27017"
    volumes:
      - mongodata:/data/db
    healthcheck:
      test: ["CMD", "mongosh", "--quiet", "--eval", "db.adminCommand('ping')"]
      interval: 10s
      timeout: 5s
      retries: 10
      start_period: 20s

  # Swap in instead of postgres for a MySQL-shaped project.
  # mariadb:
  #   image: mariadb:12.3.2
  #   environment:
  #     MARIADB_ROOT_PASSWORD: devpassword
  #     MARIADB_DATABASE: appdb
  #     MARIADB_USER: app
  #     MARIADB_PASSWORD: devpassword
  #   ports: ["127.0.0.1:3306:3306"]
  #   volumes: [mariadbdata:/var/lib/mysql]
  #   healthcheck:
  #     test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
  #     interval: 10s
  #     retries: 10

volumes:
  pgdata:
  redisdata:
  mongodata:
  # mariadbdata:

docker compose up -d                 # start everything, detached
docker compose ps                    # STATUS column shows (healthy)
docker compose logs -f postgres      # tail one service
docker compose exec postgres psql -U app -d appdb

If your Next.js app also runs as a Compose service, make it wait for a healthy database, not merely a started one:

  web:
    build: .
    depends_on:
      postgres:
        condition: service_healthy
      redis:
        condition: service_started
    environment:
      # inside the compose network: service name + CONTAINER port
      DATABASE_URL: postgresql://app:devpassword@postgres:5432/appdb

The port that matters depends on who is asking. From the Windows host or next dev running on the host → localhost:5432 (the published port). From another container on the same Compose network → postgres:5432 (the service name and the container port). Mixing these up is the single most common "connection refused" in a local stack.

Seeding: /docker-entrypoint-initdb.d

Postgres, MySQL, MariaDB and MongoDB all run scripts from /docker-entrypoint-initdb.d in alphabetical order, only on first init of an empty data directory. Postgres takes .sh, .sql, .sql.gz; MongoDB takes .sh and .js (run through mongosh).

-- db/init/001_schema.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE deliveries (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   uuid NOT NULL,
  rider_phone text NOT NULL,          -- 254XXXXXXXXX
  fare_kes    integer NOT NULL CHECK (fare_kes > 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Mirror production: RLS on locally too, so a missing policy
-- fails on your laptop instead of in Supabase.
ALTER TABLE deliveries ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON deliveries
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Because they only run on an empty volume, re-seeding means resetting the volume — which is the next section, and is the whole point of the local mirror.

5. Connecting from the Windows host

WSL 2's default NAT mode forwards published container ports to Windows localhost, so once a container publishes 5432 inside Ubuntu, localhost:5432 from Windows just works — psql, TablePlus, DBeaver, Prisma Studio, next dev running natively on Windows, all of it. Docker Desktop's WSL integration does the same thing through its own proxy.

# From Windows PowerShell
psql "postgresql://app:devpassword@localhost:5432/appdb"

# Which distro IP is it actually on? (rarely needed in NAT mode)
wsl.exe hostname -I

If you'd rather have Windows and Linux share one loopback outright, Windows 11 22H2+ supports mirrored networking — put this in C:\Users\<you>\.wslconfig and wsl --shutdown:

[wsl2]
networkingMode=mirrored

Mirrored mode also lets Linux reach Windows servers at 127.0.0.1. Note the trade: mirrored mode makes WSL directly reachable from your LAN, which is exactly why the 127.0.0.1: prefix on every -p above earns its keep.

From Next.js

Use a pooled client held in a module singleton, and cache it on globalThis so Next's dev HMR doesn't open a new pool on every hot reload. Parameterized queries only — never string-interpolate into SQL.

// lib/db.ts — server-only
import { Pool } from "pg";

const globalForDb = globalThis as unknown as { pgPool?: Pool };

export const pool =
  globalForDb.pgPool ??
  new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,                    // local container: keep it small
    idleTimeoutMillis: 30_000,
    // Local containers have no TLS. Production (Supabase/Neon) requires it —
    // key off the env, never off a hardcoded `false`.
    ssl: process.env.DATABASE_URL?.includes("localhost")
      ? false
      : { rejectUnauthorized: true },
  });

if (process.env.NODE_ENV !== "production") globalForDb.pgPool = pool;

export async function getDeliveries(tenantId: string) {
  const { rows } = await pool.query(
    "SELECT id, rider_phone, fare_kes FROM deliveries WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 50",
    [tenantId],                 // parameterized — $1, not template literals
  );
  return rows;
}

To exercise the RLS policy the way Supabase will, set the tenant on the connection inside a transaction:

export async function withTenant<T>(tenantId: string, fn: (c: import("pg").PoolClient) => Promise<T>) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    const out = await fn(client);
    await client.query("COMMIT");
    return out;
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();           // always return it to the pool
  }
}

Verified current client libraries (npm, 2026-08-23): pg 8.23.0, postgres (postgres.js) 3.4.9, mysql2 3.23.4, ioredis 6.0.0, mongodb 7.5.0.

6. Teardown, reset, and inspection

docker compose stop                  # pause; volumes and data survive
docker compose down                  # remove containers + network; volumes SURVIVE
docker compose down -v               # remove volumes too — the true reset
docker compose up -d --force-recreate --pull always

# Single-container equivalents
docker stop pg && docker rm pg
docker volume rm pgdata              # only after the container is gone

# What's on disk?
docker volume ls
docker volume inspect pgdata
docker system df                     # images + volumes + build cache totals
docker system prune -a --volumes     # nuclear: everything unused, all projects

# Back up / restore a local Postgres volume without leaving the container
docker compose exec -T postgres pg_dump -U app -d appdb > backup.sql
docker compose exec -T postgres psql -U app -d appdb < backup.sql

The reset loop — down -v && up -d — is the reason this stack exists. It makes "let me just try the destructive migration" a two-second decision instead of a Neon branch and a prayer.

7. Picking the store

flowchart TD
  A["What are you storing?"] --> B{"Relational, and<br/>prod is Supabase or Neon?"}
  B -->|"yes, plus embeddings"| C["pgvector/pgvector:0.8.6-pg18-trixie"]
  B -->|"yes, plain"| D["postgres:18.6-trixie"]
  B -->|"no"| E{"Shape?"}
  E -->|"ephemeral · counters · rate limits · queue"| F["redis:8.10.1-alpine<br/>local stand-in for Upstash"]
  E -->|"documents · flexible schema"| G["mongo:8.0.29-noble<br/>+ --replSet for transactions"]
  E -->|"legacy MySQL app"| H["mysql:9.7.2 (LTS)<br/>or mariadb:12.3.2 (LTS)"]
  C --> Z["Same schema + RLS as production"]
  D --> Z

codeAmani notes

• The local container mirrors production's shape, never relaxes its rules. Enable RLS in the local init SQL exactly as Supabase has it. A policy you only add in production is a policy nobody has ever tested; a policy you develop against locally fails on your laptop, which is where failure is cheap.
• POSTGRES_HOST_AUTH_METHOD=trust never leaves your machine. The official image docs are blunt: trust "allows anyone to connect without a password, even if one is set." It is a convenience for a throwaway container on loopback and nothing else. If a config with trust — or a -p 0.0.0.0:5432:5432 — ever reaches a Dockerfile, a compose file that gets deployed, a Render/Cloud Run service, or a Codespace, that is an unauthenticated database on the internet. Grep for both before every push; the [[supply-chain]] and SECURITY.md pre-deploy checklists cover the same ground.
• Dev credentials are still credentials. .env.local stays gitignored and devpassword stays out of committed compose files — put real values behind ${POSTGRES_PASSWORD} interpolation and let Compose read .env. The habit is what protects the production string that eventually lives in the same file. Store the production one in Hazina, not here.
• Parameterized queries, pooled connections. $1 placeholders, pool.query, client.release() in a finally. Local Postgres tolerates a leaked connection for a while; Supabase's pooler and Neon's compute do not, and the code you ship is the code you wrote here.
• AI routing: the pgvector image is the offline half of codeAmani's RAG story — embed with whichever provider the [[anthropic]]/[[open-ai]]/[[hugging-face]] routing picks, but develop the retrieval SQL, the HNSW index, and the vector_cosine_ops operator choice against a local container before spending a single token against a hosted DB.
• Kenya-targeted projects: this is the offline-development story. On an intermittent connection in Nairobi, a developer with docker compose up -d keeps a full Postgres + Redis + pgvector stack working with the uplink down — no round-trip to a US-region Supabase for every query, no data egress, no dropped migration halfway through. Seed the local DB with realistic KES integers and 254XXXXXXXXX phone numbers so format bugs (a leading 0, a decimal in an M-Pesa amount) surface against a CHECK constraint on your laptop rather than against Daraja's sandbox.
• What the local mirror can't do: Supabase Auth, Storage, Realtime and Edge Functions, and Neon branching. If a feature depends on those, develop it against a Supabase local/dev project or a Neon branch — see [[supabase]] and [[neon]]. Everything that is just Postgres belongs here.

Troubleshooting

Official docs:

• https://docs.docker.com/desktop/features/wsl/
• https://docs.docker.com/engine/install/ubuntu/
• https://hub.docker.com/_/postgres
• https://docs.docker.com/reference/compose-file/
• https://docs.docker.com/engine/storage/volumes/
• https://learn.microsoft.com/en-us/windows/wsl/networking