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 
import hashlib
from collections import defaultdict
from pathlib import Path
 

Finding duplicate files by size then hash

hashing file-io deduplication
Intermediate 7 steps
python 
from flask import Flask, request, g, jsonify
from flask_babel import Babel, gettext as _, format_datetime
from datetime import datetime
 

Per-request localization in Flask with Babel

i18n content-negotiation request-lifecycle
Intermediate 8 steps
go 
package handlers
 
import (
	"encoding/base64"

Cursor pagination in a Gin handler

pagination cursor rest-api
Intermediate 8 steps
python 
from django.core.cache import cache
from django.core.cache.utils import make_template_fragment_key
from django.db.models.signals import post_save, post_delete
from django.dispatch import receiver

Busting template fragment caches in Django

caching signals cache-invalidation
Intermediate 4 steps
python 
from datetime import datetime, timezone
 
_INTERVALS = (
    ("year", 60 * 60 * 24 * 365),

Building a human-friendly time_ago helper

datetime timezones formatting
Intermediate 5 steps
python 
import time
import threading
from flask import Flask, request, jsonify, g
 

A token-bucket rate limiter in Flask

rate-limiting token-bucket middleware
Intermediate 7 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