IN lists from Go slices

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

IN (...) filters by a list of values, but a placeholder stands for one value, and the length of the list is only known when the program runs. Building the list into the SQL text by hand is how injection bugs start. With Seagull you pass a Go slice, and the generated code turns it into a list the database understands, with every element sent as a bound value.

A binding line marks the parameter as a slice: sgl.param(beaches, kind: slice). The generated method then takes a slice of the element's type, and expands the list when it is called.

This example lets out beach huts: find the huts on some beaches, book several at once, and find free huts in the colours a family likes.

A slice parameter

db/queries/huts.sql

-- sgl.query(HutsOnBeaches, many)
-- ?1 as sgl.param(beaches, kind: slice)
SELECT number, beach, colour, booked
FROM huts
WHERE beach IN (?1)
ORDER BY number;

beaches is compared with the beach column, so it is a []string. It comes in a Params struct, even though it is the only parameter:

internal/store

type HutsOnBeachesParams struct {
	Beaches []string
}

type HutsOnBeachesRow struct {
	Number int64  `json:"number"`
	Beach  string `json:"beach"`
	Colour string `json:"colour"`
	Booked bool   `json:"booked"`
}

func (queries *Queries) HutsOnBeaches(ctx context.Context, params HutsOnBeachesParams) ([]HutsOnBeachesRow, error) {
	if err := sglCheckBinds(len(params.Beaches)); err != nil {
		return nil, err
	}
	query := sglSplice(hutsOnBeachesSQL, sglList(sglListMark{Empty: `IN (SELECT NULL FROM DUAL WHERE FALSE)`, Operator: 59, Start: 62, End: 65}, 1, len(params.Beaches)))
	args := make([]any, 0, len(params.Beaches))
	for _, v := range params.Beaches {
		args = append(args, v)
	}
	rows, err := queries.reader.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []HutsOnBeachesRow
	if rows.Next() {
		var row HutsOnBeachesRow
		scanArgs := []any{&row.Number, &row.Beach, &row.Colour, &row.Booked}
		for more := true; more; more = rows.Next() {
			row = HutsOnBeachesRow{}
			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 SQL keeps IN with one placeholder. At call time, sglExpandSlicePlaceholders rewrites it to one ? per element, and turns every other ?N into the plain ? that MariaDB takes, and the method passes each element as its own argument. An empty slice becomes IN (NULL), which matches nothing, so asking for the huts on no beaches gives no huts rather than an error.

The generated bind_limits.go holds a limit on how many values one statement may bind, and a slice that would expand past it makes the method return an error before the statement is sent.

A slice after a plain parameter

FreeHutsInColours has an ordinary parameter and a slice in the same query. The beach is one value, and the colours are a list:

db/queries/huts.sql

-- sgl.query(FreeHutsInColours, many)
-- ?1 as sgl.param(beach)
-- ?2 as sgl.param(colours, kind: slice)
SELECT number, colour
FROM huts
WHERE beach = ?1 AND colour IN (?2) AND booked = FALSE
ORDER BY number;

The struct has a Beach string and a Colours slice. The expansion keeps every placeholder lined up with its value, however many colours there are.

Writing with a slice

Slices work in writes too. BookHuts books every free hut in the list at once:

db/queries/huts.sql

-- sgl.query(BookHuts, execrows)
-- ?1 as sgl.param(numbers, kind: slice)
UPDATE huts SET booked = TRUE WHERE number IN (?1) AND booked = FALSE;

It is execrows, so it returns how many huts it booked. Hut 3 is taken by the first booking, so the second, which asks for huts 3 and 5, books only one.

Where a slice cannot go

Because the list is expanded each time the method is called, a slice parameter cannot appear in a batch or copyfrom query, which sends one row of parameters per item (Q017), or in a dynamic: runtime query, whose builder writes the SQL at call time itself (Q018). sgl vet reports both as errors.

Using it

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/010_slices/internal/store"
)

// run lets out beach huts by passing Go slices where the SQL has an IN list. A binding
// such as ?1 as sgl.param(beaches, kind: slice) makes the parameter a slice, and the
// generated method rewrites IN (?1) to one ? per element at call time; the generated SQL
// already writes every other ?N as a plain ?, the only placeholder MySQL takes. An empty
// slice turns IN (?1) into IN (SELECT NULL FROM DUAL WHERE FALSE), which matches nothing.
//
// The rewrite happens before the statement runs, so sgl vet refuses a slice parameter on
// a batch or copyfrom query (Q017) or a dynamic: runtime query (Q018).
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search and booking.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	huts := []store.AddHutParams{
		{Number: 1, Beach: "Polzeath", Colour: "blue"},
		{Number: 2, Beach: "Polzeath", Colour: "yellow"},
		{Number: 3, Beach: "Polzeath", Colour: "red"},
		{Number: 4, Beach: "Watergate", Colour: "blue"},
		{Number: 5, Beach: "Watergate", Colour: "red"},
		{Number: 6, Beach: "Fistral", Colour: "yellow"},
	}
	for _, hut := range huts {
		if err := queries.AddHut(ctx, hut); err != nil {
			return fmt.Errorf("adding hut %d: %w", hut.Number, err)
		}
	}

	for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
		found, err := queries.HutsOnBeaches(ctx, store.HutsOnBeachesParams{Beaches: beaches})
		if err != nil {
			return fmt.Errorf("finding huts on %v: %w", beaches, err)
		}
		numbers := make([]int64, 0, len(found))
		for _, hut := range found {
			numbers = append(numbers, hut.Number)
		}
		fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
	}

	// Hut 3 is taken by the first booking, so the second books only hut 5.
	for _, numbers := range [][]int64{{1, 3, 4}, {3, 5}} {
		booked, err := queries.BookHuts(ctx, store.BookHutsParams{Numbers: numbers})
		if err != nil {
			return fmt.Errorf("booking huts %v: %w", numbers, err)
		}
		fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
	}

	free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
		Beach:   "Polzeath",
		Colours: []string{"blue", "yellow"},
	})
	if err != nil {
		return fmt.Errorf("finding free huts: %w", err)
	}
	for _, hut := range free {
		fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
	}
	return nil
}

What it prints

huts on [Polzeath Fistral]: [1 2 3 6]
huts on [Watergate]: [4 5]
huts on []: []
booking huts [1 3 4] booked 3
booking huts [3 5] booked 1
hut 2 at Polzeath is yellow and free

Across engines

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

	MySQL	SQLite	PostgreSQL	DuckDB	ClickHouse
number column	int64	int64	int64	int64	uint64

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 ./010_slices

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

cd seagull/examples/mysql && SGL_EXAMPLE_ENGINE=mariadb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./010_slices

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

db/migrations/0001_huts.up.sql

CREATE TABLE huts (
    number BIGINT      NOT NULL PRIMARY KEY,
    beach  VARCHAR(50) NOT NULL,
    colour VARCHAR(20) NOT NULL,
    booked BOOLEAN     NOT NULL DEFAULT FALSE
);

db/migrations/0001_huts.down.sql

DROP TABLE huts;

db/queries/huts.sql

-- sgl.query(AddHut, exec)
INSERT INTO huts (number, beach, colour) VALUES (?, ?, ?);

-- sgl.query(HutsOnBeaches, many)
-- ?1 as sgl.param(beaches, kind: slice)
SELECT number, beach, colour, booked
FROM huts
WHERE beach IN (?1)
ORDER BY number;

-- sgl.query(BookHuts, execrows)
-- ?1 as sgl.param(numbers, kind: slice)
UPDATE huts SET booked = TRUE WHERE number IN (?1) AND booked = FALSE;

-- sgl.query(FreeHutsInColours, many)
-- ?1 as sgl.param(beach)
-- ?2 as sgl.param(colours, kind: slice)
SELECT number, colour
FROM huts
WHERE beach = ?1 AND colour IN (?2) AND booked = FALSE
ORDER BY number;

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/010_slices/internal/store"
)

// run lets out beach huts by passing Go slices where the SQL has an IN list. A binding
// such as ?1 as sgl.param(beaches, kind: slice) makes the parameter a slice, and the
// generated method rewrites IN (?1) to one ? per element at call time; the generated SQL
// already writes every other ?N as a plain ?, the only placeholder MySQL takes. An empty
// slice turns IN (?1) into IN (SELECT NULL FROM DUAL WHERE FALSE), which matches nothing.
//
// The rewrite happens before the statement runs, so sgl vet refuses a slice parameter on
// a batch or copyfrom query (Q017) or a dynamic: runtime query (Q018).
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each search and booking.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	huts := []store.AddHutParams{
		{Number: 1, Beach: "Polzeath", Colour: "blue"},
		{Number: 2, Beach: "Polzeath", Colour: "yellow"},
		{Number: 3, Beach: "Polzeath", Colour: "red"},
		{Number: 4, Beach: "Watergate", Colour: "blue"},
		{Number: 5, Beach: "Watergate", Colour: "red"},
		{Number: 6, Beach: "Fistral", Colour: "yellow"},
	}
	for _, hut := range huts {
		if err := queries.AddHut(ctx, hut); err != nil {
			return fmt.Errorf("adding hut %d: %w", hut.Number, err)
		}
	}

	for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
		found, err := queries.HutsOnBeaches(ctx, store.HutsOnBeachesParams{Beaches: beaches})
		if err != nil {
			return fmt.Errorf("finding huts on %v: %w", beaches, err)
		}
		numbers := make([]int64, 0, len(found))
		for _, hut := range found {
			numbers = append(numbers, hut.Number)
		}
		fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
	}

	// Hut 3 is taken by the first booking, so the second books only hut 5.
	for _, numbers := range [][]int64{{1, 3, 4}, {3, 5}} {
		booked, err := queries.BookHuts(ctx, store.BookHutsParams{Numbers: numbers})
		if err != nil {
			return fmt.Errorf("booking huts %v: %w", numbers, err)
		}
		fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
	}

	free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
		Beach:   "Polzeath",
		Colours: []string{"blue", "yellow"},
	})
	if err != nil {
		return fmt.Errorf("finding free huts: %w", err)
	}
	for _, hut := range free {
		fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
	}
	return nil
}

main.go

// Command 010_slices applies its migrations to a MySQL database, then runs the example in
// example.go: beach huts are found and booked by passing Go slices to IN lists.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/huts'`, 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/bind_limits.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"errors"
	"fmt"
	"strings"
)

const sglMaxBindVariables = 65535

var ErrSglTooManyBindVariables = errors.New("sgl: too many bind variables")

type sglEdit struct {
	Text  string
	Start int
	End   int
}

func sglCheckBinds(count int) error {
	if count > sglMaxBindVariables {
		return fmt.Errorf("sgl: a statement of %d bind variables exceeds the limit of %d: %w", count, sglMaxBindVariables, ErrSglTooManyBindVariables)
	}
	return nil
}

func sglSplice(query string, edits ...[]sglEdit) string {
	size, count := len(query), 0
	for _, group := range edits {
		for _, edit := range group {
			size += len(edit.Text) - (edit.End - edit.Start)
			count++
		}
	}
	if count == 0 {
		return query
	}
	var builder strings.Builder
	builder.Grow(size)
	cursor := 0
	for _, group := range edits {
		for _, edit := range group {
			builder.WriteString(query[cursor:edit.Start])
			builder.WriteString(edit.Text)
			cursor = edit.End
		}
	}
	builder.WriteString(query[cursor:])
	return builder.String()
}

internal/store/huts.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addHutSQL = `INSERT INTO huts (number, beach, colour) VALUES (?, ?, ?);`

type AddHutParams struct {
	Number int64
	Beach  string
	Colour string
}

func (queries *Queries) AddHut(ctx context.Context, params AddHutParams) error {
	_, err := queries.writer.ExecContext(ctx, addHutSQL, params.Number, params.Beach, params.Colour)
	return err
}

const bookHutsSQL = `UPDATE huts SET booked = TRUE WHERE number IN (?) AND booked = FALSE;`

type BookHutsParams struct {
	Numbers []int64
}

func (queries *Queries) BookHuts(ctx context.Context, params BookHutsParams) (int64, error) {
	if err := sglCheckBinds(len(params.Numbers)); err != nil {
		return 0, err
	}
	query := sglSplice(bookHutsSQL, sglList(sglListMark{Empty: `IN (SELECT NULL FROM DUAL WHERE FALSE)`, Operator: 43, Start: 46, End: 49}, 1, len(params.Numbers)))
	args := make([]any, 0, len(params.Numbers))
	for _, v := range params.Numbers {
		args = append(args, v)
	}
	results, err := queries.writer.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const freeHutsInColoursSQL = `SELECT number, colour
FROM huts
WHERE beach = ? AND colour IN (?) AND booked = FALSE
ORDER BY number;`

type FreeHutsInColoursParams struct {
	Beach   string
	Colours []string
}

type FreeHutsInColoursRow struct {
	Number int64  `json:"number"`
	Colour string `json:"colour"`
}

func (queries *Queries) FreeHutsInColours(ctx context.Context, params FreeHutsInColoursParams) ([]FreeHutsInColoursRow, error) {
	if err := sglCheckBinds(1 + len(params.Colours)); err != nil {
		return nil, err
	}
	query := sglSplice(freeHutsInColoursSQL, sglList(sglListMark{Empty: `IN (SELECT NULL FROM DUAL WHERE FALSE)`, Operator: 59, Start: 62, End: 65}, 2, len(params.Colours)))
	args := make([]any, 0, 1+len(params.Colours))
	args = append(args, params.Beach)
	for _, v := range params.Colours {
		args = append(args, v)
	}
	rows, err := queries.reader.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FreeHutsInColoursRow
	if rows.Next() {
		var row FreeHutsInColoursRow
		scanArgs := []any{&row.Number, &row.Colour}
		for more := true; more; more = rows.Next() {
			row = FreeHutsInColoursRow{}
			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 hutsOnBeachesSQL = `SELECT number, beach, colour, booked
FROM huts
WHERE beach IN (?)
ORDER BY number;`

type HutsOnBeachesParams struct {
	Beaches []string
}

type HutsOnBeachesRow struct {
	Number int64  `json:"number"`
	Beach  string `json:"beach"`
	Colour string `json:"colour"`
	Booked bool   `json:"booked"`
}

func (queries *Queries) HutsOnBeaches(ctx context.Context, params HutsOnBeachesParams) ([]HutsOnBeachesRow, error) {
	if err := sglCheckBinds(len(params.Beaches)); err != nil {
		return nil, err
	}
	query := sglSplice(hutsOnBeachesSQL, sglList(sglListMark{Empty: `IN (SELECT NULL FROM DUAL WHERE FALSE)`, Operator: 59, Start: 62, End: 65}, 1, len(params.Beaches)))
	args := make([]any, 0, len(params.Beaches))
	for _, v := range params.Beaches {
		args = append(args, v)
	}
	rows, err := queries.reader.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []HutsOnBeachesRow
	if rows.Next() {
		var row HutsOnBeachesRow
		scanArgs := []any{&row.Number, &row.Beach, &row.Colour, &row.Booked}
		for more := true; more; more = rows.Next() {
			row = HutsOnBeachesRow{}
			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 Huts struct {
	Number int64  `json:"number"`
	Beach  string `json:"beach"`
	Colour string `json:"colour"`
	Booked bool   `json:"booked"`
}

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/slice_helpers.go

// Code generated by sgl. DO NOT EDIT.

package store

import "strings"

type sglListMark struct {
	Empty    string
	Operator int
	Start    int
	End      int
}

func sglList(mark sglListMark, first, count int) []sglEdit {
	const decimalBase = 10
	if count == 0 {
		return []sglEdit{{Text: mark.Empty, Start: mark.Operator, End: mark.End}}
	}
	var builder strings.Builder
	width := len("?") + len("") + 1
	builder.Grow(2 + count*width)
	builder.WriteByte('(')
	for index := range count {
		if index > 0 {
			builder.WriteByte(',')
		}
		builder.WriteString("?")
	}
	builder.WriteByte(')')
	return []sglEdit{{Text: builder.String(), Start: mark.Start, End: mark.End}}
}

output.txt

huts on [Polzeath Fistral]: [1 2 3 6]
huts on [Watergate]: [4 5]
huts on []: []
booking huts [1 3 4] booked 3
booking huts [3 5] booked 1
hut 2 at Polzeath is yellow and free