Paginate Endpoint results with a cursor

Cursor pagination, also called keyset pagination, uses values from the last row of one page to retrieve the next page. Use it to process a large result set sequentially without the increasing query cost of a large OFFSET.

This guide creates a Data Source and an API Endpoint that paginate tenant activity by timestamp and activity ID.

Before you start

Before you create the resources, ensure you have:

  • A Tinybird project
  • The Tinybird CLI installed
  • A dataset with columns that define a stable, unique order

Step 1: Create the Data Source

Create a tenant_activity_log.datasource file:

datasources/tenant_activity_log.datasource
DESCRIPTION >
    Fake tenant activity data for testing keyset pagination

SCHEMA >
    `tenant_id` String `json:$.tenant_id`,
    `occurred_at` DateTime64(3) `json:$.occurred_at`,
    `activity_id` UInt64 `json:$.activity_id`,
    `activity_type` LowCardinality(String) `json:$.activity_type`,
    `details` String `json:$.details`

ENGINE "MergeTree"
ENGINE_SORTING_KEY "tenant_id, occurred_at, activity_id"

The sorting key matches how the Endpoint filters and orders the data:

  • tenant_id identifies one tenant and appears first in the sorting key.
  • occurred_at orders the tenant's activity chronologically.
  • activity_id provides a unique tie-breaker when multiple activities have the same timestamp.

Step 2: Create the API Endpoint

Create a tenant_activity_page.pipe file:

pipes/tenant_activity_page.pipe
TOKEN tenant_activity_page_read READ

NODE paginated_activity
SQL >
    %
    SELECT
        tenant_id,
        occurred_at,
        activity_id,
        activity_type,
        details
    FROM tenant_activity_log
    WHERE tenant_id = {{String(tenant_id, required=True)}}

        AND (occurred_at, activity_id) < (
            {{DateTime64(cursor_occurred_at)}},
            {{UInt64(cursor_activity_id)}}
        )

    ORDER BY
        occurred_at DESC,
        activity_id DESC

    LIMIT {{UInt16(page_size, 5)}}

TYPE ENDPOINT
Updated
Looking for Classic docs?