Placeholders, inferred names and Params structs

On PostgreSQL:

• $N placeholders, and :name placeholders, which the generated SQL turns into $N
• Binding lines name a placeholder by its number: -- $1 as sgl.param(earliest)
• A named placeholder used twice, like :seats, becomes one $N and binds once
• Integer columns are BIGINT, so the Go types are the same on CockroachDB, whose INTEGER is 64-bit

Every placeholder in a query becomes a parameter of the generated method. A query with a single plain parameter, such as an id, takes it as an ordinary argument. With two or more, the method takes a <Name>Params struct instead, so a call site reads as named fields rather than a row of positional values that are easy to swap by mistake.

Those fields need good names. Seagull names each one after the column it is compared with or inserted into, and where that guess is poor, a binding line lets you choose the name yourself.

This example is a ferry timetable: add sailings, search them two ways, and book seats.

Names from the SQL

FindSailings compares origin and destination with a placeholder each, so its parameters are called Origin and Destination, and they take those columns' types.

db/queries/sailings.sql

-- sgl.query(FindSailings, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = $1 AND destination = $2
ORDER BY departs;

internal/store

type FindSailingsParams struct {
	Origin      string
	Destination string
}

type FindSailingsRow struct {
	ID          int64  `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int64  `json:"seats_free"`
}

func (queries *Queries) FindSailings(ctx context.Context, params FindSailingsParams) ([]FindSailingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, findSailingsSQL, params.Origin, params.Destination)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FindSailingsRow
	if rows.Next() {
		var row FindSailingsRow
		scanArgs := []any{&row.ID, &row.Origin, &row.Destination, &row.Departs, &row.SeatsFree}
		for more := true; more; more = rows.Next() {
			row = FindSailingsRow{}
			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
}

AddSailing works the same way from its INSERT: each value is named after the column in the same position of the column list, which gives Origin, Destination, Departs and SeatsFree.

Naming a parameter yourself

SailingsDeparting compares departs twice, for the start and the end of a window. Named after the column, its fields would be Departs and Departs2, which say nothing about which bound is which. A binding line above the SQL gives a placeholder a name of your choosing, referring to it by its number:

db/queries/sailings.sql

-- sgl.query(SailingsDeparting, many)
-- $1 as sgl.param(earliest)
-- $2 as sgl.param(latest)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE departs >= $1 AND departs < $2
ORDER BY departs;

The struct now has Earliest and Latest fields, and the SQL is unchanged: a binding line only names the parameter.

One value, used twice

BookSeats takes seats off a sailing, but only when enough are left, so it needs the number of seats twice: once to subtract and once in the guard.

It uses named placeholders. :seats appears twice, but it is one parameter, so it is one field, and a named placeholder needs no binding line because it already has a name.

db/queries/sailings.sql

-- sgl.query(BookSeats, execrows)
UPDATE sailings
SET seats_free = seats_free - :seats
WHERE id = :sailing_id AND seats_free >= :seats;

The generated SQL numbers the names: both uses of :seats become $1 and :sailing_id becomes $2, so the method passes the driver two values, not three.

internal/store

type BookSeatsParams struct {
	Seats     int64
	SailingID int64
}

func (queries *Queries) BookSeats(ctx context.Context, params BookSeatsParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, bookSeatsSQL, params.Seats, params.SailingID)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

Using it

The program fills the timetable, searches it by route and by time of day, then makes the same booking twice. The first booking takes three of the four free seats on sailing 3. The second asks for three again, finds only one left, and the guard stops it. BookSeats is execrows, so the program learns that from the count: the second call changes no row and returns 0.

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/003_parameters/internal/store"
)

// run fills a ferry timetable, searches it two ways and books seats. Every query has two
// or more parameters, so each method takes a Params struct, and the struct's field names
// come from the SQL:
//
//   - AddSailing and FindSailings name each $N after the column it is inserted into or
//     compared with: Origin, Destination, Departs, SeatsFree;
//   - SailingsDeparting compares departs twice, which would give Departs and Departs2, so
//     its binding lines ($1 as sgl.param(earliest)) choose better names;
//   - BookSeats uses named placeholders; :seats appears twice but is one field. The
//     generated SQL numbers them, so :seats becomes $1 in both places, as PostgreSQL
//     binds a reused number once.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the search results and bookings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	timetable := []store.AddSailingParams{
		{Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
		{Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
		{Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
		{Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
	}
	for _, sailing := range timetable {
		if err := queries.AddSailing(ctx, sailing); err != nil {
			return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
		}
	}

	route, err := queries.FindSailings(ctx, store.FindSailingsParams{
		Origin:      "Penzance",
		Destination: "St Mary's",
	})
	if err != nil {
		return fmt.Errorf("finding sailings: %w", err)
	}
	fmt.Fprintln(output, "Penzance to St Mary's:")
	for _, sailing := range route {
		fmt.Fprintf(output, "  sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
	}

	midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
		Earliest: "10:00",
		Latest:   "15:00",
	})
	if err != nil {
		return fmt.Errorf("finding midday sailings: %w", err)
	}
	fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
	for _, sailing := range midday {
		fmt.Fprintf(output, "  sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
	}

	// The same booking twice: the first takes 3 of the 4 free seats, so the second changes
	// no row because seats_free >= :seats is now false.
	booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
	for range 2 {
		booked, err := queries.BookSeats(ctx, booking)
		if err != nil {
			return fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
		}
		if booked == 0 {
			fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
			continue
		}
		fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
	}
	return nil
}

What it prints

Penzance to St Mary's:
  sailing 1 at 09:15, 40 seats free
  sailing 3 at 14:30, 4 seats free
Departing between 10:00 and 15:00:
  sailing 2 at 11:00, St Mary's to Tresco
  sailing 3 at 14:30, Penzance to St Mary's
booked 3 seats on sailing 3
sailing 3 has fewer than 3 seats free

Try it

Delete the two binding lines from SailingsDeparting and run sgl generate. The struct's fields go back to the names Seagull infers, Departs and Departs2, and the program no longer compiles until you rename them.

Across engines

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

	PostgreSQL	SQLite	MySQL	DuckDB	ClickHouse
id column	int64	int64	int64	int64	uint64
seats_free column	int64	int64	int32	int32	int32

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 ./003_parameters

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

cd seagull/examples/postgres && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./003_parameters

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

db/migrations/0001_sailings.up.sql

CREATE TABLE sailings (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    origin      TEXT   NOT NULL,
    destination TEXT   NOT NULL,
    departs     TEXT   NOT NULL,
    seats_free  BIGINT NOT NULL
);

db/migrations/0001_sailings.down.sql

DROP TABLE sailings;

db/queries/sailings.sql

-- sgl.query(AddSailing, exec)
INSERT INTO sailings (origin, destination, departs, seats_free) VALUES ($1, $2, $3, $4);

-- sgl.query(FindSailings, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = $1 AND destination = $2
ORDER BY departs;

-- sgl.query(SailingsDeparting, many)
-- $1 as sgl.param(earliest)
-- $2 as sgl.param(latest)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE departs >= $1 AND departs < $2
ORDER BY departs;

-- sgl.query(BookSeats, execrows)
UPDATE sailings
SET seats_free = seats_free - :seats
WHERE id = :sailing_id AND seats_free >= :seats;

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/003_parameters/internal/store"
)

// run fills a ferry timetable, searches it two ways and books seats. Every query has two
// or more parameters, so each method takes a Params struct, and the struct's field names
// come from the SQL:
//
//   - AddSailing and FindSailings name each $N after the column it is inserted into or
//     compared with: Origin, Destination, Departs, SeatsFree;
//   - SailingsDeparting compares departs twice, which would give Departs and Departs2, so
//     its binding lines ($1 as sgl.param(earliest)) choose better names;
//   - BookSeats uses named placeholders; :seats appears twice but is one field. The
//     generated SQL numbers them, so :seats becomes $1 in both places, as PostgreSQL
//     binds a reused number once.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the search results and bookings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	timetable := []store.AddSailingParams{
		{Origin: "Penzance", Destination: "St Mary's", Departs: "09:15", SeatsFree: 40},
		{Origin: "St Mary's", Destination: "Tresco", Departs: "11:00", SeatsFree: 12},
		{Origin: "Penzance", Destination: "St Mary's", Departs: "14:30", SeatsFree: 4},
		{Origin: "St Mary's", Destination: "Penzance", Departs: "16:30", SeatsFree: 60},
	}
	for _, sailing := range timetable {
		if err := queries.AddSailing(ctx, sailing); err != nil {
			return fmt.Errorf("adding the %s sailing: %w", sailing.Departs, err)
		}
	}

	route, err := queries.FindSailings(ctx, store.FindSailingsParams{
		Origin:      "Penzance",
		Destination: "St Mary's",
	})
	if err != nil {
		return fmt.Errorf("finding sailings: %w", err)
	}
	fmt.Fprintln(output, "Penzance to St Mary's:")
	for _, sailing := range route {
		fmt.Fprintf(output, "  sailing %d at %s, %d seats free\n", sailing.ID, sailing.Departs, sailing.SeatsFree)
	}

	midday, err := queries.SailingsDeparting(ctx, store.SailingsDepartingParams{
		Earliest: "10:00",
		Latest:   "15:00",
	})
	if err != nil {
		return fmt.Errorf("finding midday sailings: %w", err)
	}
	fmt.Fprintln(output, "Departing between 10:00 and 15:00:")
	for _, sailing := range midday {
		fmt.Fprintf(output, "  sailing %d at %s, %s to %s\n", sailing.ID, sailing.Departs, sailing.Origin, sailing.Destination)
	}

	// The same booking twice: the first takes 3 of the 4 free seats, so the second changes
	// no row because seats_free >= :seats is now false.
	booking := store.BookSeatsParams{Seats: 3, SailingID: 3}
	for range 2 {
		booked, err := queries.BookSeats(ctx, booking)
		if err != nil {
			return fmt.Errorf("booking sailing %d: %w", booking.SailingID, err)
		}
		if booked == 0 {
			fmt.Fprintf(output, "sailing %d has fewer than %d seats free\n", booking.SailingID, booking.Seats)
			continue
		}
		fmt.Fprintf(output, "booked %d seats on sailing %d\n", booking.Seats, booking.SailingID)
	}
	return nil
}

main.go

// Command 003_parameters applies its migrations to a PostgreSQL database, then runs the
// example in example.go: a ferry timetable is filled, searched two ways, and seats are
// booked.
//
// 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 Sailings struct {
	ID          int64  `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int64  `json:"seats_free"`
}

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/sailings.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addSailingSQL = `INSERT INTO sailings (origin, destination, departs, seats_free) VALUES ($1, $2, $3, $4);`

type AddSailingParams struct {
	Origin      string
	Destination string
	Departs     string
	SeatsFree   int64
}

func (queries *Queries) AddSailing(ctx context.Context, params AddSailingParams) error {
	_, err := queries.writer.ExecContext(ctx, addSailingSQL, params.Origin, params.Destination, params.Departs, params.SeatsFree)
	return err
}

const bookSeatsSQL = `UPDATE sailings
SET seats_free = seats_free - $1
WHERE id = $2 AND seats_free >= $1;`

type BookSeatsParams struct {
	Seats     int64
	SailingID int64
}

func (queries *Queries) BookSeats(ctx context.Context, params BookSeatsParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, bookSeatsSQL, params.Seats, params.SailingID)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const findSailingsSQL = `SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = $1 AND destination = $2
ORDER BY departs;`

type FindSailingsParams struct {
	Origin      string
	Destination string
}

type FindSailingsRow struct {
	ID          int64  `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int64  `json:"seats_free"`
}

func (queries *Queries) FindSailings(ctx context.Context, params FindSailingsParams) ([]FindSailingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, findSailingsSQL, params.Origin, params.Destination)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FindSailingsRow
	if rows.Next() {
		var row FindSailingsRow
		scanArgs := []any{&row.ID, &row.Origin, &row.Destination, &row.Departs, &row.SeatsFree}
		for more := true; more; more = rows.Next() {
			row = FindSailingsRow{}
			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 sailingsDepartingSQL = `SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE departs >= $1 AND departs < $2
ORDER BY departs;`

type SailingsDepartingParams struct {
	Earliest string
	Latest   string
}

type SailingsDepartingRow struct {
	ID          int64  `json:"id"`
	Origin      string `json:"origin"`
	Destination string `json:"destination"`
	Departs     string `json:"departs"`
	SeatsFree   int64  `json:"seats_free"`
}

func (queries *Queries) SailingsDeparting(ctx context.Context, params SailingsDepartingParams) ([]SailingsDepartingRow, error) {
	rows, err := queries.reader.QueryContext(ctx, sailingsDepartingSQL, params.Earliest, params.Latest)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SailingsDepartingRow
	if rows.Next() {
		var row SailingsDepartingRow
		scanArgs := []any{&row.ID, &row.Origin, &row.Destination, &row.Departs, &row.SeatsFree}
		for more := true; more; more = rows.Next() {
			row = SailingsDepartingRow{}
			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

Penzance to St Mary's:
  sailing 1 at 09:15, 40 seats free
  sailing 3 at 14:30, 4 seats free
Departing between 10:00 and 15:00:
  sailing 2 at 11:00, St Mary's to Tresco
  sailing 3 at 14:30, Penzance to St Mary's
booked 3 seats on sailing 3
sailing 3 has fewer than 3 seats free