Using PostgreSQL with node-postgres (pg)

PostgreSQL is the default relational database for many Node.js applications, and pg (node-postgres) is the de facto driver for talking to it. It is a pure-JavaScript client with no compilation step, supports connection pooling out of the box, and maps PostgreSQL types to native JavaScript values. This page walks through connecting, running parameterized queries safely, pooling connections for production, and wrapping multiple statements in a transaction.

Installing and connecting

Install the driver from npm. The pg package contains everything you need; the optional pg-native add-on swaps in the libpq C bindings for a small speed boost but is rarely necessary.

npm install pg

A single Client represents one dedicated connection to the database. You connect explicitly, run queries, then release the socket with end(). Configuration comes either from an options object or from standard PG* environment variables (PGHOST, PGUSER, PGPASSWORD, PGDATABASE, PGPORT).

import { Client } from "pg";

const client = new Client({
  host: "localhost",
  port: 5432,
  user: "app",
  password: process.env.PGPASSWORD,
  database: "shop",
});

await client.connect();
const { rows } = await client.query("SELECT version()");
console.log(rows[0].version);
await client.end();

Output:

PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc 13.2.0, 64-bit

CommonJS users can write const { Client } = require("pg"). The API is identical; only the import syntax differs.

Parameterized queries

Never build SQL by concatenating user input — that is how SQL injection happens. Instead, pass values separately using numbered placeholders ($1, $2, …). The driver sends the query text and the values independently, so the database treats them strictly as data.

const email = "[email protected]";

const { rows } = await client.query(
  "SELECT id, name FROM users WHERE email = $1",
  [email]
);

console.log(rows);

Output:

[ { id: 42, name: 'Ada Lovelace' } ]

Placeholders work for INSERT, UPDATE, and DELETE too. Use RETURNING to get generated columns (such as a serial id) back in the same round trip.

const { rows } = await client.query(
  "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
  ["Grace Hopper", "[email protected]"]
);

console.log(`Created user ${rows[0].id}`);

Handling results

Every query resolves to a result object. The most-used fields are below.

Property	Description
`rows`	Array of row objects, keyed by column name
`rowCount`	Number of rows affected or returned
`fields`	Column metadata (name, data type OID)
`command`	The SQL command executed (`SELECT`, `INSERT`, …)

const result = await client.query("UPDATE users SET active = true WHERE active IS NULL");
console.log(`${result.command} affected ${result.rowCount} rows`);

Output:

UPDATE affected 3 rows

PostgreSQL types are converted automatically: integer and numeric become JavaScript numbers (large bigint columns are returned as strings to avoid precision loss), timestamptz becomes a Date, json/jsonb become parsed objects, and arrays become JavaScript arrays.

Connection pooling

Opening a new connection per request is slow and exhausts the database’s connection limit. In production, use a Pool, which keeps a set of reusable connections and hands them out on demand. The pool’s own query method checks out a client, runs the query, and returns it automatically — ideal for one-off statements.

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

export async function findUser(id) {
  const { rows } = await pool.query("SELECT * FROM users WHERE id = $1", [id]);
  return rows[0];
}

A single shared Pool should live for the lifetime of your application — create it once at startup and reuse it everywhere. Call pool.end() during graceful shutdown to drain it.

Set max to match your database’s capacity divided by the number of app instances. Too many pools each opening max connections is a common cause of “too many clients already” errors.

Transactions

When several statements must succeed or fail together, run them on a single checked-out client inside a BEGIN/COMMIT block. Because a transaction is tied to one connection, you must use pool.connect() to reserve a client rather than the pool’s shortcut query. Always release the client in a finally block so it returns to the pool even on error.

export 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");
    throw err;
  } finally {
    client.release();
  }
}

If either UPDATE throws — for example a check constraint preventing a negative balance — the ROLLBACK undoes the partial work, leaving both accounts untouched.

Best Practices

Always use $1 placeholders for dynamic values; never interpolate user input into SQL strings.
Use one long-lived Pool per process instead of creating clients per request.
Wrap related writes in a transaction on a single pool.connect() client, and release it in finally.
Keep secrets like PGPASSWORD or DATABASE_URL in environment variables, not in source.
Set sensible connectionTimeoutMillis and idleTimeoutMillis so stalled connections fail fast.
Call pool.end() on shutdown (e.g. on SIGTERM) to close sockets cleanly.
Prefer RETURNING to fetch generated keys in the same round trip rather than issuing a follow-up SELECT.

Using PostgreSQL with node-postgres (pg)

Installing and connecting

Parameterized queries

Handling results

Connection pooling

Transactions

Best Practices

Related Topics