PostgreSQL With Node.js

Connecting to PostgreSQL With pg

pg (also known as node-postgres) is the standard PostgreSQL driver for Node.js, supporting promises natively and providing a Pool class for connection pooling, very similar in shape to mysql2, but with PostgreSQL's own placeholder syntax and feature set.

PostgreSQL supports advanced features beyond basic relational storage, JSON/JSONB columns, array columns, and powerful indexing, that make it a popular choice even for applications with some document-like data needs alongside relational data.

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const result = await pool.query('SELECT * FROM products WHERE id = $1', [1]);
console.log(result.rows[0]);

PostgreSQL uses $1, $2, ... as positional placeholders (instead of MySQL's ?), and pool.query() returns a rows array directly on the result object.

Parameterized Queries in PostgreSQL

pool.query('SELECT * FROM users WHERE email = $1', [email]);
pool.query('INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *', [name, email]);
pool.query('UPDATE users SET name = $1 WHERE id = $2', [name, id]);

• $1, $2, etc. are positional placeholders, matched by their position in the parameters array.
• RETURNING * (or specific columns) lets an INSERT/UPDATE return the affected row directly, no separate SELECT needed.
• result.rows holds the returned rows; result.rowCount tells you how many were affected.
• Never interpolate values directly into the query string, always use $n placeholders.

PostgreSQL (pg) Cheatsheet

Common query patterns using node-postgres.

The RETURNING Clause

One of PostgreSQL's most convenient features for application code is RETURNING, appended to INSERT, UPDATE, or DELETE statements to get back the affected row(s) in the same round trip, avoiding a second query just to fetch what you just wrote.

const result = await pool.query(
  'INSERT INTO products (name, price) VALUES ($1, $2) RETURNING *',
  ['Keyboard', 49.99]
);

const newProduct = result.rows[0];
console.log(newProduct.id, newProduct.name);

Working With JSONB Columns

PostgreSQL's JSONB column type lets you store semi-structured JSON data directly in a relational table, combining relational integrity for core fields with document-like flexibility for the parts of your schema that change more often.

await pool.query(
  'INSERT INTO products (name, metadata) VALUES ($1, $2)',
  ['Keyboard', JSON.stringify({ color: 'black', wireless: true })]
);

const result = await pool.query(
  "SELECT * FROM products WHERE metadata->>'color' = $1",
  ['black']
);

The ->>'field' operator extracts a JSONB field as text directly in SQL, letting you query semi-structured data without a separate document store.

Common Mistakes

• Using ? placeholders (MySQL syntax) instead of PostgreSQL's $1, $2 syntax.
• Running a separate SELECT after an INSERT/UPDATE instead of using RETURNING.
• Storing everything as JSONB out of convenience instead of using proper relational columns where structure is known and stable.
• Not closing the pool on graceful shutdown, leaving lingering open connections.

Key Takeaways

• pg (node-postgres) is the standard PostgreSQL driver, with promise support and connection pooling built in.
• PostgreSQL placeholders use $1, $2, ... syntax, not ?.
• RETURNING lets writes return affected rows without a separate follow-up query.
• JSONB columns add flexible, document-like storage within an otherwise relational schema.