{"id":4773,"library":"sqlakeyset","title":"Offset-free Paging for SQLAlchemy","description":"sqlakeyset implements keyset-based paging for SQLAlchemy (both ORM and core). It provides an efficient alternative to traditional offset-based pagination, which can become slow with deep pages. The library supports SQLAlchemy 2.0, includes type hints, and is tested with PostgreSQL, MariaDB/MySQL, and SQLite. The current version is 2.0.1775222100. While no explicit release cadence is stated, the GitHub repository shows regular updates and contributions.","status":"active","version":"2.0.1775222100","language":"en","source_language":"en","source_url":"https://github.com/djrobstep/sqlakeyset","tags":["sqlalchemy","pagination","keyset pagination","offset-free paging"],"install":[{"cmd":"pip install sqlakeyset","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"Core dependency for database interaction; supports versions >=1.3.11.","package":"sqlalchemy","optional":false},{"reason":"Required for date/time handling in keyset serialization; versions >=2.0.","package":"python-dateutil","optional":false},{"reason":"Used for version handling; versions >=20.0.","package":"packaging","optional":false},{"reason":"Provides typing features for older Python versions (<3.13); versions <5, >=4.7.","package":"typing-extensions","optional":true}],"imports":[{"note":"Use for SQLAlchemy 2.0 style queries with a `Session` or `Connection` object.","symbol":"select_page","correct":"from sqlakeyset import select_page"},{"note":"Use for legacy SQLAlchemy 1.3 ORM queries (which omit the session/connection argument).","symbol":"get_page","correct":"from sqlakeyset import get_page"},{"note":"Use for asynchronous SQLAlchemy queries with `AsyncSession`.","symbol":"select_page (asyncio)","correct":"from sqlakeyset.asyncio import select_page"}],"quickstart":{"code":"from sqlalchemy import create_engine, select, String, Integer\nfrom sqlalchemy.orm import declarative_base, Session, Mapped, mapped_column\nfrom sqlakeyset import select_page\n\n# 1. Define Base\nBase = declarative_base()\n\n# 2. Define a simple model\nclass Book(Base):\n    __tablename__ = \"books\"\n    id: Mapped[int] = mapped_column(Integer, primary_key=True)\n    author: Mapped[str] = mapped_column(String(50))\n    title: Mapped[str] = mapped_column(String(100))\n\n    def __repr__(self):\n        return f\"Book(id={self.id}, author='{self.author}', title='{self.title}')\"\n\n# 3. Create an in-memory SQLite engine\nengine = create_engine(\"sqlite:///:memory:\")\n\n# 4. Create tables\nBase.metadata.create_all(engine)\n\n# 5. Insert some sample data\nwith Session(engine) as session:\n    session.add_all([\n        Book(author=\"Stephen King\", title=\"It\"),\n        Book(author=\"J.R.R. Tolkien\", title=\"The Hobbit\"),\n        Book(author=\"Stephen King\", title=\"The Stand\"),\n        Book(author=\"Frank Herbert\", title=\"Dune\"),\n        Book(author=\"J.R.R. Tolkien\", title=\"The Lord of the Rings\"),\n    ])\n    session.commit()\n\n# 6. Perform keyset pagination\nwith Session(engine) as session:\n    # Build a query with ordering, including a unique key (id) at the end\n    q = select(Book).order_by(Book.author, Book.title, Book.id)\n\n    # Get the first page\n    page1 = select_page(session, q, per_page=2)\n    print(f\"Page 1 items: {page1.items}\")\n    print(f\"Page 1 has next: {page1.paging.has_next}\")\n    print(f\"Page 1 next bookmark: {page1.paging.bookmark_next}\")\n\n    # Get the second page using the bookmark from the first page\n    if page1.paging.has_next:\n        page2 = select_page(session, q, per_page=2, bookmark=page1.paging.bookmark_next)\n        print(f\"Page 2 items: {page2.items}\")\n        print(f\"Page 2 has next: {page2.paging.has_next}\")\n        print(f\"Page 2 next bookmark: {page2.paging.bookmark_next}\")","lang":"python","description":"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."},"warnings":[{"fix":"Upgrade your Python environment to 3.8 or newer.","message":"Python versions earlier than 3.8 are no longer supported. Users on older Python versions must upgrade or use an older `sqlakeyset` release.","severity":"breaking","affected_versions":"<2.0.1733532871"},{"fix":"Modify your `order_by` clause to include the primary key(s) as the final ordering criteria, e.g., `query.order_by(..., MyModel.id)`.","message":"Keysets 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.","severity":"gotcha","affected_versions":"All"},{"fix":"Ensure 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.","message":"Rows 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`.","severity":"gotcha","affected_versions":"All"},{"fix":"For complex types, consult the documentation (when available) or implement custom serialization logic for your bookmarks.","message":"The 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.","severity":"gotcha","affected_versions":"All"},{"fix":"Migrate to SQLAlchemy 2.0 style queries and use `select_page(session, select(MyModel).order_by(...), ...)`.","message":"The `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.","severity":"deprecated","affected_versions":"All (in context of SQLAlchemy 2.0 adoption)"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}