Drivers and connection pools

📘What it really is

• A driver lets your code talk to the database. For PostgreSQL in Node.js, the standard is pg (node-postgres).
• Opening a connection is slow (network, encryption, sign-in). A connection pool keeps a few connections open and lends them to requests:

import pg from "pg";

export const pool = new pg.Pool({
  connectionString: config.databaseUrl,
  max: 10,                       // at most 10 connections
});

const { rows } = await pool.query(
  "SELECT id, name FROM customers WHERE city = $1 ORDER BY name LIMIT $2",
  [city, 25],
);

• PostgreSQL placeholders are $1, $2... Parameterised queries work exactly as in Module 59, Lesson 3.

• Pool size matters: PostgreSQL allows a limited number of connections (often 100, fewer on free tiers). Several app servers each with a large pool can run out. Serverless hosting (many short-lived app copies) needs a shared pooler like PgBouncer, or the pooled connection string that Supabase and Neon provide.

• Transactions need one connection for all their statements:

const client = await pool.connect();
try {
  await client.query("BEGIN");
  // ... several queries with client.query(...)
  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();              // always return it to the pool
}

• Forgetting release() leaks connections until the pool runs dry and the app freezes.