Batch and copyfrom

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.

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   int32
	HeightCm int32
	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 += 16383 {
		end := min(offset+16383, len(params))
		chunk := params[offset:end]
		args := make([]any, 0, len(chunk)*4)
		var values strings.Builder
		values.Grow(64 + len(chunk)*14)
		values.WriteString(recordReadingsSQL[:63])
		for i, item := range chunk {
			if i > 0 {
				values.WriteString(", ")
			}
			values.WriteString("(?, ?, ?, ?)")
			args = append(args, item.Station, item.Minute, item.HeightCm, item.Note)
		}
		values.WriteString(recordReadingsSQL[75:])
		if _, err := queries.writer.ExecContext(ctx, values.String(), args...); err != nil {
			return err
		}
	}
	return nil
}

The chunk size comes from the bind-variable limit. A MySQL prepared statement holds at most 65,535 variables, and each row binds four, so a chunk holds 16,383 rows.

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/mysql/022_bulk_writes/internal/store"
)

const (
	// minutesPerWeek is how many readings each tide gauge takes in a week.
	minutesPerWeek = 7 * 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 week 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
	// 65,535 variables, the most a MySQL prepared statement can hold: four columns a row
	// allows 16,383 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 week 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)*minutesPerWeek)
	for _, station := range stations {
		for minute := range int32(minutesPerWeek) {
			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

The example writes a week of readings, 20160 rows, so that the chunks show: at 16383 rows a chunk they take two statements. The empty batch took none.

0 readings sent in 0 statements
20160 readings sent in 2 statements
harbour  10080 readings, 168 with a note
pier     10080 readings, 168 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.

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

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

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

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

db/migrations/0001_readings.up.sql

CREATE TABLE readings (
    id        BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    station   VARCHAR(20) NOT NULL,
    minute    INT         NOT NULL,
    height_cm INT         NOT NULL,
    note      VARCHAR(50)
);

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/mysql/022_bulk_writes/internal/store"
)

const (
	// minutesPerWeek is how many readings each tide gauge takes in a week.
	minutesPerWeek = 7 * 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 week 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
	// 65,535 variables, the most a MySQL prepared statement can hold: four columns a row
	// allows 16,383 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 week 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)*minutesPerWeek)
	for _, station := range stations {
		for minute := range int32(minutesPerWeek) {
			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 MySQL database, then runs the
// example in example.go: a week of tide readings from two gauges is written with one
// batch call, which Seagull splits into multi-row INSERTs under MySQL's bind limit.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/tides'`, 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 Readings struct {
	ID       int64   `json:"id"`
	Station  string  `json:"station"`
	Minute   int32   `json:"minute"`
	HeightCm int32   `json:"height_cm"`
	Note     *string `json:"note"`
}

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

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"strings"
)

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

type RecordReadingsParams struct {
	Station  string
	Minute   int32
	HeightCm int32
	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 += 16383 {
		end := min(offset+16383, len(params))
		chunk := params[offset:end]
		args := make([]any, 0, len(chunk)*4)
		var values strings.Builder
		values.Grow(64 + len(chunk)*14)
		values.WriteString(recordReadingsSQL[:63])
		for i, item := range chunk {
			if i > 0 {
				values.WriteString(", ")
			}
			values.WriteString("(?, ?, ?, ?)")
			args = append(args, item.Station, item.Minute, item.HeightCm, item.Note)
		}
		values.WriteString(recordReadingsSQL[75:])
		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
20160 readings sent in 2 statements
harbour  10080 readings, 168 with a note
pier     10080 readings, 168 with a note