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 on it.

Install

sqlakeyset on PyPI

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.

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

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)

Package facts

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

Tags

More Database packages

See also fastcrud · django-cursor-pagination · sqlalchemy-jdbcapi · SQLAlchemy · sqlalchemy-solr · sqlalchemy-spanner · elasticsearch-dbapi · casbin-async-sqlalchemy-adapter · sqlalchemy-cockroachdb · fastapi-pagination