Support standard PostgreSQL environment variables in postgresAdapter

[vprasanth]

Summary

The postgresAdapter should support standard PostgreSQL environment variables (PGUSER, PGHOST, PGPASSWORD, PGDATABASE, PGPORT) to improve developer experience and compatibility with existing PostgreSQL tooling.

Problem Statement

Currently, the postgresAdapter requires either:

1. A connection string via DATABASE_URI or POSTGRES_URL
2. Explicit configuration of individual pool parameters

This creates friction when working with:

• Standard PostgreSQL tools (psql, pg_dump, pg_restore)
• Docker environments that use standard PostgreSQL environment variables
• Deployment platforms that automatically set these standard variables
• Teams already using PostgreSQL conventions

Current Workaround

Developers must either:

// Option 1: Use connection string
db: postgresAdapter({
  pool: {
    connectionString: process.env.DATABASE_URI,
  }
})

// Option 2: Map each variable manually
db: postgresAdapter({
  pool: {
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT || '5432'),
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
  }
})

Proposed Solution

The postgresAdapter should automatically detect and use standard PostgreSQL environment variables when no explicit configuration is provided:

// Should work automatically with standard env vars
db: postgresAdapter({
  // Automatically uses PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD
})

// Or with explicit fallback priority
db: postgresAdapter({
  pool: {
    // Uses connectionString if provided, falls back to standard PG env vars
    connectionString: process.env.DATABASE_URI,
  }
})

Environment Variables to Support

• PGHOST - Database host (default: localhost)
• PGPORT - Database port (default: 5432)
• PGDATABASE - Database name
• PGUSER - Database username
• PGPASSWORD - Database password
• PGSSLMODE - SSL mode configuration

Priority Order

1. Explicit pool configuration (current behavior)
2. Connection string (DATABASE_URI/POSTGRES_URL)
3. Standard PostgreSQL environment variables (new feature)
4. Reasonable defaults

Benefits

1. Better DX: Works out-of-the-box with PostgreSQL conventions
2. Tool Compatibility: Same env vars work for psql, pg_dump, and Payload
3. Docker Integration: Standard PostgreSQL Docker images use these variables
4. Platform Compatibility: Many hosting platforms set these automatically
5. Team Consistency: Reduces configuration overhead and confusion

Implementation Notes

This could leverage the existing pg library's built-in support for these environment variables, or implement similar logic in the adapter initialization.

Example Use Cases

Docker Compose

services:
  app:
    environment:
      - PGHOST=postgres
      - PGDATABASE=myapp
      - PGUSER=postgres
      - PGPASSWORD=secret
  postgres:
    image: postgres:15

Deployment Platforms

Many platforms (Railway, Render, etc.) automatically provide these standard variables.

Development Workflow

# Same .env works for all PostgreSQL tools
export PGHOST=localhost
export PGDATABASE=myapp_dev
export PGUSER=postgres
export PGPASSWORD=dev_password

# Works with psql
psql

# Works with pg_dump  
pg_dump > backup.sql

# Should work with Payload (proposed)
npm run dev

Related Issues

• Inconsistent Environment Variable Naming in Payload CMS Setup for Vercel Postgres (DATABASE_URI vs. POSTGRES_URL) #9996 - Inconsistent environment variable naming
• PayloadCMS PostgreSQL adapter ignores external DB connection string and always attempts to connect to localhost:5432 #12245 - PostgreSQL adapter connection issues

---

Environment:

• Payload Version: 3.50.0
• Database: PostgreSQL
• Node.js Version: v20.11.0