Pagination with defaults and limits

On DuckDB:

• LIMIT and OFFSET take bound $N parameters, typed int

A list that can grow without bound should be read a page at a time. Put the page size and the starting offset in LIMIT and OFFSET as parameters, and Seagull types them from where they sit: they are counts of rows, not column values, so each becomes a plain int.

Two qualities on the binding line protect the query from its callers. default: fills in a page size when the caller leaves it at zero, and max: caps it, so nobody can ask for every row at once by passing a large number.

This example logs creatures found in rock pools and pages through them.

A page at a time

db/queries/finds.sql

-- sgl.query(ListFinds, many)
-- $1 as sgl.param(page_size, default: 3, max: 5)
-- $2 as sgl.param(page_offset)
SELECT id, creature, pool, size_mm
FROM finds
ORDER BY id
LIMIT $1 OFFSET $2;

page_size has a default of 3 and a maximum of 5. page_offset has neither, since starting from the first row is what zero already means.

internal/store

type ListFindsParams struct {
	PageSize   int
	PageOffset int
}

type ListFindsRow struct {
	ID       int64  `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   int32  `json:"size_mm"`
}

func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error) {
	if params.PageSize == 0 {
		params.PageSize = 3
	}
	if params.PageSize < 0 {
		params.PageSize = 0
	}
	if params.PageSize > 5 {
		params.PageSize = 5
	}
	if params.PageOffset < 0 {
		params.PageOffset = 0
	}
	rows, err := queries.reader.QueryContext(ctx, listFindsSQL, params.PageSize, params.PageOffset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListFindsRow
	if rows.Next() {
		var row ListFindsRow
		scanArgs := []any{&row.ID, &row.Creature, &row.Pool, &row.SizeMm}
		for more := true; more; more = rows.Next() {
			row = ListFindsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

The method adjusts the values in Go before the query runs. A size of zero becomes 3, anything over 5 becomes 5, and a negative size or offset becomes 0, so the database never sees one. The SQL itself is unchanged, and the page size is still a bound value.

LIMIT without ORDER BY takes whichever rows the database reaches first, which need not be the same on every call, so each paged query here orders its rows. That way page two starts where page one stopped.

A search endpoint

SearchFinds is the shape of a typical search endpoint: an optional filter, a fixed order and a page, all in one static query.

db/queries/finds.sql

-- sgl.query(SearchFinds, many)
-- $1 as sgl.param(creature, optional: true)
-- $2 as sgl.param(page_size, default: 3, max: 5)
-- $3 as sgl.param(page_offset)
SELECT id, creature, pool, size_mm
FROM finds
WHERE ($1 IS NULL OR creature = $1)
ORDER BY size_mm DESC, id
LIMIT $2 OFFSET $3;

The filter is the IS NULL idiom from Optional filters: a nil Creature matches every find. The page parameters work exactly as in ListFinds.

To let the caller choose the order as well, sgl.sortable combines with LIMIT and OFFSET: the generated method places its ORDER BY before the LIMIT. Validated ORDER BY shows the directive, and the runtime query builder goes further, choosing filters, order and limit as the program runs.

Using it

The program logs seven finds, then asks for three pages: one with a page size of zero, the page after it, and one asking for 50 rows. Last, it searches for crabs two at a time, biggest first.

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/duckdb/013_pagination/internal/store"
)

// run logs seven rock pool finds and pages through them. A parameter in a LIMIT or OFFSET
// clause becomes a plain int. On page_size, default: 3 replaces a zero value and max: 5
// caps a larger one, before the query runs, so a caller cannot ask for every row at once;
// a negative size or offset becomes 0.
//
// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
// page, all in one static query.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each page.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	finds := []store.LogFindParams{
		{Creature: "crab", Pool: "north", SizeMm: 45},
		{Creature: "starfish", Pool: "east", SizeMm: 80},
		{Creature: "crab", Pool: "east", SizeMm: 60},
		{Creature: "blenny", Pool: "north", SizeMm: 70},
		{Creature: "crab", Pool: "south", SizeMm: 30},
		{Creature: "prawn", Pool: "north", SizeMm: 25},
		{Creature: "crab", Pool: "north", SizeMm: 55},
	}
	for _, find := range finds {
		if err := queries.LogFind(ctx, find); err != nil {
			return fmt.Errorf("logging a %s: %w", find.Creature, err)
		}
	}

	pages := []struct {
		label      string
		parameters store.ListFindsParams
	}{
		{label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
		{label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
		{label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
	}
	for _, page := range pages {
		listed, err := queries.ListFinds(ctx, page.parameters)
		if err != nil {
			return fmt.Errorf("listing finds, %s: %w", page.label, err)
		}
		fmt.Fprintf(output, "%s:\n", page.label)
		for _, find := range listed {
			fmt.Fprintf(output, "  #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
		}
	}

	for _, offset := range []int{0, 2} {
		crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
			Creature:   new("crab"),
			PageSize:   2,
			PageOffset: offset,
		})
		if err != nil {
			return fmt.Errorf("searching crabs from %d: %w", offset, err)
		}
		fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
		for _, crab := range crabs {
			fmt.Fprintf(output, "  #%d %dmm\n", crab.ID, crab.SizeMm)
		}
	}
	return nil
}

What it prints

The page size of zero gets the default of three rows, and the request for 50 is cut to five.

size 0 uses the default of 3:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
the next page, offset 3:
  #4 blenny in the north pool
  #5 crab in the south pool
  #6 prawn in the north pool
size 50 is capped at 5:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
  #4 blenny in the north pool
  #5 crab in the south pool
biggest crabs, offset 0:
  #3 60mm
  #7 55mm
biggest crabs, offset 2:
  #1 45mm
  #5 30mm

Across engines

Only these Go types of ListFinds differ between engines; the rest are the same everywhere.

	DuckDB	SQLite	PostgreSQL	MySQL	ClickHouse
id column	int64	int64	int64	int64	uint64
size_mm column	int32	int64	int64	int32	uint32

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with SQLite, PostgreSQL, MySQL and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/duckdb && go run ./013_pagination

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/duckdb && go test ./013_pagination

Needs cgo and a C compiler (the DuckDB driver).

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: rockpools
    dialect: duckdb
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_finds.up.sql

CREATE SEQUENCE find_ids;

CREATE TABLE finds (
    id       BIGINT  PRIMARY KEY DEFAULT nextval('find_ids'),
    creature VARCHAR NOT NULL,
    pool     VARCHAR NOT NULL,
    size_mm  INTEGER NOT NULL
);

db/migrations/0001_finds.down.sql

DROP TABLE finds;
DROP SEQUENCE find_ids;

db/queries/finds.sql

-- sgl.query(LogFind, exec)
INSERT INTO finds (creature, pool, size_mm) VALUES ($1, $2, $3);

-- sgl.query(ListFinds, many)
-- $1 as sgl.param(page_size, default: 3, max: 5)
-- $2 as sgl.param(page_offset)
SELECT id, creature, pool, size_mm
FROM finds
ORDER BY id
LIMIT $1 OFFSET $2;

-- sgl.query(SearchFinds, many)
-- $1 as sgl.param(creature, optional: true)
-- $2 as sgl.param(page_size, default: 3, max: 5)
-- $3 as sgl.param(page_offset)
SELECT id, creature, pool, size_mm
FROM finds
WHERE ($1 IS NULL OR creature = $1)
ORDER BY size_mm DESC, id
LIMIT $2 OFFSET $3;

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/duckdb/013_pagination/internal/store"
)

// run logs seven rock pool finds and pages through them. A parameter in a LIMIT or OFFSET
// clause becomes a plain int. On page_size, default: 3 replaces a zero value and max: 5
// caps a larger one, before the query runs, so a caller cannot ask for every row at once;
// a negative size or offset becomes 0.
//
// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
// page, all in one static query.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each page.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	finds := []store.LogFindParams{
		{Creature: "crab", Pool: "north", SizeMm: 45},
		{Creature: "starfish", Pool: "east", SizeMm: 80},
		{Creature: "crab", Pool: "east", SizeMm: 60},
		{Creature: "blenny", Pool: "north", SizeMm: 70},
		{Creature: "crab", Pool: "south", SizeMm: 30},
		{Creature: "prawn", Pool: "north", SizeMm: 25},
		{Creature: "crab", Pool: "north", SizeMm: 55},
	}
	for _, find := range finds {
		if err := queries.LogFind(ctx, find); err != nil {
			return fmt.Errorf("logging a %s: %w", find.Creature, err)
		}
	}

	pages := []struct {
		label      string
		parameters store.ListFindsParams
	}{
		{label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
		{label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
		{label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
	}
	for _, page := range pages {
		listed, err := queries.ListFinds(ctx, page.parameters)
		if err != nil {
			return fmt.Errorf("listing finds, %s: %w", page.label, err)
		}
		fmt.Fprintf(output, "%s:\n", page.label)
		for _, find := range listed {
			fmt.Fprintf(output, "  #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
		}
	}

	for _, offset := range []int{0, 2} {
		crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
			Creature:   new("crab"),
			PageSize:   2,
			PageOffset: offset,
		})
		if err != nil {
			return fmt.Errorf("searching crabs from %d: %w", offset, err)
		}
		fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
		for _, crab := range crabs {
			fmt.Fprintf(output, "  #%d %dmm\n", crab.ID, crab.SizeMm)
		}
	}
	return nil
}

main.go

// Command 013_pagination applies its migrations to an in-memory DuckDB database, then
// runs the example in example.go: rock pool finds are read a page at a time, with a
// default and a maximum page size, then searched with an optional filter and a page.
//
// Run it with `go run .`, or `go run . -db example.duckdb` to keep the database in a
// file.
package main

import (
	"context"
	"database/sql"
	"embed"
	"flag"
	"fmt"
	"log"
	"os"

	_ "github.com/duckdb/duckdb-go/v2"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_duckdb"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	path := flag.String("db", "", "DuckDB database file; an in-memory database when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the DuckDB database at path, or an in-memory one when path
// is empty, and runs the example against it.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, path string) error {
	database, err := sql.Open("duckdb", path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/finds.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const listFindsSQL = `SELECT id, creature, pool, size_mm
FROM finds
ORDER BY id
LIMIT $1 OFFSET $2;`

type ListFindsParams struct {
	PageSize   int
	PageOffset int
}

type ListFindsRow struct {
	ID       int64  `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   int32  `json:"size_mm"`
}

func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error) {
	if params.PageSize == 0 {
		params.PageSize = 3
	}
	if params.PageSize < 0 {
		params.PageSize = 0
	}
	if params.PageSize > 5 {
		params.PageSize = 5
	}
	if params.PageOffset < 0 {
		params.PageOffset = 0
	}
	rows, err := queries.reader.QueryContext(ctx, listFindsSQL, params.PageSize, params.PageOffset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListFindsRow
	if rows.Next() {
		var row ListFindsRow
		scanArgs := []any{&row.ID, &row.Creature, &row.Pool, &row.SizeMm}
		for more := true; more; more = rows.Next() {
			row = ListFindsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const logFindSQL = `INSERT INTO finds (creature, pool, size_mm) VALUES ($1, $2, $3);`

type LogFindParams struct {
	Creature string
	Pool     string
	SizeMm   int32
}

func (queries *Queries) LogFind(ctx context.Context, params LogFindParams) error {
	_, err := queries.writer.ExecContext(ctx, logFindSQL, params.Creature, params.Pool, params.SizeMm)
	return err
}

const searchFindsSQL = `SELECT id, creature, pool, size_mm
FROM finds
WHERE ($1 IS NULL OR creature = $1)
ORDER BY size_mm DESC, id
LIMIT $2 OFFSET $3;`

type SearchFindsParams struct {
	Creature   *string
	PageSize   int
	PageOffset int
}

type SearchFindsRow struct {
	ID       int64  `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   int32  `json:"size_mm"`
}

func (queries *Queries) SearchFinds(ctx context.Context, params SearchFindsParams) ([]SearchFindsRow, error) {
	if params.PageSize == 0 {
		params.PageSize = 3
	}
	if params.PageSize < 0 {
		params.PageSize = 0
	}
	if params.PageSize > 5 {
		params.PageSize = 5
	}
	if params.PageOffset < 0 {
		params.PageOffset = 0
	}
	rows, err := queries.reader.QueryContext(ctx, searchFindsSQL, params.Creature, params.PageSize, params.PageOffset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchFindsRow
	if rows.Next() {
		var row SearchFindsRow
		scanArgs := []any{&row.ID, &row.Creature, &row.Pool, &row.SizeMm}
		for more := true; more; more = rows.Next() {
			row = SearchFindsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Finds struct {
	ID       int64  `json:"id"`
	Creature string `json:"creature"`
	Pool     string `json:"pool"`
	SizeMm   int32  `json:"size_mm"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

output.txt

size 0 uses the default of 3:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
the next page, offset 3:
  #4 blenny in the north pool
  #5 crab in the south pool
  #6 prawn in the north pool
size 50 is capped at 5:
  #1 crab in the north pool
  #2 starfish in the east pool
  #3 crab in the east pool
  #4 blenny in the north pool
  #5 crab in the south pool
biggest crabs, offset 0:
  #3 60mm
  #7 55mm
biggest crabs, offset 2:
  #1 45mm
  #5 30mm