PostgreSQL (pg, drizzle)

// npm i pg
import { Pool, type PoolClient } from 'pg';

// 1) Pool — created once at module scope
export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                         // max connections (per process)
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
  ssl: { rejectUnauthorized: true },   // production: verify the cert
});

pool.on('error', (err) => {
  console.error('pg pool error', err);
});

// 2) Parameterised queries — $1, $2, ... placeholders
export async function findCustomer(email: string) {
  const res = await pool.query<{ id: number; name: string }>(
    'SELECT id, name FROM customers WHERE email = $1',
    [email],
  );
  return res.rows[0] ?? null;
}

export async function listOrders(customerId: number, limit = 20) {
  const res = await pool.query(
    'SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2',
    [customerId, limit],
  );
  return res.rows;
}

// 3) Insert + RETURNING
export async function createOrder(customerId: number, totalCents: number) {
  const res = await pool.query<{ id: number }>(
    \`INSERT INTO orders (customer_id, total_cents, status)
     VALUES ($1, $2, 'new')
     RETURNING id\`,
    [customerId, totalCents],
  );
  return res.rows[0].id;
}

// 4) Transactions — wrap in a helper
export async function withTransaction<T>(fn: (c: 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 placeOrder(input: { email: string; name: string; totalCents: number }) {
  return withTransaction(async (c) => {
    const u = await c.query<{ id: number }>(
      \`INSERT INTO customers (email, name) VALUES ($1, $2)
       ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
       RETURNING id\`,
      [input.email, input.name],
    );
    const o = await c.query<{ id: number }>(
      'INSERT INTO orders (customer_id, total_cents, status) VALUES ($1, $2, $3) RETURNING id',
      [u.rows[0].id, input.totalCents, 'new'],
    );
    return o.rows[0].id;
  });
}

// 5) Streaming large result sets
import QueryStream from 'pg-query-stream';
export async function streamOrders(onRow: (row: any) => void) {
  const client = await pool.connect();
  try {
    const stream = client.query(new QueryStream('SELECT * FROM orders ORDER BY id', [], { batchSize: 1000 }));
    await new Promise<void>((resolve, reject) => {
      stream.on('data', onRow);
      stream.on('end', resolve);
      stream.on('error', reject);
    });
  } finally {
    client.release();
  }
}

// 6) Type-safe queries with Drizzle / Kysely (recommended for non-trivial apps)
// npm i kysely pg
// import { Kysely, PostgresDialect } from 'kysely';
// interface Database { customers: { id: number; email: string; name: string }; orders: { ... } }
// const db = new Kysely<Database>({ dialect: new PostgresDialect({ pool }) });
// const rows = await db.selectFrom('orders').select(['id','customer_id']).where('status', '=', 'new').execute();

// 7) Migrations — use a tool (node-pg-migrate, knex, drizzle-kit), not ad-hoc CREATE TABLE
// node-pg-migrate up
// drizzle-kit push

// 8) LISTEN / NOTIFY for cache invalidation
const subClient = await pool.connect();
await subClient.query('LISTEN orders_changed');
subClient.on('notification', (msg) => {
  console.log('cache invalidate', msg.payload);
});

// 9) Health check
export async function checkDb(): Promise<boolean> {
  try { await pool.query('SELECT 1'); return true; }
  catch { return false; }
}

// 10) Pitfalls
// - new Pool() inside a request handler -> connection storm
// - Forgetting client.release() -> pool starvation
// - String concat in SQL (NEVER)
// - SELECT * on huge tables in transactions
// - Returning a connection from a function -> leak (always release in finally)

import pg from 'pg';
const client = new pg.Client();
await client.connect();
const r = await client.query('SELECT NOW()');