Add Pagination to List Endpoints

You've built a gorgeous REST API. Your GET /api/items endpoint returns every row in the database — all 500,000 of them. The first user fires off a request, waits 8 seconds, and gets a giant JSON blob that crashes their browser. The second user's request locks the database. Sounds familiar? That's the moment you realize: every list endpoint needs pagination. Without it, your API is slow, memory-hungry, and frankly, unusable at scale. In this lesson, you'll learn how to add pagination to list endpoints the right way — with clean code, predictable behavior, and zero risk of breaking your existing clients.

The problem this lesson solves

At first, a list endpoint seems trivial: SELECT * FROM items and return it. But as your data grows, this naive approach falls apart in three brutal ways:

• Response size explodes. A 10 MB JSON response is terrible for mobile users and makes debugging impossible.
• Latency spikes. The database has to scan, transfer, and serialize every row, even though the client only shows 20 at a time.
• Memory pressure. Your web server loads all records into RAM before sending them, which can starve other requests in a multi-threaded app.

The core problem: without pagination, request time and memory usage scale linearly with your dataset. With pagination, they stay constant. That's the difference between an API that barely works at 1k rows and one that sails at 1M rows.

Also, think about the client side. A mobile app only shows 10 items per screen. A dashboard only loads the first page of logs. Pagination aligns your backend with what the frontend actually needs, which reduces bandwidth, improves perceived speed, and lets users jump to a specific page (like 'page 5 of 20').

Core concept / mental model

Think of a library. You don't ask the librarian for every book in the building. You browse shelf by shelf, or you ask for a specific range of shelves. Pagination is the same: you ask the API for a slice of the full dataset.

There are two main flavors:

1. Offset-based (page number & size) — you say 'give me items 21–30' by passing ?page=3&size=10. Easy to implement, but slow when pages get deep because the database still traverses all skipped rows.

2. Cursor-based (keyset) — you say 'give me the next 10 items after the one with ID 2043' by passing ?cursor=2043. Faster for large datasets and stable if new items are added, but more complex to implement.

A mental map of a paginated response

Request:  GET /api/items?page=2&size=20
Response: {
  "items": [ ... 20 records ... ],
  "pagination": {
    "page": 2,
    "size": 20,
    "total": 145,
    "total_pages": 8,
    "has_next": true,
    "has_prev": true
  }
}

The key insight: the client never sees the whole dataset. It only sees what it asked for, plus a small metadata block that tells it where the next slice lives.

How it works step by step

Here's the logical sequence to add pagination to any list endpoint — whether you're using Flask, Django, FastAPI, or a plain WSGI app (though we'll use Flask in the hands-on section).

1. Accept query parameters — usually page (or offset) and size (or limit). Validate them to avoid negative numbers or absurd sizes.

2. Extract query params from the request object — they come as strings, so convert to integers.

3. Compute the offset — in offset-based pagination, offset = (page - 1) * size. For page 1, offset is 0; for page 2 with size 20, offset is 20.

4. Query the database with LIMIT & OFFSET — e.g., SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 20. This grabs only the rows you need.

5. Add metadata — compute total count with SELECT COUNT(*), then calculate total_pages as ceil(total / size). Include has_next and has_prev so clients can present navigation.

6. Serialize and return — wrap the list of records and the metadata in a consistent envelope that clients can parse.

Pro tip: Always include ORDER BY when paginating. Without a stable sort order, rows may shift between pages, causing duplicates or missing records.

Hands-on walkthrough

Let's implement a minimal but complete example using Flask (you can adapt the same pattern to Django or FastAPI). We'll use a simple in-memory list to represent database rows.

Step 1: The naive (broken) endpoint

from flask import Flask, jsonify

app = Flask(__name__)

# Fake database: 150 items
ITEMS = [{"id": i, "name": f"Item {i}"} for i in range(1, 151)]

@app.route("/api/items")
def list_items():
    return jsonify(ITEMS)

if __name__ == "__main__":
    app.run(debug=True)

This returns all 150 records. Now let's add pagination.

Step 2: Add pagination parameters

from flask import Flask, jsonify, request
import math

app = Flask(__name__)
ITEMS = [{"id": i, "name": f"Item {i}"} for i in range(1, 151)]

@app.route("/api/items")
def list_items():
    # Default: page 1, size 10
    try:
        page = int(request.args.get("page", 1))
        size = int(request.args.get("size", 10))
    except ValueError:
        return jsonify({"error": "page and size must be integers"}), 400

    # Validate bounds
    if page < 1 or size < 1 or size > 100:
        return jsonify({"error": "page must be >= 1 and size between 1 and 100"}), 400

    total = len(ITEMS)
    total_pages = math.ceil(total / size)
    if page > total_pages:
        return jsonify({"error": "page out of range", "total_pages": total_pages}), 404

    start = (page - 1) * size
    end = start + size
    page_items = ITEMS[start:end]

    return jsonify({
        "items": page_items,
        "pagination": {
            "page": page,
            "size": size,
            "total": total,
            "total_pages": total_pages,
            "has_next": page < total_pages,
            "has_prev": page > 1
        }
    })

if __name__ == "__main__":
    app.run(debug=True)

Test it — request GET /api/items?page=2&size=20. The response will contain exactly 20 items (IDs 21–40) and metadata showing page: 2, total_pages: 8.

Step 3: Adding pagination to a SQL query (using SQLAlchemy-style)

When you have a real database, the principle is identical but you use SQL's LIMIT and OFFSET:

# Assume you have a SQLAlchemy model Item
from sqlalchemy.orm import Session

session = Session()

page, size = 2, 20
offset = (page - 1) * size

# Use a stable order
records = session.query(Item).order_by(Item.id).limit(size).offset(offset).all()
total = session.query(Item).count()

The offset is exactly the start we calculated earlier. The key is to always use an index on the ORDER BY column to keep the query fast.

Compare options / when to choose what

Not all pagination is created equal. Here's a comparison to help you choose.

When to choose what:

• Use page/size for internal tools, small tables, or when you need to jump to a specific page number (e.g., 'go to page 5').
• Use cursor when your dataset is huge or data is constantly inserted (like a live feed) — it prevents missing or duplicate items.

Variation: Some APIs use offset & limit instead of page & size. That's functionally identical — just pick one and document it. Another popular approach is to return a next link in the response, which is a simplified cursor pattern.

Troubleshooting & edge cases

Even a tiny mistake can break your API. Here are the most common pitfalls:

• Out-of-range page numbers. If page is 50 but there are only 8 pages, you get an empty list. Return a 404 with helpful info, as we did, or return an empty items array — but be consistent.
• Negative or zero values. page=0 or size=-5 cause weird behavior. Always validate >= 1. Also cap size to avoid someone requesting 10,000 items.
• String size values. ?size=abc raises a ValueError. Catch it and return a 400, not a 500.
• Duplicate or missing records when new data arrives. If a new row is inserted between page requests, offset pagination can show the same row on two pages. Use cursor pagination if this matters.
• Large offsets are slow. The database still scans all skipped rows. For page=10000 with size 20, that's 200,000 rows scanned. Use indexes or switch to cursor pagination.
• Forgetting ORDER BY. Without it, the database may return rows in arbitrary order, breaking pagination consistency. Always order by a unique column.

What you learned & what's next

Now you understand the core problem that add pagination to list endpoints solves: protecting your API from runaway queries and delivering predictable, fast responses. You've learned the two main strategies (offset vs. cursor), implemented a full paginated endpoint in Flask, validated inputs, and built metadata that clients can use for navigation.

You can now:

• Explain why pagination is a non-negotiable part of API design.
• Implement page/size parameters with proper validation.
• Attach pagination metadata (total, has_next, etc.) to your JSON responses.
• Use LIMIT and OFFSET in SQL queries for safe pagination.
• Troubleshoot common edge cases like out-of-range pages and string parameters.

Next step in the track: In the next lesson, you'll learn how to secure your list endpoints with authentication and authorization — because a paginated API that leaks private data isn't worth much. Get ready to add token validation and role-based access control to the endpoints you just mastered.