db.js

Go to the documentation of this file.

/**
 * @file PostgreSQL Database Connection Pool
 * @module services/db
 * @description
 * Centralized PostgreSQL connection pool using node-postgres (pg). Provides database
 * connectivity for all backend services with automatic connection management, pooling,
 * and error handling.
 *
 * **Configuration Priority:**
 * 1. Root `.env` file - Shared configuration for DevOps scripts
 * 2. Backend `.env` file - Overrides for backend-specific settings
 *
 * **Connection Modes:**
 * - **Managed Database** (DigitalOcean, Heroku): Uses `DATABASE_URL` connection string
 * - **Self-Hosted**: Uses individual environment variables (DB_HOST, DB_PORT, etc.)
 *
 * **SSL Handling:**
 * - Automatically enables SSL for DATABASE_URL or if DB_SSL=true
 * - Uses `rejectUnauthorized: false` for managed databases with self-signed certs
 * - Strips `sslmode=require` from URL to prevent pg conflicts
 *
 * **Environment Variables:**
 * - `DATABASE_URL` - PostgreSQL connection string (overrides individual vars)
 * - `DB_HOST` - Database host (default: DigitalOcean managed DB)
 * - `DB_PORT` - Database port (default: 25060)
 * - `DB_USER` - Database username (default: doadmin)
 * - `DB_PASSWORD` - Database password (strips quotes)
 * - `DB_NAME` - Database name (default: defaultdb)
 * - `DB_SSL` - Enable SSL (default: true)
 *
 * **Pool Configuration:**
 * - Uses pg.Pool defaults (10 max connections, 30s idle timeout)
 * - Automatic connection reuse and recycling
 * - Event handlers for connection lifecycle
 *
 * **Error Handling:**
 * - Logs unexpected errors on idle clients
 * - Exits process on connection pool errors (requires restart)
 * - Prevents silent connection failures
 * @exports {Pool} PostgreSQL connection pool instance
 * @example
 * // Query with parameterized values (SQL injection safe)
 * const pool = require('./services/db');
 * const result = await pool.query(
 *   'SELECT * FROM customers WHERE tenant_id = $1',
 *   [tenantId]
 * );
 * console.log(result.rows);
 * @example
 * // Transaction with client checkout
 * const pool = require('./services/db');
 * const client = await pool.connect();
 * try {
 *   await client.query('BEGIN');
 *   await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
 *   await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
 *   await client.query('COMMIT');
 * } catch (e) {
 *   await client.query('ROLLBACK');
 *   throw e;
 * } finally {
 *   client.release();
 * }
 * @example
 * // Bulk insert with transaction
 * const pool = require('./services/db');
 * const client = await pool.connect();
 * try {
 *   await client.query('BEGIN');
 *   for (const row of data) {
 *     await client.query('INSERT INTO metrics (...) VALUES (...)', row);
 *   }
 *   await client.query('COMMIT');
 * } finally {
 *   client.release();
 * }
 * @exports pool - PostgreSQL connection pool instance
 * @see {@link https://node-postgres.com/} node-postgres documentation
 * @see {@link https://www.postgresql.org/docs/} PostgreSQL documentation
 * @see {@link module:middleware/tenant} for tenant-scoped queries
 * @requires pg - PostgreSQL client library
 * @requires dotenv - Environment variable loader
 */
 
const { Pool } = require('pg');
const path = require('path');
 
// Load env in this order:
// 1) Project root .env (used by devops scripts and shared config)
// 2) backend/.env overrides root values for backend-specific settings
require('dotenv').config({ path: path.resolve(__dirname, '../../.env') });
require('dotenv').config({ path: path.resolve(__dirname, '../.env'), override: true });
 
// Support both individual vars and DATABASE_URL for managed DB
const dbConfig = process.env.DATABASE_URL
  ? (() => {
      // Strip sslmode=require from URL to ensure pg respects explicit ssl options
      const cleanedUrl = process.env.DATABASE_URL.replace(/([?&])sslmode=require(&|$)/, (m, p1, p2) => (p2 === '&' ? p1 : ''));
      return {
        connectionString: cleanedUrl,
        ssl: process.env.DB_SSL === 'true' || process.env.DATABASE_URL.includes('sslmode=require')
          ? { rejectUnauthorized: false }
          : false,
      };
    })()
  : {
      host: process.env.DB_HOST || 'rmm-psa-db-do-user-28531160-0.i.db.ondigitalocean.com',
      port: parseInt(process.env.DB_PORT || '25060', 10),
      user: process.env.DB_USER || 'doadmin',
      password: (process.env.DB_PASSWORD || 'AVNS_J8RJAmsEwsHFG52_-F2').replace(/^"|"$/g, ''),
      database: process.env.DB_NAME || 'defaultdb',
      ssl: (process.env.DB_SSL === 'false' ? false : true)
        ? { rejectUnauthorized: false }
        : false,
    };
 
const pool = new Pool(dbConfig);
 
// Test connection and log status
let logged = false;
pool.on('connect', () => {
  if (!logged) {
    console.log('[DB] Connected to PostgreSQL');
    logged = true;
  }
});
 
pool.on('error', (err) => {
  console.error('[DB] Unexpected error on idle client', err);
  process.exit(-1);
});
 
module.exports = pool;