PostgreSQL connection error when DB_SSL_MODE is omitted in AdonisJS production

Root Cause

When DB_SSL_MODE is omitted, the pg driver (used by Lucid via Knex) defaults to a non-TLS connection. Managed PostgreSQL services that enforce TLS will reject this connection, causing errors during the first query or migration.

Configuration Fix

Define SSL per connection in config/database.ts to avoid affecting SQLite:

// config/database.ts
export default { 
  connection: Env.get('DB_CONNECTION'),
  connections: {
    pg: {
      client: 'pg',
      connection: {
        host: Env.get('DB_HOST'),
        database: Env.get('DB_NAME'),
        user: Env.get('DB_USER'),
        password: Env.get('DB_PASSWORD'),
        ssl: Env.get('DB_SSL') === 'true' ? { rejectUnauthorized: false } : false,
      },
    },
    sqlite: {
      client: 'sqlite3',
      connection: { filename: './tmp/db.sqlite' },
      useNullAsDefault: true,
    },
  },
}

Environment Setup

In production .env:

DB_CONNECTION=pg
DB_SSL=true

SSL Mode Selection

• ssl: true - Basic TLS (works for most providers)
• ssl: { rejectUnauthorized: false } - Required for providers without verifiable CA certificates
• ssl: { ca: fs.readFileSync('path/to/ca.crt') } - Most secure when CA certificate is available

Early Warning Implementation

Add validation in a service provider to fail fast:

// start/boot.ts or a service provider
if (process.env.NODE_ENV === 'production' && 
    process.env.DB_CONNECTION === 'pg' && 
    !process.env.DB_SSL) {
  console.error('ERROR: DB_SSL must be set in production when using PostgreSQL')
  process.exit(1)
}

Important Notes

Security: Using rejectUnauthorized: false disables certificate verification. Obtain your provider's CA certificate for production deployments.

Version: AdonisJS v5/v6 syntax may vary slightly. Verify against your installed version.

Connection Poolers: If using PgBouncer or similar, you may need to disable prepared statements to avoid conflicts.