Skip to content

# Pagination & search

This example builds an articles listing endpoint that supports offset pagination, cursor pagination, full-text search, faceted filtering, and sorting — all from a single `CrudFactory` definition.

## Models

models.py

    import datetime
    import uuid

    from sqlalchemy import Boolean, DateTime, ForeignKey, String, Text, func
    from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

    class Base(DeclarativeBase):
        pass

    class Category(Base):
        __tablename__ = "categories"

        id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
        name: Mapped[str] = mapped_column(String(64), unique=True)

        articles: Mapped[list["Article"]] = relationship(back_populates="category")

    class Article(Base):
        __tablename__ = "articles"

        id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
        created_at: Mapped[datetime.datetime] = mapped_column(
            DateTime(timezone=True), server_default=func.now()
        )
        title: Mapped[str] = mapped_column(String(256))
        body: Mapped[str] = mapped_column(Text)
        status: Mapped[str] = mapped_column(String(32))
        published: Mapped[bool] = mapped_column(Boolean, default=False)
        category_id: Mapped[uuid.UUID | None] = mapped_column(
            ForeignKey("categories.id"), nullable=True
        )

        category: Mapped["Category | None"] = relationship(back_populates="articles")

## Schemas

schemas.py

    import datetime
    import uuid

    from fastapi_toolsets.schemas import PydanticBase

    class ArticleRead(PydanticBase):
        id: uuid.UUID
        created_at: datetime.datetime
        title: str
        status: str
        published: bool
        category_id: uuid.UUID | None

## Crud

Declare `searchable_fields`, `facet_fields`, and `order_fields` once on `CrudFactory`. All endpoints built from this class share the same defaults and can override them per call.

crud.py

    from fastapi_toolsets.crud import CrudFactory

    from .models import Article, Category

    ArticleCrud = CrudFactory(
        model=Article,
        cursor_column=Article.created_at,
        searchable_fields=[  # default fields for full-text search
            Article.title,
            Article.body,
            (Article.category, Category.name),
        ],
        facet_fields=[  # fields exposed as filter dropdowns
            Article.status,
            (Article.category, Category.name),
        ],
        order_fields=[  # fields exposed for client-driven ordering
            Article.title,
            Article.created_at,
        ],
    )

## Session dependency

db.py

    from typing import Annotated

    from fastapi import Depends
    from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

    from fastapi_toolsets.db import create_db_context, create_db_dependency

    DATABASE_URL = "postgresql+asyncpg://postgres:postgres@localhost:5432/postgres"

    engine = create_async_engine(url=DATABASE_URL, future=True)
    async_session_maker = async_sessionmaker(bind=engine, expire_on_commit=False)

    get_db = create_db_dependency(session_maker=async_session_maker)
    get_db_context = create_db_context(session_maker=async_session_maker)

    SessionDep = Annotated[AsyncSession, Depends(get_db)]

Deploy a Postgres DB with docker

    docker run -d --name postgres -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 5432:5432 postgres:18-alpine

## App

app.py

    from fastapi import FastAPI

    from fastapi_toolsets.exceptions import init_exceptions_handlers

    from .routes import router

    app = FastAPI()
    init_exceptions_handlers(app=app)
    app.include_router(router=router)

## Routes

### Offset pagination

Best for admin panels or any UI that needs a total item count and numbered pages.

routes.py:1:36

    from typing import Annotated

    from fastapi import APIRouter, Depends, Query

    from fastapi_toolsets.crud import OrderByClause
    from fastapi_toolsets.schemas import PaginatedResponse

    from .crud import ArticleCrud
    from .db import SessionDep
    from .models import Article
    from .schemas import ArticleRead

    router = APIRouter(prefix="/articles")

    @router.get("/offset")
    async def list_articles_offset(
        session: SessionDep,
        filter_by: Annotated[dict[str, list[str]], Depends(ArticleCrud.filter_params())],
        order_by: Annotated[
            OrderByClause | None,
            Depends(ArticleCrud.order_params(default_field=Article.created_at)),
        ],
        page: int = Query(1, ge=1),
        items_per_page: int = Query(20, ge=1, le=100),
        search: str | None = None,
    ) -> PaginatedResponse[ArticleRead]:
        return await ArticleCrud.offset_paginate(
            session=session,
            page=page,
            items_per_page=items_per_page,
            search=search,
            filter_by=filter_by or None,
            order_by=order_by,
            schema=ArticleRead,
        )

Example request

    GET /articles/offset?page=2&items_per_page=10&search=fastapi&status=published&order_by=title&order=asc

Example response

    {
      "status": "SUCCESS",
      "data": [
        { "id": "3f47ac69-...", "title": "FastAPI tips", "status": "published", ... }
      ],
      "pagination": {
        "total_count": 42,
        "page": 2,
        "items_per_page": 10,
        "has_more": true
      },
      "filter_attributes": {
        "status": ["archived", "draft", "published"],
        "name": ["backend", "frontend", "python"]
      }
    }

`filter_attributes` always reflects the values visible after applying the active filters. Use it to populate filter dropdowns on the client.

### Cursor pagination

Best for feeds, infinite scroll, or any high-throughput API where offset performance degrades.

routes.py:39:59

    @router.get("/cursor")
    async def list_articles_cursor(
        session: SessionDep,
        filter_by: Annotated[dict[str, list[str]], Depends(ArticleCrud.filter_params())],
        order_by: Annotated[
            OrderByClause | None,
            Depends(ArticleCrud.order_params(default_field=Article.created_at)),
        ],
        cursor: str | None = None,
        items_per_page: int = Query(20, ge=1, le=100),
        search: str | None = None,
    ) -> PaginatedResponse[ArticleRead]:
        return await ArticleCrud.cursor_paginate(
            session=session,
            cursor=cursor,
            items_per_page=items_per_page,
            search=search,
            filter_by=filter_by or None,
            order_by=order_by,
            schema=ArticleRead,
        )

Example request

    GET /articles/cursor?items_per_page=10&status=published&order_by=created_at&order=desc

Example response

    {
      "status": "SUCCESS",
      "data": [
        { "id": "3f47ac69-...", "title": "FastAPI tips", "status": "published", ... }
      ],
      "pagination": {
        "next_cursor": "eyJ2YWx1ZSI6ICIzZjQ3YWM2OS0uLi4ifQ==",
        "prev_cursor": null,
        "items_per_page": 10,
        "has_more": true
      },
      "filter_attributes": {
        "status": ["published"],
        "name": ["backend", "python"]
      }
    }

Pass `next_cursor` as the `cursor` query parameter on the next request to advance to the next page.

## Search behaviour

Both endpoints inherit the same `searchable_fields` declared on `ArticleCrud`:

Search is case-insensitive and uses a `LIKE %query%` pattern. Pass a `SearchConfig` instead of a plain string to control case sensitivity or switch to `match_mode="all"` (AND across all fields instead of OR).

    from fastapi_toolsets.crud import SearchConfig

    # Both title AND body must contain "fastapi"
    result = await ArticleCrud.offset_paginate(
        session,
        search=SearchConfig(query="fastapi", case_sensitive=True, match_mode="all"),
        search_fields=[Article.title, Article.body],
    )