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

1postgres

package.

Running Postgres Locally

The fastest path is Docker:

1docker run --name pg-dev \
2  -e POSTGRES_USER=dev \
3  -e POSTGRES_PASSWORD=secret \
4  -e POSTGRES_DB=myapp \
5  -p 5432:5432 \
6  -d postgres:16
7

Connect with

1psql

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

Core SQL Patterns

Creating a Table

1CREATE TABLE users (
2  id         SERIAL PRIMARY KEY,
3  email      TEXT NOT NULL UNIQUE,
4  name       TEXT NOT NULL,
5  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
6);
7

1SERIAL

is shorthand for an auto-incrementing integer.

1TIMESTAMPTZ

stores timestamps with a time zone, which is almost always what you want over plain

1TIMESTAMP

Inserting Rows

1INSERT INTO users (email, name)
2VALUES ('alice@example.com', 'Alice')
3RETURNING id, created_at;
4

1RETURNING

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

1id

Querying

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

Updating and Deleting

1UPDATE users SET name = 'Alicia' WHERE id = 1 RETURNING *;
2
3DELETE FROM users WHERE id = 1;
4

JOINs

1CREATE TABLE posts (
2  id      SERIAL PRIMARY KEY,
3  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
4  title   TEXT NOT NULL,
5  body    TEXT NOT NULL
6);
7
8-- Posts with their author's email
9SELECT p.id, p.title, u.email
10FROM posts p
11JOIN users u ON u.id = p.user_id
12WHERE u.email = 'alice@example.com';
13

1ON 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

1UNIQUE

columns. For any other column you filter or join on frequently, add one explicitly:

1CREATE INDEX idx_posts_user_id ON posts(user_id);
2

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

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

Check whether your queries are actually using indexes with

1EXPLAIN ANALYZE

1EXPLAIN ANALYZE
2SELECT * FROM posts WHERE user_id = 42;
3

Look for

1Index Scan

in the output.

1Seq 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:

1BEGIN;
2
3UPDATE accounts SET balance = balance - 100 WHERE id = 1;
4UPDATE accounts SET balance = balance + 100 WHERE id = 2;
5
6COMMIT;
7

If anything goes wrong between

1BEGIN

and

1COMMIT

, issue

1ROLLBACK

instead and the database reverts both changes as if neither happened.

Connecting from Node.js

Install the

1postgres

package — a lightweight client that uses tagged template literals:

1npm install postgres
2

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

1// db.js
2import postgres from 'postgres';
3
4const sql = postgres(process.env.DATABASE_URL, {
5  max: 10,          // connection pool size
6  idle_timeout: 20, // close idle connections after 20 seconds
7});
8
9export default sql;
10

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

1import sql from './db.js';
2
3export async function getUserByEmail(email) {
4  const [user] = await sql`
5    SELECT id, name, email
6    FROM users
7    WHERE email = ${email}
8  `;
9  return user ?? null;
10}
11
12export async function createPost(userId, title, body) {
13  const [post] = await sql`
14    INSERT INTO posts (user_id, title, body)
15    VALUES (${userId}, ${title}, ${body})
16    RETURNING *
17  `;
18  return post;
19}
20

Transactions use a callback that receives a scoped

1tx

client. If any query inside throws, the whole transaction rolls back automatically:

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

Environment Variables

Keep connection details out of your source code. A minimal

1.env

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

Load it with

1dotenv

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

1tsvector

1JSONB

columns for semi-structured data, window functions, and

1COPY

for bulk data imports.