Pagination and Chunking

Two jobs, four tools. Pick by what you need.

Need	Use
A page of results with total count and page numbers	Paginate
"Load more" or next/previous, no count	SimplePaginate
Fast deep paging, stable while rows are inserted	CursorPaginate
Process every row in batches	Chunk / ChunkByID
Process every row one at a time	Each / Stream

Length-aware pagination

var users []User
page, err := conn.Select().From("users").OrderBy("id").Paginate(ctx, 2, 15, &users)
// page.Total, page.PerPage, page.CurrentPage, page.LastPage, page.From, page.To

Paginate(ctx, page, perPage, &dest) runs a count (ignoring order, limit and offset), then fetches the page.

• page is 1-based. Below 1 becomes 1.
• perPage must be at least 1.
• An empty table gives Total: 0, LastPage: 1 and an empty slice.

Simple pagination

p, err := conn.Select().From("users").OrderBy("id").SimplePaginate(ctx, 1, 15, &users)
// p.HasMore, p.CurrentPage, p.PerPage

Fetches one extra row to learn whether another page exists, and skips the count query. Cheaper than Paginate on large tables.

Cursor (keyset) pagination

var users []User
p, err := conn.Select().From("users").CursorPaginate(ctx, 15, "", &users)             // first page
p, err  = conn.Select().From("users").CursorPaginate(ctx, 15, p.NextCursor, &users)   // next page
// p.HasMore, p.NextCursor ("" when there is no next page)

conn.Select().From("events").CursorPaginate(ctx, 50, token, &events,
    db.CursorOptions{Column: "created_at", Desc: true})

• Pages by WHERE key > last ORDER BY key LIMIT n, so it stays fast on deep pages and is stable while rows are inserted.
• The key column (default "id") must be unique and sortable. Ties would skip or repeat rows. It replaces any existing ORDER BY.
• The cursor is an opaque, URL-safe token. Its value is only ever bound as an argument, so a tampered token cannot change the SQL.
• The token is not encrypted or signed and reveals the last key. See Security.
• A NULL key cannot be a cursor. The next request is rejected with ErrInvalidArgs.
• On a query with Union, keyset paging returns ErrInvalidArgs. Wrap with FromSub first.

Chunking

Chunk

var batch []User
err := conn.Select().From("users").OrderBy("id").Chunk(ctx, 500, &batch, func() (bool, error) {
    for _, u := range batch {
        // process u
    }
    return true, nil // false stops early; an error stops and is returned
})

Chunk pages by offset, so it needs an ORDER BY (db.ErrNoOrder otherwise). If the callback changes which rows match the filter, offset paging can skip or repeat rows. Use ChunkByID for that.

ChunkByID

err := conn.Select().From("jobs").Where("state", "new").ChunkByID(ctx, 100, &batch, func() (bool, error) {
    // safe to update or delete the rows in batch
    return true, nil
}) // keyset on "id"; pass a column name last to use another unique column

• The next page starts after the last key as fetched, read before the callback runs, so the callback may sort, filter or truncate batch without changing which rows come next.
• It replaces any OrderBy with the key order.
• Keyset on a Union is rejected.

One row at a time

var row User
err := conn.Select().From("users").OrderBy("id").Each(ctx, &row, func() error {
    // row holds the current row, reset before every scan
    return nil
})

for u, err := range db.Stream[User](ctx, conn.Select().From("users").OrderBy("id")) {
    if err != nil {
        return err
    }
    if u.Name == "stop" {
        break // breaking closes the cursor
    }
}

Both hold a single row in memory. Stream is a Go iterator (iter.Seq2[T, error]). An error ends the sequence after being yielded once.

Single-connection pools (such as an in-memory SQLite database): do not run another query from inside an Each callback or a Stream loop. The open cursor holds the connection.

Typed versions

The Typed API returns pages with items included: db.Paged[T], db.SimplePaged[T] and db.CursorPaged[T] each carry an Items []T field.