24060649903

Committed 07 Apr 2026 02:03AM UTC coverage: 90.547% (-0.01%) from 90.557%

Build # 24060649903

Build Type

Pull #438

github

Committed by

web-flow

Commit Message

Merge 7da694b43 into d130899e6

Pull Request Pull Request #438: chore(deps): bump simple-icons from 16.14.0 to 16.15.0

Coverage Stats

6766 of 7924 branches covered (85.39%)

Branch coverage included in aggregate %.

11462 of 12207 relevant lines covered (93.9%)

218.16 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

81.22

/src/db.js

/**
 * Database Module
 * PostgreSQL connection pool and migration runner
 */

import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { runner } from 'node-pg-migrate';
import pg from 'pg';
import { debug, info, error as logError, warn } from './logger.js';

const { Pool } = pg;

/** @type {pg.Pool | null} */
let pool = null;

/** @type {boolean} Re-entrancy guard for initDb */
let initializing = false;

/** @type {ReturnType<typeof setInterval> | null} */
let leakDetectionInterval = null;

/**
 * Selects the SSL configuration for a pg.Pool based on DATABASE_SSL and the connection string.
 *
 * DATABASE_SSL values:
 *   "false" / "off" / "disable" → SSL disabled
 *   "no-verify"                 → SSL enabled but server certificate not verified
 *   "true" / "on" / "require"   → SSL enabled with server certificate verification
 *
 * If DATABASE_SSL is unset, SSL is disabled for local connections and enabled
 * with full certificate verification for non-local connections.
 *
 * @param {string} connectionString - Database connection URL
 * @returns {false|{rejectUnauthorized: boolean}} `false` to disable SSL, or an object with `rejectUnauthorized` indicating whether server certificates must be verified
 */
function getSslConfig(connectionString) {
  let hostname = '';
  let sslMode = '';

  try {
    const connectionUrl = new URL(connectionString);
    hostname = connectionUrl.hostname.toLowerCase();
    sslMode = (connectionUrl.searchParams.get('sslmode') || '').toLowerCase().trim();
  } catch {
    // Ignore malformed URLs and fall back to safe defaults.
  }

  // Explicit sslmode=disable in connection string takes precedence.
  if (sslMode === 'disable' || sslMode === 'off' || sslMode === 'false') {
    return false;
  }

  // Railway internal connections never need SSL.
  if (hostname.includes('railway.internal') || connectionString.includes('railway.internal')) {
    return false;
  }

  const sslEnv = (process.env.DATABASE_SSL || '').toLowerCase().trim();

  if (sslEnv === 'false' || sslEnv === 'off' || sslEnv === 'disable' || sslEnv === '0') {
    return false;
  }

  if (sslEnv === 'no-verify') {
    return { rejectUnauthorized: false };
  }

  if (sslEnv === 'true' || sslEnv === 'on' || sslEnv === 'require' || sslEnv === '1') {
    return { rejectUnauthorized: true };
  }

  // Local development databases commonly run without TLS.
  if (!sslEnv && ['localhost', '127.0.0.1', '::1'].includes(hostname)) {
    return false;
  }

  if (sslEnv) {
    warn('Unrecognized DATABASE_SSL value, using secure default', {
      value: sslEnv,
      source: 'database_ssl',
    });
  }

  // Default: SSL with full verification.
  return { rejectUnauthorized: true };
}

/**
 * Apply pending PostgreSQL schema migrations from the project's migrations directory.
 *
 * @param {string} databaseUrl - Connection string used to run migrations against the database.
 * @returns {Promise<void>}
 */
async function runMigrations(databaseUrl) {
  const __filename = fileURLToPath(import.meta.url);
  const __dirname = path.dirname(__filename);
  const migrationsDir = path.resolve(__dirname, '..', 'migrations');

  await runner({
    databaseUrl,
    dir: migrationsDir,
    direction: 'up',
    migrationsTable: 'pgmigrations',
    log: (msg) => {
      if (typeof msg === 'string' && msg.includes("Can't determine timestamp")) return;
      info(msg);
    },
  });

  info('Database migrations applied');
}

/**
 * Wrap pool.query with slow query detection.
 * Logs queries that exceed the threshold with timing info.
 *
 * @param {pg.Pool} poolInstance - The pool instance to wrap
 * @param {number} thresholdMs - Slow query threshold in milliseconds
 */
function wrapPoolQuery(poolInstance, thresholdMs) {
  const originalQuery = poolInstance.query.bind(poolInstance);

  poolInstance.query = async function wrappedQuery(...args) {
    const start = Date.now();
    try {
      const result = await originalQuery(...args);
      const duration = Date.now() - start;

      if (duration >= thresholdMs) {
        const queryText = typeof args[0] === 'string' ? args[0] : (args[0]?.text ?? 'unknown');
        warn('Slow query detected', {
          duration_ms: duration,
          threshold_ms: thresholdMs,
          query: queryText.slice(0, 500),
          source: 'slow_query_log',
        });

        // Attempt EXPLAIN (best-effort, fire-and-forget — do not await)
        const explainText = typeof args[0] === 'string' ? args[0] : (args[0]?.text ?? '');
        const explainValues = Array.isArray(args[1]) ? args[1] : (args[0]?.values ?? []);

        if (/^\s*SELECT/i.test(explainText)) {
          originalQuery({
            text: `EXPLAIN ${explainText}`,
            values: explainValues,
          })
            .then((explainResult) => {
              const plan = explainResult.rows.map((r) => Object.values(r)[0]).join('\n');
              warn('Slow query EXPLAIN plan', {
                duration_ms: duration,
                query: queryText.slice(0, 200),
                plan: plan.slice(0, 2000),
                source: 'slow_query_log',
              });
            })
            .catch(() => {});
        }
      }

      return result;
    } catch (err) {
      const duration = Date.now() - start;
      const queryText = typeof args[0] === 'string' ? args[0] : (args[0]?.text ?? 'unknown');
      logError('Query failed', {
        duration_ms: duration,
        query: queryText.slice(0, 500),
        error: err.message,
        source: 'db_query',
      });
      throw err;
    }
  };
}

/**
 * Start the connection leak detection interval.
 * Warns if the pool is near capacity or has waiting requests.
 *
 * @param {pg.Pool} poolInstance - The pool to monitor
 * @param {number} maxSize - Configured pool max size
 */
function startLeakDetection(poolInstance, maxSize) {
  if (leakDetectionInterval) return;

  leakDetectionInterval = setInterval(() => {
    const waiting = poolInstance.waitingCount;
    const total = poolInstance.totalCount;

    if (waiting > 0) {
      warn('Database connection pool has waiting clients', {
        waiting,
        total,
        idle: poolInstance.idleCount,
        max: maxSize,
        source: 'pool_monitor',
      });
    } else {
      const activeCount = poolInstance.totalCount - poolInstance.idleCount;
      if (activeCount >= maxSize * 0.8) {
        warn('Database connection pool nearing capacity', {
          total,
          active: activeCount,
          idle: poolInstance.idleCount,
          waiting,
          max: maxSize,
          utilization_pct: Math.round((activeCount / maxSize) * 100),
          source: 'pool_monitor',
        });
      }
    }
  }, 30_000).unref();
}

/**
 * Stop the connection leak detection interval.
 */
export function stopLeakDetection() {
  if (leakDetectionInterval) {
    clearInterval(leakDetectionInterval);
    leakDetectionInterval = null;
  }
}

/**
 * Get pool statistics.
 *
 * @returns {{ total: number, idle: number, waiting: number } | null} Pool stats or null if not initialized
 */
export function getPoolStats() {
  if (!pool) return null;
  return {
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  };
}

/**
 * Initialize the PostgreSQL connection pool and apply any pending database migrations.
 *
 * @returns {Promise<pg.Pool>} The initialized pg.Pool instance.
 * @throws {Error} If initialization is already in progress.
 * @throws {Error} If the DATABASE_URL environment variable is not set.
 * @throws {Error} If the connection test or migration application fails.
 */
export async function initDb() {
  if (initializing) {
    throw new Error('initDb is already in progress');
  }
  if (pool) return pool;

  initializing = true;
  try {
    const connectionString = process.env.DATABASE_URL;
    if (!connectionString) {
      throw new Error('DATABASE_URL environment variable is not set');
    }

    /** @param {string|undefined} env @param {number} defaultVal */
    const parsePositiveInt = (env, defaultVal) => {
      const val = parseInt(env, 10);
      return Number.isNaN(val) || val < 0 ? defaultVal : val;
    };

    const poolSize = Math.max(1, parsePositiveInt(process.env.PG_POOL_SIZE, 5));
    const idleTimeoutMs = parsePositiveInt(process.env.PG_IDLE_TIMEOUT_MS, 30000);
    const connectionTimeoutMs = parsePositiveInt(process.env.PG_CONNECTION_TIMEOUT_MS, 10000);

    pool = new Pool({
      connectionString,
      max: poolSize,
      idleTimeoutMillis: idleTimeoutMs,
      connectionTimeoutMillis: connectionTimeoutMs,
      ssl: getSslConfig(connectionString),
    });

    // Prevent unhandled pool errors from crashing the process
    pool.on('error', (err) => {
      logError('Unexpected database pool error', { error: err.message, source: 'database_pool' });
    });

    // Pool event listeners for observability
    pool.on('connect', () => {
      debug('Database pool: new client connected', {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
        source: 'pool_events',
      });
    });

    pool.on('acquire', () => {
      debug('Database pool: client acquired', {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
        source: 'pool_events',
      });
    });

    pool.on('remove', () => {
      debug('Database pool: client removed', {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
        source: 'pool_events',
      });
    });

    // Wrap query with slow query logging
    const slowQueryThresholdMs = parsePositiveInt(process.env.PG_SLOW_QUERY_MS, 100);
    wrapPoolQuery(pool, slowQueryThresholdMs);

    try {
      // Test connection
      const client = await pool.connect();
      try {
        await client.query('SELECT NOW()');
        info('Database connected');
      } finally {
        client.release();
      }

      // Run pending migrations
      await runMigrations(connectionString);

      info('Database schema initialized');
    } catch (err) {
      // Clean up the pool so getPool() doesn't return an unusable instance
      await pool.end().catch(() => {});
      pool = null;
      throw err;
    }

    // Start connection leak detection
    startLeakDetection(pool, poolSize);

    return pool;
  } finally {
    initializing = false;
  }
}

/**
 * Get the database pool
 * @returns {pg.Pool} The connection pool
 * @throws {Error} If pool is not initialized
 */
export function getPool() {
  if (!pool) {
    throw new Error('Database not initialized. Call initDb() first.');
  }
  return pool;
}

/**
 * Gracefully close the database pool
 */
export async function closeDb() {
  stopLeakDetection();
  if (pool) {
    try {
      await pool.end();
      info('Database pool closed');
    } catch (err) {
      logError('Error closing database pool', { error: err.message });
    } finally {
      pool = null;
    }
  }
}

1	/**
2	* Database Module
3	* PostgreSQL connection pool and migration runner
4	*/
5
6	import path from 'node:path';
7	import { fileURLToPath } from 'node:url';
8	import { runner } from 'node-pg-migrate';
9	import pg from 'pg';
10	import { debug, info, error as logError, warn } from './logger.js';
11
12	const { Pool } = pg;	83✔
13
14	/** @type {pg.Pool \| null} */
15	let pool = null;	83✔
16
17	/** @type {boolean} Re-entrancy guard for initDb */
18	let initializing = false;	83✔
19
20	/** @type {ReturnType<typeof setInterval> \| null} */
21	let leakDetectionInterval = null;	83✔
22
23	/**
24	* Selects the SSL configuration for a pg.Pool based on DATABASE_SSL and the connection string.
25	*
26	* DATABASE_SSL values:
27	* "false" / "off" / "disable" → SSL disabled
28	* "no-verify" → SSL enabled but server certificate not verified
29	* "true" / "on" / "require" → SSL enabled with server certificate verification
30	*
31	* If DATABASE_SSL is unset, SSL is disabled for local connections and enabled
32	* with full certificate verification for non-local connections.
33	*
34	* @param {string} connectionString - Database connection URL
35	* @returns {false\|{rejectUnauthorized: boolean}} `false` to disable SSL, or an object with `rejectUnauthorized` indicating whether server certificates must be verified
36	*/
37	function getSslConfig(connectionString) {
38	let hostname = '';	27✔
39	let sslMode = '';	27✔
40
41	try {	27✔
42	const connectionUrl = new URL(connectionString);	27✔
43	hostname = connectionUrl.hostname.toLowerCase();	27✔
44	sslMode = (connectionUrl.searchParams.get('sslmode') \|\| '').toLowerCase().trim();	27✔
45	} catch {
46	// Ignore malformed URLs and fall back to safe defaults.
47	}
48
49	// Explicit sslmode=disable in connection string takes precedence.
50	if (sslMode === 'disable' \|\| sslMode === 'off' \|\| sslMode === 'false') {	27✔
51	return false;	1✔
52	}
53
54	// Railway internal connections never need SSL.
55	if (hostname.includes('railway.internal') \|\| connectionString.includes('railway.internal')) {	26✔
56	return false;	1✔
57	}
58
59	const sslEnv = (process.env.DATABASE_SSL \|\| '').toLowerCase().trim();	25✔
60
61	if (sslEnv === 'false' \|\| sslEnv === 'off' \|\| sslEnv === 'disable' \|\| sslEnv === '0') {	27✔
62	return false;	2✔
63	}
64
65	if (sslEnv === 'no-verify') {	23✔
66	return { rejectUnauthorized: false };	1✔
67	}
68
69	if (sslEnv === 'true' \|\| sslEnv === 'on' \|\| sslEnv === 'require' \|\| sslEnv === '1') {	22✔
70	return { rejectUnauthorized: true };	1✔
71	}
72
73	// Local development databases commonly run without TLS.
74	if (!sslEnv && ['localhost', '127.0.0.1', '::1'].includes(hostname)) {	21✔
75	return false;	20✔
76	}
77
78	if (sslEnv) {	1!
79	warn('Unrecognized DATABASE_SSL value, using secure default', {	×
80	value: sslEnv,
81	source: 'database_ssl',
82	});
83	}
84
85	// Default: SSL with full verification.
86	return { rejectUnauthorized: true };	1✔
87	}
88
89	/**
90	* Apply pending PostgreSQL schema migrations from the project's migrations directory.
91	*
92	* @param {string} databaseUrl - Connection string used to run migrations against the database.
93	* @returns {Promise<void>}
94	*/
95	async function runMigrations(databaseUrl) {
96	const __filename = fileURLToPath(import.meta.url);	26✔
97	const __dirname = path.dirname(__filename);	26✔
98	const migrationsDir = path.resolve(__dirname, '..', 'migrations');	26✔
99
100	await runner({	26✔
101	databaseUrl,
102	dir: migrationsDir,
103	direction: 'up',
104	migrationsTable: 'pgmigrations',
105	log: (msg) => {
106	if (typeof msg === 'string' && msg.includes("Can't determine timestamp")) return;	×
107	info(msg);	×
108	},
109	});
110
111	info('Database migrations applied');	25✔
112	}
113
114	/**
115	* Wrap pool.query with slow query detection.
116	* Logs queries that exceed the threshold with timing info.
117	*
118	* @param {pg.Pool} poolInstance - The pool instance to wrap
119	* @param {number} thresholdMs - Slow query threshold in milliseconds
120	*/
121	function wrapPoolQuery(poolInstance, thresholdMs) {
122	const originalQuery = poolInstance.query.bind(poolInstance);	27✔
123
124	poolInstance.query = async function wrappedQuery(...args) {	27✔
125	const start = Date.now();	4✔
126	try {	4✔
127	const result = await originalQuery(...args);	4✔
128	const duration = Date.now() - start;	3✔
129
130	if (duration >= thresholdMs) {	3✔
131	const queryText = typeof args[0] === 'string' ? args[0] : (args[0]?.text ?? 'unknown');	1!
132	warn('Slow query detected', {	1✔
133	duration_ms: duration,
134	threshold_ms: thresholdMs,
135	query: queryText.slice(0, 500),
136	source: 'slow_query_log',
137	});
138
139	// Attempt EXPLAIN (best-effort, fire-and-forget — do not await)
140	const explainText = typeof args[0] === 'string' ? args[0] : (args[0]?.text ?? '');	1!
141	const explainValues = Array.isArray(args[1]) ? args[1] : (args[0]?.values ?? []);	1!
142
143	if (/^\s*SELECT/i.test(explainText)) {	1!
144	originalQuery({	1✔
145	text: `EXPLAIN ${explainText}`,
146	values: explainValues,
147	})
148	.then((explainResult) => {
149	const plan = explainResult.rows.map((r) => Object.values(r)[0]).join('\n');	×
150	warn('Slow query EXPLAIN plan', {	×
151	duration_ms: duration,
152	query: queryText.slice(0, 200),
153	plan: plan.slice(0, 2000),
154	source: 'slow_query_log',
155	});
156	})
157	.catch(() => {});
158	}
159	}
160
161	return result;	3✔
162	} catch (err) {
163	const duration = Date.now() - start;	1✔
164	const queryText = typeof args[0] === 'string' ? args[0] : (args[0]?.text ?? 'unknown');	1!
165	logError('Query failed', {	1✔
166	duration_ms: duration,
167	query: queryText.slice(0, 500),
168	error: err.message,
169	source: 'db_query',
170	});
171	throw err;	1✔
172	}
173	};
174	}
175
176	/**
177	* Start the connection leak detection interval.
178	* Warns if the pool is near capacity or has waiting requests.
179	*
180	* @param {pg.Pool} poolInstance - The pool to monitor
181	* @param {number} maxSize - Configured pool max size
182	*/
183	function startLeakDetection(poolInstance, maxSize) {
184	if (leakDetectionInterval) return;	25!
185
186	leakDetectionInterval = setInterval(() => {	25✔
187	const waiting = poolInstance.waitingCount;	×
188	const total = poolInstance.totalCount;	×
189
190	if (waiting > 0) {	×
191	warn('Database connection pool has waiting clients', {	×
192	waiting,
193	total,
194	idle: poolInstance.idleCount,
195	max: maxSize,
196	source: 'pool_monitor',
197	});
198	} else {
199	const activeCount = poolInstance.totalCount - poolInstance.idleCount;	×
200	if (activeCount >= maxSize * 0.8) {	×
201	warn('Database connection pool nearing capacity', {	×
202	total,
203	active: activeCount,
204	idle: poolInstance.idleCount,
205	waiting,
206	max: maxSize,
207	utilization_pct: Math.round((activeCount / maxSize) * 100),
208	source: 'pool_monitor',
209	});
210	}
211	}
212	}, 30_000).unref();
213	}
214
215	/**
216	* Stop the connection leak detection interval.
217	*/
218	export function stopLeakDetection() {
219	if (leakDetectionInterval) {	34✔
220	clearInterval(leakDetectionInterval);	25✔
221	leakDetectionInterval = null;	25✔
222	}
223	}
224
225	/**
226	* Get pool statistics.
227	*
228	* @returns {{ total: number, idle: number, waiting: number } \| null} Pool stats or null if not initialized
229	*/
230	export function getPoolStats() {
231	if (!pool) return null;	8✔
232	return {	2✔
233	total: pool.totalCount,
234	idle: pool.idleCount,
235	waiting: pool.waitingCount,
236	};
237	}
238
239	/**
240	* Initialize the PostgreSQL connection pool and apply any pending database migrations.
241	*
242	* @returns {Promise<pg.Pool>} The initialized pg.Pool instance.
243	* @throws {Error} If initialization is already in progress.
244	* @throws {Error} If the DATABASE_URL environment variable is not set.
245	* @throws {Error} If the connection test or migration application fails.
246	*/
247	export async function initDb() {
248	if (initializing) {	30✔
249	throw new Error('initDb is already in progress');	1✔
250	}
251	if (pool) return pool;	29✔
252
253	initializing = true;	28✔
254	try {	28✔
255	const connectionString = process.env.DATABASE_URL;	28✔
256	if (!connectionString) {	28✔
257	throw new Error('DATABASE_URL environment variable is not set');	1✔
258	}
259
260	/** @param {string\|undefined} env @param {number} defaultVal */
261	const parsePositiveInt = (env, defaultVal) => {	27✔
262	const val = parseInt(env, 10);	108✔
263	return Number.isNaN(val) \|\| val < 0 ? defaultVal : val;	108✔
264	};
265
266	const poolSize = Math.max(1, parsePositiveInt(process.env.PG_POOL_SIZE, 5));	27✔
267	const idleTimeoutMs = parsePositiveInt(process.env.PG_IDLE_TIMEOUT_MS, 30000);	27✔
268	const connectionTimeoutMs = parsePositiveInt(process.env.PG_CONNECTION_TIMEOUT_MS, 10000);	27✔
269
270	pool = new Pool({	27✔
271	connectionString,
272	max: poolSize,
273	idleTimeoutMillis: idleTimeoutMs,
274	connectionTimeoutMillis: connectionTimeoutMs,
275	ssl: getSslConfig(connectionString),
276	});
277
278	// Prevent unhandled pool errors from crashing the process
279	pool.on('error', (err) => {	27✔
280	logError('Unexpected database pool error', { error: err.message, source: 'database_pool' });	×
281	});
282
283	// Pool event listeners for observability
284	pool.on('connect', () => {	27✔
285	debug('Database pool: new client connected', {	×
286	total: pool.totalCount,
287	idle: pool.idleCount,
288	waiting: pool.waitingCount,
289	source: 'pool_events',
290	});
291	});
292
293	pool.on('acquire', () => {	27✔
294	debug('Database pool: client acquired', {	×
295	total: pool.totalCount,
296	idle: pool.idleCount,
297	waiting: pool.waitingCount,
298	source: 'pool_events',
299	});
300	});
301
302	pool.on('remove', () => {	27✔
303	debug('Database pool: client removed', {	×
304	total: pool.totalCount,
305	idle: pool.idleCount,
306	waiting: pool.waitingCount,
307	source: 'pool_events',
308	});
309	});
310
311	// Wrap query with slow query logging
312	const slowQueryThresholdMs = parsePositiveInt(process.env.PG_SLOW_QUERY_MS, 100);	27✔
313	wrapPoolQuery(pool, slowQueryThresholdMs);	27✔
314
315	try {	27✔
316	// Test connection
317	const client = await pool.connect();	27✔
318	try {	26✔
319	await client.query('SELECT NOW()');	26✔
320	info('Database connected');	26✔
321	} finally {
322	client.release();	26✔
323	}
324
325	// Run pending migrations
326	await runMigrations(connectionString);	26✔
327
328	info('Database schema initialized');	25✔
329	} catch (err) {
330	// Clean up the pool so getPool() doesn't return an unusable instance
331	await pool.end().catch(() => {});	2✔
332	pool = null;	2✔
333	throw err;	2✔
334	}
335
336	// Start connection leak detection
337	startLeakDetection(pool, poolSize);	25✔
338
339	return pool;	25✔
340	} finally {
341	initializing = false;	28✔
342	}
343	}
344
345	/**
346	* Get the database pool
347	* @returns {pg.Pool} The connection pool
348	* @throws {Error} If pool is not initialized
349	*/
350	export function getPool() {
351	if (!pool) {	58✔
352	throw new Error('Database not initialized. Call initDb() first.');	53✔
353	}
354	return pool;	5✔
355	}
356
357	/**
358	* Gracefully close the database pool
359	*/
360	export async function closeDb() {
361	stopLeakDetection();	34✔
362	if (pool) {	34✔
363	try {	25✔
364	await pool.end();	25✔
365	info('Database pool closed');	24✔
366	} catch (err) {
367	logError('Error closing database pool', { error: err.message });	1✔
368	} finally {
369	pool = null;	25✔
370	}
371	}
372	}

VolvoxLLC / volvox-bot / 24060649903

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous