Pagination & sync

Page through a table with limit/offset or a keyset filter, and keep a local copy of a table in sync - complete loops in curl, JavaScript, Python and C#.

A list returns at most limit rows (default 100, maximum 1000) as a plain JSON array. There is no cursor or Link header: a page with fewer than limit rows is the last one. A page past the end is []. To show "page 3 of 12", add count=true for the total (Total count); it costs an extra query, so request it once rather than on every page.

Pages are stable: without sort, rows come in primary-key order, and with sort the primary key breaks ties, so the same query always pages through rows in the same order. (Tables without a primary key have no guaranteed order.)

Offset pagination

limit + offset + sort walks a table page by page. Simple, but if rows are inserted or deleted while you page, later pages shift.

Shell

curl -s "$RESTWARD_API_URL/api/public/customer?sort=customer_id&limit=100&offset=0" \
  -H "X-API-Key: $RESTWARD_API_KEY"

Keyset pagination (recommended)

Instead of an offset, ask for rows after the last key you saw, using a gt filter on the sort column. Each request is independent of the ones before it, so inserts and deletes do not shift pages, and the database does not re-read skipped rows.

Shell

curl -sg "$RESTWARD_API_URL/api/public/invoice?invoice_id[gt]=400&sort=invoice_id&limit=3" \
  -H "X-API-Key: $RESTWARD_API_KEY"

JSON

[
  { "invoice_id": 401, "customer_id": 46, "invoice_date": "2025-11-04T00:00:00", "billing_city": "Dublin", "total": 3.96, "...": "…" },
  { "invoice_id": 402, "customer_id": 50, "invoice_date": "2025-11-05T00:00:00", "billing_city": "Madrid", "total": 5.94, "...": "…" },
  { "invoice_id": 403, "customer_id": 56, "invoice_date": "2025-11-08T00:00:00", "billing_city": "Buenos Aires", "total": 8.91, "...": "…" }
]

The next request uses invoice_id[gt]=403. Keyset paging needs a single-column key that only grows (an auto-increment id, or a timestamp combined with the rules below).

Read a whole table

curlJavaScriptPythonC#

last=0
while :; do
  page=$(curl -sfg "$RESTWARD_API_URL/api/public/invoice?invoice_id[gt]=$last&sort=invoice_id&limit=1000" \
    -H "X-API-Key: $RESTWARD_API_KEY")
  count=$(jq length <<<"$page")
  [ "$count" -eq 0 ] && break
  jq -c '.[]' <<<"$page" >> invoices.jsonl
  last=$(jq '.[-1].invoice_id' <<<"$page")
  [ "$count" -lt 1000 ] && break
done

async function* readAll(table, key, pageSize = 1000) {
  let last = null;
  for (;;) {
    const url = new URL(`/api/public/${table}`, process.env.RESTWARD_API_URL);
    url.searchParams.set("sort", key);
    url.searchParams.set("limit", String(pageSize));
    if (last !== null) url.searchParams.set(`${key}[gt]`, String(last));

    const res = await fetch(url, { headers: { "X-API-Key": process.env.RESTWARD_API_KEY } });
    if (!res.ok) throw new Error(`HTTP ${res.status}`);
    const rows = await res.json();
    yield* rows;
    if (rows.length < pageSize) return;
    last = rows.at(-1)[key];
  }
}

for await (const invoice of readAll("invoice", "invoice_id")) { /* ... */ }

def read_all(session, api, table, key, page_size=1000):
    last = None
    while True:
        params = {"sort": key, "limit": page_size}
        if last is not None:
            params[f"{key}[gt]"] = last
        res = session.get(f"{api}/api/public/{table}", params=params)
        res.raise_for_status()
        rows = res.json()
        yield from rows
        if len(rows) < page_size:
            return
        last = rows[-1][key]

for invoice in read_all(session, api, "invoice", "invoice_id"):
    ...

async IAsyncEnumerable<JsonElement> ReadAllAsync(HttpClient http, string table, string key, int pageSize = 1000)
{
    long? last = null;
    while (true)
    {
        var url = $"/api/public/{table}?sort={key}&limit={pageSize}"
            + (last is null ? "" : $"&{Uri.EscapeDataString($"{key}[gt]")}={last}");
        var rows = await http.GetFromJsonAsync<List<JsonElement>>(url) ?? [];
        foreach (var row in rows) yield return row;
        if (rows.Count < pageSize) yield break;
        last = rows[^1].GetProperty(key).GetInt64();
    }
}

await foreach (var invoice in ReadAllAsync(http, "invoice", "invoice_id")) { /* ... */ }

Keep a copy in sync

The API has no change feed, so a sync job asks for what changed since its last run:

New rows in an append-only table (orders, invoices, events): store the highest key you have and read on with the keyset loop above starting from it (invoice_id[gt]=<stored>).
Updated rows: this needs a column your database updates on every change, such as updated_at. Read with updated_at[gte]=<last run>&sort=updated_at,<primary key> and upsert by primary key. Use gte, not gt: several rows can share a timestamp, and the re-read rows are harmless because the upsert is idempotent.
Deleted rows are not visible through the API. Use soft deletes (a deleted_at column you filter on), or periodically compare the full set of keys.

Store the high-water mark only after the whole run succeeded, so a failed run is simply repeated.