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

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?

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.