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

Rules

SQL Safety (CRITICAL)

sql-parameterized

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

Incorrect: SQL injection vulnerability

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

Correct: parameterized query

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

sql-transactions

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

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.

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.

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

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.

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.

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.

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.

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.

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.

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],
);