PostgreSQL for Node.js Developers: A Practical Guide

PostgreSQL is the go-to relational database for serious projects. It is ACID-compliant, supports JSON alongside traditional relational data, has one of the richest SQL feature sets of any open-source database, and the community has maintained it reliably for over 25 years. If you are reaching for a database in a Node.js project, Postgres is rarely the wrong choice.

This post covers the essentials: running it locally, core SQL patterns you will use every day, indexing basics, transactions, and connecting from Node.js with the postgres package.

Running Postgres Locally

The fastest path is Docker:

docker run --name pg-dev \
  -e POSTGRES_USER=dev \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  -d postgres:16

Connect with psql:

psql postgres://dev:secret@localhost:5432/myapp

Core SQL Patterns

Creating a Table

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

SERIAL is shorthand for an auto-incrementing integer. TIMESTAMPTZ stores timestamps with a time zone, which is almost always what you want over plain TIMESTAMP.

Inserting Rows

INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
RETURNING id, created_at;

RETURNING hands back the generated values immediately — no need for a second query to get the new id.

Querying

-- All users created in the past week
SELECT id, email, name
FROM users
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

Updating and Deleting

UPDATE users SET name = 'Alicia' WHERE id = 1 RETURNING *;

DELETE FROM users WHERE id = 1;

JOINs

CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title   TEXT NOT NULL,
  body    TEXT NOT NULL
);

-- Posts with their author's email
SELECT p.id, p.title, u.email
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE u.email = '[email protected]';

ON DELETE CASCADE means deleting a user automatically deletes their posts — you declare the relationship once in the schema and the database enforces it everywhere.

Indexes

Postgres creates an index automatically on primary keys and UNIQUE columns. For any other column you filter or join on frequently, add one explicitly:

CREATE INDEX idx_posts_user_id ON posts(user_id);

Partial indexes are one of Postgres's killer features — they index only the rows matching a condition, keeping them small and fast:

-- Only index published posts; unpublished rows are excluded
CREATE INDEX idx_posts_published ON posts(created_at)
WHERE published = TRUE;

Check whether your queries are actually using indexes with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42;

Look for Index Scan in the output. Seq Scan on a large table is a sign you might need an index.

Transactions

Wrap multiple operations in a transaction so they either all succeed or all roll back together:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If anything goes wrong between BEGIN and COMMIT, issue ROLLBACK instead and the database reverts both changes as if neither happened.

Connecting from Node.js

Install the postgres package — a lightweight client that uses tagged template literals:

npm install postgres

Create a single connection module and re-use it across your app:

// db.js
import postgres from 'postgres';

const sql = postgres(process.env.DATABASE_URL, {
  max: 10,          // connection pool size
  idle_timeout: 20, // close idle connections after 20 seconds
});

export default sql;

The tagged template literal syntax automatically parameterizes every interpolated value, so SQL injection is not possible:

import sql from './db.js';

export async function getUserByEmail(email) {
  const [user] = await sql`
    SELECT id, name, email
    FROM users
    WHERE email = ${email}
  `;
  return user ?? null;
}

export async function createPost(userId, title, body) {
  const [post] = await sql`
    INSERT INTO posts (user_id, title, body)
    VALUES (${userId}, ${title}, ${body})
    RETURNING *
  `;
  return post;
}

Transactions use a callback that receives a scoped tx client. If any query inside throws, the whole transaction rolls back automatically:

export async function transferBalance(fromId, toId, amount) {
  await sql.begin(async (tx) => {
    await tx`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${fromId}`;
    await tx`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${toId}`;
  });
}

Environment Variables

Keep connection details out of your source code. A minimal .env:

DATABASE_URL=postgres://dev:secret@localhost:5432/myapp

Load it with dotenv in development, or provide it through your hosting platform's secret manager in production. Vercel, Railway, Supabase, and Render all have first-class PostgreSQL support with one-click provisioning.

PostgreSQL rewards you the more of it you learn. Once you are comfortable with the patterns here, the next things worth exploring are full-text search with tsvector, JSONB columns for semi-structured data, window functions, and COPY for bulk data imports.