Skip to content

Pagination, Filtering, and Sorting in REST APIs — Complete Guide

DodaTech Updated 2026-06-23 9 min read

In this tutorial, you'll learn about Pagination, Filtering, and Sorting in REST APIs. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Pagination, filtering, and sorting are API patterns that allow clients to retrieve large datasets in manageable chunks, narrow results by specific criteria, and control the order of returned data without overloading the server or client.

What You'll Learn

You will learn offset-based and Cursor-based pagination, query parameter conventions for filtering and sorting, efficient database queries with indexes, and client-side best practices for fetching paginated data.

Why Pagination, Filtering, and Sorting Matter

APIs that return all records at once crash under large datasets. A single request for 100,000 records consumes excessive memory, bandwidth, and processing time. Pagination limits response sizes, filtering reduces data transfer, and sorting ensures consistent ordering. These patterns are essential for any API serving more than a few dozen records.

Real-World Use

DodaTech platforms handle millions of records. Doda Browser sync paginates bookmark lists, DodaZIP file browser filters archives by type and sorts by date, and Durga Antivirus Pro threat feed uses Cursor-based pagination for efficient real-time threat data streaming.

Pagination, Filtering, and Sorting Learning Path

flowchart LR
  A[REST API Design] --> B[Offset Pagination]
  A --> C[Cursor Pagination]
  B --> D[Filtering]
  C --> D
  D --> E[Sorting]
  E --> F[Database Optimization]
  F --> G[Client Implementation]
  B:::current

  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Prerequisites

Understand RESTful Api Design Best Practices and HTTP Protocol Basics. Familiarity with API Development Concepts and basic SQL query knowledge is required.

Offset-Based Pagination

The simplest approach: skip N records and return M records.

Request Format

GET /api/users?page=2&limit=20

Server Implementation (Express.js)

app.get("/api/users", async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const offset = (page - 1) * limit;

  const [users, total] = await Promise.all([
    db.query("SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2", [limit, offset]),
    db.query("SELECT COUNT(*) FROM users")
  ]);

  res.json({
    data: users.rows,
    pagination: {
      page,
      limit,
      total: parseInt(total.rows[0].count),
      pages: Math.ceil(parseInt(total.rows[0].count) / limit),
      hasNext: page * limit < parseInt(total.rows[0].count),
      hasPrev: page > 1
    }
  });
});

Expected Response

{
  "data": [
    { "id": 21, "name": "Alice", "email": "alice"@example".com" },
    { "id": 22, "name": "Bob", "email": "bob"@example".com" }
  ],
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 95,
    "pages": 5,
    "hasNext": true,
    "hasPrev": true
  }
}

Pros and Cons

Pros Cons
Simple to implement Inefficient for large offsets
Works with any database Results shift if data changes
Easy to jump to any page Performance degrades at high page numbers
Intuitive for clients Duplicate records on page boundaries

Cursor-Based Pagination

Use a Cursor (opaque token) that points to a specific record.

Request Format

GET /api/users?cursor=eyJpZCI6IDIwfQ==&limit=20

Server Implementation

app.get("/api/users", async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const cursor = req.query.cursor
    ? JSON.parse(Buffer.from(req.query.cursor, "base64").toString())
    : null;

  let query;
  let params;

  if (cursor) {
    query = `
      SELECT * FROM users
      WHERE (created_at, id) < ($1, $2)
      ORDER BY created_at DESC, id DESC
      LIMIT $3
    `;
    params = [cursor.createdAt, cursor.id, limit];
  } else {
    query = `
      SELECT * FROM users
      ORDER BY created_at DESC, id DESC
      LIMIT $1
    `;
    params = [limit];
  }

  const result = await db.query(query, params);
  const users = result.rows;

  // Generate next cursor from last record
  let nextCursor = null;
  if (users.length === limit) {
    const last = users[users.length - 1];
    nextCursor = Buffer.from(
      JSON.stringify({ createdAt: last.created_at, id: last.id })
    ).toString("base64");
  }

  res.json({
    data: users,
    pagination: {
      limit,
      nextCursor,
      hasMore: users.length === limit
    }
  });
});

Expected Response

{
  "data": [
    { "id": 20, "name": "Tina", "email": "tina"@example".com" },
    { "id": 19, "name": "Steve", "email": "steve"@example".com" }
  ],
  "pagination": {
    "limit": 20,
    "nextCursor": "eyJjcmVhdGVkQXQiOiAiMjAyNi0wNi0yM1QwOTowMDowMFoiLCAiaWQiOiAxOX0=",
    "hasMore": true
  }
}

Pros and Cons

Pros Cons
Consistent results even with data changes Cannot jump to arbitrary pages
Efficient for large datasets More complex to implement
No offset performance penalty Requires unique, sortable Cursor field
Stable for real-time data Base64 encoding adds minimal overhead

Filtering

Query Parameter Conventions

# Exact match
GET /api/users?role=admin

# Multiple values
GET /api/users?role=admin,member

# Partial match (search)
GET /api/users?search=alice

# Range filters
GET /api/products?price_min=10&price_max=100
GET /api/orders?created_after=2026-01-01&created_before=2026-06-23

# Status filters
GET /api/orders?status=pending,shipped

# Negation
GET /api/users?role!=admin

Implementation

app.get("/api/users", async (req, res) => {
  const { search, role, status } = req.query;
  const conditions = [];
  const params = [];
  let paramIndex = 1;

  if (search) {
    conditions.push(`(name ILIKE $${paramIndex} OR email ILIKE $${paramIndex})`);
    params.push(`%${search}%`);
    paramIndex++;
  }

  if (role) {
    const roles = role.split(",");
    const placeholders = roles.map(() => `$${paramIndex++}`);
    conditions.push(`role IN (${placeholders.join(", ")})`);
    params.push(...roles);
  }

  if (status) {
    const statuses = status.split(",");
    const placeholders = statuses.map(() => `$${paramIndex++}`);
    conditions.push(`status IN (${placeholders.join(", ")})`);
    params.push(...statuses);
  }

  const whereClause = conditions.length > 0
    ? `WHERE ${conditions.join(" AND ")}`
    : "";

  const query = `SELECT * FROM users ${whereClause} ORDER BY created_at DESC`;
  const result = await db.query(query, params);

  res.json({ data: result.rows });
});

Sorting

Query Parameter Convention

# Single field sort (ascending default)
GET /api/users?sort=name

# Descending sort
GET /api/users?sort=-name

# Multiple field sort
GET /api/users?sort=-created_at,name

# Explicit direction
GET /api/users?sort=name:asc,created_at:desc

Implementation

app.get("/api/users", async (req, res) => {
  const { sort } = req.query;
  const allowedSortFields = ["name", "email", "role", "created_at"];

  let orderClause = "ORDER BY created_at DESC";

  if (sort) {
    const sortFields = sort.split(",").map((field) => {
      let direction = "ASC";
      let fieldName = field;

      if (field.startsWith("-")) {
        direction = "DESC";
        fieldName = field.slice(1);
      } else if (field.includes(":")) {
        const parts = field.split(":");
        fieldName = parts[0];
        direction = parts[1].toUpperCase() === "DESC" ? "DESC" : "ASC";
      }

      // Whitelist allowed fields to prevent SQL injection
      if (!allowedSortFields.includes(fieldName)) {
        return null;
      }

      return `${fieldName} ${direction}`;
    }).filter(Boolean);

    if (sortFields.length > 0) {
      orderClause = `ORDER BY ${sortFields.join(", ")}`;
    }
  }

  const query = `SELECT * FROM users ${orderClause} LIMIT $1 OFFSET $2`;
  const result = await db.query(query, [limit, offset]);

  res.json({ data: result.rows });
});

Filtering with FastAPI

from fastapi import FastAPI, Query
from typing import Optional

app = FastAPI()

@app.get("/api/users")
async def list_users(
    page: int = Query(1, ge=1),
    limit: int = Query(20, ge=1, le=100),
    search: Optional[str] = None,
    role: Optional[str] = None,
    sort: str = Query("-created_at", regex="^[a-zA-Z_,:-]+$")
):
    # Build query dynamically
    query = "SELECT * FROM users WHERE 1=1"
    params = []

    if search:
        query += " AND (name ILIKE %s OR email ILIKE %s)"
        params.extend([f"%{search}%", f"%{search}%"])

    if role:
        roles = role.split(",")
        placeholders = ", ".join(["%s"] * len(roles))
        query += f" AND role IN ({placeholders})"
        params.extend(roles)

    # Sort whitelist
    allowed_sort = {"name", "email", "role", "created_at"}
    sort_clauses = []

    for field in sort.split(","):
        direction = "DESC" if field.startswith("-") else "ASC"
        clean_field = field.lstrip("-")
        if clean_field in allowed_sort:
            sort_clauses.append(f"{clean_field} {direction}")

    if sort_clauses:
        query += f" ORDER BY {', '.join(sort_clauses)}"

    offset = (page - 1) * limit
    query += " LIMIT %s OFFSET %s"
    params.extend([limit, offset])

    return {"data": execute_query(query, params)}

Combined Example

GET /api/users?search=alice&role=admin&sort=-created_at,name&page=1&limit=20

This request searches for users matching "alice", filters by admin role, sorts by creation date descending then name ascending, and returns page 1 with 20 records per page.

Database Optimization

Indexes for Pagination

-- For offset pagination
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- For Cursor pagination (Composite index)
CREATE INDEX idx_users_Cursor ON users(created_at DESC, id DESC);

-- For filtering
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_email ON users(email);

-- For full-text search
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

EXPLAIN ANALYZE for Large Offsets

EXPLAIN ANALYZE
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- Result: 10020 rows scanned, then 20 returned

EXPLAIN ANALYZE
SELECT * FROM users WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 20;
-- Result: 20 rows scanned

Common Errors

  1. No maximum limit — Allowing clients to request unlimited records (limit=1000000). Always cap the maximum limit (usually 100). Return an error if the limit exceeds the maximum.

  2. Using offset for large datasets — Offset-based pagination scans all skipped rows. For datasets over 10,000 records, Cursor-based pagination is necessary for performance.

  3. Sorting by user input without whitelist — Allowing raw SQL field names in sort parameters enables SQL Injection. Always validate sort fields against a whitelist.

  4. Filtering without indexes — Running filters on unindexed columns causes full table scans. Add database indexes for all filterable and sortable columns.

  5. Inconsistent pagination metadata — Some endpoints return totalPages, others return hasMore. Choose one convention and use it consistently across all endpoints.

  6. Case-sensitive search — Using LIKE instead of ILIKE in PostgreSQL or LOWER() for case-insensitive search. Always use case-insensitive matching for text search unless case sensitivity is specifically required.

  7. Not escaping special characters in search — Users searching for "O'Brien" or "100%" can break queries. Use parameterized queries and escape LIKE special characters.

Practice Questions

  1. What are the advantages of Cursor-based pagination over offset-based pagination?
  2. How do you prevent SQL Injection in sort parameters?
  3. What database indexes should you create for paginated queries?
  4. How do you implement multi-field sorting?
  5. What is the recommended maximum limit for paginated responses?

Challenge

Build a paginated, filterable, sortable API for an e-commerce product catalog with 100,000 products. Implement offset-based pagination for the first 10 pages and Cursor-based pagination beyond that, filtering by category, price range, and in-stock status, sorting by price, name, rating, and creation date, search across name and description with full-text search, and database indexes for all filterable and sortable columns.

FAQ

Which pagination method should I use? Use offset pagination for small, static datasets where users need to jump to specific pages. Use Cursor pagination for large, dynamic datasets, real-time feeds, or Infinite Scroll interfaces. Cursor pagination is more performant and consistent.

How do I handle filtering with special characters? Use parameterized queries to handle special characters safely. For PostgreSQL ILIKE searches, escape % and _ characters by prepending a backslash. Use pg-promise or psycopg2 parameterization.

Should I include total count in paginated responses? Include total count for offset pagination (for page calculation). For Cursor pagination, total count is optional and expensive to compute on large datasets. Use hasMore instead.

How do I paginate real-time data that changes frequently? Use Cursor-based pagination with a stable sort key like creation timestamp combined with a unique ID. Avoid offset pagination because records shift between pages when data is inserted or deleted.

Can I combine filtering with Cursor pagination? Yes. Apply the WHERE filters before the Cursor condition. Ensure the Cursor includes enough fields to maintain a unique position within the filtered result set. Create Composite indexes that cover both filter and sort columns.

← Previous Error Handling in REST APIs — Status Codes and Responses Guide Next → API Gateway Concepts — Kong, NGINX, and AWS API Gateway Guide

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Api Development