---
id: sqlakeyset
version: "2.0.1775222100"
license: Unlicense
license_treatment: permissive
maintenance: active
---

# sqlakeyset — offset-free paging for sqlalchemy

License: permissive · Maintenance: active · Downloads: 1.3M/mo

## What it is and what it does

sqlakeyset replaces SQL OFFSET-based pagination with keyset (cursor) pagination for SQLAlchemy. Traditional offset queries slow down as you page deeper into results and can skip or repeat rows if data changes between requests; keyset paging avoids both problems by bookmarking your position using the actual row values and fetching only rows after that bookmark. The library works with both SQLAlchemy ORM and Core, supports SQLAlchemy 2 with full type hints, and includes async support via sqlakeyset.asyncio.

You order your query normally, then call select_page() or get_page() to fetch a page and receive a Page object (a list with attached paging metadata). The metadata includes next and previous markers—tuples of the row's key values—which you serialize to bookmarks (strings) for passing around URLs or APIs. On the next request, you pass that bookmark back to fetch the next or previous page. The library handles all the WHERE clause manipulation and ordering reversal internally.

Use it for:

- Paginating large result sets in web applications without performance degradation as users navigate deeper into results.
- Implementing cursor-based API pagination where clients receive opaque bookmark strings to request next/previous pages.
- Handling frequently-changing datasets where offset-based pagination risks skipping or repeating rows between page requests.
- Building async database applications with keyset pagination using AsyncSession.
- Sorting by nullable columns by wrapping them with coalesce() to ensure consistent, non-null ordering keys.

## Worth the install?

AI-flagged interpretation of the facts above — verify before relying.

Implements keyset-based paging for SQLAlchemy queries, replacing offset-based pagination to avoid performance degradation and result skipping on large datasets.

Yes. sqlakeyset solves a real performance and correctness problem in paginated queries—offset pagination degrades as you go deeper and can lose or duplicate rows in changing datasets. The library is actively maintained, has no known vulnerabilities, requires only common dependencies, and supports both modern SQLAlchemy 2 and legacy ORM styles. The Unlicense places no restrictions. Install if you paginate large or frequently-changing result sets.

## Install

pip install sqlakeyset
uv add sqlakeyset
poetry add sqlakeyset

## Installing sqlakeyset

Before you install: Low friction install with four lightweight runtime dependencies (packaging, python-dateutil, sqlalchemy, typing-extensions). Active maintenance with recent commits; last release 133 days ago.

License in practice: Unlicense (permissive) places no restrictions on use, modification, or distribution.

Quickstart:

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session
from sqlakeyset import select_page

engine = create_engine('postgresql:///books')
with Session(engine) as s:
    q = select(Book).order_by(Book.author, Book.title, Book.id)
    page1 = select_page(s, q, per_page=20)
    next_page = page1.paging.next
    page2 = select_page(s, q, per_page=20, page=next_page)

Requires Python 3.9 or later; ordering columns must be NOT NULL and unique per row (include primary key at end of order clause).

Verify before relying:

- Whether keyset paging performance remains constant at very large offsets compared to offset-based queries.
- Compatibility with databases beyond PostgreSQL, MariaDB/MySQL, and SQLite mentioned in the description.
- Whether async support (sqlakeyset.asyncio) is feature-complete relative to synchronous API.

## Package facts

- License: Unlicense (permissive)
- Python support: supports_current
- Install friction: low
- Maintenance: active
- Downloads: 1.3M/month (top 5,000 on PyPI)
- Known vulnerabilities: none known

## Tags

keyset paging sqlalchemy, offset-free pagination, sqlalchemy cursor pagination, efficient database result paging, sqlalchemy large dataset pagination, bookmark-based query navigation, sqlalchemy keyset navigation, pagination, sqlalchemy, cursor-based

[View on SkillFed](https://skillfed.io/packages/sqlakeyset) · [View on PyPI](https://pypi.org/project/sqlakeyset/)