`src/db.ts`

103 lines
// directory-core / db
//
// Shared PostgreSQL primitives for directory verticals. Each consuming project
// owns its OWN database (lawyer_professional_directory / doctor_professional_directory /
// animals_directory / restaurant_professional_directory) — Steve's hard rule that
// verticals never share PG. This module just gives them a consistent pool +
// query helper + transaction wrapper.
//
// Source: extracted from `lawyer-directory-builder/src/db/pool.ts` 2026-05-04
// (the cleanest implementation across the 4 verticals at extraction time).
// 2026-05-04 update: accepts EITHER DATABASE_URL (lawyer-directory style) OR
// individual PGHOST/PGPORT/PGDATABASE/PGUSER/PGPASSWORD vars (restaurant-directory
// style). Either is fine — pg.Pool resolves both natively. Throws only if
// neither is set.
//
// Audit 2026-05-04: side-effect-at-import — the throw lives in `getPool()` not
// at module load, so this lib is import-safe in test environments where no DB
// is configured. Pool is instantiated lazily on first use.

import 'dotenv/config';
import pg from 'pg';

const { Pool } = pg;

let _pool: pg.Pool | null = null;

function envHasPgConfig(): boolean {
  return Boolean(
    process.env.DATABASE_URL ||
    process.env.PGHOST ||
    process.env.PGDATABASE ||
    process.env.PGUSER
  );
}

function buildPool(): pg.Pool {
  if (!envHasPgConfig()) {
    throw new Error(
      'directory-core/db: no PG config in env — set DATABASE_URL, OR PGHOST/PGDATABASE/PGUSER (and optionally PGPORT/PGPASSWORD).'
    );
  }
  const max = parseInt(process.env.PG_POOL_MAX || '10', 10);
  // pg.Pool reads connectionString OR the discrete PG* env vars. We pass
  // connectionString explicitly only if DATABASE_URL is set; otherwise pg
  // picks up PGHOST/PGPORT/PGDATABASE/PGUSER/PGPASSWORD on its own.
  if (process.env.DATABASE_URL) {
    return new Pool({ connectionString: process.env.DATABASE_URL, max });
  }
  return new Pool({ max });
}

// Lazy single-step initializer. Note (audit P1-a 2026-05-04): the assignment is
// a single synchronous JS statement — no await between the null-check and the
// store — so two concurrent first-touches both observe `null`, then both run
// `buildPool()`, but whichever wins the JS turn sets `_pool` first; the second
// runner overwrites with its own pool and the first one is orphaned (sockets
// leak). The synchronous fast path avoids that by collapsing check+assign to
// one statement and letting v8's reorder guarantee single-publication.
function ensurePool(): pg.Pool {
  return _pool ?? (_pool = buildPool());
}

/**
 * Lazily-initialized PG pool. Reads env on first access; subsequent calls
 * return the same pool. Use this when you need direct pool access (e.g.
 * pool.connect() for advanced patterns); for normal queries prefer query()
 * or withTx() below.
 */
export const pool: pg.Pool = new Proxy({} as pg.Pool, {
  get(_target, prop, _receiver) {
    const p = ensurePool();
    const v = (p as unknown as Record<string | symbol, unknown>)[prop as string];
    return typeof v === 'function' ? (v as Function).bind(p) : v;
  },
});

export async function query<T extends pg.QueryResultRow = pg.QueryResultRow>(
  text: string,
  params: unknown[] = []
): Promise<pg.QueryResult<T>> {
  return pool.query<T>(text, params as never[]);
}

export async function withTx<T>(fn: (client: pg.PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const out = await fn(client);
    await client.query('COMMIT');
    return out;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

// Optional: graceful shutdown helper. Call from your server's SIGTERM handler.
export async function closePool() {
  await pool.end();
}