node-postgres (pg)

import pg from "pg";
 
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
});
 
export async function findOrder(id: string) {
  const { rows } = await pool.query(
    "SELECT id, total_cents FROM orders WHERE id = $1",
    [id]
  );
  return rows[0] ?? null;
}

// src/db/pool.ts
import pg from "pg";
 
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: Number(process.env.PG_POOL_MAX ?? 10),
  ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: true } : undefined,
});
 
// src/db/orders.ts
import type { Pool, PoolClient } from "pg";
import { pool } from "./pool";
 
export type Order = { id: string; userId: string; totalCents: number };
 
export async function createOrderWithItems(
  userId: string,
  items: { sku: string; qty: number; priceCents: number }[]
): Promise<Order> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const orderRes = await client.query<{ id: string }>(
      "INSERT INTO orders (user_id, total_cents) VALUES ($1, $2) RETURNING id",
      [userId, items.reduce((s, i) => s + i.qty * i.priceCents, 0)]
    );
    const orderId = orderRes.rows[0].id;
    for (const item of items) {
      await client.query(
        "INSERT INTO order_items (order_id, sku, qty, price_cents) VALUES ($1, $2, $3, $4)",
        [orderId, item.sku, item.qty, item.priceCents]
      );
    }
    await client.query("COMMIT");
    return { id: orderId, userId, totalCents: items.reduce((s, i) => s + i.qty * i.priceCents, 0) };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}
 
// src/routes/orders.ts (Express 5)
import express from "express";
import { createOrderWithItems } from "../db/orders";
 
const router = express.Router();
 
router.post("/", async (req, res, next) => {
  try {
    const { userId, items } = req.body;
    const order = await createOrderWithItems(userId, items);
    res.status(201).json(order);
  } catch (err) {
    next(err);
  }
});
 
export default router;

// Safe
await pool.query("SELECT * FROM users WHERE email = $1", [email]);
 
// Unsafe - never do this
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

const res = await pool.query<{ id: string; email: string }>(
  "SELECT id, email FROM users WHERE id = $1",
  [id]
);

pool.on("error", (err) => {
  console.error({ msg: "idle client error", err: err.message });
});