Batch and copyfrom

On SQLite:

• database/sql has no batch protocol, so batch becomes chunked multi-row INSERT statements
• copyfrom generates the same chunked INSERTs as batch; only pgx uses COPY
• chunks are sized for 999 bind variables, the default limit of SQLite before 3.32, so the code runs on older builds too
• the method returns only error, and an empty slice sends nothing

Inserting rows one call at a time costs a round trip for every row, which adds up quickly when you have thousands of them. A batch query is written as the INSERT for a single row, but its generated method takes a slice, one Params value per row, and writes them all in as few round trips as the driver allows. copyfrom asks for the driver's fastest bulk-load path in the same way.

How the rows travel depends on the driver, and Seagull generates the right code for each:

• With database/sql, which has no batch protocol, the rows become multi-row INSERT statements, split into chunks so that no statement binds more variables than the engine accepts.
• With pgx, a batch query becomes a pgx.Batch and a copyfrom query uses PostgreSQL's COPY.
• With the native ClickHouse driver, both become one prepared batch.

This page uses database/sql.

The example records tide readings: one a minute from two gauges, thousands of rows in a single call.

The query

The INSERT lists one row's values. The batch command is what makes the method take a slice.

db/queries/readings.sql

-- sgl.query(RecordReadings, batch)
INSERT INTO readings (station, minute, height_cm, note) VALUES (?, ?, ?, ?);

What sgl generates

The method loops over the slice in chunks. For each chunk it writes one VALUES tuple per row, collects the arguments in order, and runs a single INSERT through queries.writer. A small generated helper, sglBatchExpandValues, splices the tuples into the statement's VALUES clause.

internal/store

type RecordReadingsParams struct {
	Station  string
	Minute   int64
	HeightCm int64
	Note     *string
}

func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error {
	if len(params) == 0 {
		return nil
	}
	for offset := 0; offset < len(params); offset += 249 {
		end := min(offset+249, len(params))
		chunk := params[offset:end]
		args := make([]any, 0, len(chunk)*4)
		var values strings.Builder
		values.Grow(64 + len(chunk)*26)
		values.WriteString(recordReadingsSQL[:63])
		for i, item := range chunk {
			if i > 0 {
				values.WriteString(", ")
			}
			var digits [20]byte
			values.WriteString("(?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+1), 10))
			values.WriteString(", ?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+2), 10))
			values.WriteString(", ?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+3), 10))
			values.WriteString(", ?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+4), 10))
			values.WriteString(")")
			args = append(args, item.Station, item.Minute, item.HeightCm, item.Note)
		}
		values.WriteString(recordReadingsSQL[79:])
		if _, err := queries.writer.ExecContext(ctx, values.String(), args...); err != nil {
			return err
		}
	}
	return nil
}

The chunk size comes from the bind-variable limit. Each row binds four variables, and SQLite accepted at most 999 per statement by default before version 3.32, so a chunk holds 249 rows. Sizing for the older limit means the code runs on any build.

The method returns only an error, and an empty slice returns straight away without sending anything. Each chunk is its own statement, so if a later chunk fails, the earlier ones have already been written. When every row must land or none, make the call inside RunInTx, as transactions shows.

On database/sql, a copyfrom query generates exactly the same chunked INSERTs as batch: only pgx has a COPY to use.

Using it

To show what the method sends, the program hands store.New a statementCounter: a type that embeds the *sql.DB and counts every ExecContext call. Any value with the three DBTX methods will do, which is also how you would add logging or metrics. It records an empty batch first, then the readings, and prints the count after each.

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/022_bulk_writes/internal/store"
)

const (
	// minutesPerDay is how many readings each tide gauge takes in a day.
	minutesPerDay = 24 * 60
)

// statementCounter is a store.DBTX that counts the statements sent through ExecContext.
// QueryContext and QueryRowContext come from the embedded *sql.DB.
type statementCounter struct {
	*sql.DB

	// statements is how many times ExecContext has been called.
	statements int
}

// ExecContext counts the statement, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns sql.Result which is the database's result.
// Returns error when the statement fails.
func (counter *statementCounter) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
	counter.statements++
	return counter.DB.ExecContext(ctx, query, arguments...)
}

// run records a day of readings from two tide gauges in one batch call, shows how many
// INSERT statements that took, and summarises what landed in the table.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the statement counts and the summary.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	counter := &statementCounter{DB: database}
	queries := store.New(counter)

	// An empty batch is a no-op: nothing is sent to the database.
	if err := queries.RecordReadings(ctx, nil); err != nil {
		return fmt.Errorf("recording no readings: %w", err)
	}
	fmt.Fprintf(output, "0 readings sent in %d statements\n", counter.statements)

	readings := tideReadings("harbour", "pier")

	// A batch query takes a slice of Params, one per row. On database/sql the generated
	// method writes them as multi-row INSERTs, chunked so that no statement binds more than
	// 999 variables, the default limit of SQLite before 3.32: four columns a row allows 249
	// rows a statement. copyfrom generates the same code on database/sql.
	//
	// Each chunk is its own statement, so run the call inside RunInTx (concept 23) when
	// every row must land or none.
	if err := queries.RecordReadings(ctx, readings); err != nil {
		return fmt.Errorf("recording readings: %w", err)
	}
	fmt.Fprintf(output, "%d readings sent in %d statements\n", len(readings), counter.statements)

	summary, err := queries.SummariseReadings(ctx)
	if err != nil {
		return fmt.Errorf("summarising readings: %w", err)
	}
	for _, row := range summary {
		fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
	}
	return nil
}

// tideReadings makes one reading a minute for a day at each station. The height rises and
// falls in a simple saw-tooth, and the reading on the hour carries a note.
//
// Takes stations (...string) which name the gauges.
//
// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
func tideReadings(stations ...string) []store.RecordReadingsParams {
	readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerDay)
	for _, station := range stations {
		for minute := range int64(minutesPerDay) {
			var note *string
			if minute%60 == 0 {
				note = new(fmt.Sprintf("hour %d check", minute/60))
			}
			readings = append(readings, store.RecordReadingsParams{
				Station:  station,
				Minute:   minute,
				HeightCm: 200 + minute%360,
				Note:     note,
			})
		}
	}
	return readings
}

What it prints

2880 readings at 249 rows a chunk take 12 statements, and the empty batch took none.

0 readings sent in 0 statements
2880 readings sent in 12 statements
harbour  1440 readings, 24 with a note
pier     1440 readings, 24 with a note

Try it

Give RecordReadings a parameter binding with kind: slice, as in slices. A batch method already takes one set of parameters per row, so it cannot also expand a slice into a list of placeholders, and sgl vet reports Q017.

Across engines

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

	SQLite	PostgreSQL	MySQL	DuckDB	ClickHouse
minute parameter	int64	int64	int32	int32	uint16
height_cm parameter	int64	int64	int32	int32	uint16

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 ./022_bulk_writes

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

cd seagull/examples/sqlite && go test ./022_bulk_writes

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

db/migrations/0001_readings.up.sql

CREATE TABLE readings (
    id        INTEGER PRIMARY KEY,
    station   TEXT    NOT NULL,
    minute    INTEGER NOT NULL,
    height_cm INTEGER NOT NULL,
    note      TEXT
);

db/migrations/0001_readings.down.sql

DROP TABLE readings;

db/queries/readings.sql

-- sgl.query(RecordReadings, batch)
INSERT INTO readings (station, minute, height_cm, note) VALUES (?, ?, ?, ?);

-- sgl.query(SummariseReadings, many)
SELECT station, COUNT(*) AS readings, COUNT(note) AS noted
FROM readings
GROUP BY station
ORDER BY station;

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/022_bulk_writes/internal/store"
)

const (
	// minutesPerDay is how many readings each tide gauge takes in a day.
	minutesPerDay = 24 * 60
)

// statementCounter is a store.DBTX that counts the statements sent through ExecContext.
// QueryContext and QueryRowContext come from the embedded *sql.DB.
type statementCounter struct {
	*sql.DB

	// statements is how many times ExecContext has been called.
	statements int
}

// ExecContext counts the statement, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns sql.Result which is the database's result.
// Returns error when the statement fails.
func (counter *statementCounter) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
	counter.statements++
	return counter.DB.ExecContext(ctx, query, arguments...)
}

// run records a day of readings from two tide gauges in one batch call, shows how many
// INSERT statements that took, and summarises what landed in the table.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the statement counts and the summary.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	counter := &statementCounter{DB: database}
	queries := store.New(counter)

	// An empty batch is a no-op: nothing is sent to the database.
	if err := queries.RecordReadings(ctx, nil); err != nil {
		return fmt.Errorf("recording no readings: %w", err)
	}
	fmt.Fprintf(output, "0 readings sent in %d statements\n", counter.statements)

	readings := tideReadings("harbour", "pier")

	// A batch query takes a slice of Params, one per row. On database/sql the generated
	// method writes them as multi-row INSERTs, chunked so that no statement binds more than
	// 999 variables, the default limit of SQLite before 3.32: four columns a row allows 249
	// rows a statement. copyfrom generates the same code on database/sql.
	//
	// Each chunk is its own statement, so run the call inside RunInTx (concept 23) when
	// every row must land or none.
	if err := queries.RecordReadings(ctx, readings); err != nil {
		return fmt.Errorf("recording readings: %w", err)
	}
	fmt.Fprintf(output, "%d readings sent in %d statements\n", len(readings), counter.statements)

	summary, err := queries.SummariseReadings(ctx)
	if err != nil {
		return fmt.Errorf("summarising readings: %w", err)
	}
	for _, row := range summary {
		fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
	}
	return nil
}

// tideReadings makes one reading a minute for a day at each station. The height rises and
// falls in a simple saw-tooth, and the reading on the hour carries a note.
//
// Takes stations (...string) which name the gauges.
//
// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
func tideReadings(stations ...string) []store.RecordReadingsParams {
	readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerDay)
	for _, station := range stations {
		for minute := range int64(minutesPerDay) {
			var note *string
			if minute%60 == 0 {
				note = new(fmt.Sprintf("hour %d check", minute/60))
			}
			readings = append(readings, store.RecordReadingsParams{
				Station:  station,
				Minute:   minute,
				HeightCm: 200 + minute%360,
				Note:     note,
			})
		}
	}
	return readings
}

main.go

// Command 022_bulk_writes applies its migrations to a SQLite database, then runs the
// example in example.go: a day of tide readings from two gauges is written with one batch
// call, which Seagull splits into multi-row INSERTs under SQLite's bind limit.
//
// Run it with `go run .`, or `go run . -db tides.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/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Readings struct {
	ID       int64   `json:"id"`
	Station  string  `json:"station"`
	Minute   int64   `json:"minute"`
	HeightCm int64   `json:"height_cm"`
	Note     *string `json:"note"`
}

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

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"strconv"
	"strings"
)

const recordReadingsSQL = `INSERT INTO readings (station, minute, height_cm, note) VALUES (?1, ?2, ?3, ?4);`

type RecordReadingsParams struct {
	Station  string
	Minute   int64
	HeightCm int64
	Note     *string
}

func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error {
	if len(params) == 0 {
		return nil
	}
	for offset := 0; offset < len(params); offset += 249 {
		end := min(offset+249, len(params))
		chunk := params[offset:end]
		args := make([]any, 0, len(chunk)*4)
		var values strings.Builder
		values.Grow(64 + len(chunk)*26)
		values.WriteString(recordReadingsSQL[:63])
		for i, item := range chunk {
			if i > 0 {
				values.WriteString(", ")
			}
			var digits [20]byte
			values.WriteString("(?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+1), 10))
			values.WriteString(", ?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+2), 10))
			values.WriteString(", ?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+3), 10))
			values.WriteString(", ?")
			values.Write(strconv.AppendInt(digits[:0], int64(i*4+4), 10))
			values.WriteString(")")
			args = append(args, item.Station, item.Minute, item.HeightCm, item.Note)
		}
		values.WriteString(recordReadingsSQL[79:])
		if _, err := queries.writer.ExecContext(ctx, values.String(), args...); err != nil {
			return err
		}
	}
	return nil
}

const summariseReadingsSQL = `SELECT station, COUNT(*) AS readings, COUNT(note) AS noted
FROM readings
GROUP BY station
ORDER BY station;`

type SummariseReadingsRow struct {
	Station  string `json:"station"`
	Readings int64  `json:"readings"`
	Noted    int64  `json:"noted"`
}

func (queries *Queries) SummariseReadings(ctx context.Context) ([]SummariseReadingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, summariseReadingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SummariseReadingsRow
	if rows.Next() {
		var row SummariseReadingsRow
		scanArgs := []any{&row.Station, &row.Readings, &row.Noted}
		for more := true; more; more = rows.Next() {
			row = SummariseReadingsRow{}
			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

0 readings sent in 0 statements
2880 readings sent in 12 statements
harbour  1440 readings, 24 with a note
pier     1440 readings, 24 with a note