Placeholders, inferred names and Params structs

On MySQL:

• MySQL takes only ? placeholders; Seagull also reads ?N, and rewrites each use to ? with its value repeated
• BookSeats uses ?1 twice where SQLite uses :seats twice, as a reused :name placeholder loses its second value on MySQL
• Times of day are kept in CHAR(5), as in the SQLite version's TEXT

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 = ? AND destination = ?
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   int32  `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. The SQL uses ?1 and ?2 so that the binding lines have a number to refer to. MySQL itself only takes ?, so the generated SQL has a plain ? in their place.

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 numbers its placeholders and names them with binding lines. ?1 appears twice, but it is one parameter, so it is one Seats field.

db/queries/sailings.sql

-- sgl.query(BookSeats, execrows)
-- ?1 as sgl.param(seats)
-- ?2 as sgl.param(sailing_id)
UPDATE sailings
SET seats_free = seats_free - ?1
WHERE id = ?2 AND seats_free >= ?1;

The generated SQL has a plain ? for each use, so the method passes Seats once for each of them: three values for the driver, from two fields.

internal/store

type BookSeatsParams struct {
	Seats     int32
	SailingID int64
}

func (queries *Queries) BookSeats(ctx context.Context, params BookSeatsParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, bookSeatsSQL, params.Seats, params.SailingID, params.Seats)
	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/mysql/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 ? 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 numbers its placeholders: ?1 appears twice but is one Seats field.
//
// MySQL itself takes only ?, so the generated SQL has a plain ? for each use, and the
// method passes Seats once for each.
//
// 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 >= ?1 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.

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

MariaDB runs the MySQL version unchanged.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/mysql && go run ./003_parameters

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

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

Needs a running MySQL 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: mysql
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_sailings.up.sql

CREATE TABLE sailings (
    id          BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    origin      VARCHAR(100) NOT NULL,
    destination VARCHAR(100) NOT NULL,
    departs     CHAR(5)      NOT NULL,
    seats_free  INT          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 (?, ?, ?, ?);

-- sgl.query(FindSailings, many)
SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = ? AND destination = ?
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)
-- ?1 as sgl.param(seats)
-- ?2 as sgl.param(sailing_id)
UPDATE sailings
SET seats_free = seats_free - ?1
WHERE id = ?2 AND seats_free >= ?1;

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/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 ? 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 numbers its placeholders: ?1 appears twice but is one Seats field.
//
// MySQL itself takes only ?, so the generated SQL has a plain ? for each use, and the
// method passes Seats once for each.
//
// 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 >= ?1 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 MySQL database, then runs the
// example in example.go: a ferry timetable is searched and booked through queries whose
// parameters are named by inference and by binding lines.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/ferries'`, 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/go-sql-driver/mysql"

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

// 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"), "MySQL data source name; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database dsn names, and runs the example against it.
//
// Takes dsn (string) which is the database's data source name.
//
// 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 MySQL data source name")
	}
	config, err := mysql.ParseDSN(dsn)
	if err != nil {
		return err
	}
	// Seagull maps DATETIME and TIMESTAMP columns to time.Time, which the driver returns
	// only when parseTime is set.
	config.ParseTime = true
	dsn = config.FormatDSN()

	database, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_mysql.New(migrate.WithDriverMultiStatements(config.MultiStatements)), 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   int32  `json:"seats_free"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

// Package store reads DATETIME, DATE and TIMESTAMP columns into time.Time, so the DSN it connects with must set parseTime=true.
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 (?, ?, ?, ?);`

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

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 - ?
WHERE id = ? AND seats_free >= ?;`

type BookSeatsParams struct {
	Seats     int32
	SailingID int64
}

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

const findSailingsSQL = `SELECT id, origin, destination, departs, seats_free
FROM sailings
WHERE origin = ? AND destination = ?
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   int32  `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 >= ? AND departs < ?
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   int32  `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