Pros: Simple, supports "jump to page" Cons: Slow on large datasets, inconsistent with concurrent writes

Cursor Pagination (Recommended)

GET /users?cursor=eyJpZCI6MTIzfQ&limit=20

Pros: Fast, consistent, works with real-time data Cons: No "jump to page", slightly more complex

TypeScript Implementation

Cursor Pagination

// pagination.ts
interface PaginationParams {
  cursor?: string;
  limit?: number;
  direction?: 'forward' | 'backward';
}

interface PaginatedResult<T> {
  data: T[];
  pagination: {
    hasMore: boolean;
    nextCursor: string | null;
    prevCursor: string | null;
    total?: number;
  };
}

function encodeCursor(data: Record<string, unknown>): string {
  return Buffer.from(JSON.stringify(data)).toString('base64url');
}

function decodeCursor(cursor: string): Record<string, unknown> {
  return JSON.parse(Buffer.from(cursor, 'base64url').toString());
}

async function paginate<T extends { id: string; createdAt: Date }>(
  query: (where: any, orderBy: any, take: number) => Promise<T[]>,
  params: PaginationParams,
  defaultLimit = 20,
  maxLimit = 100
): Promise<PaginatedResult<T>> {
  const limit = Math.min(params.limit || defaultLimit, maxLimit);
  const direction = params.direction || 'forward';

  let where: any = {};
  let orderBy: any = { createdAt: 'desc', id: 'desc' };

  if (params.cursor) {
    const decoded = decodeCursor(params.cursor);
    
    if (direction === 'forward') {
      where = {
        OR: [
          { createdAt: { lt: decoded.createdAt } },
          { createdAt: decoded.createdAt, id: { lt: decoded.id } },
        ],
      };
    } else {
      where = {
        OR: [
          { createdAt: { gt: decoded.createdAt } },
          { createdAt: decoded.createdAt, id: { gt: decoded.id } },
        ],
      };
      orderBy = { createdAt: 'asc', id: 'asc' };
    }
  }

  // Fetch one extra to check if there's more
  const items = await query(where, orderBy, limit + 1);
  const hasMore = items.length > limit;
  const data = hasMore ? items.slice(0, limit) : items;

  // Reverse if going backward
  if (direction === 'backward') {
    data.reverse();
  }

  return {
    data,
    pagination: {
      hasMore,
      nextCursor: data.length > 0
        ? encodeCursor({ createdAt: data[data.length - 1].createdAt, id: data[data.length - 1].id })
        : null,
      prevCursor: data.length > 0
        ? encodeCursor({ createdAt: data[0].createdAt, id: data[0].id })
        : null,
    },
  };
}

export { paginate, PaginationParams, PaginatedResult, encodeCursor, decodeCursor };

Express Route

// users-route.ts
import { paginate } from './pagination';

router.get('/users', async (req, res) => {
  const { cursor, limit } = req.query;

  const result = await paginate(
    (where, orderBy, take) =>
      db.users.findMany({ where, orderBy, take }),
    { cursor: cursor as string, limit: Number(limit) || 20 }
  );

  res.json(result);
});

Response Format

{
  "data": [
    { "id": "user_123", "name": "Alice", "createdAt": "2024-01-15T10:00:00Z" },
    { "id": "user_122", "name": "Bob", "createdAt": "2024-01-14T09:00:00Z" }
  ],
  "pagination": {
    "hasMore": true,
    "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI0LTAxLTE0VDA5OjAwOjAwWiIsImlkIjoidXNlcl8xMjIifQ",
    "prevCursor": "eyJjcmVhdGVkQXQiOiIyMDI0LTAxLTE1VDEwOjAwOjAwWiIsImlkIjoidXNlcl8xMjMifQ"
  }
}

Python Implementation

# pagination.py
import base64
import json
from dataclasses import dataclass
from typing import TypeVar, Generic, Callable, Optional

T = TypeVar('T')

@dataclass
class PaginatedResult(Generic[T]):
    data: list[T]
    has_more: bool
    next_cursor: Optional[str]
    prev_cursor: Optional[str]

def encode_cursor(data: dict) -> str:
    return base64.urlsafe_b64encode(json.dumps(data).encode()).decode()

def decode_cursor(cursor: str) -> dict:
    return json.loads(base64.urlsafe_b64decode(cursor).decode())

async def paginate(
    query_fn: Callable,
    cursor: Optional[str] = None,
    limit: int = 20,
    max_limit: int = 100,
) -> PaginatedResult:
    limit = min(limit, max_limit)
    
    filters = {}
    if cursor:
        decoded = decode_cursor(cursor)
        filters = {"created_at__lt": decoded["created_at"]}

    items = await query_fn(filters, limit + 1)
    has_more = len(items) > limit
    data = items[:limit] if has_more else items

    return PaginatedResult(
        data=data,
        has_more=has_more,
        next_cursor=encode_cursor({"created_at": data[-1].created_at.isoformat()}) if data else None,
        prev_cursor=encode_cursor({"created_at": data[0].created_at.isoformat()}) if data else None,
    )

FastAPI Route

@router.get("/users")
async def list_users(cursor: str = None, limit: int = 20):
    async def query(filters, take):
        return await db.users.find_many(
            where=filters,
            order_by={"created_at": "desc"},
            take=take,
        )

    result = await paginate(query, cursor=cursor, limit=limit)
    return {
        "data": result.data,
        "pagination": {
            "hasMore": result.has_more,
            "nextCursor": result.next_cursor,
        },
    }

Database Optimization

-- Essential index for cursor pagination
CREATE INDEX idx_users_pagination ON users(created_at DESC, id DESC);

-- For filtered pagination
CREATE INDEX idx_users_org_pagination ON users(organization_id, created_at DESC, id DESC);

Frontend Integration

// useInfiniteQuery with cursor pagination
function useUsers() {
  return useInfiniteQuery({
    queryKey: ['users'],
    queryFn: ({ pageParam }) =>
      fetch(`/api/users?cursor=${pageParam || ''}`).then(r => r.json()),
    getNextPageParam: (lastPage) =>
      lastPage.pagination.hasMore ? lastPage.pagination.nextCursor : undefined,
  });
}

// Usage
const { data, fetchNextPage, hasNextPage, isFetchingNextPage } = useUsers();

const allUsers = data?.pages.flatMap(page => page.data) ?? [];

Best Practices

Always use stable sort - Include ID in sort to handle ties
Index your sort columns - Pagination is only fast with proper indexes
Limit the limit - Cap maximum page size (100 is reasonable)
Use cursor for real-time data - Offset breaks with concurrent writes
Include total count sparingly - COUNT(*) is expensive on large tables

Common Mistakes

Using OFFSET on large tables (scans all skipped rows)
Not including ID in cursor (unstable with same timestamps)
Missing index on sort columns
Returning total count on every request
Not handling deleted items between pages

pagination

API Pagination

When to Use This Skill

Pagination Strategies

Offset Pagination (Simple)