Connecting to Databases
Pools, Not Connections
LinkedIn Hook
"Your API works fine in development. In production, it dies under 50 concurrent users. Why?"
Nine times out of ten, the answer is the same: you opened a brand new database connection for every single request.
Every TCP handshake to Postgres is a 3-way SYN/ACK, then a TLS negotiation, then authentication, then a session setup. That's 50-200ms of latency before your query even starts. Multiply by every request and your event loop drowns in half-open sockets while Postgres screams
too many clients already.The fix is older than Node itself: connection pooling. Open a small fixed set of connections once, hand them out to handlers, take them back when the handler is done. Reuse, don't recreate.
In Lesson 7.1, I break down how
pg.Pool,mysql2/promise, andmongoosemanage connections under the hood — pool sizing, error listeners, reconnection, graceful SIGTERM shutdown, and the health check endpoint every production service needs.Read the full lesson -> [link]
#NodeJS #Postgres #MongoDB #BackendDevelopment #InterviewPrep
What You'll Learn
- How connection strings encode driver, host, credentials, database, and TLS options
- What a connection pool is, why it exists, and what dies without one
- How to size a pool sensibly (and why "more is not better")
- Using
pg.Pool,mysql2/promise, andmongoosethe right way - Attaching error listeners so a dead socket does not crash the process
- Reconnection strategies and what each driver does automatically
- Graceful shutdown — draining the pool on
SIGTERM - Building a
/healthendpoint that actually pings the database
The Phone Booth vs Taxi Rank Analogy
Imagine your office of 200 people needs to make outbound phone calls. Two designs:
Design A — Phone booth per call. Every time someone needs to make a call, a contractor shows up, installs a brand new phone line from the street, runs cable through the wall, plugs it in, the employee makes a 30-second call, then the contractor rips the cable out and leaves. The next caller waits for the contractor again. The phone company starts refusing new lines because you have used your entire allocation.
Design B — Taxi rank of phones. You install ten phones in the lobby once. Anyone who needs to call walks up, grabs a free phone, makes the call, hangs up, and the phone is immediately available for the next person. If all ten are busy, you wait in line for one to free up. No installation. No teardown. Predictable cost.
Design A is opening a TCP connection per request. Design B is a connection pool. The "ten phones" are the pool size — small enough that the phone company (Postgres) is happy, large enough that the line rarely backs up.
This is not a micro-optimization. The TCP + TLS + auth handshake to Postgres typically takes 50-200ms. Your actual SELECT id FROM users WHERE id = $1 takes 0.5ms. Without a pool, 99% of your latency is setup overhead. With a pool, the handshake happens once at boot and never again.
+---------------------------------------------------------------+
| WITHOUT POOL — A connection per request |
+---------------------------------------------------------------+
| |
| Req 1 -> [TCP][TLS][AUTH][QUERY][CLOSE] ~180ms |
| Req 2 -> [TCP][TLS][AUTH][QUERY][CLOSE] ~180ms |
| Req 3 -> [TCP][TLS][AUTH][QUERY][CLOSE] ~180ms |
| Req 4 -> ERROR: too many clients already |
| |
| Postgres rejects connections. Sockets pile up in TIME_WAIT. |
| File descriptors leak. Process eventually crashes. |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| WITH POOL — Reuse a small fixed set |
+---------------------------------------------------------------+
| |
| App boot: |
| +-----+ [TCP+TLS+AUTH] x 10 (one time, ~2 sec total) |
| | App | -------------------------------> +----------+ |
| +-----+ | Postgres | |
| +----------+ |
| |
| Per request: |
| Req 1 -> acquire conn #3 -> [QUERY 0.5ms] -> release conn #3 |
| Req 2 -> acquire conn #7 -> [QUERY 0.4ms] -> release conn #7 |
| Req 3 -> acquire conn #1 -> [QUERY 0.6ms] -> release conn #1 |
| |
| Pool: [#1 free][#2 busy][#3 free][#4 free]...[#10 free] |
| Handed out and returned, never recreated. |
| |
+---------------------------------------------------------------+Connection Strings — One URL, Everything Inside
Every modern driver accepts a URL-style connection string. The scheme tells the driver which database, the rest tells it where and how to connect.
postgres://user:password@host:5432/dbname?sslmode=require&pool_max=20
^^^^^^^^ ^^^^ ^^^^^^^^ ^^^^ ^^^^ ^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
scheme user pass host port db query string options
mongodb://user:password@host1:27017,host2:27017/dbname?replicaSet=rs0&authSource=admin
^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
scheme multiple hosts (replica set) options
mysql://user:password@host:3306/dbname?ssl-mode=REQUIREDWhy URLs? They are environment-variable friendly (DATABASE_URL=...), platform-portable (Heroku, Railway, Fly all expose them), and they keep secrets out of code. Never hard-code credentials. Always read from process.env.DATABASE_URL.
Common gotcha: if your password contains @, :, /, or ?, you must URL-encode it. p@ss becomes p%40ss. Otherwise the parser will see @ss as the host.
Postgres with pg.Pool — The Reference Implementation
The pg package ships a Pool class that does exactly what the name says. You create one pool per process, share it across the entire app, and never call pg.Client directly in request handlers.
// db.js — single shared pool for the whole process
const { Pool } = require('pg');
// Create the pool once at module load time.
// Every require('./db') returns the SAME pool instance.
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 20, // Max connections held open at once
min: 2, // Keep at least 2 warm and ready
idleTimeoutMillis: 30_000, // Close idle conns after 30s
connectionTimeoutMillis: 5_000, // Fail fast if pool is exhausted
ssl: process.env.NODE_ENV === 'production'
? { rejectUnauthorized: true }
: false,
});
// CRITICAL: attach an error listener.
// Idle clients can die (network blip, server restart). Without this
// listener, the 'error' event becomes an unhandled exception and
// crashes the entire Node process.
pool.on('error', (err) => {
console.error('[pg] idle client error', err.message);
// Do NOT exit. The pool will discard the broken client and
// create a fresh one on next acquire.
});
// Helper that handlers actually call.
async function query(text, params) {
const start = Date.now();
const result = await pool.query(text, params);
const ms = Date.now() - start;
if (ms > 100) console.warn(`[pg] slow query ${ms}ms: ${text}`);
return result;
}
module.exports = { pool, query };Pool sizing — the formula nobody tells you. A common mistake is setting max: 100 because "more is faster". It is not. Postgres uses one OS process per connection, and 100 processes will starve your DB server of CPU and RAM long before they help you. The classic guideline from the HikariCP project:
connections = ((core_count * 2) + effective_spindle_count)For a 4-core Postgres server with SSDs, that is around 8-10 connections. Multiply by the number of app instances. If you run 4 Node processes, each pool should hold ~5 connections to stay under the DB's tolerable total. Always size down, not up.
MySQL with mysql2/promise — Same Idea, Different API
// db-mysql.js
const mysql = require('mysql2/promise');
// createPool returns a promise-based pool out of the box.
const pool = mysql.createPool({
uri: process.env.DATABASE_URL,
waitForConnections: true, // Queue requests when pool is full
connectionLimit: 15, // Max open connections
queueLimit: 0, // Unlimited queued requests (0 = no cap)
enableKeepAlive: true, // Send TCP keepalives so NAT/LB do not drop
keepAliveInitialDelay: 10_000,
});
// mysql2 does not emit a pool-level 'error' event the same way as pg.
// Instead, you attach listeners on each connection as it is acquired.
pool.on('connection', (conn) => {
conn.on('error', (err) => {
console.error('[mysql] connection error', err.code, err.message);
});
});
async function query(sql, params) {
// pool.execute uses prepared statements (safer than pool.query).
const [rows] = await pool.execute(sql, params);
return rows;
}
module.exports = { pool, query };The shape is identical: one shared pool, attach error listeners, expose a thin query helper. The driver-specific bits (waitForConnections, enableKeepAlive) reflect MySQL's quirks but the architecture is the same.
MongoDB with Mongoose — Pooled By Default
Mongoose (and the underlying mongodb driver) uses a connection pool automatically. You do not create one explicitly — mongoose.connect() opens the pool, and every model query borrows from it.
// db-mongo.js
const mongoose = require('mongoose');
async function connect() {
// mongoose.connect resolves when the initial pool is established.
await mongoose.connect(process.env.MONGO_URL, {
maxPoolSize: 20, // Hard cap on simultaneous connections
minPoolSize: 2, // Keep this many warm
serverSelectionTimeoutMS: 5_000, // Fail fast if no server reachable
socketTimeoutMS: 45_000, // Kill sockets idle longer than this
});
console.log('[mongo] connected');
}
// Mongoose exposes connection events on mongoose.connection.
// Wire them up BEFORE calling connect() so you do not miss early events.
mongoose.connection.on('error', (err) => {
console.error('[mongo] connection error', err.message);
});
mongoose.connection.on('disconnected', () => {
console.warn('[mongo] disconnected — driver will auto-reconnect');
});
mongoose.connection.on('reconnected', () => {
console.log('[mongo] reconnected');
});
module.exports = { connect, mongoose };Reconnection behavior. The MongoDB driver reconnects automatically forever — you do not write retry logic. The pg driver does not reconnect a dead client; instead, it discards the bad client and creates a new one on the next acquire. mysql2 falls between the two: dead clients are discarded, but you get explicit PROTOCOL_CONNECTION_LOST errors that you must handle.
Graceful Shutdown — Drain the Pool on SIGTERM
When Kubernetes, systemd, or PM2 restarts your service, it sends SIGTERM. You have a small grace window (usually 30 seconds) to finish in-flight requests, close the pool, and exit cleanly. If you ignore SIGTERM, the process gets SIGKILL'd, which leaves dangling DB connections that take Postgres minutes to time out.
// server.js
const http = require('http');
const { pool } = require('./db');
const app = require('./app');
const server = http.createServer(app);
server.listen(3000, () => console.log('listening on 3000'));
// Graceful shutdown handler.
async function shutdown(signal) {
console.log(`[shutdown] received ${signal}, draining...`);
// 1. Stop accepting new HTTP connections.
// Existing requests are allowed to finish.
server.close((err) => {
if (err) {
console.error('[shutdown] http close error', err);
process.exit(1);
}
});
// 2. Wait briefly so in-flight requests can complete.
await new Promise((resolve) => setTimeout(resolve, 2_000));
// 3. Drain the DB pool. pool.end() waits for all checked-out
// clients to be released, then closes every TCP socket.
try {
await pool.end();
console.log('[shutdown] pg pool drained');
} catch (err) {
console.error('[shutdown] pool drain error', err);
}
// 4. Exit clean.
process.exit(0);
}
process.on('SIGTERM', () => shutdown('SIGTERM'));
process.on('SIGINT', () => shutdown('SIGINT'));For Mongoose, replace pool.end() with await mongoose.connection.close(false) (the false argument means "do not force-kill in-flight ops").
The Health Check Endpoint — Actually Ping the DB
A health check that returns 200 OK without touching the database is worse than no health check at all. The orchestrator thinks your service is fine, keeps routing traffic to it, while every request times out because the DB is unreachable. Real health checks ping the database.
// health.js
const express = require('express');
const { pool } = require('./db');
const router = express.Router();
// Liveness — is the process running at all?
// Kubernetes calls this every few seconds. Keep it cheap.
router.get('/live', (req, res) => {
res.status(200).json({ status: 'alive' });
});
// Readiness — can we actually serve traffic?
// Pings the DB with a tiny query. If it fails, return 503 so the
// load balancer pulls this pod out of rotation until it recovers.
router.get('/ready', async (req, res) => {
try {
// SELECT 1 is the cheapest possible query. It uses a real
// pooled connection and verifies the DB is reachable.
const start = Date.now();
await pool.query('SELECT 1');
const ms = Date.now() - start;
res.status(200).json({
status: 'ready',
db: 'ok',
latencyMs: ms,
pool: {
total: pool.totalCount, // total clients in pool
idle: pool.idleCount, // idle clients available
waiting: pool.waitingCount, // requests queued for a client
},
});
} catch (err) {
// 503 tells k8s/LBs to stop sending traffic.
res.status(503).json({
status: 'not_ready',
db: 'error',
message: err.message,
});
}
});
module.exports = router;The pool stats (totalCount, idleCount, waitingCount) are the single best operational metric you can expose. If waitingCount > 0 consistently, your pool is too small or your queries are too slow. Scrape these into Prometheus and alert on them.
Common Mistakes
1. Creating a new client (or pool) inside every request handler.
This is the #1 production-killing bug. new Pool() or new Client() inside a route function means a fresh TCP+TLS handshake on every request, plus you never call .end() on the discarded clients, so file descriptors leak until the process dies. Create the pool once at module load and import the same instance everywhere.
2. Forgetting pool.on('error', ...).
Pooled clients sit idle between requests. The DB server can drop them (restart, network blip, idle timeout). When that happens the client emits error. With no listener, Node treats it as an unhandled exception and crashes the process. Always attach a pool-level error listener — even an empty one is safer than none.
3. Not closing the pool on shutdown.
On SIGTERM, if you process.exit(0) without calling pool.end(), the open TCP sockets are abandoned. Postgres holds them in idle in transaction state until its tcp_keepalives time out (often minutes). Across rolling deploys this exhausts max_connections and the next deploy fails to connect.
4. Setting max too high.
Devs think "more connections = more throughput". Wrong. Each Postgres connection is a full OS process. Twenty connections per app instance times ten instances equals 200 backend processes — most DBs cannot handle that. Size pools small (5-20) and scale by adding app instances, not by inflating pool size.
5. Using pool.query() for transactions.
Each call to pool.query() may grab a different client from the pool. A BEGIN on client #3 followed by an UPDATE on client #7 is two unrelated transactions. For multi-statement transactions, always const client = await pool.connect(), run statements on that client, and client.release() in a finally block.
Interview Questions
1. "Why is opening a new database connection per HTTP request a bad idea?"
Each new connection requires a TCP handshake (1 round trip), TLS negotiation (1-2 round trips), authentication (1 round trip), and session setup. Total cost is typically 50-200ms before the first query runs, while the query itself is often under 1ms. Per-request connections also exhaust the database's max_connections limit very quickly — Postgres typically allows ~100 by default, and each connection is a full OS process. Sockets accumulate in TIME_WAIT, file descriptors leak, and the application crashes under modest load. A connection pool amortizes the handshake cost across the lifetime of the process by opening a small fixed set of connections once and reusing them.
2. "How do you size a database connection pool?"
The HikariCP guideline is connections = (core_count * 2) + effective_spindle_count, which for a typical 4-core SSD-backed Postgres server is 8-12 connections total across the entire cluster. Divide that by the number of app instances. For four Node processes, each pool should hold around 3-5 connections. Counter-intuitively, smaller pools usually outperform larger ones because the database does not waste CPU thrashing between processes. The signal that a pool is too small is waitingCount > 0 in pool metrics — requests queueing for a free client. The fix is more app instances, not more connections per instance.
3. "What does graceful shutdown look like for a Node service that uses a database pool?"
Listen for SIGTERM and SIGINT. On signal: (1) call server.close() to stop accepting new HTTP connections while letting in-flight requests finish; (2) wait briefly so handlers can complete; (3) call pool.end() (or mongoose.connection.close()) to drain checked-out clients and close the underlying TCP sockets; (4) process.exit(0). Skipping pool.end() leaves abandoned sockets that the database holds open until its keepalive timer expires, which can exhaust max_connections during rolling deploys. Set a hard timeout (e.g. 10 seconds) so a stuck handler does not block shutdown forever.
4. "What is the difference between a liveness check and a readiness check, and what should each one do?"
Liveness answers "is this process alive?" — it should return 200 as long as the event loop is responsive. It must not touch the database, because a transient DB outage would make Kubernetes restart healthy pods unnecessarily. Readiness answers "can this instance serve traffic right now?" — it pings the database with a cheap query like SELECT 1 and returns 503 if the ping fails. The orchestrator pulls instances with failing readiness out of the load balancer rotation but does not restart them. Liveness restarts; readiness reroutes. Conflating the two causes either crash loops (DB blip kills all pods) or black-hole routing (dead pods keep receiving traffic).
5. "How do pg, mysql2, and mongoose differ in how they handle dropped connections?"
pg.Pool does not reconnect a dead client. When an idle client errors, the pool discards it and creates a fresh one on the next acquire — but only if you have attached a pool.on('error', ...) listener; otherwise the unhandled error event crashes Node. mysql2 similarly discards broken clients and surfaces PROTOCOL_CONNECTION_LOST errors that the application can log; the next pool acquire opens a new connection. mongoose (via the underlying MongoDB driver) reconnects automatically and indefinitely with exponential backoff — you write zero retry logic and the driver buffers operations during the outage. The takeaway: Postgres and MySQL drivers expect you to wire error listeners; MongoDB hides reconnection entirely.
Quick Reference — Database Connection Cheat Sheet
+---------------------------------------------------------------+
| CONNECTION STRINGS |
+---------------------------------------------------------------+
| |
| postgres://user:pass@host:5432/db?sslmode=require |
| mysql://user:pass@host:3306/db?ssl-mode=REQUIRED |
| mongodb://user:pass@host:27017/db?replicaSet=rs0 |
| |
| Always read from process.env.DATABASE_URL |
| URL-encode special chars in passwords (@ -> %40) |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| POOL SETUP |
+---------------------------------------------------------------+
| |
| PG: new Pool({ connectionString, max: 20 }) |
| MYSQL2: mysql.createPool({ uri, connectionLimit: 15 }) |
| MONGOOSE: mongoose.connect(url, { maxPoolSize: 20 }) |
| |
| ALWAYS: pool.on('error', handler) |
| one shared pool per process |
| size = (cores * 2) + spindles |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| GRACEFUL SHUTDOWN |
+---------------------------------------------------------------+
| |
| process.on('SIGTERM', async () => { |
| server.close(); |
| await pool.end(); // pg / mysql2 |
| await mongoose.connection.close(); // mongoose |
| process.exit(0); |
| }); |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| HEALTH CHECK |
+---------------------------------------------------------------+
| |
| GET /live -> 200 if process is up (no DB ping) |
| GET /ready -> SELECT 1; 200 ok / 503 not_ready |
| |
| Expose pool.totalCount / idleCount / waitingCount |
| Alert when waitingCount > 0 sustained |
| |
+---------------------------------------------------------------+| Concern | pg.Pool | mysql2/promise | mongoose |
|---|---|---|---|
| Pool built in | Yes (Pool) | Yes (createPool) | Yes (automatic) |
| Reconnects dead clients | No (discards) | No (discards) | Yes (forever) |
| Pool error event | pool.on('error') | per-conn listener | connection.on('error') |
| Drain on shutdown | await pool.end() | await pool.end() | mongoose.connection.close() |
| Pool stats | totalCount/idleCount/waitingCount | _allConnections/_freeConnections | mongoose.connection.client |
| Default max | 10 | 10 | 100 |
Prev: Lesson 6.5 -- Request Validation & Security Next: Lesson 7.2 -- ORMs & Query Builders
This is Lesson 7.1 of the Node.js Interview Prep Course -- 10 chapters, 42 lessons.