API Pagination
API Pagination
This guide shows you how to add pagination to your API endpoints using the boilerplate's built-in utilities. Pagination helps you handle large datasets efficiently.
Quick Start
Here's how to add basic pagination to any endpoint:
from fastcrud.paginated import PaginatedListResponse
@router.get("/", response_model=PaginatedListResponse[UserRead])
async def get_users(
page: int = 1,
items_per_page: int = 10,
db: Annotated[AsyncSession, Depends(async_get_db)]
):
users = await crud_users.get_multi(
db=db,
offset=(page - 1) * items_per_page,
limit=items_per_page,
schema_to_select=UserRead,
return_as_model=True,
return_total_count=True
)
return paginated_response(
crud_data=users,
page=page,
items_per_page=items_per_page
)That's it! Your endpoint now returns paginated results with metadata.
What You Get
The response includes everything frontends need:
{
"data": [
{
"id": 1,
"name": "John Doe",
"username": "johndoe",
"email": "john@example.com"
}
// ... more users
],
"total_count": 150,
"has_more": true,
"page": 1,
"items_per_page": 10,
"total_pages": 15
}Adding Filters
You can easily add filtering to paginated endpoints:
@router.get("/", response_model=PaginatedListResponse[UserRead])
async def get_users(
page: int = 1,
items_per_page: int = 10,
# Add filter parameters
search: str | None = None,
is_active: bool | None = None,
tier_id: int | None = None,
db: Annotated[AsyncSession, Depends(async_get_db)]
):
# Build filters
filters = {}
if search:
filters["name__icontains"] = search # Search by name
if is_active is not None:
filters["is_active"] = is_active
if tier_id:
filters["tier_id"] = tier_id
users = await crud_users.get_multi(
db=db,
offset=(page - 1) * items_per_page,
limit=items_per_page,
schema_to_select=UserRead,
return_as_model=True,
return_total_count=True,
**filters
)
return paginated_response(
crud_data=users,
page=page,
items_per_page=items_per_page
)Now you can call:
/users/?search=john- Find users with "john" in their name/users/?is_active=true- Only active users/users/?tier_id=1&page=2- Users in tier 1, page 2
Adding Sorting
Add sorting options to your paginated endpoints:
@router.get("/", response_model=PaginatedListResponse[UserRead])
async def get_users(
page: int = 1,
items_per_page: int = 10,
# Add sorting parameters
sort_by: str = "created_at",
sort_order: str = "desc",
db: Annotated[AsyncSession, Depends(async_get_db)]
):
users = await crud_users.get_multi(
db=db,
offset=(page - 1) * items_per_page,
limit=items_per_page,
schema_to_select=UserRead,
return_as_model=True,
return_total_count=True,
sort_columns=sort_by,
sort_orders=sort_order
)
return paginated_response(
crud_data=users,
page=page,
items_per_page=items_per_page
)Usage:
/users/?sort_by=name&sort_order=asc- Sort by name A-Z/users/?sort_by=created_at&sort_order=desc- Newest first
Validation
Add validation to prevent issues:
from fastapi import Query
@router.get("/", response_model=PaginatedListResponse[UserRead])
async def get_users(
page: Annotated[int, Query(ge=1)] = 1, # Must be >= 1
items_per_page: Annotated[int, Query(ge=1, le=100)] = 10, # Between 1-100
db: Annotated[AsyncSession, Depends(async_get_db)]
):
# Your pagination logic hereComplete Example
Here's a full-featured paginated endpoint:
@router.get("/", response_model=PaginatedListResponse[UserRead])
async def get_users(
# Pagination
page: Annotated[int, Query(ge=1)] = 1,
items_per_page: Annotated[int, Query(ge=1, le=100)] = 10,
# Filtering
search: Annotated[str | None, Query(max_length=100)] = None,
is_active: bool | None = None,
tier_id: int | None = None,
# Sorting
sort_by: str = "created_at",
sort_order: str = "desc",
db: Annotated[AsyncSession, Depends(async_get_db)]
):
"""Get paginated users with filtering and sorting."""
# Build filters
filters = {"is_deleted": False} # Always exclude deleted users
if is_active is not None:
filters["is_active"] = is_active
if tier_id:
filters["tier_id"] = tier_id
# Handle search
search_criteria = []
if search:
from sqlalchemy import or_, func
search_criteria = [
or_(
func.lower(User.name).contains(search.lower()),
func.lower(User.username).contains(search.lower()),
func.lower(User.email).contains(search.lower())
)
]
users = await crud_users.get_multi(
db=db,
offset=(page - 1) * items_per_page,
limit=items_per_page,
schema_to_select=UserRead,
return_as_model=True,
return_total_count=True,
sort_columns=sort_by,
sort_orders=sort_order,
**filters,
**{"filter_criteria": search_criteria} if search_criteria else {}
)
return paginated_response(
crud_data=users,
page=page,
items_per_page=items_per_page
)This endpoint supports:
/users/- First 10 users/users/?page=2&items_per_page=20- Page 2, 20 items/users/?search=john&is_active=true- Active users named john/users/?sort_by=name&sort_order=asc- Sorted by name
Simple List (No Pagination)
Sometimes you just want a simple list without pagination:
@router.get("/all", response_model=list[UserRead])
async def get_all_users(
limit: int = 100, # Prevent too many results
db: Annotated[AsyncSession, Depends(async_get_db)]
):
users = await crud_users.get_multi(
db=db,
limit=limit,
schema_to_select=UserRead,
return_as_model=True
)
return users["data"]Performance Tips
- Always set a maximum page size:
items_per_page: Annotated[int, Query(ge=1, le=100)] = 10 # Max 100 items- Use
schema_to_selectto only fetch needed fields:
users = await crud_users.get_multi(
schema_to_select=UserRead, # Only fetch UserRead fields
return_as_model=True
)- Add database indexes for columns you sort by:
-- In your migration
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_name ON users(name);Common Patterns
Admin List with All Users
@router.get("/admin", dependencies=[Depends(get_current_superuser)])
async def get_all_users_admin(
include_deleted: bool = False,
page: int = 1,
items_per_page: int = 50,
db: Annotated[AsyncSession, Depends(async_get_db)]
):
filters = {}
if not include_deleted:
filters["is_deleted"] = False
users = await crud_users.get_multi(db=db, **filters)
return paginated_response(users, page, items_per_page)User's Own Items
@router.get("/my-posts", response_model=PaginatedListResponse[PostRead])
async def get_my_posts(
page: int = 1,
items_per_page: int = 10,
current_user: Annotated[dict, Depends(get_current_user)],
db: Annotated[AsyncSession, Depends(async_get_db)]
):
posts = await crud_posts.get_multi(
db=db,
author_id=current_user["id"], # Only user's own posts
offset=(page - 1) * items_per_page,
limit=items_per_page
)
return paginated_response(posts, page, items_per_page)What's Next
Now that you understand pagination:
- Database CRUD - Learn more about the CRUD operations
- Database Schemas - Create schemas for your data
- Authentication - Add user authentication to your endpoints
The boilerplate makes pagination simple - just use these patterns!