PostgreSQL with node-postgres

When you want raw SQL and full control over your queries, node-postgres (the pg package) is the standard, dependency-light way to talk to PostgreSQL from Express. It is not an ORM — you write SQL directly — which keeps the mental model simple and the queries fast. The two things you must get right are using a connection pool instead of one-off clients, and using parameterized queries so user input can never inject SQL. Get those right and pg is a rock-solid foundation for any Express data layer.

Installing and creating a pool

Install the library alongside Express. There are no native compilation steps for the pure-JS driver.

npm install express pg

A Pool manages a set of reusable PostgreSQL connections. Opening a TCP connection and authenticating is expensive, so you create one pool at startup and share it across every request. The pool hands a connection to each query and returns it automatically, so you never connect per request.

// db.js
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                     // max clients in the pool
  idleTimeoutMillis: 30000,    // close idle clients after 30s
  connectionTimeoutMillis: 5000, // fail fast if no connection is free
});

// Surface unexpected errors on idle clients instead of crashing silently
pool.on('error', (err) => {
  console.error('Unexpected pool error', err);
});

module.exports = pool;

The connectionString looks like postgres://user:pass@localhost:5432/mydb. Wire the pool into your app and start listening once it is reachable.

// server.js
const express = require('express');
const pool = require('./db');

const app = express();
app.use(express.json()); // parse JSON request bodies

app.use('/users', require('./routes/users'));

pool
  .query('SELECT 1') // verify connectivity at boot
  .then(() => app.listen(3000, () => console.log('Listening on :3000')))
  .catch((err) => {
    console.error('DB connection failed', err);
    process.exit(1);
  });

Tip: Store credentials in an environment variable, never in source. For most apps a single shared pool is all you need — do not create a pool per request or per route.

Parameterized queries

Never build SQL by concatenating user input. Instead, pass values separately using numbered placeholders ($1, $2, …). The driver sends the query and the values independently, so input is treated strictly as data and SQL injection becomes impossible.

// Safe: values are bound, not interpolated
const { rows } = await pool.query(
  'SELECT id, name, email FROM users WHERE email = $1',
  ['[email protected]']
);

// NEVER do this — string interpolation is an injection hole
// const sql = `SELECT * FROM users WHERE email = '${req.query.email}'`;

Every call to pool.query returns a result object. The most useful fields are rows (an array of row objects) and rowCount (number of rows affected or returned).

Property	Description
`rows`	Array of result rows as plain objects
`rowCount`	Rows returned (SELECT) or affected (INSERT/UPDATE/DELETE)
`fields`	Column metadata (name, data type)
`command`	The SQL command that ran (`SELECT`, `INSERT`, …)

Use RETURNING on writes so you get the affected row back in one round trip instead of a second query.

Integrating queries into route handlers

Inside an async route handler you await pool.query directly. Below is a complete REST resource on an Express Router.

// routes/users.js
const express = require('express');
const pool = require('../db');

const router = express.Router();

// CREATE
router.post('/', async (req, res, next) => {
  try {
    const { name, email } = req.body;
    const { rows } = await pool.query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [name, email]
    );
    res.status(201).json(rows[0]);
  } catch (err) {
    next(err);
  }
});

// READ (list + single)
router.get('/', async (req, res, next) => {
  try {
    const { rows } = await pool.query(
      'SELECT * FROM users ORDER BY created_at DESC LIMIT 20'
    );
    res.json(rows);
  } catch (err) {
    next(err);
  }
});

router.get('/:id', async (req, res, next) => {
  try {
    const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [
      req.params.id,
    ]);
    if (rows.length === 0) return res.status(404).json({ error: 'Not found' });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
});

// DELETE
router.delete('/:id', async (req, res, next) => {
  try {
    const { rowCount } = await pool.query('DELETE FROM users WHERE id = $1', [
      req.params.id,
    ]);
    if (rowCount === 0) return res.status(404).json({ error: 'Not found' });
    res.status(204).end();
  } catch (err) {
    next(err);
  }
});

module.exports = router;

A successful create responds like this:

Output:

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8

{
  "id": 1,
  "name": "Ada Lovelace",
  "email": "[email protected]",
  "created_at": "2026-06-14T10:00:00.000Z"
}

Transactions

When several statements must succeed or fail together — say, debiting one account and crediting another — wrap them in a transaction. You must check out a single client from the pool so every statement runs on the same connection, then BEGIN, run your queries, and COMMIT. On any error, ROLLBACK. Always release the client in a finally block so it returns to the pool even when something throws.

async function transfer(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK'); // undo every statement in the transaction
    throw err;
  } finally {
    client.release(); // ALWAYS return the client to the pool
  }
}

Warning: Forgetting client.release() leaks a connection. After max leaks the pool is exhausted and every new request hangs on pool.connect(). The finally block is not optional.

Best Practices

Create exactly one shared Pool at startup and reuse it for every request; never call new Pool() inside a handler.
Always use $1, $2 placeholders for any user-supplied value — never interpolate input into SQL strings.
Use RETURNING * on INSERT/UPDATE/DELETE to get affected rows back in a single round trip.
For transactions, check out one client with pool.connect(), and release it in a finally block; pool.query alone cannot span a transaction.
On Express 4 wrap async handlers in try/catch and call next(err); on Express 5 rejected promises forward to the error handler automatically.
Set sensible pool limits (max, connectionTimeoutMillis) and listen for the pool’s error event so dropped idle connections do not crash the process.
Index columns you filter or sort on frequently to keep reads fast as tables grow.