Module F-7·20 min read

Connecting to PostgreSQL with node-postgres, parameterized queries, SQL injection prevention, and a first look at Prisma ORM.

JJS

Written by Jatin Jain Saraf · Senior Software Engineer

Module F-7 — Working with Databases from Node.js

Q: Why are parameterized queries (e.g., `WHERE name = $1`) essential when using raw SQL via `node-postgres`?

They prevent SQL injection attacks by treating user input strictly as literal data, rather than executable SQL logic. — Parameterized queries separate the SQL command template from the data payload. When passed to the PostgreSQL engine, the variables (`$1`, `$2`) are parsed strictly as values. This ensures that malicious inputs like `'; DROP TABLE users; --` are treated merely as strings of text, preventing SQL injection vulnerabilities.

Q: What is the primary advantage of using a database connection pool (`pg.Pool`) instead of managing single connections (`pg.Client`)?

A pool maintains a group of open connections that can be reused across multiple requests, eliminating the overhead of repeatedly connecting and disconnecting. — Establishing a new database connection is an expensive, latency-heavy operation. A connection pool keeps a set of active connections open. When a request arrives, it temporarily borrows a connection and returns it when finished, ensuring high performance under concurrent load.

Q: When executing a SQL transaction in `node-postgres`, why must you call `client.release()` in a `finally` block?

To return the borrowed connection to the connection pool, preventing connection exhaustion if an error occurs. — Transactions require borrowing a dedicated `client` from the pool because all statements (BEGIN, queries, COMMIT/ROLLBACK) must occur on the exact same connection. The `finally` block ensures that regardless of whether the transaction succeeds or throws an error, the connection is returned (`released`) back to the pool. Failing to do this causes the pool to run out of available connections, stalling the application.

What this module covers: Almost every Node.js application talks to a database. This module covers the two dominant approaches: using node-postgres (pg) directly for full control with raw SQL, and using Prisma ORM for a type-safe, schema-driven workflow. You will learn how to connect, query, parameterize inputs to prevent SQL injection, manage connection pooling, and handle errors correctly. By the end you will have a working database layer you can drop into the Express API from F-6.

Two Approaches: Raw SQL vs ORM

Before writing code, choose your approach:

Raw SQL with node-postgres

Full control over every query
No abstraction layer — what you write is what runs
Best when queries are complex, performance-critical, or team is SQL-proficient
Requires manual migration management

Prisma ORM

Type-safe queries with autocomplete
Schema-first: define your models in schema.prisma, Prisma generates the client
Auto-generated migrations
Faster to get started, excellent for standard CRUD
Less control over complex queries (though raw SQL escape hatch exists)

Most production codebases use one or the other, or a mix (Prisma for standard queries, raw SQL for complex reports). This module teaches both.

Option 1: `node-postgres` (Raw SQL)

Installing and connecting

bash

npm install pg
npm install -D @types/pg   # if using TypeScript

javascript

// src/db/pool.js
import pg from 'pg';

const { Pool } = pg;

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // OR specify individually:
  // host: process.env.DB_HOST,
  // port: parseInt(process.env.DB_PORT || '5432'),
  // database: process.env.DB_NAME,
  // user: process.env.DB_USER,
  // password: process.env.DB_PASSWORD,
  max: 10,                // max pool size (default: 10)
  idleTimeoutMillis: 30000, // close idle connections after 30s
  connectionTimeoutMillis: 2000, // fail fast if can't connect in 2s
});

// Test connection on startup
pool.connect()
  .then(client => {
    console.log('Database connected');
    client.release();
  })
  .catch(err => {
    console.error('Database connection failed:', err.message);
    process.exit(1);
  });

bash

# .env
DATABASE_URL=postgres://username:password@localhost:5432/myapp_dev

Why a Pool, not a single connection?

A database connection is expensive to open (~5–50ms). A connection pool keeps a set of connections open and reuses them. When your code needs a connection, it borrows one from the pool, uses it, and returns it. Under high load (100 concurrent requests), each request gets a connection instantly rather than waiting to open a new one.

A connection pool is a taxi rank, not a car dealership — instead of manufacturing a brand-new car for every passenger, you keep a small fleet idling and hand off whichever one is free.

pg.Pool handles this automatically. Never use new pg.Client() for a server — that creates a single connection with no pooling.

Production story: a UPI settlement service running at roughly 20K TPS across 12 pods had pool.max: 20 on each pod — 240 connections combined, against a Postgres instance capped at max_connections = 200. A traffic spike pushed the fleet to its combined ceiling. Every pod's health check queried the database as part of its readiness probe; every one of those queries failed identically because the pool was exhausted, every pod reported unhealthy at once, and the orchestrator restarted the entire fleet simultaneously — turning a capacity spike into a full outage. The fix wasn't a bigger Postgres instance, it was arithmetic: pool.max × running instances has to stay comfortably under max_connections, with headroom for migrations, admin connections, and any other service sharing the database.

A shorter, second story from the same class of bug: a blockchain indexer stored block_number as a Postgres BIGINT. When pg returned it, blockNumber + 1 silently produced string concatenation instead of incrementing the number — see BigInt Handling below for why.

Pool Sizing at Fleet Scale

pool.max is a per-instance setting, not a global one. If you run 12 replicas of your service and each opens a pool of 20 connections, you are asking Postgres for 240 simultaneous connections, not 20. Postgres has a hard max_connections limit (default 100, commonly raised to 200–500) shared across every client connecting to it — your app fleet, migration jobs, admin consoles, read replicas, all of it.

The sizing rule of thumb:

(pool.max × number_of_running_instances) + headroom  <  postgres max_connections

Getting this wrong doesn't fail gracefully — it fails all at once, the way the story above played out. When scaling out horizontally (more pods/instances), scale pool.max down proportionally, or put a connection pooler like PgBouncer in front of Postgres so the fleet can grow without the connection count growing linearly with it.

Basic queries

javascript

// src/db/users.js
import { pool } from './pool.js';

// SELECT — returns multiple rows
export async function findAllUsers() {
  const result = await pool.query('SELECT id, name, email, role FROM users ORDER BY created_at DESC');
  return result.rows;  // array of row objects
}

// SELECT with a filter
export async function findUserById(id) {
  const result = await pool.query(
    'SELECT id, name, email, role FROM users WHERE id = $1',
    [id]  // parameterized — $1 is replaced with id safely
  );
  return result.rows[0] ?? null;  // undefined → null if not found
}

// INSERT
export async function createUser({ name, email, role = 'user' }) {
  const result = await pool.query(
    `INSERT INTO users (name, email, role, created_at)
     VALUES ($1, $2, $3, NOW())
     RETURNING id, name, email, role, created_at`,
    [name, email, role]
  );
  return result.rows[0];
}

// UPDATE
export async function updateUser(id, { name, email, role }) {
  const result = await pool.query(
    `UPDATE users
     SET name = COALESCE($2, name),
         email = COALESCE($3, email),
         role = COALESCE($4, role),
         updated_at = NOW()
     WHERE id = $1
     RETURNING id, name, email, role`,
    [id, name, email, role]
  );
  return result.rows[0] ?? null;
}

// DELETE
export async function deleteUser(id) {
  const result = await pool.query(
    'DELETE FROM users WHERE id = $1',
    [id]
  );
  return result.rowCount > 0;  // true if a row was deleted
}

SQL Injection — Always Use Parameters

This cannot be overstated. Never concatenate user input into SQL strings:

javascript

// ❌ CRITICAL VULNERABILITY — SQL injection
const name = req.body.name;  // attacker sends: "'; DROP TABLE users; --"
const result = await pool.query(
  `SELECT * FROM users WHERE name = '${name}'`  // DO NOT DO THIS
);

// ✅ SAFE — parameterized query
const result = await pool.query(
  'SELECT * FROM users WHERE name = $1',
  [req.body.name]  // pg escapes this safely
);

With parameterized queries, pg sends the SQL template and the values to PostgreSQL separately. PostgreSQL treats the values as data, never as SQL. Even if a user sends '; DROP TABLE users; -- as their name, it is stored as a literal string, not executed.

Transactions

When multiple queries must succeed or fail together, use a transaction:

javascript

export async function transferCredits(fromUserId, toUserId, amount) {
  const client = await pool.connect(); // get a dedicated connection

  try {
    await client.query('BEGIN');

    await client.query(
      'UPDATE users SET credits = credits - $1 WHERE id = $2',
      [amount, fromUserId]
    );

    await client.query(
      'UPDATE users SET credits = credits + $1 WHERE id = $2',
      [amount, toUserId]
    );

    await client.query('COMMIT');
    return true;
  } catch (err) {
    await client.query('ROLLBACK'); // undo everything if anything fails
    throw err;
  } finally {
    client.release(); // always return the connection to the pool
  }
}

The finally block ensures the connection is always released back to the pool, even if an error occurs. Failing to release connections causes pool exhaustion — one of the most common production bugs.

Handling query errors

javascript

export async function findUserByEmail(email) {
  try {
    const result = await pool.query(
      'SELECT id, name, email FROM users WHERE email = $1',
      [email]
    );
    return result.rows[0] ?? null;
  } catch (err) {
    // PostgreSQL error codes
    if (err.code === '42P01') {
      throw new Error('Table "users" does not exist — run migrations');
    }
    throw err; // re-throw unexpected errors
  }
}

Common PostgreSQL error codes:

23505 — unique constraint violation (duplicate email, etc.)
23503 — foreign key constraint violation
42P01 — table does not exist
08006 — connection failure

BigInt Handling

PostgreSQL's BIGINT type can hold values far larger than JavaScript's number type can represent exactly (Number.MAX_SAFE_INTEGER is about 9 × 10¹⁵; BIGINT goes up to about 9 × 10¹⁸). To avoid silently losing precision, node-postgres returns BIGINT columns as JavaScript strings, not numbers:

javascript

const result = await pool.query('SELECT block_number FROM blocks WHERE id = $1', [1]);
console.log(result.rows[0].block_number); // "18234501" — a string, not a number

This matters directly for this course's blockchain-indexer framing, where block_number is a natural BIGINT column. The bug it causes is easy to miss because it doesn't throw — it just produces the wrong answer:

javascript

// ❌ block_number came back as the string "18234501"
const blockNumber = result.rows[0].block_number;
const next = blockNumber + 1; // "182345011" — string concatenation, not addition!

// ✅ convert explicitly first
const next = Number(blockNumber) + 1; // 18234502 — correct, as long as the value fits in a safe integer

A "numeric-looking" string plus a number in JavaScript coerces the number to a string and concatenates — it does not throw, so this class of bug tends to surface much later, as corrupted data rather than a crash (for example, a resumption checkpoint that silently drifts). If the value could realistically exceed Number.MAX_SAFE_INTEGER, convert with BigInt(value) instead of Number(value) and use BigInt arithmetic throughout.

Option 2: Prisma ORM

Prisma is a TypeScript-first ORM. You define your schema in schema.prisma, run a migration to create the tables, and Prisma generates a fully-typed client.

Installing Prisma

bash

npm install prisma --save-dev
npm install @prisma/client

npx prisma init  # creates prisma/schema.prisma and adds DATABASE_URL to .env

Defining the schema

prisma

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  role      String   @default("user")
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

Running migrations

bash

# Create and apply a migration
npx prisma migrate dev --name init

# Apply migrations in production (no interactive prompts)
npx prisma migrate deploy

# Open Prisma Studio (visual database browser)
npx prisma studio

Using the Prisma Client

javascript

// src/db/prisma.js
import { PrismaClient } from '@prisma/client';

// Create a single instance (not one per request)
export const prisma = new PrismaClient();

javascript

// src/db/users.js
import { prisma } from './prisma.js';

// Find all users
export async function findAllUsers() {
  return prisma.user.findMany({
    orderBy: { createdAt: 'desc' },
    select: { id: true, name: true, email: true, role: true },
  });
}

// Find one by id
export async function findUserById(id) {
  return prisma.user.findUnique({
    where: { id },
    include: { posts: true },  // join posts
  });
}

// Create a user
export async function createUser(data) {
  return prisma.user.create({ data });
}

// Update a user
export async function updateUser(id, data) {
  return prisma.user.update({
    where: { id },
    data,
  });
}

// Delete a user
export async function deleteUser(id) {
  return prisma.user.delete({ where: { id } });
}

// Filter with where
export async function findUsersByRole(role) {
  return prisma.user.findMany({
    where: { role },
  });
}

Handling Prisma errors

javascript

import { Prisma } from '@prisma/client';

export async function createUser(data) {
  try {
    return await prisma.user.create({ data });
  } catch (err) {
    if (err instanceof Prisma.PrismaClientKnownRequestError) {
      if (err.code === 'P2002') {
        // Unique constraint violation
        throw new Error(`Email ${data.email} is already registered`);
      }
    }
    throw err;
  }
}

Common Prisma error codes:

P2002 — unique constraint violation
P2003 — foreign key constraint violation
P2025 — record not found (for findUniqueOrThrow, update, delete)

Prisma's Own Connection Pool

PrismaClient isn't a thin wrapper around a single connection — it manages its own internal connection pool, the same way pg.Pool does. Its default size is:

num_cpus * 2 + 1

That default is tuned for a single long-lived server process. It compounds the exact same "Pool Sizing at Fleet Scale" problem covered earlier in this module, and it gets worse in two common deployment shapes:

Many replicas. 12 pods each running Prisma's default pool against a Postgres instance capped at 200 connections hits the same ceiling as the raw-SQL example above — Prisma just picked the number for you instead of you writing pool.max: 20 yourself.
Serverless. Each serverless function invocation can spin up its own PrismaClient (and therefore its own pool) if you're not careful to reuse a singleton across invocations. At any real concurrency, that can multiply into far more connections than a fixed-size fleet would ever open.

Override the default explicitly via the connection_limit parameter on your DATABASE_URL, sized with the same arithmetic as any other pool:

DATABASE_URL="postgres://user:password@host:5432/db?connection_limit=10"

Connecting the Database Layer to Express

Putting it all together with the Express router from F-6:

javascript

// src/routes/users.js
import { Router } from 'express';
import { findAllUsers, findUserById, createUser, updateUser, deleteUser } from '../db/users.js';

const router = Router();

router.get('/', async (req, res, next) => {
  try {
    const users = await findAllUsers();
    res.json(users);
  } catch (err) {
    next(err);
  }
});

router.get('/:id', async (req, res, next) => {
  try {
    const user = await findUserById(parseInt(req.params.id));
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json(user);
  } catch (err) {
    next(err);
  }
});

router.post('/', async (req, res, next) => {
  try {
    const { name, email } = req.body;
    if (!name || !email) {
      return res.status(400).json({ error: 'name and email are required' });
    }
    const user = await createUser({ name, email });
    res.status(201).json(user);
  } catch (err) {
    if (err.message.includes('already registered')) {
      return res.status(409).json({ error: err.message });
    }
    next(err);
  }
});

router.patch('/:id', async (req, res, next) => {
  try {
    const user = await updateUser(parseInt(req.params.id), req.body);
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json(user);
  } catch (err) {
    next(err);
  }
});

router.delete('/:id', async (req, res, next) => {
  try {
    const deleted = await deleteUser(parseInt(req.params.id));
    if (!deleted) return res.status(404).json({ error: 'User not found' });
    res.status(204).send();
  } catch (err) {
    next(err);
  }
});

export default router;

Which Should You Use?

Scenario	Recommendation
New project, TypeScript, standard CRUD	Prisma
Complex analytical queries, heavy JOINs, window functions	Raw SQL (`pg`)
Team is SQL-proficient	Raw SQL or Prisma with `$queryRaw`
Team is JS-first, less SQL experience	Prisma
Performance-critical, fine-grained query control	Raw SQL
Rapid prototyping	Prisma

Many production applications use both: Prisma for standard CRUD, and pool.query() for complex reports and bulk operations that would be awkward with an ORM.

Summary

node-postgres (pg) gives you direct SQL access. Create a Pool — never a single Client — for connection reuse. Parameters are $1, $2, etc.
Always use parameterized queries. Never concatenate user input into SQL strings. This is how SQL injection happens.
Transactions require a dedicated client from the pool. Always client.release() in a finally block.
Prisma is schema-first. Define models in schema.prisma, run prisma migrate dev, import and use the generated client. Fully typed.
Prisma error codes (P2002, P2025) are how you detect constraint violations and missing records.
Connect the DB layer to Express by calling your data functions inside route handlers and passing errors to next(err).

Next: building your first complete real-world application — pulling everything from F-1 through F-7 into a structured project.

Knowledge Check

Why are parameterized queries (e.g., WHERE name = $1) essential when using raw SQL via node-postgres?

What is the primary advantage of using a database connection pool (pg.Pool) instead of managing single connections (pg.Client)?

When executing a SQL transaction in node-postgres, why must you call client.release() in a finally block?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule F-6: Building HTTP Servers and REST APIs with Express Next Module F-8: Your First Real Application

Discussion

Join the discussion

Loading comments...

Module F-7 — Working with Databases from Node.js

Two Approaches: Raw SQL vs ORM

Option 1: node-postgres (Raw SQL)

Installing and connecting

Why a Pool, not a single connection?

Pool Sizing at Fleet Scale

Basic queries

Sign in to keep reading

Discussion

Option 1: `node-postgres` (Raw SQL)