python 50 lines · 8 steps

Building a pagination helper with SQLAlchemy

A dataclass holds one page of articles while derived properties and a query builder compute the slice.

Explained by highlit
SQLAlchemy Intermediate pagination dataclass computed-properties query-building orm
1from dataclasses import dataclass
2from typing import Optional
3 
4from sqlalchemy import select, func
5from sqlalchemy.orm import Session
6 
7from .models import Article
8 
9 
10@dataclass
11class Page:
12 items: list[Article]
13 total: int
14 page: int
15 per_page: int
16 
17 @property
18 def pages(self) -> int:
19 return max(1, -(-self.total // self.per_page))
20 
21 @property
22 def has_next(self) -> bool:
23 return self.page < self.pages
24 
25 @property
26 def has_prev(self) -> bool:
27 return self.page > 1
28 
29 @property
30 def next_page(self) -> Optional[int]:
31 return self.page + 1 if self.has_next else None
32 
33 
34def paginate_articles(session: Session, page: int = 1, per_page: int = 20) -> Page:
35 page = max(page, 1)
36 per_page = min(max(per_page, 1), 100)
37 offset = (page - 1) * per_page
38 
39 total = session.scalar(select(func.count()).select_from(Article))
40 
41 stmt = (
42 select(Article)
43 .where(Article.published.is_(True))
44 .order_by(Article.created_at.desc(), Article.id.desc())
45 .limit(per_page)
46 .offset(offset)
47 )
48 items = list(session.scalars(stmt))
49 
50 return Page(items=items, total=total or 0, page=page, per_page=per_page)
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Separating the page container from the query keeps navigation logic testable without a database.
  2. 2Clamping page and per_page inputs guards against negative offsets and abusive page sizes.
  3. 3Deriving totals and navigation flags from raw counts avoids storing redundant, drift-prone state.

Related explainers

python 
from fastapi import FastAPI, WebSocket, WebSocketDisconnect
 
app = FastAPI()
 

Building a WebSocket chat with FastAPI

websockets broadcast connection-management
Intermediate 9 steps
python 
import time
import uuid
 
from django.utils.deprecation import MiddlewareMixin

Attaching per-request context in Django

middleware request lifecycle multi-tenancy
Intermediate 7 steps
python 
import random
from typing import Iterator, List
 
 

How reservoir sampling picks k items

reservoir-sampling streaming randomness
Intermediate 5 steps
python 
import secrets
 
from django.contrib.auth import authenticate, login
from django.core.cache import cache

Two-factor login with OTP in Django

two-factor-auth one-time-passwords caching
Intermediate 9 steps
python 
import re
from functools import total_ordering
from typing import Optional
 

Parsing and comparing semantic versions

regex operator-overloading sorting
Intermediate 7 steps
python 
from typing import Any, Sequence, Mapping
 
 
def render_markdown_table(

Rendering an aligned Markdown table in Python

string formatting data transformation closures
Intermediate 8 steps

Share this explainer

Here's the card — post it anywhere.

Building a pagination helper with SQLAlchemy — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code