python 55 lines · 8 steps

How a paginated list endpoint works in FastAPI

A FastAPI route validates pagination params, runs a filtered count and page query against an async SQLAlchemy session, and returns a typed Page.

Explained by highlit

FastAPI Intermediate pagination dependency-injection async-orm query-validation response-modeling

1from fastapi import APIRouter, Depends, Query
2from pydantic import BaseModel
3from sqlalchemy import func, select
4from sqlalchemy.ext.asyncio import AsyncSession
5 
6from .database import get_session
7from .models import Article
8 
9router = APIRouter(prefix="/articles", tags=["articles"])
10 
11 
12class ArticleOut(BaseModel):
13    id: int
14    title: str
15    published: bool
16 
17    model_config = {"from_attributes": True}
18 
19 
20class Page(BaseModel):
21    items: list[ArticleOut]
22    total: int
23    limit: int
24    offset: int
25 
26 
27@router.get("", response_model=Page)
28async def list_articles(
29    limit: int = Query(20, ge=1, le=100),
30    offset: int = Query(0, ge=0),
31    published: bool | None = None,
32    session: AsyncSession = Depends(get_session),
33) -> Page:
34    filters = []
35    if published is not None:
36        filters.append(Article.published == published)
37 
38    total = await session.scalar(
39        select(func.count()).select_from(Article).where(*filters)
40    )
41 
42    rows = await session.scalars(
43        select(Article)
44        .where(*filters)
45        .order_by(Article.id.desc())
46        .limit(limit)
47        .offset(offset)
48    )
49 
50    return Page(
51        items=list(rows),
52        total=total or 0,
53        limit=limit,
54        offset=offset,
55    )

01 / 01

STEP 01

Walkthrough

Space play ←→ step click any line

Three takeaways

1Returning both a total count and the page lets clients know how far they can paginate without fetching everything.
2Building a shared filter list and applying it to both the count and data queries keeps the two results consistent.
3Pydantic response models with from_attributes turn ORM rows into validated, serializable output for free.

Related explainers

python

import argparse
import sys
from pathlib import Path

Building a subcommand CLI with argparse

cli argparse subcommands

Intermediate 6 steps

python

from collections.abc import Mapping
from typing import Any, Iterator

Flattening nested config into dotted keys

recursion generators tree-traversal

Intermediate 7 steps

javascript

const express = require('express');
 
const v1 = express.Router();

Versioning an API with Express Routers

api versioning routing modularity

Intermediate 10 steps

python

import csv
import io
from datetime import datetime

Streaming a CSV export in Flask

streaming generators csv

Intermediate 9 steps

python

import time
from collections import defaultdict
from threading import Lock

Sliding-window login rate limiting in Flask

rate-limiting sliding-window thread-safety

Intermediate 7 steps

python

from django.conf import settings
from django.contrib.auth import get_user_model
from django.core.mail import EmailMultiAlternatives
from django.db.models.signals import post_save