cve-dashboard/backend/db.js

// PostgreSQL Connection Pool
// All route files import this module instead of receiving a sqlite3 `db` parameter.
// Configured via DATABASE_URL environment variable.

// Ensure dotenv is loaded before accessing env vars
require('dotenv').config({ path: require('path').join(__dirname, '.env') });

const { Pool } = require('pg');

if (!process.env.DATABASE_URL) {
    console.error('[DB] FATAL: DATABASE_URL environment variable is not set.');
    console.error('[DB] Expected format: postgresql://user:password@host:port/database');
    process.exit(1);
}

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,                        // Maximum connections in pool
    idleTimeoutMillis: 30000,       // Close idle connections after 30s
    connectionTimeoutMillis: 5000,  // Fail if connection takes >5s
});

// Log unexpected pool errors (connection drops, etc.)
pool.on('error', (err) => {
    console.error('[DB Pool] Unexpected error on idle client:', err.message);
});

// Track active connections and warn when approaching exhaustion
let _activeCount = 0;
pool.on('acquire', () => {
    _activeCount++;
    if (_activeCount >= 8) {
        console.warn(`[DB Pool] WARNING: ${_activeCount}/10 connections active — approaching exhaustion`);
    }
});
pool.on('release', () => { _activeCount--; });

// Health check — verify connection on startup
pool.query('SELECT NOW()')
    .then(() => console.log('[DB Pool] Connected to PostgreSQL'))
    .catch((err) => {
        console.error('[DB Pool] Failed to connect:', err.message);
        console.error('[DB Pool] Check DATABASE_URL and ensure Postgres is running on port 5433');
    });

module.exports = pool;