express-api-patterns

name	express-api-patterns
description	Express.js API patterns for TypeScript backends with raw SQL (pg), JWT auth, RBAC, and Zod validation. This skill should be used when writing API routes, middleware, database queries, or auth logic. Triggers on tasks involving Express endpoints, middleware, SQL queries, authentication, or authorization.
license	MIT
metadata	{"author":"tennis-team","version":"1.0.0"}

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

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