Local Development with PostgreSQL

Purpose: Set up and work with PostgreSQL database locally
Last Updated: 2024-11-26

Overview

Local development uses Docker Compose to run a PostgreSQL database with all schema loaded automatically. The setup includes supporting services for observability (Grafana, Loki) and database management (pgAdmin).

Docker Compose Services

PostgreSQL Database

Service name: postgres
Image: Custom build from Dockerfile.postgres
Port: 54320:5432 (host:container)
Data: Persisted in postgres_data volume

Key features:

• Loads all SQL files from db/ directory on startup
• Configures logging to postgres_logs volume
• Uses template database pattern for fast test database creation
• Includes pg_cron extension for scheduled jobs

Supporting Services

pgAdmin (Database Management UI)

• Port: 5050:80
• Access: http://localhost:5050
• Pre-configured to connect to postgres service
• Default credentials: admin@savvifi.com / postgres

Grafana (Metrics & Logs)

• Port: 3101:3000
• Access: http://localhost:3101
• Pre-configured datasource for Loki

Loki + Promtail (Log Aggregation)

• Collects PostgreSQL logs
• Port: 3100 (Loki)

Quick Start

Start Services

# Start all services
docker compose up -d

# Start only database
docker compose up -d postgres

# View logs
docker compose logs -f postgres

Verify Database

# Check health
docker compose ps

# Connect with psql
psql -h localhost -p 54320 -U savvi -d savvi_studio

# Or use pgAdmin
open http://localhost:5050

Stop Services

# Stop all services
docker compose down

# Stop and remove volumes (complete reset)
docker compose down -v

Database Initialization

Build Process (Dockerfile.postgres)

The custom PostgreSQL image:

FROM postgres:18-alpine

# Install pg_cron extension
RUN apk add --no-cache postgresql-dev make gcc musl-dev && \
    # ... build pg_cron ...

# Copy SQL files
COPY db/ /docker-entrypoint-initdb.d/

Key points:

• Based on PostgreSQL 18 Alpine
• Includes pg_cron extension
• Copies all db/ files to init directory

SQL File Loading

Files in db/ directory are loaded automatically on first run:

Loading order:

1. Files sorted numerically by filename
2. Executed in order: 000_*.sql, 001_*.sql, etc.
3. Data files loaded via util.load_json_data() function
4. See Schema Organization for details

Example loading sequence:

000_ids_schema.sql       → Core ID types
000_prelude.sql          → Utility functions
001_cursors.sql          → Cursor system
100_audit_system.sql     → Audit logging
200_auth_schema.sql      → Authentication schema
...
500_studio_schema.sql    → Studio schema

Environment Variables

Required variables in .env.development:

# Database connection
PGHOST=localhost
PGPORT=54320
PGDATABASE=savvi_studio
PGUSER=savvi
PGPASSWORD=password

Variables from .env.docker-compose:

# Template database config
TEMPLATE_DB_NAME=${PGDATABASE}_template

# Role passwords for testing
TEST_USER_PASSWORD=test_password
TEST_ADMIN_PASSWORD=admin_password

Template Database Pattern

Purpose

Template databases enable fast test database creation (milliseconds vs seconds).

How It Works

1. Initial Setup: postgres-post-init service runs after first startup
2. Template Creation: Renames main database to {PGDATABASE}_template
3. Template Marking: Sets IS_TEMPLATE = TRUE on template
4. Working Database: Creates new working database from template
5. Test Creation: Tests create databases instantly from template

Implementation

In docker-compose.yml:

postgres-post-init:
  image: postgres:18-alpine
  command: sh /usr/local/bin/post-init.sh
  depends_on:
    postgres:
      condition: service_healthy

Post-init script:

# Terminate connections
psql -d postgres -c "SELECT pg_terminate_backend(pid) 
  FROM pg_stat_activity 
  WHERE datname='$PGDATABASE'"

# Rename to template
psql -d postgres -c "ALTER DATABASE $PGDATABASE 
  RENAME TO ${PGDATABASE}_template"

# Mark as template
psql -d postgres -c "ALTER DATABASE ${PGDATABASE}_template 
  WITH IS_TEMPLATE = TRUE"

# Create working database
psql -d postgres -c "CREATE DATABASE $PGDATABASE 
  WITH TEMPLATE ${PGDATABASE}_template"

Benefits

• Fast test setup: Create test DB in ~10ms instead of ~5s
• Consistent schema: All tests use same template
• Parallel tests: Multiple test databases from one template
• CI efficiency: Dramatically faster test suites

Common Development Workflows

Reset Database

# Stop and remove volumes
docker compose down -v

# Start fresh
docker compose up -d postgres

# Wait for initialization
docker compose logs -f postgres

Apply Schema Changes

Option 1: Reset (development)

# Reset database (loses all data)
docker compose down -v
docker compose up -d postgres

Option 2: Migration (production-like)

# Add SQL file to db/
# Restart container
docker compose restart postgres

# Or run migration manually
psql -h localhost -p 54320 -U savvi -d savvi_studio \
  -f db/999_your_migration.sql

Inspect Database

Using psql:

# Connect
psql -h localhost -p 54320 -U savvi -d savvi_studio

# List schemas
\dn

# List tables in schema
\dt graph.*

# Describe table
\d graph.nodes

# Run query
SELECT * FROM graph.nodes LIMIT 10;

Using pgAdmin:

1. Open http://localhost:5050
2. Navigate to Servers → Docker → Databases → savvi_studio
3. Use query tool or browse schemas

View Logs

PostgreSQL logs:

# Real-time logs
docker compose logs -f postgres

# Specific time range
docker compose logs --since 1h postgres

# Search logs
docker compose logs postgres | grep ERROR

Structured logs in Grafana:

1. Open http://localhost:3101
2. Explore → Loki → Select postgres job
3. Filter by level, user, database, etc.

Export/Import Data

Export schema:

pg_dump -h localhost -p 54320 -U savvi -d savvi_studio \
  --schema-only > schema.sql

Export data:

pg_dump -h localhost -p 54320 -U savvi -d savvi_studio \
  --data-only --table=graph.nodes > nodes_data.sql

Import:

psql -h localhost -p 54320 -U savvi -d savvi_studio < backup.sql

Troubleshooting

Database Won't Start

Check logs:

docker compose logs postgres

Common issues:

• Port 54320 already in use
• Invalid SQL in db/ files
• Missing environment variables
• Corrupted data volume

Solution:

# Reset everything
docker compose down -v
docker compose up -d

Connection Refused

Check service status:

docker compose ps

Verify health:

docker compose exec postgres pg_isready -U savvi

Check environment:

# Verify env vars
cat .env.development | grep PG

Slow Queries

Enable query logging:

Already configured in docker-compose.yml:

- "log_min_duration_statement=1000"  # Log queries > 1s
- "log_statement=mod"                # Log modifications

View slow queries in logs:

docker compose logs postgres | grep "duration:"

Use Grafana:

1. Navigate to Grafana dashboard
2. Filter logs by duration: keyword
3. Analyze slow query patterns

Template Database Issues

Template not created:

# Check if post-init ran
docker compose logs postgres-post-init

# Manually run post-init
docker compose up postgres-post-init

Template corrupted:

# Drop template and recreate
docker compose down -v
docker compose up -d postgres

pgAdmin Connection Issues

Reset pgAdmin:

docker compose down
docker volume rm savvi-studio-full_pgadmin_data
docker compose up -d pgadmin

Manual connection setup:

• Host: postgres (service name, not localhost)
• Port: 5432 (container port, not 54320)
• Database: savvi_studio
• Username: savvi
• Password: from PGPASSWORD env var

Development Tips

Use Connection Pooling

Application uses connection pooling automatically via src/lib/db/pool.ts.

Pool configuration:

• Default pool: 10 connections max
• Management pool: 5 connections max
• Idle timeout: 10 seconds

Inspect Generated Types

# View generated wrappers
ls src/__generated__/

# View specific schema
cat src/__generated__/graph/index.ts

Test Database Queries

# Quick test
psql -h localhost -p 54320 -U savvi -d savvi_studio \
  -c "SELECT graph.create_resource('test.type'::ltree, 'test-id')"

Monitor Connections

# Active connections
psql -h localhost -p 54320 -U savvi -d savvi_studio \
  -c "SELECT count(*) FROM pg_stat_activity"

# Connection details
psql -h localhost -p 54320 -U savvi -d savvi_studio \
  -c "SELECT datname, usename, application_name, state 
      FROM pg_stat_activity"

Related Documentation

• Schema Organization - Understand SQL file structure
• Migrations - Schema evolution patterns
• Testing - Use template database in tests
• Codegen System - Generate type-safe wrappers

Quick Reference

Task	Command
Start database	docker compose up -d postgres
View logs	docker compose logs -f postgres
Connect with psql	psql -h localhost -p 54320 -U savvi -d savvi_studio
Reset database	docker compose down -v && docker compose up -d
Open pgAdmin	http://localhost:5050
Open Grafana	http://localhost:3101
Export schema	pg_dump -h localhost -p 54320 -U savvi --schema-only

---

Next steps: Once local database is running, see Schema Organization to understand the SQL file structure, or Best Practices to learn how to interact with the database from code.