Pagination

The paginate package centralizes the two boring-but-dangerous halves of pagination: normalizing client input (bounds, defaults, overflow protection) and wrapping results for the generated response models. It offers two modes: classic page/per-page (offset) pagination and cursor (keyset) pagination.

import "github.com/gp-system/gpsystem/paginate"

Offset pagination

Generated request parameters arrive as optional pointers (*int); Normalize clamps them once, instead of in every handler:

func Normalize(page, perPage *int, opts ...Option) Params

type Params struct{ Page, PerPage int }
func (p Params) Limit() int  // SQL LIMIT
func (p Params) Offset() int // SQL OFFSET: (Page-1)*PerPage

p := paginate.Normalize(req.Params.Page, req.Params.PerPage) // defaults: page 1, perPage 20
rows, err := r.db.Query(ctx,
    `SELECT ... ORDER BY created_at DESC LIMIT $1 OFFSET $2`, p.Limit(), p.Offset())

The bounds are tunable with options:

p := paginate.Normalize(page, perPage,
    paginate.WithDefaultPerPage(15), // when the request omits perPage (default: 20)
    paginate.WithMaxPerPage(50),     // hard cap, whatever the client asks for (default: 100)
    paginate.WithMaxPage(10_000))    // cap on the page number (default: 1,000,000)

WithMaxPage is not cosmetic: Offset() is the product (page-1)*perPage. Without a cap, a hostile ?page= value could provoke an int overflow or a pathological OFFSET. After Normalize, Offset() is guaranteed safe.

Results are wrapped for the response by Page[T]:

func NewPage[T any](items []T, total int64, p Params) Page[T]

type Page[T any] struct {
    Items   []T
    Total   int64
    Page    int
    PerPage int
}

Page[T] mirrors the scaffolded shared/paginator.tsp Paginated<T> model, so mapping onto the generated response model is mechanical.

Cursor (keyset) pagination

Offset pagination has two structural problems on large or frequently mutated tables: OFFSET n computes and throws away the first n rows on every request (O(n), page 400 means combing through 8000 rows), and under concurrent inserts the pages drift: the same row shows up on two pages, or on none. Cursor pagination solves both: the client receives an opaque cursor encoding the sort key of the last row it saw, and the next page continues from there with an indexed seek (WHERE (created_at, id) < (...)), at near-constant cost, however deep you page.

func NormalizeCursor(cursor *string, limit *int, sort string, opts ...Option) (CursorParams, error)

type CursorParams struct{ Limit int /* + decoded keys */ }
func (p CursorParams) HasCursor() bool          // was a cursor supplied, i.e. is this a page after the first
func (p CursorParams) DecodeKey(dests ...any) error // read out the key values, Scan-style
func (p CursorParams) FetchLimit() int          // Limit+1, how we detect whether another page follows
func (p CursorParams) NextCursor(keys ...any) (string, error)

func NewCursorPage[T any](rows []T, p CursorParams, key func(last T) []any) (CursorPage[T], error)

type CursorPage[T any] struct {
    Items      []T
    NextCursor string // "" on the last page
    HasMore    bool
}

var ErrInvalidCursor // errs definition: HTTP 400, code: paginate_invalid_cursor

NormalizeCursor clamps limit with the same WithDefaultPerPage/WithMaxPerPage options as Normalize (WithMaxPage is ignored, there is no page number here). The sort argument must be exactly the ORDER BY expression the query uses: its fingerprint is stamped into issued cursors, and a cursor issued for a different sort is later rejected.

The shop product listing, end to end

The full path of GET /api/v1/shop/products?cursor=...&limit=... in the sample app, handler → service → repository → response:

func (h *Handler) ListProducts(ctx context.Context, req gen.ListProductsRequest) (gen.ListProductsResponse, error) {
    page, err := h.svc.ListProducts(ctx, req.Params.Cursor, req.Params.Limit)
    if err != nil {
        return nil, service.ErrListProducts.Wrap(err, "api: list products")
    }
    return mapper.ToProductList(page), nil // paginate.CursorPage[Product] → gen.CursorPageProduct
}

const productSort = "created_at DESC, id DESC" // must match the repository's ORDER BY

func (s *Service) ListProducts(ctx context.Context, cursor *string, limit *int) (paginate.CursorPage[Product], error) {
    p, err := paginate.NormalizeCursor(cursor, limit, productSort)
    if err != nil {
        return paginate.CursorPage[Product]{}, err // ErrInvalidCursor → HTTP 400
    }
    return s.products.List(ctx, p)
}

func (r *Products) List(ctx context.Context, p paginate.CursorParams) (paginate.CursorPage[Product], error) {
    query := `SELECT id, name, price_cents, created_at FROM products`
    args := []any{p.FetchLimit()}

    if p.HasCursor() {
        var createdAt time.Time
        var id string
        if err := p.DecodeKey(&createdAt, &id); err != nil {
            return paginate.CursorPage[Product]{}, err
        }
        query += ` WHERE (created_at, id) < ($2, $3)`
        args = append(args, createdAt, id)
    }
    query += ` ORDER BY created_at DESC, id DESC LIMIT $1`

    rows, err := r.db.Query(ctx, query, args...)
    if err != nil {
        return paginate.CursorPage[Product]{}, errs.Wrap(err, "products: list")
    }
    products, err := pgx.CollectRows(rows, pgx.RowToStructByName[Product])
    if err != nil {
        return paginate.CursorPage[Product]{}, errs.Wrap(err, "products: scan")
    }

    return paginate.NewCursorPage(products, p, func(last Product) []any {
        return []any{last.CreatedAt, last.ID}
    })
}

The key to the mechanics is the +1 row: the query requests FetchLimit() (= Limit+1), and NewCursorPage cuts the page down to Limit items. If the extra row was present, HasMore = true, and NextCursor is built from the key of the last actually returned item: no separate COUNT query. The key function must return exactly the values, in exactly the order, used by the ORDER BY / seek predicate.

Requirements of the seek predicate

The Postgres row-value comparison ((created_at, id) < ($2, $3)) is only correct and fast when:

• the sort direction is uniform across all key columns: mixed ASC/DESC cannot be expressed with a tuple comparison;
• every key column is NOT NULL: a NULL tuple element makes the whole comparison NULL, silently dropping the row from every page;
• the last key is a unique tiebreaker: typically the primary key, so a non-unique leading column (e.g. created_at) cannot cause skipped or duplicated rows at page boundaries;
• a composite index matching the ORDER BY exists (in the shop: CREATE INDEX ON products (created_at DESC, id DESC)), so the seek stays an index scan.

The opaque cursor

Towards the client the cursor is an opaque string: a base64url-encoded, versioned JSON envelope carrying the key values and a fingerprint of the sort spec. NormalizeCursor rejects with ErrInvalidCursor (HTTP 400, code paginate_invalid_cursor) any cursor that fails to decode, was made for a different version, or was issued for a different sort order. It never silently falls back to the first page, because that would hide the client's bug. Two consequences:

• changing an endpoint's default sort invalidates outstanding cursors, by design;
• the cursor is not encrypted: its key values (timestamp, id) are decodable. Don't put anything in it the client must not see.

Which one, when

Rule of thumb from the shop: the public product listing (large, growing, infinite scroll) uses cursors; an admin-side list of a few hundred rows is more comfortable with page numbers on offset.

The SQL is yours

An important property: paginate doesn't hide pagination behind a query builder: the SQL is yours, and the package only provides the parameter discipline and the page mechanics (paginate.Normalize / paginate.NormalizeCursor for input, paginate.NewPage / paginate.NewCursorPage for the response). In exchange, the seek predicate and the index are in your hands, which is exactly where it matters once you've reached for cursor pagination at all.

Patterns used

• Opaque, tamper-resistant cursor (base64url + sha256 sort fingerprint): Design patterns.
• Generic result wrappers (Page[T], CursorPage[T]): Design patterns (the With* options for page bounds).