Module F-7·20 min read

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

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.

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

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

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)

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

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?

Basic queries

SQL Injection — Always Use Parameters

Transactions

Handling query errors

Option 2: Prisma ORM

Installing Prisma

Defining the schema

Running migrations

Using the Prisma Client

Handling Prisma errors

Connecting the Database Layer to Express

Which Should You Use?

Summary

Test your knowledge with more question sets

Discussion

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