011 Optional filters on TimescaleDB

The same on TimescaleDB. TimescaleDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes.

A search form has fields the user may leave blank: a destination, a top price, whether dogs are welcome. You want one query that applies the filters it is given and ignores the rest, not a query for every combination, and not SQL assembled from strings at run time.

The SQL does the switching. Write each filter so that a NULL turns it off, (param IS NULL OR column = param), and mark the parameter optional: true. Seagull makes its field a pointer, and a nil pointer is sent as NULL. The SQL never changes, only the values bound to it.

This example searches a harbour's boat trips four ways with one query.

The query

db/queries/trips.sql

-- sgl.query(SearchTrips, many)
-- $1 as sgl.param(destination, optional: true)
-- $2 as sgl.param(max_price, optional: true)
-- $3 as sgl.param(dogs_welcome, optional: true)
SELECT id, name, destination, price_pence, dogs_welcome
FROM trips
WHERE ($1::text IS NULL OR destination = $1)
  AND ($2::bigint IS NULL OR price_pence <= $2)
  AND ($3::boolean IS NULL OR dogs_welcome = $3)
ORDER BY price_pence, id;

Each filter uses its parameter twice, once in the IS NULL test and once in the comparison, and $1 in both places is the same value, bound once. The binding lines name each parameter and mark it optional.

The casts matter. In $1 IS NULL on its own, the server cannot tell what type the parameter is, and refuses to prepare the statement. $1::text tells it, and the cast has to match the column it is compared with, so the three filters use ::text, ::bigint and ::boolean.

What sgl generates

Every optional parameter is a pointer field, so the zero value of the struct, with every field nil, is a search with no filters at all:

internal/store

type SearchTripsParams struct {
	Destination *string
	MaxPrice    *int64
	DogsWelcome *bool
}

type SearchTripsRow struct {
	ID          int64  `json:"id"`
	Name        string `json:"name"`
	Destination string `json:"destination"`
	PricePence  int64  `json:"price_pence"`
	DogsWelcome bool   `json:"dogs_welcome"`
}

func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, searchTripsSQL, params.Destination, params.MaxPrice, params.DogsWelcome)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchTripsRow
	if rows.Next() {
		var row SearchTripsRow
		scanArgs := []any{&row.ID, &row.Name, &row.Destination, &row.PricePence, &row.DogsWelcome}
		for more := true; more; more = rows.Next() {
			row = SearchTripsRow{}
			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
}

Using it

The program adds four trips, then runs the same search four times: with no filters, by destination, by price, and by destination and dogs together. new("Gull Rock") makes a pointer to a value in one step, and any field left out stays nil.

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/011_optional_filters/internal/store"
)

// run searches boat trips four ways with one query. Each filter in SearchTrips is written
// as ($1::text IS NULL OR destination = $1), and its binding says optional: true, which
// makes the Params field a pointer. A nil pointer is sent as NULL, so $1::text IS NULL is
// true and that filter matches every row; a set pointer applies the filter. The SQL never
// changes, only the values bound to it.
//
// PostgreSQL needs the cast: in $1 IS NULL alone the server cannot tell the parameter's
// type and refuses to prepare the statement. The cast must match the column, so the
// filters use ::text, ::bigint and ::boolean.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search's trips.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	trips := []store.AddTripParams{
		{Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
		{Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
		{Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
		{Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
	}
	for _, trip := range trips {
		if err := queries.AddTrip(ctx, trip); err != nil {
			return fmt.Errorf("adding %s: %w", trip.Name, err)
		}
	}

	searches := []struct {
		label      string
		parameters store.SearchTripsParams
	}{
		{label: "no filters", parameters: store.SearchTripsParams{}},
		{label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
		{label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(int64(1500))}},
		{label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
	}
	for _, search := range searches {
		found, err := queries.SearchTrips(ctx, search.parameters)
		if err != nil {
			return fmt.Errorf("searching %s: %w", search.label, err)
		}
		names := make([]string, 0, len(found))
		for _, trip := range found {
			names = append(names, trip.Name)
		}
		fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
	}
	return nil
}

What it prints

Each line is the same query with different fields set. Results come back cheapest first, as the query orders them.

no filters:              Sunset sail, Seal spotting, Mackerel fishing, Puffin cruise
to Gull Rock:            Seal spotting, Mackerel fishing
£15 or less:             Sunset sail, Seal spotting
to Gull Rock with a dog: Seal spotting

Try it

Remove the ::text cast from the first filter and run the program: the server cannot type $1 and refuses the statement.

Across engines

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

	PostgreSQL	SQLite	MySQL	DuckDB	ClickHouse
`max_price` parameter	`*int64`	`*int64`	`*int32`	`*int32`	`*uint32`
`id` column	`int64`	`int64`	`int64`	`int64`	`uint64`
`price_pence` 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 ./011_optional_filters

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=timescaledb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./011_optional_filters

Needs a running TimescaleDB 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: trips
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql

db/migrations/0001_trips.up.sql

CREATE TABLE trips (
    id           BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name         TEXT    NOT NULL,
    destination  TEXT    NOT NULL,
    price_pence  BIGINT  NOT NULL,
    dogs_welcome BOOLEAN NOT NULL
);

db/migrations/0001_trips.down.sql

DROP TABLE trips;

db/queries/trips.sql

-- sgl.query(AddTrip, exec)
INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES ($1, $2, $3, $4);

-- sgl.query(SearchTrips, many)
-- $1 as sgl.param(destination, optional: true)
-- $2 as sgl.param(max_price, optional: true)
-- $3 as sgl.param(dogs_welcome, optional: true)
SELECT id, name, destination, price_pence, dogs_welcome
FROM trips
WHERE ($1::text IS NULL OR destination = $1)
  AND ($2::bigint IS NULL OR price_pence <= $2)
  AND ($3::boolean IS NULL OR dogs_welcome = $3)
ORDER BY price_pence, id;

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/011_optional_filters/internal/store"
)

// run searches boat trips four ways with one query. Each filter in SearchTrips is written
// as ($1::text IS NULL OR destination = $1), and its binding says optional: true, which
// makes the Params field a pointer. A nil pointer is sent as NULL, so $1::text IS NULL is
// true and that filter matches every row; a set pointer applies the filter. The SQL never
// changes, only the values bound to it.
//
// PostgreSQL needs the cast: in $1 IS NULL alone the server cannot tell the parameter's
// type and refuses to prepare the statement. The cast must match the column, so the
// filters use ::text, ::bigint and ::boolean.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search's trips.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	trips := []store.AddTripParams{
		{Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
		{Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
		{Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
		{Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
	}
	for _, trip := range trips {
		if err := queries.AddTrip(ctx, trip); err != nil {
			return fmt.Errorf("adding %s: %w", trip.Name, err)
		}
	}

	searches := []struct {
		label      string
		parameters store.SearchTripsParams
	}{
		{label: "no filters", parameters: store.SearchTripsParams{}},
		{label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
		{label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(int64(1500))}},
		{label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
	}
	for _, search := range searches {
		found, err := queries.SearchTrips(ctx, search.parameters)
		if err != nil {
			return fmt.Errorf("searching %s: %w", search.label, err)
		}
		names := make([]string, 0, len(found))
		for _, trip := range found {
			names = append(names, trip.Name)
		}
		fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
	}
	return nil
}

main.go

// Command 011_optional_filters applies its migrations to a PostgreSQL database, then runs
// the example in example.go: one boat trip search runs four ways, each optional filter
// switched on or off by a pointer.
//
// 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/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Trips struct {
	ID          int64  `json:"id"`
	Name        string `json:"name"`
	Destination string `json:"destination"`
	PricePence  int64  `json:"price_pence"`
	DogsWelcome bool   `json:"dogs_welcome"`
}

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()
}

internal/store/trips.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addTripSQL = `INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES ($1, $2, $3, $4);`

type AddTripParams struct {
	Name        string
	Destination string
	PricePence  int64
	DogsWelcome bool
}

func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error {
	_, err := queries.writer.ExecContext(ctx, addTripSQL, params.Name, params.Destination, params.PricePence, params.DogsWelcome)
	return err
}

const searchTripsSQL = `SELECT id, name, destination, price_pence, dogs_welcome
FROM trips
WHERE ($1::text IS NULL OR destination = $1)
  AND ($2::bigint IS NULL OR price_pence <= $2)
  AND ($3::boolean IS NULL OR dogs_welcome = $3)
ORDER BY price_pence, id;`

type SearchTripsParams struct {
	Destination *string
	MaxPrice    *int64
	DogsWelcome *bool
}

type SearchTripsRow struct {
	ID          int64  `json:"id"`
	Name        string `json:"name"`
	Destination string `json:"destination"`
	PricePence  int64  `json:"price_pence"`
	DogsWelcome bool   `json:"dogs_welcome"`
}

func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, searchTripsSQL, params.Destination, params.MaxPrice, params.DogsWelcome)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchTripsRow
	if rows.Next() {
		var row SearchTripsRow
		scanArgs := []any{&row.ID, &row.Name, &row.Destination, &row.PricePence, &row.DogsWelcome}
		for more := true; more; more = rows.Next() {
			row = SearchTripsRow{}
			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
}

output.txt

no filters:              Sunset sail, Seal spotting, Mackerel fishing, Puffin cruise
to Gull Rock:            Seal spotting, Mackerel fishing
£15 or less:             Sunset sail, Seal spotting
to Gull Rock with a dog: Seal spotting

Optional filters