Connecting to Postgres
Create one pool of reusable Postgres connections at startup and lend a connection to each request.
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 })A database is a separate program, usually on another machine. Your Node process, the running copy of your program, reaches it over a network connection. Opening that connection costs a round trip, a password check, and on Postgres a new server-side process of its own. Opening one per incoming request would spend more time connecting than querying.
A pool fixes that. A pool holds a few already-open connections and lends them out. A request borrows one, runs its queries, hands it back. The connection stays open for the next request.
How it works
Install the driver with npm install pg. Create exactly one Pool for the process, at startup, outside any request handler. Pass the address as a connection string read from an environment variable, which is a named value handed to your process when it starts, so the same code runs against your laptop's database and the production one. Node can load those values from a file itself with node --env-file=.env server.js, added in Node 20.6.0; a variable already set in the environment wins over the file.
pool.query(text, values) is shorthand. It borrows a connection, runs one statement, and returns the connection for you. The longhand is const client = await pool.connect(), then client.query(...), then client.release(). Same work. Reach for the longhand only when several statements must share one connection.
What trips people up
The defaults decide your behaviour under load. max is 10 connections, idleTimeoutMillis is 10000, and connectionTimeoutMillis is 0, which means a request waiting for a free connection waits forever. Set a timeout.
Example
When to use it
- Give an HTTP API a single shared Postgres pool instead of connecting inside each route handler.
- Point the same build at a local database and a production one by changing only DATABASE_URL.
- Add a startup query so a wrong password or unreachable host fails at boot, not on the first request.
- Stop a reporting script from hanging at the end by calling pool.end().
More examples
One pool for the whole process, in its own module
Exporting the pool from one module is what keeps it to one pool per process: every file that imports db.js gets the same object. Reach for this shape as soon as a second file needs the database.
// db.js
import { Pool } from 'pg';
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 10, // per process; the default is also 10
idleTimeoutMillis: 10_000, // close a connection unused for 10s (default)
connectionTimeoutMillis: 5_000, // give up waiting for one after 5s
});
// Without this listener, an error on an idle connection is unhandled.
pool.on('error', (err) => {
console.error('idle client error', err.message);
});
// server.js
import { createServer } from 'node:http';
import { pool } from './db.js';
createServer(async (req, res) => {
const { rows } = await pool.query('SELECT now() AS at');
res.writeHead(200, { 'content-type': 'application/json' });
res.end(JSON.stringify(rows[0]));
}).listen(3000);The longhand: borrowing and releasing a client by hand
The finally block is the whole point: a client that is never released stays checked out and the pool shrinks by one. Running a cheap query at startup turns a bad DATABASE_URL into an immediate crash instead of a 500 for the first visitor.
import { pool } from './db.js';
// pool.query() does all of this for you in one call. Do it yourself only
// when several statements must run on the same connection.
async function countUsers() {
const client = await pool.connect();
try {
const result = await client.query('SELECT count(*) AS n FROM users');
return Number(result.rows[0].n); // count() arrives as a string
} finally {
client.release(); // always, on both the success and the error path
}
}
// At startup, fail loudly rather than on the first user's request.
const n = await countUsers();
console.log(`database reachable, ${n} users`);Closing the pool when a script is done
A one-off script that forgets pool.end() appears to hang after printing its output, because the open connections keep Node's event loop busy. A long-running server does the opposite and keeps the pool for its whole life.
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const { rows } = await pool.query(
'SELECT id, email FROM users ORDER BY id LIMIT $1',
[5],
);
for (const row of rows) console.log(row.id, row.email);
// Open connections keep the process alive. end() closes them and the
// promise resolves once they are all shut.
await pool.end();
Discussion