Express API Patterns

Best practices for building Express.js REST APIs with TypeScript, raw SQL (pg), JWT authentication, and role-based access control.

When to Apply

Reference these guidelines when:

•Creating new API routes or modules
•Writing database queries with pg
•Implementing authentication or authorization logic
•Adding request validation with Zod
•Writing middleware
•Handling errors

Rule Categories

Category	Impact	Prefix
SQL Safety	CRITICAL	`sql-`
Auth & JWT	CRITICAL	`auth-`
Validation	HIGH	`validate-`
Error Handling	HIGH	`error-`
Module Structure	MEDIUM	`module-`
Query Patterns	MEDIUM	`query-`

Rules

SQL Safety (CRITICAL)

sql-parameterized

ALWAYS use parameterized queries. NEVER interpolate user input into SQL strings.

Incorrect: SQL injection vulnerability

typescript

const result = await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

Correct: parameterized query

typescript

const result = await pool.query('SELECT * FROM users WHERE email = $1', [email]);

sql-transactions

Use transactions for multi-table writes. Always rollback on error.

typescript

const client = await pool.connect();
try {
  await client.query('BEGIN');

  const {
    rows: [txn],
  } = await client.query(
    `INSERT INTO ledger_transactions (group_id, type, amount, description)
     VALUES ($1, $2, $3, $4) RETURNING id`,
    [groupId, 'CHARGE', amount, description],
  );

  await client.query(
    `INSERT INTO ledger_entries (transaction_id, account_id, type, amount)
     VALUES ($1, $2, 'DEBIT', $3), ($1, $4, 'CREDIT', $3)`,
    [txn.id, memberAccountId, amount, revenueAccountId],
  );

  await client.query('COMMIT');
  return txn;
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

sql-migrations

Use numbered SQL migration files. Never modify existing migrations; always create new ones.

code

db/migrations/
  001_users_auth_groups.sql
  002_events_participants.sql
  003_tournaments.sql
  004_ledger.sql
  005_notifications.sql

Auth & JWT (CRITICAL)

auth-token-storage

Store access tokens in memory only. Use httpOnly Secure SameSite=Strict cookies for refresh tokens.

typescript

// Set refresh token as cookie
res.cookie('refreshToken', token, {
  httpOnly: true,
  secure: process.env.NODE_ENV === 'production',
  sameSite: 'strict',
  maxAge: 30 * 24 * 60 * 60 * 1000, // 30 days
  path: '/api/v1/auth/refresh',
});

// Access token returned in response body (stored in memory by frontend)
res.json({ accessToken, user });

auth-refresh-rotation

Rotate refresh tokens on every use. Revoke the old token.

typescript

async function refreshTokens(oldToken: string) {
  const hashed = sha256(oldToken);
  const stored = await pool.query(
    'SELECT * FROM refresh_tokens WHERE token_hash = $1 AND revoked_at IS NULL AND expires_at > NOW()',
    [hashed],
  );
  if (!stored.rows[0]) throw new UnauthorizedError('Invalid refresh token');

  // Revoke old token
  await pool.query('UPDATE refresh_tokens SET revoked_at = NOW() WHERE id = $1', [
    stored.rows[0].id,
  ]);

  // Issue new pair
  const newRefreshToken = generateRefreshToken();
  await pool.query(
    'INSERT INTO refresh_tokens (user_id, token_hash, expires_at, device_info) VALUES ($1, $2, $3, $4)',
    [stored.rows[0].user_id, sha256(newRefreshToken), addDays(30), deviceInfo],
  );

  const accessToken = signAccessToken(stored.rows[0].user_id);
  return { accessToken, refreshToken: newRefreshToken };
}

auth-rbac-middleware

Check roles against group_memberships, not a global role.

typescript

function requireRole(...roles: GroupRole[]) {
  return async (req: Request, res: Response, next: NextFunction) => {
    const groupId = req.params.groupId || req.body.groupId;
    if (!groupId) return res.status(400).json({ error: 'Group ID required' });

    const { rows } = await pool.query(
      `SELECT role FROM group_memberships
       WHERE user_id = $1 AND group_id = $2 AND status = 'active'`,
      [req.user.id, groupId],
    );

    if (!rows[0] || !roles.includes(rows[0].role)) {
      return res.status(403).json({ error: 'Insufficient permissions' });
    }

    req.membership = rows[0];
    next();
  };
}

// Usage
router.post('/events', authenticate, requireRole('owner', 'admin', 'coach'), createEvent);

Validation (HIGH)

validate-zod-middleware

Validate request body, params, and query with Zod schemas from the shared package.

typescript

import { z } from 'zod';

function validate(schema: { body?: z.ZodSchema; params?: z.ZodSchema; query?: z.ZodSchema }) {
  return (req: Request, res: Response, next: NextFunction) => {
    try {
      if (schema.body) req.body = schema.body.parse(req.body);
      if (schema.params) req.params = schema.params.parse(req.params) as any;
      if (schema.query) req.query = schema.query.parse(req.query) as any;
      next();
    } catch (err) {
      if (err instanceof z.ZodError) {
        return res.status(400).json({ error: 'Validation failed', details: err.errors });
      }
      next(err);
    }
  };
}

Error Handling (HIGH)

error-centralized-handler

Use a centralized error handler middleware. Throw typed errors from route handlers.

typescript

class AppError extends Error {
  constructor(
    public statusCode: number,
    message: string,
  ) {
    super(message);
  }
}
class NotFoundError extends AppError {
  constructor(message = 'Not found') {
    super(404, message);
  }
}
class UnauthorizedError extends AppError {
  constructor(message = 'Unauthorized') {
    super(401, message);
  }
}
class ForbiddenError extends AppError {
  constructor(message = 'Forbidden') {
    super(403, message);
  }
}
class ConflictError extends AppError {
  constructor(message = 'Conflict') {
    super(409, message);
  }
}

// Error handler middleware (register last)
function errorHandler(err: Error, req: Request, res: Response, next: NextFunction) {
  if (err instanceof AppError) {
    return res.status(err.statusCode).json({ error: err.message });
  }
  logger.error(err);
  res.status(500).json({ error: 'Internal server error' });
}

Module Structure (MEDIUM)

module-file-structure

Each API module follows a consistent file structure.

code

modules/events/
  events.routes.ts    # Router with route definitions
  events.handlers.ts  # Request handlers (thin: validate, call service, respond)
  events.service.ts   # Business logic + DB queries
  events.schemas.ts   # Zod schemas for this module
  events.types.ts     # Module-specific TypeScript types

Handlers are thin — they extract validated input, call the service, and send the response. All business logic lives in the service layer.

Query Patterns (MEDIUM)

query-pagination

Use cursor-based or offset pagination consistently.

typescript

async function listEvents(groupId: string, cursor?: string, limit = 20) {
  const params: any[] = [groupId, limit + 1];
  let where = 'WHERE e.group_id = $1';

  if (cursor) {
    where += ' AND e.start_time > $3';
    params.push(cursor);
  }

  const { rows } = await pool.query(
    `SELECT e.*, c.name as court_name
     FROM events e
     LEFT JOIN courts c ON e.court_id = c.id
     ${where}
     ORDER BY e.start_time ASC
     LIMIT $2`,
    params,
  );

  const hasMore = rows.length > limit;
  if (hasMore) rows.pop();

  return {
    data: rows,
    nextCursor: hasMore ? rows[rows.length - 1].start_time : null,
  };
}

query-returning

Use RETURNING clause to avoid a second SELECT after INSERT/UPDATE.

typescript

const {
  rows: [event],
} = await pool.query(
  `INSERT INTO events (group_id, title, court_id, start_time, end_time, capacity)
   VALUES ($1, $2, $3, $4, $5, $6)
   RETURNING *`,
  [groupId, title, courtId, startTime, endTime, capacity],
);