Database Connection

APSO uses PostgreSQL as its database. This guide covers connection setup for local development, production, and APSO Cloud.

Connection String

The database connection is configured via the DATABASE_URL environment variable:

DATABASE_URL=postgresql://user:password@host:port/database

Connection String Components

Local Development

Option 1: Docker (Recommended)

The fastest way to get PostgreSQL running locally:

# Start PostgreSQL container
docker run --name apso-db \
  -e POSTGRES_USER=apso \
  -e POSTGRES_PASSWORD=apso \
  -e POSTGRES_DB=apso_dev \
  -p 5432:5432 \
  -d postgres:16
 
# Connection string
DATABASE_URL=postgresql://apso:apso@localhost:5432/apso_dev

Option 2: Local Installation

If you have PostgreSQL installed locally:

# Create user and database
createuser -s apso_user
createdb -O apso_user apso_dev
 
# Connection string
DATABASE_URL=postgresql://apso_user@localhost:5432/apso_dev

Option 3: Docker Compose

For a complete development stack:

version: '3.8'
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: apso
      POSTGRES_PASSWORD: apso
      POSTGRES_DB: apso_dev
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
 
volumes:
  pgdata:

docker-compose up -d

APSO Cloud Database

When using APSO Cloud, your database is automatically provisioned:

1. Go to your service in the APSO Dashboard
2. Navigate to Settings > Database
3. Copy the connection string

Your APSO Cloud connection string:

DATABASE_URL=postgresql://user:pass@db.apso.cloud:5432/service-name

Features

• Automatic backups (daily)
• Point-in-time recovery
• SSL/TLS encryption
• Connection pooling
• Monitoring & alerts

Production Configuration

SSL Connection

For production, always use SSL:

DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=require

SSL modes:

Connection Pooling

For high-traffic applications, use connection pooling:

{
  "database": {
    "pool": {
      "min": 5,
      "max": 20,
      "idleTimeout": 30000
    }
  }
}

Environment-Specific Configuration

DATABASE_URL=postgresql://apso:apso@localhost:5432/apso_dev

DATABASE_URL=postgresql://user:pass@prod.db.com:5432/apso_prod?sslmode=require

Testing Connection

Verify your database connection:

# Using psql
psql $DATABASE_URL -c "SELECT 1"
 
# Using your APSO server
npm run dev
# Check logs for "Database connected"

PostgreSQL Requirements

Required Extensions

APSO uses these PostgreSQL extensions (auto-enabled):

• uuid-ossp — UUID generation
• pgcrypto — Encryption functions

Troubleshooting

Connection Refused

Error: connect ECONNREFUSED 127.0.0.1:5432

Solutions:

• Check if PostgreSQL is running: docker ps or pg_isready
• Verify the port is correct
• Check firewall settings

Authentication Failed

Error: password authentication failed for user "apso"

Solutions:

• Verify username and password
• Check pg_hba.conf for authentication rules
• Reset the user password

SSL Required

Error: SSL connection is required

Solutions:

• Add ?sslmode=require to your connection string
• Or disable SSL for local development (not recommended)

Related

• Local Development →
• Migrations →
• Troubleshooting →