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

SQL Injection — Always Use Parameters

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

// ❌ 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:

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

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:

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:

// ❌ 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

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/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

# 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

// src/db/prisma.js
import { PrismaClient } from '@prisma/client';
 
// Create a single instance (not one per request)
export const prisma = new PrismaClient();

// 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

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:

// 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?

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.

---