SQLite Basics: Page Through Rows with Composite Cursors

A next-page button needs a stable place to continue. Keyset pagination records the last displayed sort key and asks for rows after it, rather than skipping a growing number of earlier results. A unique tie-breaker prevents equal names or timestamps from losing rows between pages.

These examples require SQLite 3.15.0 or later for row-value comparisons. They show small result sets, not a performance benchmark. Only the installed runtime below was executed.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Continue after a name and its unique ID

Save as next-name-page.sql:

CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE INDEX contacts_page ON contacts(name, id);
INSERT INTO contacts VALUES
    (1, 'Ada'), (2, 'Ben'), (3, 'Ben'), (4, 'Cara'), (5, 'Dana');
SELECT 'first', id, name FROM contacts
ORDER BY name, id LIMIT 2;
SELECT 'next', id, name FROM contacts
WHERE (name, id) > ('Ben', 2)
ORDER BY name, id LIMIT 2;

Run:

python3 run-sql.py next-name-page.sql

Output:

first|1|Ada
first|2|Ben
next|3|Ben
next|4|Cara

The first page ends at Ben with id 2. Saving both values lets the next page include the other Ben, id 3. A filter using only name greater than Ben would skip that contact. The tuple comparison considers name first and uses id when the names tie.

The cursor values are written literally to keep this script self-contained. A real application should take them from the last displayed row and bind them as query parameters. Keep the cursor’s fields, collations, and ORDER BY terms aligned. Changing the search filter should normally start a fresh page sequence rather than reuse an unrelated cursor.

2. Page through a newest-first feed

Save as older-feed-page.sql:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    published_utc TEXT NOT NULL,
    title TEXT NOT NULL
);
CREATE INDEX posts_page ON posts(published_utc DESC, id DESC);
INSERT INTO posts VALUES
    (1, '2026-10-20 09:00:00', 'Setup'),
    (2, '2026-10-21 09:00:00', 'Queries'),
    (3, '2026-10-21 09:00:00', 'Indexes'),
    (4, '2026-10-22 09:00:00', 'Paging');
SELECT 'newest', id, title FROM posts
ORDER BY published_utc DESC, id DESC LIMIT 2;
SELECT 'older', id, title FROM posts
WHERE (published_utc, id) < ('2026-10-21 09:00:00', 3)
ORDER BY published_utc DESC, id DESC LIMIT 2;

Run:

python3 run-sql.py older-feed-page.sql

Output:

newest|4|Paging
newest|3|Indexes
older|2|Queries
older|1|Setup

Both sort directions are descending, so the next page uses less-than. The second page still includes id 2, which shares the cursor’s timestamp but has a smaller ID. Switching only the comparison while leaving an inconsistent ORDER BY would produce the wrong page.

All timestamps here use one fixed-width UTC representation. Mixing timezone offsets or date formats would undermine chronological text ordering. Also keep these cursor fields non-null; an unknown component can make a tuple comparison unknown and omit rows from the WHERE result. Mixed ascending and descending keys need a different predicate, not this simple all-descending recipe.

3. Fetch the preceding page in display order

Save as previous-name-page.sql:

CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE INDEX contacts_page ON contacts(name, id);
INSERT INTO contacts VALUES
    (1, 'Ada'), (2, 'Ben'), (3, 'Ben'),
    (4, 'Cara'), (5, 'Dana'), (6, 'Evan');
SELECT id, name
FROM (
    SELECT id, name FROM contacts
    WHERE (name, id) < ('Dana', 5)
    ORDER BY name DESC, id DESC LIMIT 2
)
ORDER BY name, id;

Run:

python3 run-sql.py previous-name-page.sql

Output:

3|Ben
4|Cara

Suppose the current page begins with Dana, id 5. The inner query walks backward from that first row and chooses the two nearest earlier contacts. The outer query restores ascending display order. Without reversing the inner ordering, LIMIT would pick the earliest contacts rather than the immediately preceding page.

A cursor is a boundary, not a frozen snapshot. Deleting the cursor row does not remove its saved values, but inserting, deleting, or editing other rows may change later pages. Updating a displayed name can move that row across the boundary. Use immutable ordering keys or a deliberate snapshot strategy when the application needs a consistent export.

SQLite’s row-value documentation covers tuple comparison, NULL behavior, and indexed scrolling queries. The 3.15.0 release notes establish the version requirement. The indexes shown match these ordering keys; inspect a real query plan and representative workload before making performance claims.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py next-name-page.sql older-feed-page.sql previous-name-page.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.