Arrays and lists as typed slices

On this page

On DuckDB:

  • DuckDB calls arrays lists: VARCHAR[] and INTEGER[] declare them, and they count from 1
  • DuckDB can hold NULL in any list, so rows read the elements as pointers, []*string and []*int32, while parameters take plain []string and []int32; non_null_elements: true opts out where the data never holds a NULL element
  • The generated SQL selects list columns as written, and the generated code reads the lists duckdb-go returns into the slices, where PostgreSQL on database/sql reads arrays through to_json()
  • duckdb-go binds a nil slice as an empty list, so the generated code converts a nullable list parameter and binds nil as NULL: voyage 2's sightings come back NULL, voyage 3's empty
  • array_length(ports) counts the elements; DuckDB's cardinality works on maps only
  • $1 = ANY(ports) needs sgl.param(port, type: varchar), as inference leaves it as any

Some values are lists by nature: the ports a voyage called at, the species in a haul. When the database stores them in an array column, Seagull maps that column to a Go slice of the element's type, so you get a []string or a []int64 to range over, never an array literal to parse or a []any to type-assert. Slices go back in as parameters the same way.

This example logs fishing voyages, each with the ports it called at, the catch it landed and, sometimes, what the crew saw on the way.

The schema

DuckDB calls arrays lists, and declares them the same way: VARCHAR[], INTEGER[]. ports and catch_kg cannot be NULL. sightings can.

db/migrations/0001_voyages.up.sql

CREATE SEQUENCE voyage_ids;

CREATE TABLE voyages (
    id        BIGINT    PRIMARY KEY DEFAULT nextval('voyage_ids'),
    boat      VARCHAR   NOT NULL,
    ports     VARCHAR[] NOT NULL,
    catch_kg  INTEGER[] NOT NULL,
    sightings VARCHAR[]
);

Reading arrays

ListVoyages selects the array columns as they are, and each becomes a typed slice:

internal/store

type ListVoyagesRow struct {
	ID        int64     `json:"id"`
	Boat      string    `json:"boat"`
	Ports     []*string `json:"ports"`
	CatchKg   []*int32  `json:"catch_kg"`
	Sightings []*string `json:"sightings"`
}

func (queries *Queries) ListVoyages(ctx context.Context) ([]ListVoyagesRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listVoyagesSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListVoyagesRow
	for rows.Next() {
		var row ListVoyagesRow
		if err := rows.Scan(&row.ID, &row.Boat, sglScanDriverComposite(&row.Ports), sglScanDriverComposite(&row.CatchKg), sglScanDriverComposite(&row.Sightings)); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

VARCHAR[] gives []string and INTEGER[] gives []int32. The generated SQL wraps each list column in to_json(), as in to_json(ports) AS "ports", and the method decodes the JSON into the slice. You write the column, and the generated code does the rest.

Sightings is nullable, but it is still a []string, not a pointer: a NULL array scans to a nil slice, and an empty array to an empty one, so the two stay apart.

Writing arrays

LogVoyage takes the same slices as parameters.

The driver binds each slice as a DuckDB list, a nil slice included, so a nil slice is stored as an empty list rather than NULL. Voyage 2 passes nil sightings and gets an empty list back, the same as voyage 3's empty slice.

Searching inside an array

VoyagesCallingAt finds the voyages that called at one port, and reads two more things from each voyage's ports on the way.

db/queries/voyages.sql

-- sgl.query(VoyagesCallingAt, many)
-- $1 as sgl.param(port, type: varchar)
SELECT boat, ports[1] AS first_port, array_length(ports) AS stops
FROM voyages
WHERE $1 = ANY(ports)
ORDER BY id;

ANY(ports) compares the parameter with every element. On its own, a parameter compared with ANY could be anything, so inference leaves it as any; the binding line sgl.param(port, type: varchar) makes it a string. ports[1] reads the first element, as lists count from 1. It is a *string, because an element that is not there is NULL. array_length(ports) counts the elements.

internal/store

type VoyagesCallingAtRow struct {
	Boat      string  `json:"boat"`
	FirstPort *string `json:"first_port"`
	Stops     int64   `json:"stops"`
}

func (queries *Queries) VoyagesCallingAt(ctx context.Context, port string) ([]VoyagesCallingAtRow, error) {
	rows, err := queries.reader.QueryContext(ctx, voyagesCallingAtSQL, port)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []VoyagesCallingAtRow
	for rows.Next() {
		var row VoyagesCallingAtRow
		if err := rows.Scan(&row.Boat, &row.FirstPort, &row.Stops); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Building an array

array_agg goes the other way, collecting a column from many rows into one array. Its result is a slice of its argument's type, so the voyage ids, a BIGINT column, come back as []int64:

db/queries/voyages.sql

-- sgl.query(VoyagesPerBoat, many)
SELECT boat, array_agg(id ORDER BY id) AS voyages
FROM voyages
GROUP BY boat
ORDER BY boat;

Using it

example.go

package main

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

	"seagull.sh/seagull/examples/duckdb/008_arrays/internal/store"
)

// run logs three fishing voyages and reads them back. A DuckDB list column becomes a
// typed Go slice: VARCHAR[] gives []*string and INTEGER[] gives []*int32, and array_agg
// over the NOT NULL id column gives []int64: the list can hold no NULL, so its elements
// are plain values.
//
// The elements are pointers because DuckDB can hold NULL in any list, whatever the
// column's type says, so a NULL element reads as nil, which elements prints as NULL.
// Parameters keep plain elements, so LogVoyage takes []string and []int32. When the data
// never holds a NULL element, non_null_elements: true opts out, in an sgl.column line of
// a query or migration or as go.types.non_null_elements in sgl.yaml: the elements then
// read as plain values, and a NULL one fails the read.
//
// A slice parameter is bound as a DuckDB list. duckdb-go would bind a nil slice as an
// empty list, so the generated code converts the nullable sightings parameter and binds a
// nil slice as NULL, as other drivers do: Salty Chip's Sightings: nil is stored as NULL,
// and the second Kittiwake voyage's empty slice as an empty list.
//
// The generated SQL selects each list column as it is, and the generated code reads the
// list duckdb-go returns into the slice; open internal/store to see it. A NULL list
// therefore scans to a nil slice, and an empty list to an empty one.
//
// VoyagesCallingAt searches inside a list with $1 = ANY(ports), whose parameter is pinned
// with sgl.param(port, type: varchar) as inference leaves it as any, and reads one
// element with ports[1], which is *string because a missing element is NULL. DuckDB lists
// count from 1, like PostgreSQL arrays.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the voyages and the searches.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	voyages := []store.LogVoyageParams{
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: []int32{120, 85}, Sightings: []string{"dolphin", "basking shark"}},
		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: []int32{40}, Sightings: nil},
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: []int32{60, 30, 15}, Sightings: []string{}},
	}
	for _, voyage := range voyages {
		if err := queries.LogVoyage(ctx, voyage); err != nil {
			return fmt.Errorf("logging %s's voyage: %w", voyage.Boat, err)
		}
	}

	logged, err := queries.ListVoyages(ctx)
	if err != nil {
		return fmt.Errorf("listing voyages: %w", err)
	}
	for _, voyage := range logged {
		sightings := elements(voyage.Sightings)
		if voyage.Sightings == nil {
			sightings = "NULL"
		}
		fmt.Fprintf(output, "voyage %d  %-10s  ports %s, catch %s kg, sightings %s\n", voyage.ID, voyage.Boat, elements(voyage.Ports), elements(voyage.CatchKg), sightings)
	}

	calling, err := queries.VoyagesCallingAt(ctx, "Mousehole")
	if err != nil {
		return fmt.Errorf("finding voyages calling at Mousehole: %w", err)
	}
	fmt.Fprintln(output, "\ncalling at Mousehole:")
	for _, voyage := range calling {
		fmt.Fprintf(output, "  %-10s  from %s, %d stops\n", voyage.Boat, *voyage.FirstPort, voyage.Stops)
	}

	perBoat, err := queries.VoyagesPerBoat(ctx)
	if err != nil {
		return fmt.Errorf("grouping voyages by boat: %w", err)
	}
	fmt.Fprintln(output, "\nvoyages per boat:")
	for _, boat := range perBoat {
		fmt.Fprintf(output, "  %-10s  %v\n", boat.Boat, boat.Voyages)
	}
	return nil
}

// elements returns a list's elements as fmt prints a slice, with NULL for each element
// the database holds NULL in.
//
// Takes list ([]*T) which holds the elements, nil for a NULL one.
//
// Returns string which is the elements in brackets, separated by spaces.
func elements[T any](list []*T) string {
	texts := make([]string, 0, len(list))
	for _, element := range list {
		if element == nil {
			texts = append(texts, "NULL")
			continue
		}
		texts = append(texts, fmt.Sprint(*element))
	}
	return fmt.Sprint(texts)
}

What it prints

voyage 1  Kittiwake   ports [Newlyn St Ives Mousehole], catch [120 85] kg, sightings [dolphin basking shark]
voyage 2  Salty Chip  ports [Penzance Mousehole], catch [40] kg, sightings NULL
voyage 3  Kittiwake   ports [Newlyn Lamorna], catch [60 30 15] kg, sightings []

calling at Mousehole:
  Kittiwake   from Newlyn, 3 stops
  Salty Chip  from Penzance, 2 stops

voyages per boat:
  Kittiwake   [1 3]
  Salty Chip  [2]

To pass a Go slice where the SQL expects a list of values, as in IN (...), see IN lists from Go slices.

Across engines

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

DuckDBPostgreSQL
ports column[]*string[]string
catch_kg column[]*int32[]*int64

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with PostgreSQL.

Where the example is missing:

  • Not on SQLite. SQLite has no array type.
  • Not on MySQL. MySQL has no array type.
  • Not on MariaDB. MariaDB has no array type.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/duckdb && go run ./008_arrays

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

cd seagull/examples/duckdb && go test ./008_arrays

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

db/migrations/0001_voyages.up.sql

CREATE SEQUENCE voyage_ids;

CREATE TABLE voyages (
    id        BIGINT    PRIMARY KEY DEFAULT nextval('voyage_ids'),
    boat      VARCHAR   NOT NULL,
    ports     VARCHAR[] NOT NULL,
    catch_kg  INTEGER[] NOT NULL,
    sightings VARCHAR[]
);

db/migrations/0001_voyages.down.sql

DROP TABLE voyages;
DROP SEQUENCE voyage_ids;

db/queries/voyages.sql

-- sgl.query(LogVoyage, exec)
INSERT INTO voyages (boat, ports, catch_kg, sightings) VALUES ($1, $2, $3, $4);

-- sgl.query(ListVoyages, many)
SELECT id, boat, ports, catch_kg, sightings FROM voyages ORDER BY id;

-- sgl.query(VoyagesCallingAt, many)
-- $1 as sgl.param(port, type: varchar)
SELECT boat, ports[1] AS first_port, array_length(ports) AS stops
FROM voyages
WHERE $1 = ANY(ports)
ORDER BY id;

-- sgl.query(VoyagesPerBoat, many)
SELECT boat, array_agg(id ORDER BY id) AS voyages
FROM voyages
GROUP BY boat
ORDER BY boat;

example.go

package main

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

	"seagull.sh/seagull/examples/duckdb/008_arrays/internal/store"
)

// run logs three fishing voyages and reads them back. A DuckDB list column becomes a
// typed Go slice: VARCHAR[] gives []*string and INTEGER[] gives []*int32, and array_agg
// over the NOT NULL id column gives []int64: the list can hold no NULL, so its elements
// are plain values.
//
// The elements are pointers because DuckDB can hold NULL in any list, whatever the
// column's type says, so a NULL element reads as nil, which elements prints as NULL.
// Parameters keep plain elements, so LogVoyage takes []string and []int32. When the data
// never holds a NULL element, non_null_elements: true opts out, in an sgl.column line of
// a query or migration or as go.types.non_null_elements in sgl.yaml: the elements then
// read as plain values, and a NULL one fails the read.
//
// A slice parameter is bound as a DuckDB list. duckdb-go would bind a nil slice as an
// empty list, so the generated code converts the nullable sightings parameter and binds a
// nil slice as NULL, as other drivers do: Salty Chip's Sightings: nil is stored as NULL,
// and the second Kittiwake voyage's empty slice as an empty list.
//
// The generated SQL selects each list column as it is, and the generated code reads the
// list duckdb-go returns into the slice; open internal/store to see it. A NULL list
// therefore scans to a nil slice, and an empty list to an empty one.
//
// VoyagesCallingAt searches inside a list with $1 = ANY(ports), whose parameter is pinned
// with sgl.param(port, type: varchar) as inference leaves it as any, and reads one
// element with ports[1], which is *string because a missing element is NULL. DuckDB lists
// count from 1, like PostgreSQL arrays.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the voyages and the searches.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	voyages := []store.LogVoyageParams{
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: []int32{120, 85}, Sightings: []string{"dolphin", "basking shark"}},
		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: []int32{40}, Sightings: nil},
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: []int32{60, 30, 15}, Sightings: []string{}},
	}
	for _, voyage := range voyages {
		if err := queries.LogVoyage(ctx, voyage); err != nil {
			return fmt.Errorf("logging %s's voyage: %w", voyage.Boat, err)
		}
	}

	logged, err := queries.ListVoyages(ctx)
	if err != nil {
		return fmt.Errorf("listing voyages: %w", err)
	}
	for _, voyage := range logged {
		sightings := elements(voyage.Sightings)
		if voyage.Sightings == nil {
			sightings = "NULL"
		}
		fmt.Fprintf(output, "voyage %d  %-10s  ports %s, catch %s kg, sightings %s\n", voyage.ID, voyage.Boat, elements(voyage.Ports), elements(voyage.CatchKg), sightings)
	}

	calling, err := queries.VoyagesCallingAt(ctx, "Mousehole")
	if err != nil {
		return fmt.Errorf("finding voyages calling at Mousehole: %w", err)
	}
	fmt.Fprintln(output, "\ncalling at Mousehole:")
	for _, voyage := range calling {
		fmt.Fprintf(output, "  %-10s  from %s, %d stops\n", voyage.Boat, *voyage.FirstPort, voyage.Stops)
	}

	perBoat, err := queries.VoyagesPerBoat(ctx)
	if err != nil {
		return fmt.Errorf("grouping voyages by boat: %w", err)
	}
	fmt.Fprintln(output, "\nvoyages per boat:")
	for _, boat := range perBoat {
		fmt.Fprintf(output, "  %-10s  %v\n", boat.Boat, boat.Voyages)
	}
	return nil
}

// elements returns a list's elements as fmt prints a slice, with NULL for each element
// the database holds NULL in.
//
// Takes list ([]*T) which holds the elements, nil for a NULL one.
//
// Returns string which is the elements in brackets, separated by spaces.
func elements[T any](list []*T) string {
	texts := make([]string, 0, len(list))
	for _, element := range list {
		if element == nil {
			texts = append(texts, "NULL")
			continue
		}
		texts = append(texts, fmt.Sprint(*element))
	}
	return fmt.Sprint(texts)
}

main.go

// Command 008_arrays applies its migrations to an in-memory DuckDB database, then runs
// the example in example.go: fishing voyages with list columns are logged and read back
// as typed Go slices, searched with ANY and grouped with array_agg.
//
// 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/driver_composites.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"database/sql"
	"database/sql/driver"
	"encoding"
	"encoding/json"
	"fmt"
	"math/big"
	"reflect"
	"strconv"
	"strings"

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

const sglMicrosecondsPerHour = 3_600_000_000

const sglMicrosecondsPerMinute = 60_000_000

const sglMicrosecondsPerSecond = 1_000_000

const sglMonthsPerYear = 12

const sglMinus = "-"

const sglDriverMaxDepth = 32

type sglDriverRefusal struct {
	err error
}

func (refusal sglDriverRefusal) Value() (driver.Value, error) {
	return nil, refusal.err
}

func sglDriverComposite(value any) any {
	converted, err := sglDriverConvert(value, 0)
	if err != nil {
		return sglDriverRefusal{err: err}
	}
	return converted
}

func sglDriverConvert(value any, depth int) (any, error) {
	if value == nil {
		return nil, nil
	}
	if depth > sglDriverMaxDepth {
		return nil, sglDriverTooDeep()
	}
	reflected := reflect.ValueOf(value)
	if reflected.Kind() == reflect.Pointer && reflected.IsNil() {
		return nil, nil
	}
	if valuer, ok := value.(driver.Valuer); ok {
		return valuer.Value()
	}
	if reflected.Kind() == reflect.Pointer {
		return sglDriverConvert(reflected.Elem().Interface(), depth+1)
	}
	if reflected.Type().PkgPath() == reflect.TypeFor[duckdb.Map]().PkgPath() {
		return value, nil
	}
	switch reflected.Kind() {
	case reflect.Map:
		return sglDriverCompositeMap(reflected, depth)
	case reflect.Slice:
		return sglDriverCompositeSlice(reflected, depth)
	case reflect.Struct:
		return sglDriverCompositeStruct(reflected, value, depth)
	default:
		return sglDriverBasic(reflected), nil
	}
}

func sglDriverTooDeep() error {
	return fmt.Errorf("sgl: a parameter nests more than %d levels deep or refers to itself", sglDriverMaxDepth)
}

func sglDriverBasic(reflected reflect.Value) any {
	if reflected.Type().PkgPath() == "" {
		return reflected.Interface()
	}
	switch reflected.Kind() {
	case reflect.String:
		return reflected.String()
	case reflect.Bool:
		return reflected.Bool()
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
		reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64,
		reflect.Float32, reflect.Float64:
		return reflected.Convert(sglDriverNumberType(reflected.Kind())).Interface()
	default:
		return reflected.Interface()
	}
}

func sglDriverNumberType(kind reflect.Kind) reflect.Type {
	switch kind {
	case reflect.Int:
		return reflect.TypeFor[int]()
	case reflect.Int8:
		return reflect.TypeFor[int8]()
	case reflect.Int16:
		return reflect.TypeFor[int16]()
	case reflect.Int32:
		return reflect.TypeFor[int32]()
	case reflect.Uint:
		return reflect.TypeFor[uint]()
	case reflect.Uint8:
		return reflect.TypeFor[uint8]()
	case reflect.Uint16:
		return reflect.TypeFor[uint16]()
	case reflect.Uint32:
		return reflect.TypeFor[uint32]()
	case reflect.Uint64:
		return reflect.TypeFor[uint64]()
	case reflect.Float32:
		return reflect.TypeFor[float32]()
	case reflect.Float64:
		return reflect.TypeFor[float64]()
	default:
		return reflect.TypeFor[int64]()
	}
}

func sglDriverCompositeMap(reflected reflect.Value, depth int) (any, error) {
	if reflected.IsNil() {
		return nil, nil
	}
	converted := make(duckdb.Map, reflected.Len())
	entries := reflected.MapRange()
	for entries.Next() {
		element, err := sglDriverConvert(entries.Value().Interface(), depth+1)
		if err != nil {
			return nil, err
		}
		converted[sglDriverBasic(entries.Key())] = element
	}
	return converted, nil
}

func sglDriverCompositeSlice(reflected reflect.Value, depth int) (any, error) {
	if reflected.IsNil() {
		return nil, nil
	}
	if reflected.Type().Elem().Kind() == reflect.Uint8 {
		return reflected.Bytes(), nil
	}
	converted := make([]any, reflected.Len())
	for index := range reflected.Len() {
		element, err := sglDriverConvert(reflected.Index(index).Interface(), depth+1)
		if err != nil {
			return nil, err
		}
		converted[index] = element
	}
	return converted, nil
}

func sglDriverCompositeStruct(reflected reflect.Value, value any, depth int) (any, error) {
	structType := reflected.Type()
	fields := make(map[string]any, structType.NumField())
	for index := range structType.NumField() {
		field := structType.Field(index)
		if !field.IsExported() {
			return value, nil
		}
		name, _, _ := strings.Cut(field.Tag.Get("json"), ",")
		if name == "-" {
			continue
		}
		if name == "" {
			name = field.Name
		}
		element, err := sglDriverConvert(reflected.Field(index).Interface(), depth+1)
		if err != nil {
			return nil, err
		}
		fields[name] = element
	}
	return fields, nil
}

func sglScanDriverComposite[T any](dest *T) sql.Scanner {
	return sglDriverCompositeScanner[T]{dest: dest}
}

type sglDriverCompositeScanner[T any] struct {
	dest *T
}

func (scanner sglDriverCompositeScanner[T]) Scan(source any) error {
	if err := sglReadDriverValue(reflect.ValueOf(scanner.dest).Elem(), source); err != nil {
		return fmt.Errorf("sgl: read %T into %T: %w", source, *scanner.dest, err)
	}
	return nil
}

func sglReadDriverValue(target reflect.Value, source any) error {
	if source == nil {
		return sglReadDriverNull(target)
	}
	value := reflect.ValueOf(source)
	if value.Type().AssignableTo(target.Type()) {
		target.Set(value)
		return nil
	}
	if target.Kind() == reflect.Pointer {
		element := reflect.New(target.Type().Elem())
		if err := sglReadDriverValue(element.Elem(), source); err != nil {
			return err
		}
		target.Set(element)
		return nil
	}
	if read, err := sglReadDriverLeaf(target, source); read {
		return err
	}
	return sglReadDriverShape(target, value)
}

func sglReadDriverNull(target reflect.Value) error {
	switch target.Kind() {
	case reflect.Pointer, reflect.Slice, reflect.Map, reflect.Interface:
		target.SetZero()
		return nil
	}
	if scanner, scans := reflect.TypeAssert[sql.Scanner](target.Addr()); scans {
		return scanner.Scan(nil)
	}
	return fmt.Errorf("converting NULL to %s is unsupported", target.Type())
}

func sglReadDriverShape(target reflect.Value, value reflect.Value) error {
	switch target.Kind() {
	case reflect.Slice:
		return sglReadList(target, value, sglReadDriverValue)
	case reflect.Map:
		return sglReadMap(target, value, sglReadDriverValue)
	case reflect.Struct:
		return sglReadDriverStruct(target, value)
	default:
		return sglReadScalar(target, value)
	}
}

func sglReadDriverLeaf(target reflect.Value, source any) (bool, error) {
	switch typed := source.(type) {
	case duckdb.Decimal:
		return true, sglReadDriverDecimal(target, typed)
	case duckdb.Interval:
		if target.Kind() == reflect.String {
			target.SetString(sglDriverIntervalText(typed))
			return true, nil
		}
		return true, sglUnreadable(reflect.TypeOf(source), target.Type())
	}
	address := target.Addr().Interface()
	if unmarshaler, ok := address.(json.Unmarshaler); ok {
		encoded, err := json.Marshal(source)
		if err != nil {
			return true, err
		}
		return true, unmarshaler.UnmarshalJSON(encoded)
	}
	if scanner, ok := address.(sql.Scanner); ok {
		return true, scanner.Scan(source)
	}
	return false, nil
}

func sglReadDriverDecimal(target reflect.Value, number duckdb.Decimal) error {
	text := sglDriverDecimalText(number)
	address := target.Addr().Interface()
	if unmarshaler, ok := address.(encoding.TextUnmarshaler); ok {
		return unmarshaler.UnmarshalText([]byte(text))
	}
	switch target.Kind() {
	case reflect.String:
		target.SetString(text)
		return nil
	case reflect.Float32, reflect.Float64:
		parsed, err := strconv.ParseFloat(text, target.Type().Bits())
		if err != nil {
			return err
		}
		target.SetFloat(parsed)
		return nil
	default:
		return fmt.Errorf("cannot read a decimal into %s", target.Type())
	}
}

func sglDriverDecimalText(number duckdb.Decimal) string {
	if number.Value == nil {
		return "0"
	}
	digits := new(big.Int).Abs(number.Value).String()
	scale := int(number.Scale)
	if scale == 0 {
		return number.Value.String()
	}
	if len(digits) <= scale {
		digits = strings.Repeat("0", scale-len(digits)+1) + digits
	}
	sign := ""
	if number.Value.Sign() < 0 {
		sign = sglMinus
	}
	return sign + digits[:len(digits)-scale] + "." + digits[len(digits)-scale:]
}

func sglReadDriverStruct(target reflect.Value, value reflect.Value) error {
	source := value.Interface()
	fields, ok := source.(map[string]any)
	if !ok {
		return sglUnreadable(value.Type(), target.Type())
	}
	target.SetZero()
	structType := target.Type()
	for index := range structType.NumField() {
		name, readable := sglDriverFieldName(structType.Field(index))
		if !readable {
			continue
		}
		element, found := sglDriverField(fields, name)
		if !found {
			continue
		}
		if err := sglReadDriverValue(target.Field(index), element); err != nil {
			return fmt.Errorf("field %s: %w", name, err)
		}
	}
	return nil
}

func sglDriverFieldName(field reflect.StructField) (string, bool) {
	if !field.IsExported() {
		return "", false
	}
	name, _, _ := strings.Cut(field.Tag.Get("json"), ",")
	switch name {
	case "-":
		return "", false
	case "":
		return field.Name, true
	default:
		return name, true
	}
}

func sglDriverField(fields map[string]any, name string) (any, bool) {
	if element, found := fields[name]; found {
		return element, true
	}
	for key, element := range fields {
		if strings.EqualFold(key, name) {
			return element, true
		}
	}
	return nil, false
}

func sglDriverIntervalText(interval duckdb.Interval) string {
	var parts []string
	parts = sglDriverIntervalPart(parts, int64(interval.Months/sglMonthsPerYear), "year")
	parts = sglDriverIntervalPart(parts, int64(interval.Months%sglMonthsPerYear), "month")
	parts = sglDriverIntervalPart(parts, int64(interval.Days), "day")
	if interval.Micros != 0 {
		parts = append(parts, sglDriverIntervalClock(interval.Micros))
	}
	if len(parts) == 0 {
		return "00:00:00"
	}
	return strings.Join(parts, " ")
}

func sglDriverIntervalPart(parts []string, count int64, unit string) []string {
	if count == 0 {
		return parts
	}
	if count != 1 && count != -1 {
		unit += "s"
	}
	return append(parts, strconv.FormatInt(count, 10)+" "+unit)
}

func sglDriverIntervalClock(micros int64) string {
	sign := ""
	remainder := micros
	if micros < 0 {
		sign = sglMinus
	} else {
		remainder = -micros
	}
	hours := -(remainder / sglMicrosecondsPerHour)
	remainder += hours * sglMicrosecondsPerHour
	minutes := -(remainder / sglMicrosecondsPerMinute)
	remainder += minutes * sglMicrosecondsPerMinute
	seconds := -(remainder / sglMicrosecondsPerSecond)
	clock := fmt.Sprintf("%s%02d:%02d:%02d", sign, hours, minutes, seconds)
	if fraction := -(remainder + seconds*sglMicrosecondsPerSecond); fraction != 0 {
		clock += "." + strings.TrimRight(fmt.Sprintf("%06d", fraction), "0")
	}
	return clock
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Voyages struct {
	ID        int64     `json:"id"`
	Boat      string    `json:"boat"`
	Ports     []*string `json:"ports"`
	CatchKg   []*int32  `json:"catch_kg"`
	Sightings []*string `json:"sightings"`
}

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/value_reading.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"fmt"
	"reflect"
)

type sglOrderedMap interface {
	Keys() []any
	Values() []any
}

func sglReadList(target reflect.Value, value reflect.Value, read func(reflect.Value, any) error) error {
	if value.Kind() != reflect.Slice && value.Kind() != reflect.Array {
		return sglUnreadable(value.Type(), target.Type())
	}
	if value.Kind() == reflect.Slice && value.IsNil() {
		target.SetZero()
		return nil
	}
	list := reflect.MakeSlice(target.Type(), value.Len(), value.Len())
	for index := range value.Len() {
		if err := read(list.Index(index), value.Index(index).Interface()); err != nil {
			return fmt.Errorf("element %d: %w", index, err)
		}
	}
	target.Set(list)
	return nil
}

func sglReadMap(target reflect.Value, value reflect.Value, read func(reflect.Value, any) error) error {
	if value.Kind() == reflect.Map && value.IsNil() {
		target.SetZero()
		return nil
	}
	keys, values, ok := sglMapEntries(value)
	if !ok {
		return sglUnreadable(value.Type(), target.Type())
	}
	converted := reflect.MakeMapWithSize(target.Type(), len(keys))
	for index, key := range keys {
		convertedKey := reflect.New(target.Type().Key()).Elem()
		if err := read(convertedKey, key); err != nil {
			return fmt.Errorf("key %v: %w", key, err)
		}
		element := reflect.New(target.Type().Elem()).Elem()
		if err := read(element, values[index]); err != nil {
			return fmt.Errorf("value of key %v: %w", key, err)
		}
		converted.SetMapIndex(convertedKey, element)
	}
	target.Set(converted)
	return nil
}

func sglMapEntries(value reflect.Value) (keys []any, values []any, ok bool) {
	if value.Kind() == reflect.Map {
		keys = make([]any, 0, value.Len())
		values = make([]any, 0, value.Len())
		entries := value.MapRange()
		for entries.Next() {
			keys = append(keys, entries.Key().Interface())
			values = append(values, entries.Value().Interface())
		}
		return keys, values, true
	}
	pointer := reflect.New(value.Type())
	pointer.Elem().Set(value)
	ordered, ordersEntries := reflect.TypeAssert[sglOrderedMap](pointer)
	if !ordersEntries {
		return nil, nil, false
	}
	keys, values = ordered.Keys(), ordered.Values()
	return keys, values, len(keys) == len(values)
}

func sglReadScalar(target reflect.Value, value reflect.Value) error {
	kind := sglScalarKind(value.Kind())
	if kind == "" || kind != sglScalarKind(target.Kind()) || !value.CanConvert(target.Type()) {
		return sglUnreadable(value.Type(), target.Type())
	}
	converted := value.Convert(target.Type())
	if !sglScalarHolds(converted, value) {
		return fmt.Errorf("%v overflows %s", value.Interface(), target.Type())
	}
	target.Set(converted)
	return nil
}

func sglScalarHolds(converted reflect.Value, value reflect.Value) bool {
	switch {
	case !value.CanInt() && !value.CanUint():
		return true
	case !converted.Convert(value.Type()).Equal(value):
		return false
	case value.CanInt() && converted.CanUint():
		return value.Int() >= 0
	case value.CanUint() && converted.CanInt():
		return converted.Int() >= 0
	default:
		return true
	}
}

func sglScalarKind(kind reflect.Kind) string {
	switch kind {
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
		reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
		return "integer"
	case reflect.Float32, reflect.Float64:
		return "float"
	case reflect.String:
		return "string"
	case reflect.Bool:
		return "boolean"
	default:
		return ""
	}
}

func sglUnreadable(source reflect.Type, target reflect.Type) error {
	return fmt.Errorf("cannot read %s into %s", source, target)
}

internal/store/voyages.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const listVoyagesSQL = `SELECT id, boat, ports, catch_kg, sightings FROM voyages ORDER BY id;`

type ListVoyagesRow struct {
	ID        int64     `json:"id"`
	Boat      string    `json:"boat"`
	Ports     []*string `json:"ports"`
	CatchKg   []*int32  `json:"catch_kg"`
	Sightings []*string `json:"sightings"`
}

func (queries *Queries) ListVoyages(ctx context.Context) ([]ListVoyagesRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listVoyagesSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListVoyagesRow
	for rows.Next() {
		var row ListVoyagesRow
		if err := rows.Scan(&row.ID, &row.Boat, sglScanDriverComposite(&row.Ports), sglScanDriverComposite(&row.CatchKg), sglScanDriverComposite(&row.Sightings)); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const logVoyageSQL = `INSERT INTO voyages (boat, ports, catch_kg, sightings) VALUES ($1, $2, $3, $4);`

type LogVoyageParams struct {
	Boat      string
	Ports     []string
	CatchKg   []int32
	Sightings []string
}

func (queries *Queries) LogVoyage(ctx context.Context, params LogVoyageParams) error {
	_, err := queries.writer.ExecContext(ctx, logVoyageSQL, params.Boat, params.Ports, params.CatchKg, sglDriverComposite(params.Sightings))
	return err
}

const voyagesCallingAtSQL = `SELECT boat, ports[1] AS first_port, array_length(ports) AS stops
FROM voyages
WHERE $1 = ANY(ports)
ORDER BY id;`

type VoyagesCallingAtRow struct {
	Boat      string  `json:"boat"`
	FirstPort *string `json:"first_port"`
	Stops     int64   `json:"stops"`
}

func (queries *Queries) VoyagesCallingAt(ctx context.Context, port string) ([]VoyagesCallingAtRow, error) {
	rows, err := queries.reader.QueryContext(ctx, voyagesCallingAtSQL, port)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []VoyagesCallingAtRow
	for rows.Next() {
		var row VoyagesCallingAtRow
		if err := rows.Scan(&row.Boat, &row.FirstPort, &row.Stops); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const voyagesPerBoatSQL = `SELECT boat, array_agg(id ORDER BY id) AS voyages
FROM voyages
GROUP BY boat
ORDER BY boat;`

type VoyagesPerBoatRow struct {
	Boat    string  `json:"boat"`
	Voyages []int64 `json:"voyages"`
}

func (queries *Queries) VoyagesPerBoat(ctx context.Context) ([]VoyagesPerBoatRow, error) {
	rows, err := queries.reader.QueryContext(ctx, voyagesPerBoatSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []VoyagesPerBoatRow
	for rows.Next() {
		var row VoyagesPerBoatRow
		if err := rows.Scan(&row.Boat, sglScanDriverComposite(&row.Voyages)); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

output.txt

voyage 1  Kittiwake   ports [Newlyn St Ives Mousehole], catch [120 85] kg, sightings [dolphin basking shark]
voyage 2  Salty Chip  ports [Penzance Mousehole], catch [40] kg, sightings NULL
voyage 3  Kittiwake   ports [Newlyn Lamorna], catch [60 30 15] kg, sightings []

calling at Mousehole:
  Kittiwake   from Newlyn, 3 stops
  Salty Chip  from Penzance, 2 stops

voyages per boat:
  Kittiwake   [1 3]
  Salty Chip  [2]

Also on other engines

PostgreSQLFishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.CockroachDBFishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.TimescaleDBFishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.ClickHouseHauls of fish with Array(String) and Array(UInt32) columns, landed from Go slices and read back as []string and []uint32, filtered on the server with has().