Install & Compatibility
Where this runs
tested against v2.0.1787969905 · pip install
no network on importno background threads
Install × environment matrix
Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux
muslpy 3.10–3.95 runs
installs and imports cleanly · install 0.0s · import 0.954s · 45MB
glibcpy 3.10–3.95 runs
installs and imports cleanly · install 3.5s · import 0.868s · 43MB
43MB installed
● package 43MB
Code
Verified usage
Verified import paths — ran on the pinned version, not inferred.
select_page
✓ from sqlakeyset import select_page
Use for SQLAlchemy 2.0 style queries with a `Session` or `Connection` object.
get_page
✓ from sqlakeyset import get_page
Use for legacy SQLAlchemy 1.3 ORM queries (which omit the session/connection argument).
select_page (asyncio)
✓ from sqlakeyset.asyncio import select_page
Use for asynchronous SQLAlchemy queries with `AsyncSession`.
Demonstrates basic keyset pagination using `select_page` with a SQLAlchemy 2.0-style query, retrieving the first page and its bookmark for subsequent pages. It includes a simple model, in-memory SQLite setup, data insertion, and then the pagination logic.
from sqlalchemy import create_engine, select, String, Integer
from sqlalchemy.orm import declarative_base, Session, Mapped, mapped_column
from sqlakeyset import select_page
# 1. Define Base
Base = declarative_base()
# 2. Define a simple model
class Book(Base):
__tablename__ = "books"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
author: Mapped[str] = mapped_column(String(50))
title: Mapped[str] = mapped_column(String(100))
def __repr__(self):
return f"Book(id={self.id}, author='{self.author}', title='{self.title}')"
# 3. Create an in-memory SQLite engine
engine = create_engine("sqlite:///:memory:")
# 4. Create tables
Base.metadata.create_all(engine)
# 5. Insert some sample data
with Session(engine) as session:
session.add_all([
Book(author="Stephen King", title="It"),
Book(author="J.R.R. Tolkien", title="The Hobbit"),
Book(author="Stephen King", title="The Stand"),
Book(author="Frank Herbert", title="Dune"),
Book(author="J.R.R. Tolkien", title="The Lord of the Rings"),
])
session.commit()
# 6. Perform keyset pagination
with Session(engine) as session:
# Build a query with ordering, including a unique key (id) at the end
q = select(Book).order_by(Book.author, Book.title, Book.id)
# Get the first page
page1 = select_page(session, q, per_page=2)
print(f"Page 1 items: {page1.items}")
print(f"Page 1 has next: {page1.paging.has_next}")
print(f"Page 1 next bookmark: {page1.paging.bookmark_next}")
# Get the second page using the bookmark from the first page
if page1.paging.has_next:
page2 = select_page(session, q, per_page=2, bookmark=page1.paging.bookmark_next)
print(f"Page 2 items: {page2.items}")
print(f"Page 2 has next: {page2.paging.has_next}")
print(f"Page 2 next bookmark: {page2.paging.bookmark_next}")
Debug
Known issues
breakingPython versions earlier than 3.8 are no longer supported. Users on older Python versions must upgrade or use an older `sqlakeyset` release.fixUpgrade your Python environment to 3.8 or newer.
affects: <2.0.1733532871
gotchaKeysets must be unique per row. Always include primary key column(s) at the end of your `order_by` clause to ensure uniqueness and prevent skipped or repeated rows.fixModify your `order_by` clause to include the primary key(s) as the final ordering criteria, e.g., `query.order_by(..., MyModel.id)`.
affects: All
gotchaRows containing `NULL` values in keyset columns will be omitted from results, as SQL comparisons against `NULL` are always false. Ordering columns should be `NOT NULL`.fixEnsure ordering columns are defined as `NOT NULL` in your schema, or use `sqlalchemy.func.coalesce()` to provide a non-NULL default value for nullable ordering columns in your query.
affects: All
gotchaThe built-in keyset serialization currently handles only basic data/column types (strings, ints, floats, datetimes, dates, booleans). More advanced custom types may require extending the serialization.fixFor complex types, consult the documentation (when available) or implement custom serialization logic for your bookmarks.
affects: All
deprecatedThe `get_page` function is for legacy SQLAlchemy 1.3 style ORM queries. For SQLAlchemy 2.0 style queries using `select()`, `select_page` should be used.fixMigrate to SQLAlchemy 2.0 style queries and use `select_page(session, select(MyModel).order_by(...), ...)`.
affects: All (in context of SQLAlchemy 2.0 adoption)
Errors
Common errors & fixes
ModuleNotFoundError: No module named 'sqlakeyset'
The `sqlakeyset` library has not been installed in the current Python environment.
fixpip install sqlakeyset
AssertionError: query requires an order_by clause
The `keyset_paginate` function requires the SQLAlchemy query or select object to have an explicit `order_by()` clause defined so it can determine the columns to use for keyset pagination.
fixquery = session.query(User).order_by(User.id) # For SQLAlchemy 1.x
# or
stmt = select(User).order_by(User.id) # For SQLAlchemy 2.x
ValueError: Keyset size mismatch: expected X elements, got Y
The `after` or `before` keyset tuple provided to `keyset_paginate` does not contain the correct number of elements, which must match the number of columns in the query's `order_by` clause.
fixpage = keyset_paginate(query, page_size=1, after=('Alice', 1)) # Example for order_by(User.name, User.id) TypeError: Keyset pagination expects a Query or Select object, got <class 'sqlalchemy.engine.result.ScalarResult'>
The `keyset_paginate` function was called with the result of a SQLAlchemy `session.execute()` call (e.g., `session.execute(stmt).scalars()`) instead of the original `select()` statement or `Query` object.
fixpage = keyset_paginate(select(User).order_by(User.id), page_size=1) # Pass the select statement directly
TypeError: 'DeclarativeMeta' object is not iterable
`sqlakeyset.get_page` expects a SQLAlchemy query object (e.g., `session.query(MyModel)` or `select(MyModel)`), but a declarative model class itself was passed.
fixPass an actual SQLAlchemy query object to `get_page`, for example: `get_page(session.query(MyModel), ...)` or `get_page(select(MyModel), ...)`.
Upgrade
Version history
2.0.1787969905latest on PyPI · released Aug 29, 2026
Audit
Dependencies
sqlalchemyrequiredCore dependency for database interaction; supports versions >=1.3.11.
python-dateutilrequiredRequired for date/time handling in keyset serialization; versions >=2.0.
packagingrequiredUsed for version handling; versions >=20.0.
typing-extensionsoptionalProvides typing features for older Python versions (<3.13); versions <5, >=4.7.