Pagination

import sqlite3
 
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conversations (id INTEGER PRIMARY KEY, title TEXT)")
db.executemany("INSERT INTO conversations (title) VALUES (?)", [(f"chat {i}",) for i in range(1, 26)])
 
# OFFSET pagination: easy, but slow on deep pages and unstable when rows are added.
def page_offset(page: int, size: int = 10):
    return [r[0] for r in db.execute(
        "SELECT id FROM conversations ORDER BY id DESC LIMIT ? OFFSET ?", (size, (page - 1) * size))]
 
# CURSOR (keyset) pagination: "give me 10 items older than the last one I saw".
def page_cursor(after_id: int | None, size: int = 10):
    if after_id is None:
        rows = db.execute("SELECT id FROM conversations ORDER BY id DESC LIMIT ?", (size,))
    else:
        rows = db.execute("SELECT id FROM conversations WHERE id < ? ORDER BY id DESC LIMIT ?", (after_id, size))
    ids = [r[0] for r in rows]
    return {"items": ids, "next_cursor": ids[-1] if len(ids) == size else None}
 
p1_offset = page_offset(1)
p1_cursor = page_cursor(None)
print("page 1        :", p1_offset)
db.executemany("INSERT INTO conversations (title) VALUES (?)", [("new chat",), ("another new chat",)])  # new rows arrive
p2_offset = page_offset(2)
p2_cursor = page_cursor(p1_cursor["next_cursor"])
print("page 2 offset :", p2_offset, "<- repeats", sorted(set(p1_offset) & set(p2_offset), reverse=True))
print("page 2 cursor :", p2_cursor)
print("page 3 cursor :", page_cursor(p2_cursor["next_cursor"]))

page 1        : [25, 24, 23, 22, 21, 20, 19, 18, 17, 16]
page 2 offset : [17, 16, 15, 14, 13, 12, 11, 10, 9, 8] <- repeats [17, 16]
page 2 cursor : {'items': [15, 14, 13, 12, 11, 10, 9, 8, 7, 6], 'next_cursor': 6}
page 3 cursor : {'items': [5, 4, 3, 2, 1], 'next_cursor': None}