PostgreSQL with node-postgres

PostgreSQL is the most popular open-source relational database, and pg (node-postgres) is the standard driver for using it from Node. Working directly with pg means writing real SQL with parameterized queries — the foundation every Postgres ORM is built on. This page covers pooling, parameterized queries, reading results, transactions, and the Postgres-specific features worth knowing.

Setup and a connection pool

Bash

npm install pg

db.js — one pool for the whole app

import { Pool } from 'pg'

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // max connections in the pool
  idleTimeoutMillis: 30_000,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: true } : false,
})

// Helper that borrows + returns a connection automatically:
export const query = (text, params) => pool.query(text, params)

Use a `Pool`, not a `Client`, for a web server

`pg` offers `Client` (a single connection) and `Pool` (a managed set). A web server needs a **Pool** created once at startup — each `pool.query()` borrows a free connection and returns it automatically. A lone `Client` serializes all queries through one connection and must be manually reconnected on error. [Connection pooling](/nodejs/connection-pooling) is essential to handling concurrent requests.

Parameterized queries — always

// Placeholders are $1, $2, ... with a separate values array:
const { rows } = await pool.query(
  'SELECT id, name, email FROM users WHERE email = $1 AND active = $2',
  [req.body.email, true],
)
const user = rows[0]   // undefined if no match

// NEVER interpolate user input into the SQL string:
// `... WHERE email = '${req.body.email}'`   ← SQL INJECTION

Parameterized queries are the only safe way to include input

Placeholders (`$1`, `$2`) send your SQL and the data on separate channels, so input can never be parsed as SQL — this is the definitive defense against [SQL injection](/nodejs/databases-intro). String-concatenating user input (`WHERE id = '${id}'`) lets an attacker inject `'; DROP TABLE users; --`. There is no acceptable reason to build queries by concatenation; parameterize every value, every time.

Reading the result object

const result = await pool.query('SELECT * FROM users WHERE active = $1', [true])

result.rows        // → array of row objects: [{ id: 1, name: 'Ada' }, ...]
result.rowCount    // → number of rows affected/returned
result.fields      // → column metadata

// INSERT ... RETURNING gets the new row back in one round-trip:
const { rows: [created] } = await pool.query(
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
  ['Ada', 'ada@x.com'],
)

`RETURNING` avoids a second query after INSERT/UPDATE/DELETE

Postgres's `RETURNING` clause sends back the affected rows from an `INSERT`/`UPDATE`/`DELETE` in the same statement — so you get the new `id` (and any DB-generated columns like `created_at`) without a follow-up `SELECT`. Use it on every write where you'd otherwise re-query. `result.rows` is always an array; index `[0]` for single-row reads.

Transactions — borrow a single client

// A multi-statement transaction must use ONE connection, not pool.query:
const client = await pool.connect()
try {
  await client.query('BEGIN')
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1])
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2])
  await client.query('COMMIT')
} catch (err) {
  await client.query('ROLLBACK')   // undo everything on any failure
  throw err
} finally {
  client.release()                 // ALWAYS return the connection to the pool
}

Transactions need one checked-out client — and `release()` in `finally`

`pool.query()` may use a *different* connection each call, so `BEGIN`/`COMMIT` issued through the pool won't wrap the right statements. Check out a single client with `pool.connect()`, run the whole transaction on it, and **always `client.release()` in `finally`** — forgetting it leaks a connection from the pool until it's exhausted and the app hangs. [Transactions](/nodejs/transactions) get a dedicated page.

Common Postgres errors to map

Error code	Meaning	Map to
`23505`	unique_violation (duplicate)	`409 Conflict`
`23503`	foreign_key_violation	`409` / `422`
`23502`	not_null_violation	`422`
`22P02`	invalid_text_representation (bad type)	`400`
`23514`	check_violation	`422`

`pg` errors carry a SQLSTATE `code` — branch on it

Database errors surface with a Postgres `code` (SQLSTATE). Inspecting `err.code` lets your [error middleware](/nodejs/api-error-responses) translate DB constraints into clean HTTP responses — a duplicate email (`23505`) becomes a friendly `409` instead of a 500 stack trace. Constraints in the database are a feature: they enforce integrity even if app code has a bug; just translate their errors for clients.

Postgres features worth using

JSONB — store and query JSON columns; relational and document where useful.
Arrays — native array columns (text[]), queryable with ANY/@>.
Rich types — uuid, timestamptz, numeric (exact decimals for money), enums.
Constraints & indexes — UNIQUE, CHECK, foreign keys, partial and GIN indexes.
Upsert — INSERT ... ON CONFLICT ... DO UPDATE for insert-or-update in one statement.

A query builder eases raw SQL without an ORM

Writing SQL strings by hand is fine, but for dynamic queries (optional filters, sorting) it gets unwieldy. **Knex** is a popular query builder that generates parameterized SQL from chained JS — a middle ground between raw `pg` and a full [ORM](/nodejs/sequelize). If you like SQL but want composability, it's worth a look before reaching for an ORM.

The other major open-source relational database: [MySQL with Node.js](/nodejs/mysql).