API Pagination

You are an API design expert. The user wants to implement cursor-based or offset pagination to handle large datasets efficiently.

What to check first

• Verify your API framework supports query parameters (Express, FastAPI, Django, etc.)
• Confirm your database has proper indexing on the field you'll paginate by (run EXPLAIN QUERY PLAN or equivalent for your DB)
• Check if you're using an ORM (Sequelize, SQLAlchemy, Prisma) — this affects pagination syntax

Steps

1. Choose pagination strategy: cursor-based (use encoded position markers) for better performance at scale, offset-based (LIMIT/OFFSET) for simpler implementation with smaller datasets
2. Define your query parameter names: ?limit=20&offset=0 for offset or ?limit=20&cursor=abc123 for cursor-based
3. Set a maximum page size limit (e.g., 100) to prevent resource exhaustion — validate limit <= maxLimit
4. For offset pagination, calculate offset = (pageNumber - 1) * pageSize and apply LIMIT pageSize OFFSET offset
5. For cursor pagination, encode the last item's ID or timestamp as a base64 cursor string: Buffer.from(JSON.stringify({id: lastId})).toString('base64')
6. Return pagination metadata in response: { data: items, pagination: { limit, offset/cursor, hasMore, nextCursor } }
7. Add index on pagination column: CREATE INDEX idx_created_at ON users(created_at DESC) for efficient sorting
8. Test with large datasets and measure query performance — cursor should handle millions of rows; offset degrades after ~10k rows

Code

// Express.js with cursor-based pagination
const express = require('express');
const app = express();

// Mock database query function
async function getUsers(limit, cursor) {
  const maxLimit = 100;
  const pageSize = Math.min(limit || 20, maxLimit);
  
  let query = 'SELECT id, name, email, created_at FROM users';
  const params = [];
  
  if (cursor) {
    const decoded = JSON.parse(Buffer.from(cursor, 'base64').toString());
    query += ' WHERE created_at < ? OR (created_at = ? AND id < ?)';
    params.push(decoded.createdAt, decoded.createdAt, decoded.id);
  }
  
  query += ' ORDER BY created_at DESC, id DESC LIMIT ?';
  params.push(pageSize + 1); // Fetch one extra to detect hasMore
  
  // Simulate database call
  const results = [
    { id: 1, name: 'Alice', email: 'alice@example.com', created_at: '2024-01-10' },
    { id: 2, name: 'Bob', email: 'bob@example.com', created_at: '2024-01-09' },

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Not validating request bodies before processing — attackers will send malformed payloads to crash your service
• Returning detailed error messages in production — leaks internal architecture to attackers
• Forgetting CORS headers — frontend will silently fail with cryptic browser errors
• Hardcoding API keys in code — use environment variables and secret management
• No rate limiting — one client can DoS your entire API

When NOT to Use This Skill

• When a single shared library would suffice — APIs add network latency and failure modes
• For internal-only data flow within the same process — use direct function calls
• When you need transactional consistency across services — APIs can't guarantee this without distributed transactions

How to Verify It Worked

• Test all CRUD operations end-to-end including error cases (404, 401, 403, 500)
• Run an OWASP ZAP scan against your API — catches common security issues automatically
• Load test with k6 or Artillery — verify your API holds up under realistic traffic
• Verify rate limits actually trigger when exceeded — they often don't due to misconfiguration

Production Considerations

• Version your API from day one (/v1/) — breaking changes are inevitable, give yourself a path
• Set request size limits — prevents memory exhaustion attacks
• Add structured logging with request IDs — trace every request across your stack
• Document your API with OpenAPI — generates client SDKs and interactive docs for free