PostgreSQL from Node.js — Node.js Course

What you'll learn

A production database connection is a shared, bounded resource. Configure node-postgres once, parameterize every value, keep HTTP concerns outside query code, and close the pool deliberately.

By the end of this lesson, you'll be able to:

Configure one reusable pool
Execute parameterized queries safely
Map rows into domain values
Handle timeouts, failures, and shutdown

Core mental model

Node.js becomes easier when you separate the JavaScript language from the runtime and the operating-system capabilities it exposes. Use this table as a decision guide.

Concept	What it means	Decision rule
Pool	A bounded set of reusable database connections	Create one per process and size it across all replicas
Parameterized query	SQL text and untrusted values travel separately	Use placeholders for values; allowlist dynamic identifiers
Row mapper	A boundary translating storage shape to domain shape	Keep driver details out of services

Professional workflow

Build and verify Node.js programs from the terminal in small, observable steps.

Define the PostgreSQL adapter boundary: inputs, outputs, invariants, ownership, and expected failures.
Design the data or message contract before choosing implementation details.
Implement the smallest correct path with dependencies passed explicitly.
Add validation, failure translation, cleanup, and concurrency behavior.
Verify the boundary with realistic data and at least one adversarial case.
Measure or observe the behavior before optimizing or extracting abstractions.

Keep the feedback loop short

Run the smallest useful command after every meaningful change. Read the complete error message before editing again, and keep inputs and outputs visible while you learn.

Guided code lab

Create one pool

Validated configuration and an error listener make resource failures visible.

db.js

import pg from 'pg';

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

pool.on('error', (error) => console.error({ error }, 'idle client failed'));

Query through a repository

Values remain separate from SQL and the mapper provides a stable application shape.

task-repository.js

export async function findOpenTasks(ownerId, limit = 20) {
  const result = await pool.query({
    text: `SELECT id, owner_id, title, created_at
           FROM tasks WHERE owner_id = $1 AND completed_at IS NULL
           ORDER BY created_at DESC LIMIT $2`,
    values: [ownerId, limit],
  });
  return result.rows.map((row) => ({ ...row, ownerId: row.owner_id }));
}

Production practice

Contract

Repositories accept validated domain inputs and return domain-shaped results or explicit not-found and conflict outcomes.

Verification

Test against disposable PostgreSQL, including quotes in values, empty results, uniqueness failures, and pool exhaustion.

Operations

Budget connections across replicas, set timeouts, expose pool pressure, and call pool.end() during graceful shutdown.

Common failure mode

String interpolation turns data into executable SQL. Placeholders protect values, but identifiers still require a fixed allowlist.

Independent workshop

Implement a PostgreSQL task repository behind the Phase 2 repository contract.

Your finished workshop must include:

Validated configuration
One shared pool
Parameterized CRUD
Row mapping
Integration tests
Graceful shutdown

Definition of done

Run the happy path and at least two edge cases, keep responsibilities separated, and add a short README explaining how to run the program.

Recap & quick check

Key takeaways

Pools are finite
Values belong in parameters
Repositories isolate SQL
Real database tests matter
Lifecycle includes shutdown

Quick check

1. How should an email enter a query?

2. How many pools should a service process usually create?

3. What belongs in a row mapper?

Next: Transactions & Concurrency