sqlakeyset

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 on this page — 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

sqlakeyset on PyPI

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

Evidence: sqlakeyset-2.0.1775222100-py3-none-any.whl

Tags

More Database packages