Node.js Fundamentals: The Runtime, the Event Loop, and Building Real APIs

Part 12 of 14 · ~2 min

Connecting to a Database

Why a pool, not a fresh connection per webhook

A database connection isn't instantaneous to open — there's a TCP handshake, typically a TLS handshake layered on that, and a round trip to authenticate, and none of it involves running an actual query yet. Repeat that setup for every single webhook billhook receives, and every query now carries a tax of several dozen extra milliseconds it didn't need to pay, stacked directly on top of whatever the query itself costs. Worse, right after a Meridian outage resolves and a backlog of retried deliveries arrives all at once, opening one connection per request is exactly the pattern that exhausts Postgres's own connection ceiling under that burst.

The fix is a connection pool: open a small, fixed set of connections exactly once, check one out whenever a request needs it, and hand it back to the pool when the query's done instead of tearing it down:

const { Pool } = require("pg");

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // maximum simultaneous connections the pool will open
});

async function saveOrder(payload) {
  // pool.query() checks out a connection, runs the query, and returns it
  // to the pool automatically once it's done
  const result = await pool.query(
    "INSERT INTO orders (order_id, meridian_id, amount_cents) VALUES ($1, $2, $3) RETURNING *",
    [payload.orderId, payload.meridianId, payload.amountCents]
  );
  return result.rows[0];
}

Picking max isn't a matter of higher-is-safer. Set it too low, and requests pile up waiting on a free connection even though billhook itself has plenty of headroom to handle more traffic right now. Set it too high, and the risk moves to Postgres's side instead — its own connection ceiling is frequently much lower than people assume, and it's a shared number: every other service pointed at that same database is drawing down the identical limit, billhook included but hardly alone.

Where an ORM would fit, and why billhook doesn't use one

An ORM — Prisma and Sequelize are the familiar names in the Node world — doesn't replace any of the pooling machinery above; it's built on top of it, offering application objects and declarative relationships in place of hand-written SQL, with migrations and relationship-loading folded into that same higher-level style. Ines and Devon looked at it and passed: billhook's actual query surface is small — insert an order, fetch one by ID, a few filtered lists — and what an ORM buys in convenience, it spends in reduced visibility into the exact SQL hitting a table that real money moves through. Neither call is objectively right; it comes down to how much a given project values that visibility against the speed of not hand-writing every query.