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 datetime import timedelta
 
from django.contrib.sessions.models import Session
from django.core.management.base import BaseCommand

A batched session-cleanup command in Django

management-command batch-processing database-cleanup
Intermediate 7 steps
php 
final class UserActivityStream
{
    public function __construct(
        private readonly \PDO $db,

Streaming user activity with PHP generators

generators pagination lazy-evaluation
Intermediate 8 steps
javascript 
export function buildPaginationUrl(baseUrl, { page, perPage, filters = {}, sort } = {}) {
  const url = new URL(baseUrl);
  const params = url.searchParams;
 

Building and parsing pagination URLs

url-parsing query-string pagination
Intermediate 8 steps
python 
import re
from email_validator import validate_email, EmailNotValidError
 
_DISPOSABLE_DOMAINS = {

Normalizing signup emails in Python

validation normalization data-cleaning
Intermediate 8 steps
python 
import time
from concurrent.futures import ThreadPoolExecutor, as_completed
from pathlib import Path
 

Parallel file downloads with a thread pool

concurrency thread-pool streaming-io
Intermediate 8 steps
python 
from datetime import datetime
from zoneinfo import ZoneInfo
 
 

Timezone-safe datetime handling in Python

timezones datetime normalization
Intermediate 4 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