Pagination with defaults and limits

The same on CockroachDB. CockroachDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes, and the program migrates with migrate_backend_cockroachdb.New().

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   int64  `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::text 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, with the cast the server needs to type $1: 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/postgres/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.

	PostgreSQL	SQLite	MySQL	DuckDB	ClickHouse
id column	int64	int64	int64	int64	uint64
size_mm column	int64	int64	int32	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, MySQL, DuckDB and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=cockroachdb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./013_pagination

Needs a running CockroachDB server: set DATABASE_URL, or pass -dsn, to the address of an empty database.

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: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql

db/migrations/0001_finds.up.sql

CREATE TABLE finds (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    creature TEXT   NOT NULL,
    pool     TEXT   NOT NULL,
    size_mm  BIGINT NOT NULL
);

db/migrations/0001_finds.down.sql

DROP TABLE finds;

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::text 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/postgres/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 a PostgreSQL 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.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, or set
// DATABASE_URL instead of passing -dsn. Give it an empty database of its own, as the
// migrations create its tables and the example adds rows each time it runs.
package main

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

	_ "github.com/jackc/pgx/v5/stdlib"

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

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

func main() {
	dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "PostgreSQL connection string; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	if dsn == "" {
		return errors.New("set -dsn or DATABASE_URL to a PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.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   int64  `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   int64
}

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::text 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   int64  `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   int64  `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