PostgreSQL Setup

Using PostgreSQL instead of SQLite

When to Use PostgreSQL

Most apps don’t need it. SQLite handles 99% of use cases.

Switch to PostgreSQL when you need:

  • Multi-region writes
  • Advanced features (PostGIS, full-text search)
  • Compliance requirements

See Philosophy → Why SQLite for details.

Development

1. Run PostgreSQL locally with Homebrew, Docker, or Postgres.app:

# Quick option: one-shot Docker container
docker run -d --name goilerplate-pg \
  -e POSTGRES_DB=goilerplate_dev \
  -e POSTGRES_USER=goilerplate \
  -e POSTGRES_PASSWORD=dev_password \
  -p 5432:5432 \
  postgres:16-alpine

2. Update .env:

DB_DRIVER=pgx
DB_CONNECTION=postgres://goilerplate:dev_password@localhost:5432/goilerplate_dev?sslmode=disable

3. Start the app:

task dev

Migrations run automatically on startup.

Production

Use a managed PostgreSQL service - no infrastructure to manage.

Self-Hosted PaaS (Best Balance):

  • Dokploy/Coolify - Click “Add Service” → PostgreSQL template
    • Runs on your VPS, managed via UI
    • Automatic backups, volumes, connection strings
    • Perfect for $5-10/month self-hosted setups

Cloud Managed:

  • Supabase - Free tier, excellent for startups
  • Neon - Serverless PostgreSQL, generous free tier
  • Railway - railway add postgresql
  • AWS RDS - Enterprise-grade
  • DigitalOcean - Managed databases ($15/month)

Setup:

  1. Create database at your provider (or via Dokploy/Coolify template)
  2. Copy connection string
  3. Set in .env: 
    DB_DRIVER=pgx
DB_CONNECTION=postgres://user:pass@host:5432/db?sslmode=require
  4. Restart your app

That’s it! Migrations run automatically.

Option 2: Self-Hosted in Docker (Advanced)

For VPS deployments where you want full control:

Via Dokploy/Coolify:

  • Use their PostgreSQL template (easiest - handles everything)

Via Docker:

  • Run a separate postgres:16-alpine container on your VPS
  • Mount a volume for /var/lib/postgresql/data
  • Connect via internal Docker network

Configuration:

DB_DRIVER=pgx
DB_CONNECTION=postgres://goilerplate:password@postgres:5432/goilerplate

Data persists in Docker volumes automatically.

Migration from SQLite

Change .env:

# Before (SQLite):
DB_DRIVER=sqlite
DB_CONNECTION=./data/acme.db

# After (PostgreSQL):
DB_DRIVER=pgx
DB_CONNECTION=postgres://user:pass@host:5432/db

Restart app. Migrations run automatically.

Note: Data does NOT migrate automatically. Export/import separately if needed.

That’s It

PostgreSQL is optional. SQLite is the default and works great for most projects.

← DeploymentUpdating goilerplate →