`lib/db.ts`

75 lines
import { Pool, PoolClient, QueryResult, QueryResultRow } from 'pg';

// 2026-05-05 (P0 leak scrub): hardcoded DB-password fallback removed.
// Also: the fallback pointed at dw_unified (DW commerce DB) which is the
// wrong database — PoppyPetitions uses its own schema. Fail-fast on missing
// DATABASE_URL is safer than silently writing to the wrong place.
if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL env var is required (no fallback)');
}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

// Log pool errors so they don't crash the process
pool.on('error', (err: Error) => {
  console.error('[db] Unexpected pool error:', err.message);
});

/**
 * Execute a parameterized query against the pool.
 * Automatically sets the search_path to the poppy schema.
 * Returns the full QueryResult so callers can read .rows, .rowCount, etc.
 */
export async function query<T extends QueryResultRow = QueryResultRow>(
  text: string,
  params?: unknown[],
): Promise<QueryResult<T>> {
  const start = Date.now();
  const client = await pool.connect();
  try {
    // Set schema search path for this session
    await client.query('SET search_path TO poppy, public');
    const result = await client.query<T>(text, params);
    const duration = Date.now() - start;
    if (duration > 2000) {
      console.warn(`[db] Slow query (${duration}ms):`, text.slice(0, 120));
    }
    return result;
  } catch (err) {
    console.error('[db] Query error:', (err as Error).message, '\n  SQL:', text.slice(0, 200));
    throw err;
  } finally {
    client.release();
  }
}

/**
 * Acquire a dedicated client from the pool — use for transactions.
 * IMPORTANT: Always call client.release() in a finally block.
 * Remember to SET search_path TO poppy, public before your queries.
 *
 * Usage:
 *   const client = await getClient();
 *   try {
 *     await client.query('SET search_path TO poppy, public');
 *     await client.query('BEGIN');
 *     // ... transactional work ...
 *     await client.query('COMMIT');
 *   } catch (err) {
 *     await client.query('ROLLBACK');
 *     throw err;
 *   } finally {
 *     client.release();
 *   }
 */
export async function getClient(): Promise<PoolClient> {
  return pool.connect();
}

export default pool;