waseel/lib/db.ts

import { lookup, setDefaultResultOrder } from "dns";
import { Pool, type QueryResultRow } from "pg";

// Neon's host resolves to both IPv6 (AAAA) and IPv4 (A). This host has no IPv6
// route, so an IPv6-first connect fails instantly with ENETUNREACH and only
// then falls back to IPv4 — wasting a round-trip on every fresh connection and
// racing Neon's wake. Force IPv4 first so the working path is tried first.
setDefaultResultOrder("ipv4first");

// Neon free-tier scales compute to zero when idle; the first connection after a
// cold wake can take 10–30s to establish. A 10s connect timeout 500s every
// request during that wake window, so allow 30s. Once a ride is active the
// driver-location + call polls keep the DB warm, so this only bites the first
// poll after a long idle.
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 30_000,
});

// A pooled client can die out from under us (Neon recycle, network blip).
// Without this handler Node logs an unhandled "idle client error" and the
// pool just drops the client; log it so a flaky connection is visible.
pool.on("error", (err) => {
  console.error("[DB_POOL_ERROR]: ", err.message);
});

// Connection errors that are safe to retry on a fresh pool client. Neon's
// direct endpoint intermittently ETIMEDOUTs while the compute wakes; a single
// retry a second later almost always succeeds once the endpoint is warm.
const RETRY_CODES = new Set([
  "ETIMEDOUT",
  "ECONNRESET",
  "ENETUNREACH",
  "EHOSTUNREACH",
  "EPIPE",
  "08000",
  "08006",
  "08001",
  "08004",
  "57P03",
]);
const isRetryable = (err: unknown): boolean => {
  const e = err as { code?: string };
  return Boolean(e && typeof e.code === "string" && RETRY_CODES.has(e.code));
};

// Retry a pool.query a couple of times on transient connection errors. The
// query itself is idempotent from the pool's perspective: a connect failure
// means no statement ran, and pg removes the dead client before the next
// attempt, so we never double-execute a committed statement.
const queryWithRetry = async <R extends QueryResultRow = QueryResultRow>(
  text: string,
  values: SqlValue[],
): Promise<R[]> => {
  let lastErr: unknown;
  for (let attempt = 0; attempt < 3; attempt++) {
    try {
      const result = await pool.query<R>(text, values);
      return result.rows;
    } catch (err) {
      lastErr = err;
      if (!isRetryable(err) || attempt === 2) throw err;
      // Back off ~1s, ~2s; Neon wake completes within a few seconds.
      await new Promise((r) => setTimeout(r, 1000 * (attempt + 1)));
    }
  }
  throw lastErr;
};

export type SqlValue = string | number | boolean | null | Date;

export async function sql<R extends QueryResultRow = QueryResultRow>(
  strings: TemplateStringsArray,
  ...values: SqlValue[]
): Promise<R[]> {
  const text = strings.reduce(
    (acc, chunk, i) =>
      acc + chunk + (i < values.length ? `$${i + 1}` : ""),
    "",
  );

  return queryWithRetry<R>(text, values);
}

export async function query<R extends QueryResultRow = QueryResultRow>(
  text: string,
  values: SqlValue[] = [],
): Promise<R[]> {
  return queryWithRetry<R>(text, values);
}

export async function transaction<T>(
  callback: (
    tx: <R extends QueryResultRow = QueryResultRow>(
      strings: TemplateStringsArray,
      ...values: SqlValue[]
    ) => Promise<R[]>,
  ) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const tx = async <R extends QueryResultRow = QueryResultRow>(
      strings: TemplateStringsArray,
      ...values: SqlValue[]
    ) => {
      const text = strings.reduce(
        (acc, chunk, i) =>
          acc + chunk + (i < values.length ? `$${i + 1}` : ""),
        "",
      );

      const result = await client.query<R>(text, values);

      return result.rows;
    };

    const out = await callback(tx);

    await client.query("COMMIT");

    return out;
  } catch (error) {
    await client.query("ROLLBACK");

    throw error;
  } finally {
    client.release();
  }
}