Home › Tutorials › Pagination

Pagination

Efficiently paginate large result sets using cursor-based pagination.

Offset vs Cursor Pagination

Offset Pagination (Simple but Slow)

-- Page 1
result := db.select_from ("posts").limit (20).offset (0).execute

-- Page 2
result := db.select_from ("posts").limit (20).offset (20).execute

-- Page 100 (SLOW - must scan 2000 rows)
result := db.select_from ("posts").limit (20).offset (1980).execute
Offset Performance

OFFSET becomes slower as values increase because SQLite must scan and discard all skipped rows.

Cursor Pagination (Efficient)

Use SIMPLE_SQL_PAGINATOR for efficient cursor-based pagination.

local
    paginator: SIMPLE_SQL_PAGINATOR
    page: SIMPLE_SQL_PAGE
do
    paginator := db.paginator ("posts")
        .columns ("id, title, created_at")
        .order_by ("id")
        .page_size (20)

    -- Get first page
    page := paginator.first_page

    across page.items as row loop
        print (row.string_value ("title") + "%N")
    end

    -- Get next page using cursor
    if page.has_more then
        page := paginator.after (page.cursor)
    end
end

SIMPLE_SQL_PAGINATOR API

Configuration

paginator := db.paginator ("documents")
    .columns ("id, title, author_id, created_at")
    .where ("published = 1")
    .order_by ("created_at DESC, id DESC")  -- Composite key for stable ordering
    .page_size (25)

Navigation

-- First page
page := paginator.first_page

-- Next page (after current cursor)
if page.has_more then
    next_page := paginator.after (page.cursor)
end

-- Previous page (before current cursor)
prev_page := paginator.before (page.cursor)

SIMPLE_SQL_PAGE

-- Access page data
page.items       -- ARRAYED_LIST [SIMPLE_SQL_ROW]
page.cursor      -- STRING for next page
page.has_more    -- BOOLEAN - more pages available?
page.count       -- INTEGER - items on this page

Complete Example: Paginated List

class DOCUMENT_LIST

feature

    paginator: SIMPLE_SQL_PAGINATOR
    current_cursor: detachable STRING

    initialize (a_db: SIMPLE_SQL_DATABASE)
        do
            paginator := a_db.paginator ("documents")
                .columns ("id, title, updated_at")
                .where ("deleted_at IS NULL")
                .order_by ("updated_at DESC, id DESC")
                .page_size (20)
        end

    load_first_page: SIMPLE_SQL_PAGE
        do
            Result := paginator.first_page
            current_cursor := Result.cursor
        end

    load_next_page: SIMPLE_SQL_PAGE
        require
            has_cursor: attached current_cursor
        do
            Result := paginator.after (current_cursor)
            current_cursor := Result.cursor
        end

    display_page (a_page: SIMPLE_SQL_PAGE)
        do
            across a_page.items as doc loop
                print (doc.integer_value ("id").out + ". ")
                print (doc.string_value ("title") + "%N")
            end

            if a_page.has_more then
                print ("[More available]%N")
            else
                print ("[End of list]%N")
            end
        end

end

API Pagination Pattern

For REST APIs, include the cursor in the response:

-- Client requests: GET /documents?cursor=abc123
-- Server responds with:
{
  "items": [...],
  "next_cursor": "def456",
  "has_more": true
}
Stable Ordering

Always include a unique column (like id) in your ORDER BY to ensure stable pagination when values in other columns are equal.

When to Use Each Approach

Approach Best For Avoid When
Offset Small datasets (<1000 rows), need to jump to specific page Large datasets, high page numbers
Cursor Large datasets, infinite scroll, real-time feeds Need to jump to arbitrary page number

Next Steps

← Eager Loading | Full-Text Search →