Batch and copyfrom

On DuckDB:

• 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 65535 bind variables, so the example writes a week of readings to need two statements
• 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 ($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   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)*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. Seagull sizes chunks for 65535 variables on engines with $N placeholders, 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/duckdb/022_bulk_writes/internal/store"
)

const (
	// minutesPerWeek is how many readings each tide gauge takes in a week, one a minute.
	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
	// 65535 variables, the limit declared by Seagull's DuckDB dialect: four columns a row
	// allows 16383 rows a statement, so a week's 20160 rows take two. 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.

	DuckDB	SQLite	PostgreSQL	MySQL	ClickHouse
minute parameter	int32	int64	int64	int32	uint16
height_cm parameter	int32	int64	int64	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, PostgreSQL, MySQL and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

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

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

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

Needs cgo and a C compiler (the DuckDB driver).

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

db/migrations/0001_readings.up.sql

CREATE SEQUENCE reading_ids;

CREATE TABLE readings (
    id        BIGINT  PRIMARY KEY DEFAULT nextval('reading_ids'),
    station   VARCHAR NOT NULL,
    minute    INTEGER NOT NULL,
    height_cm INTEGER NOT NULL,
    note      VARCHAR
);

db/migrations/0001_readings.down.sql

DROP TABLE readings;
DROP SEQUENCE reading_ids;

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

const (
	// minutesPerWeek is how many readings each tide gauge takes in a week, one a minute.
	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
	// 65535 variables, the limit declared by Seagull's DuckDB dialect: four columns a row
	// allows 16383 rows a statement, so a week's 20160 rows take two. 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 an in-memory DuckDB database, then
// runs the example in example.go: a week of tide gauge readings is written in one batch
// call as multi-row INSERT statements.
//
// Run it with `go run .`, or `go run . -db example.duckdb` to keep the database in a
// file.
package main

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

	_ "github.com/duckdb/duckdb-go/v2"

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

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

func main() {
	path := flag.String("db", "", "DuckDB database file; an in-memory database when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the DuckDB database at path, or an in-memory 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 {
	database, err := sql.Open("duckdb", path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.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   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

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   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)*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