API Pagination

Return large datasets efficiently without killing your database.

When to Use This Skill

• List endpoints returning many items

• Infinite scroll UIs

• Data export features

• Any endpoint that could return 100+ items

Pagination Strategies

Offset Pagination (Simple)

GET /users?page=2&limit=20

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