How to Implement Offset Pagination with FastCRUD: A Complete Guide to SQL Query Limiting

FastCRUD provides a built-in offset pagination system that works out-of-the-box through PaginatedRequestQuery, automatic offset calculation, and the get_multi method, requiring no manual SQL writing.

The benavlabs/fastcrud library handles offset pagination automatically for both programmatic CRUD operations and auto-generated FastAPI endpoints. This guide explains the internal architecture and provides production-ready examples for implementing cursor-style and page-based pagination in your applications.

Understanding FastCRUD's Pagination Architecture

FastCRUD implements offset pagination through a coordinated pipeline of validation, calculation, and SQL construction. The system processes requests through specific components defined in the source code.

PaginatedRequestQuery Parameter Parsing

The PaginatedRequestQuery Pydantic model in fastcrud/core/pagination.py (lines 79-124) parses incoming HTTP query strings. It captures offset, limit, page, items_per_page, and sort parameters, making them available to downstream CRUD operations.

Offset Calculation and Validation

When clients use page-based navigation, the compute_offset helper function (lines 21-34 in fastcrud/core/pagination.py) converts page numbers to raw offsets using the formula offset = (page - 1) * items_per_page.

Before any SQL executes, validate_pagination_params in fastcrud/crud/validation.py (lines 52-65) guards against negative values, ensuring database safety.

SQL Generation and Execution

The SQLQueryBuilder.apply_pagination method in fastcrud/core/query/builder.py (lines 105-126) injects OFFSET and LIMIT clauses into SQLAlchemy SELECT statements. Finally, FastCRUD.get_multi in fastcrud/crud/fast_crud.py (lines 1275-1285) orchestrates the query execution and optional total count retrieval.

Implementing Offset Pagination in Practice

Direct Offset-Based Queries

Use raw offset values when implementing infinite scroll or cursor-based navigation. Pass explicit offset and limit values to get_multi:

from fastcrud import FastCRUD

# Assume user_crud is a FastCRUD instance for a User model

# db is an async SQLAlchemy session

# Retrieve first 10 records (items 0-9)

first_page = await user_crud.get_multi(
    db,
    offset=0,
    limit=10,
    sort_columns="created_at",
    sort_orders="desc",
    return_total_count=True,
)

# Returns: {

#   "data": [...], 

#   "total_count": 237, 

#   "has_more": True,

#   "page": None, 

#   "items_per_page": None

# }

# Retrieve next 10 records (items 10-19)

second_page = await user_crud.get_multi(
    db,
    offset=10,
    limit=10,
    sort_columns="created_at",
    sort_orders="desc",
)

The library treats offset=0 as a legitimate value; only negative numbers trigger validation errors in validate_pagination_params.

Page-Based Navigation

For traditional page-number interfaces, FastCRUD automatically calculates offsets when you provide page and items_per_page. The compute_offset function handles the translation:

from fastapi import Depends
from fastcrud import PaginatedRequestQuery, compute_offset

async def list_users(
    db: AsyncSession = Depends(get_db),
    q: Annotated[PaginatedRequestQuery, Depends()] = Depends(),
):
    # When client sends ?page=3&itemsPerPage=15, compute_offset calculates offset=30

    offset = q.offset
    if offset is None and q.page is not None:
        offset = compute_offset(q.page, q.items_per_page)
    
    return await user_crud.get_multi(
        db,
        offset=offset,
        limit=q.limit,
        sort_columns=q.sort,
        return_total_count=True,
    )

When a client calls GET /users?page=3&itemsPerPage=15, the SQL receives OFFSET 30 LIMIT 15.

Custom Endpoint Integration

For endpoints requiring explicit pagination control, manually construct the offset logic:

from fastapi import APIRouter, Depends
from fastcrud import PaginatedRequestQuery, compute_offset

router = APIRouter()

@router.get("/custom/items")
async def custom_items(
    db: AsyncSession = Depends(get_db),
    q: Annotated[PaginatedRequestQuery, Depends()] = Depends(),
):
    # Handle both offset and page-based parameters

    offset = q.offset
    if offset is None and q.page is not None:
        offset = compute_offset(q.page, q.items_per_page)
    
    data = await item_crud.get_multi(
        db,
        offset=offset,
        limit=q.limit,
        return_total_count=False,  # Skip count query for performance

    )
    
    return {
        "offset": offset, 
        "limit": q.limit, 
        "data": data
    }

This approach supports both /custom/items?offset=10&limit=50 and /custom/items?page=2&itemsPerPage=20 patterns, returning the calculated offset in the response.

Summary

• FastCRUD provides native offset pagination through the get_multi method without requiring manual SQL.
• PaginatedRequestQuery automatically parses pagination parameters from HTTP requests in fastcrud/core/pagination.py.
• compute_offset translates page numbers to SQL offsets using (page - 1) * items_per_page.
• validate_pagination_params in fastcrud/crud/validation.py prevents negative pagination values before database execution.
• SQLQueryBuilder.apply_pagination injects OFFSET and LIMIT clauses in fastcrud/core/query/builder.py.
• paginated_response generates standardized JSON responses containing total_count, has_more, and navigation metadata.

Frequently Asked Questions

How does FastCRUD handle negative offset or limit values?

The validate_pagination_params function in fastcrud/crud/validation.py raises a validation error if either offset or limit is negative. Zero is treated as a valid value, allowing queries starting from the first record.

Can I use both offset and page parameters in the same request?

While technically possible, FastCRUD prioritizes explicit offset values over calculated ones. If q.offset is not None, the system ignores page and items_per_page. Best practice is to use one pattern consistently per endpoint.

Does using return_total_count=True impact performance?

Yes. When return_total_count is enabled, FastCRUD executes a separate COUNT(*) query in addition to the data retrieval query. For large datasets or high-traffic endpoints, consider caching the total count or using return_total_count=False with alternative pagination indicators like has_more.

What is the maximum limit value allowed?

FastCRUD relies on validate_pagination_params in fastcrud/crud/validation.py (lines 52-65), which primarily checks for negative values. The actual maximum limit depends on your database configuration and FastAPI's Pydantic validation rules defined in PaginatedRequestQuery. Always set reasonable upper bounds in your API layer to prevent memory exhaustion.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}