IN lists from Go slices

On SQLite:

• Slice parameters use numbered ?N placeholders, numbered after every other parameter; the generated method expands IN (?N) to one ?N per element
• An empty slice turns IN (?N) into IN (SELECT NULL WHERE 0), which matches no row
• Expansion stops at SQLite's limit of 999 bind variables with an error

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 WHERE 0)`, Operator: 59, Start: 62, End: 66}, 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 placeholder per element, renumbering any placeholders that come after it, 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 = 0
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 = 1 WHERE number IN (?1) AND booked = 0;

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/sqlite/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 placeholder per element at call time. A slice
// is numbered after every other parameter, so no other placeholder moves. An empty slice
// turns IN (?1) into IN (SELECT NULL WHERE 0), 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.

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

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/sqlite && go run ./010_slices

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

cd seagull/examples/sqlite && go test ./010_slices

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: sqlite
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_huts.up.sql

CREATE TABLE huts (
    number  INTEGER PRIMARY KEY,
    beach   TEXT    NOT NULL,
    colour  TEXT    NOT NULL,
    booked  BOOLEAN NOT NULL DEFAULT 0
);

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 = 1 WHERE number IN (?1) AND booked = 0;

-- 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 = 0
ORDER BY number;

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/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 placeholder per element at call time. A slice
// is numbered after every other parameter, so no other placeholder moves. An empty slice
// turns IN (?1) into IN (SELECT NULL WHERE 0), 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 SQLite 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 .`, or `go run . -db huts.db` to keep the database.
package main

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

	_ "modernc.org/sqlite"

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

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

func main() {
	path := flag.String("db", "", "SQLite database file; a temporary one when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at path, or a temporary one when path is empty,
// and runs the example against it.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, path string) error {
	if path == "" {
		directory, err := os.MkdirTemp("", "seagull-example-")
		if err != nil {
			return err
		}
		defer func() { _ = os.RemoveAll(directory) }()
		path = filepath.Join(directory, "example.db")
	}
	database, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_sqlite.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/bind_limits.go

// Code generated by sgl. DO NOT EDIT.

package store

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

const sglMaxBindVariables = 999

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 (?1, ?2, ?3);`

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 = 1 WHERE number IN (?1) AND booked = 0;`

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 WHERE 0)`, Operator: 40, Start: 43, End: 47}, 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 = ?1 AND colour IN (?2) AND booked = 0
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 WHERE 0)`, Operator: 60, Start: 63, End: 67}, 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 (?1)
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 WHERE 0)`, Operator: 59, Start: 62, End: 66}, 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

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 (
	"strconv"
	"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
	var digits [20]byte
	width += len(strconv.AppendInt(digits[:0], int64(first+count-1), decimalBase))
	builder.Grow(2 + count*width)
	builder.WriteByte('(')
	for index := range count {
		if index > 0 {
			builder.WriteByte(',')
		}
		builder.WriteString("?")
		var digits [20]byte
		builder.Write(strconv.AppendInt(digits[:0], int64(first+index), decimalBase))
	}
	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