Batch and copyfrom

The same on CockroachDB. CockroachDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes, and the program migrates with migrate_backend_cockroachdb.New().

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 ($1, $2, $3, $4);

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 += 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)*34)
		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. The PostgreSQL protocol carries at most 65535 variables in a statement, and each row binds four, so a chunk holds 16383 rows. The helper also renumbers each chunk's placeholders $1 to $N.

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/postgres/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, renumbering the placeholders $1, $2 and on,
	// chunked so that no statement binds more than 65535 variables, the most the PostgreSQL
	// protocol can carry: four columns a row allows 16383 rows a statement, so a week from
	// two gauges takes two. copyfrom generates the same code on database/sql; only the pgx
	// driver uses COPY.
	//
	// 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 int64(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.

	PostgreSQL	SQLite	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 SQLite, MySQL, DuckDB and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./022_bulk_writes

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=cockroachdb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./022_bulk_writes

Needs a running CockroachDB 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: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
    migrate:
      dsn: ${env:DATABASE_URL}

db/migrations/0001_readings.up.sql

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

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 ($1, $2, $3, $4);

-- 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/postgres/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, renumbering the placeholders $1, $2 and on,
	// chunked so that no statement binds more than 65535 variables, the most the PostgreSQL
	// protocol can carry: four columns a row allows 16383 rows a statement, so a week from
	// two gauges takes two. copyfrom generates the same code on database/sql; only the pgx
	// driver uses COPY.
	//
	// 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 int64(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 PostgreSQL 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 PostgreSQL's bind limit.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, 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/jackc/pgx/v5/stdlib"

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

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

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// 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 PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.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 {
	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 += 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)*34)
		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
20160 readings sent in 2 statements
harbour  10080 readings, 168 with a note
pier     10080 readings, 168 with a note