`src/lib/db.ts`

84 lines
import { Pool, PoolClient } from "pg";

const pool = new Pool({
  host: process.env.DB_HOST || "127.0.0.1",
  port: parseInt(process.env.DB_PORT || "5432"),
  database: process.env.DB_NAME || "omega_watches_v2",
  user: process.env.DB_USER || "omega_admin",
  password: process.env.DB_PASSWORD || "OmegaWatch2024Pass",
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

pool.on("error", (err) => {
  console.error("[DB] Unexpected pool error:", err.message);
});

export async function query<T = any>(
  text: string,
  params?: any[]
): Promise<T[]> {
  const start = Date.now();
  const result = await pool.query(text, params);
  const duration = Date.now() - start;
  if (duration > 1000) {
    console.warn(`[DB] Slow query (${duration}ms):`, text.slice(0, 100));
  }
  return result.rows as T[];
}

export async function queryOne<T = any>(
  text: string,
  params?: any[]
): Promise<T | null> {
  const rows = await query<T>(text, params);
  return rows[0] || null;
}

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

export async function healthCheck(): Promise<{
  ok: boolean;
  latency: number;
  connections: { total: number; idle: number; waiting: number };
}> {
  const start = Date.now();
  try {
    await pool.query("SELECT 1");
    return {
      ok: true,
      latency: Date.now() - start,
      connections: {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
      },
    };
  } catch {
    return {
      ok: false,
      latency: Date.now() - start,
      connections: { total: 0, idle: 0, waiting: 0 },
    };
  }
}

export { pool };
export default pool;