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

> Learn how to implement offset pagination with FastCRUD easily. Utilize built-in features like PaginatedRequestQuery and get multi method for efficient SQL query limiting without manual SQL.

- Repository: [Benav Labs/fastcrud](https://github.com/benavlabs/fastcrud)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`:

```python
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:

```python
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:

```python
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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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`](https://github.com/benavlabs/fastcrud/blob/main/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.