Arrays and lists as typed slices

On this page

On PostgreSQL:

  • database/sql cannot scan arrays, so the generated SQL wraps array columns in to_json() and decodes the JSON
  • A NULL array scans to a nil slice and an empty array to an empty one
  • PostgreSQL can hold NULL in any array, so rows read the elements as pointers, []*string and []*int64, and parameters take them too; non_null_elements: true opts out where the data never holds a NULL element, as the migration declares for ports, which reads and binds as []string
  • Slice parameters are sent as PostgreSQL arrays by pgx's database/sql driver; a nil slice is NULL
  • $1::text = ANY(ports) casts its parameter, which types it for Seagull and for CockroachDB
  • A subscript such as ports[1] is nullable, as a missing element is NULL
  • Integer columns are BIGINT, so the Go types are the same on CockroachDB, whose INTEGER is 64-bit

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

ports and catch_kg are arrays that cannot be NULL. sightings is an array that can.

db/migrations/0001_voyages.up.sql

CREATE TABLE voyages (
    id        BIGINT   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    boat      TEXT     NOT NULL,
    ports     TEXT[]   NOT NULL,
    catch_kg  BIGINT[] NOT NULL,
    sightings TEXT[]
);
-- sgl.column(voyages.ports, non_null_elements: true)

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   []*int64  `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, sglScanInto(&row.Ports), sglScanInto(&row.CatchKg), sglScanInto(&row.Sightings)); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

TEXT[] gives []string and BIGINT[] gives []int64. database/sql cannot scan a PostgreSQL array, so the generated SQL wraps each array 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 sends each slice as a PostgreSQL array, and a nil slice as NULL. Voyage 2 passes nil sightings and gets NULL back; voyage 3 passes an empty slice and gets an empty array.

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)
SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
FROM voyages
WHERE $1::text = 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 Seagull would leave it as any; the cast in $1::text makes it a string, and CockroachDB needs the cast to type it too. ports[1] reads the first element, as arrays count from 1. It is a *string, because an element that is not there is NULL. cardinality(ports) counts the elements.

internal/store

type VoyagesCallingAtRow struct {
	Boat      string  `json:"boat"`
	FirstPort *string `json:"first_port"`
	Stops     int32   `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/postgres/008_arrays/internal/store"
)

// run logs three fishing voyages and reads them back. An array column becomes a typed Go
// slice: TEXT[] gives []*string and BIGINT[] gives []*int64, and array_agg gives a slice
// of its argument's type, here []int64: the id column it collects is NOT NULL, so the
// array can hold no NULL and its elements are plain values. A slice parameter is sent as
// a PostgreSQL array, and a nil slice is sent as NULL.
//
// The elements are pointers because PostgreSQL can hold NULL in any array, whatever the
// column's type says, so a NULL element reads as nil, which elements prints as NULL.
// Parameters take the same pointers, so LogVoyage takes []*int64 for catch_kg and
// []*string for sightings, and a nil element writes NULL; values builds them from plain
// values. 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 and bind as plain values, and a NULL one fails the
// read. The migration declares it for ports, as a voyage never calls at a NULL port, so
// Ports is a plain []string in the model, the row and the parameters alike.
//
// database/sql cannot scan a PostgreSQL array, so the generated SQL wraps each array
// column in to_json() and decodes the JSON into the slice; open internal/store to see it.
// A NULL array therefore scans to a nil slice, and an empty array to an empty one.
//
// VoyagesCallingAt searches inside an array with $1::text = ANY(ports). The cast gives
// the parameter its type, for Seagull, which would otherwise leave it as any, and for
// CockroachDB, which cannot work it out from ANY. ports[1] reads one element, and is
// *string because a missing element is NULL.
//
// 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: values[int64](120, 85), Sightings: values("dolphin", "basking shark")},
		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: values[int64](40), Sightings: nil},
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: values[int64](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, 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 an array's elements as fmt prints a slice, with NULL for each element
// the database holds NULL in.
//
// Takes array ([]*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](array []*T) string {
	texts := make([]string, 0, len(array))
	for _, element := range array {
		if element == nil {
			texts = append(texts, "NULL")
			continue
		}
		texts = append(texts, fmt.Sprint(*element))
	}
	return fmt.Sprint(texts)
}

// values returns pointers to copies of plain values, as an array parameter whose elements
// may be NULL takes them.
//
// Takes items (...T) which are the elements.
//
// Returns []*T which points at the copies, none of them nil.
func values[T any](items ...T) []*T {
	pointed := make([]*T, len(items))
	for index := range items {
		pointed[index] = &items[index]
	}
	return pointed
}

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.

With pgx

The fishing voyages through pgx, which reads TEXT[], BIGINT[] and array_agg results as native PostgreSQL arrays into the same typed slices.

What changes:

  • pgx encodes and decodes arrays itself, so the SQL keeps its array columns as written, with no to_json() wrapper
  • The Row and Params types are the same as database/sql's: rows read []*string and []*int64, as PostgreSQL can hold NULL in any array, and parameters take them too; non_null_elements: true opts out where the data never holds a NULL element, as the migration declares for ports, which reads and binds as []string
  • A NULL array scans to a nil slice and an empty array to an empty one
  • $1::text = ANY(ports) casts its parameter, which types it for Seagull and for CockroachDB

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)
SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
FROM voyages
WHERE $1::text = 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;

internal/store

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

func (queries *Queries) ListVoyages(ctx context.Context) ([]ListVoyagesRow, error) {
	rows, err := queries.db.Query(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, &row.Ports, &row.CatchKg, &row.Sightings); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

example.go

package main

import (
	"context"
	"fmt"
	"io"

	"github.com/jackc/pgx/v5/pgxpool"

	"seagull.sh/seagull/examples/postgres/008_arrays_pgx/internal/store"
)

// run logs three fishing voyages and reads them back through pgx. An array column becomes
// a typed Go slice: TEXT[] gives []*string and BIGINT[] gives []*int64, and array_agg
// gives a slice of its argument's type, here []int64: the id column it collects is NOT
// NULL, so the array can hold no NULL and its elements are plain values. These are the
// database/sql emitter's types, but pgx encodes and decodes PostgreSQL arrays itself, so
// the generated SQL is the SQL you wrote, with no to_json() wrapper. A nil slice is sent
// as NULL, a NULL array scans to a nil slice, and an empty array to an empty one.
//
// The elements are pointers because PostgreSQL can hold NULL in any array, whatever the
// column's type says, so a NULL element reads as nil, which elements prints as NULL.
// Parameters take the same pointers, so LogVoyage takes []*int64 for catch_kg and
// []*string for sightings, and a nil element writes NULL; values builds them from plain
// values. 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 and bind as plain values, and a NULL one fails the
// read. The migration declares it for ports, as a voyage never calls at a NULL port, so
// Ports is a plain []string in the model, the row and the parameters alike.
//
// VoyagesCallingAt searches inside an array with $1::text = ANY(ports). The cast gives
// the parameter its type, for Seagull, which would otherwise leave it as any, and for
// CockroachDB, which cannot work it out from ANY. ports[1] reads one element, and is
// *string because a missing element is NULL.
//
// Takes pool (*pgxpool.Pool) which is a pool on 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, pool *pgxpool.Pool, output io.Writer) error {
	queries := store.New(pool)

	voyages := []store.LogVoyageParams{
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: values[int64](120, 85), Sightings: values("dolphin", "basking shark")},
		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: values[int64](40), Sightings: nil},
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: values[int64](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, 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 an array's elements as fmt prints a slice, with NULL for each element
// the database holds NULL in.
//
// Takes array ([]*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](array []*T) string {
	texts := make([]string, 0, len(array))
	for _, element := range array {
		if element == nil {
			texts = append(texts, "NULL")
			continue
		}
		texts = append(texts, fmt.Sprint(*element))
	}
	return fmt.Sprint(texts)
}

// values returns pointers to copies of plain values, as an array parameter whose elements
// may be NULL takes them.
//
// Takes items (...T) which are the elements.
//
// Returns []*T which points at the copies, none of them nil.
func values[T any](items ...T) []*T {
	pointed := make([]*T, len(items))
	for index := range items {
		pointed[index] = &items[index]
	}
	return pointed
}

Across engines

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

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

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with DuckDB.

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/postgres && go run ./008_arrays

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

cd seagull/examples/postgres && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./008_arrays

Needs a running PostgreSQL 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: voyages
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql

db/migrations/0001_voyages.up.sql

CREATE TABLE voyages (
    id        BIGINT   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    boat      TEXT     NOT NULL,
    ports     TEXT[]   NOT NULL,
    catch_kg  BIGINT[] NOT NULL,
    sightings TEXT[]
);
-- sgl.column(voyages.ports, non_null_elements: true)

db/migrations/0001_voyages.down.sql

DROP TABLE voyages;

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)
SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
FROM voyages
WHERE $1::text = 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/postgres/008_arrays/internal/store"
)

// run logs three fishing voyages and reads them back. An array column becomes a typed Go
// slice: TEXT[] gives []*string and BIGINT[] gives []*int64, and array_agg gives a slice
// of its argument's type, here []int64: the id column it collects is NOT NULL, so the
// array can hold no NULL and its elements are plain values. A slice parameter is sent as
// a PostgreSQL array, and a nil slice is sent as NULL.
//
// The elements are pointers because PostgreSQL can hold NULL in any array, whatever the
// column's type says, so a NULL element reads as nil, which elements prints as NULL.
// Parameters take the same pointers, so LogVoyage takes []*int64 for catch_kg and
// []*string for sightings, and a nil element writes NULL; values builds them from plain
// values. 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 and bind as plain values, and a NULL one fails the
// read. The migration declares it for ports, as a voyage never calls at a NULL port, so
// Ports is a plain []string in the model, the row and the parameters alike.
//
// database/sql cannot scan a PostgreSQL array, so the generated SQL wraps each array
// column in to_json() and decodes the JSON into the slice; open internal/store to see it.
// A NULL array therefore scans to a nil slice, and an empty array to an empty one.
//
// VoyagesCallingAt searches inside an array with $1::text = ANY(ports). The cast gives
// the parameter its type, for Seagull, which would otherwise leave it as any, and for
// CockroachDB, which cannot work it out from ANY. ports[1] reads one element, and is
// *string because a missing element is NULL.
//
// 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: values[int64](120, 85), Sightings: values("dolphin", "basking shark")},
		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: values[int64](40), Sightings: nil},
		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: values[int64](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, 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 an array's elements as fmt prints a slice, with NULL for each element
// the database holds NULL in.
//
// Takes array ([]*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](array []*T) string {
	texts := make([]string, 0, len(array))
	for _, element := range array {
		if element == nil {
			texts = append(texts, "NULL")
			continue
		}
		texts = append(texts, fmt.Sprint(*element))
	}
	return fmt.Sprint(texts)
}

// values returns pointers to copies of plain values, as an array parameter whose elements
// may be NULL takes them.
//
// Takes items (...T) which are the elements.
//
// Returns []*T which points at the copies, none of them nil.
func values[T any](items ...T) []*T {
	pointed := make([]*T, len(items))
	for index := range items {
		pointed[index] = &items[index]
	}
	return pointed
}

main.go

// Command 008_arrays applies its migrations to a PostgreSQL database, then runs the
// example in example.go: fishing voyages are logged with array columns, read back as Go
// slices and searched with ANY.
//
// 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 Voyages struct {
	ID        int64     `json:"id"`
	Boat      string    `json:"boat"`
	Ports     []string  `json:"ports"`
	CatchKg   []*int64  `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/sgl_json.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"bytes"
	"database/sql"
	"encoding"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"reflect"
	"slices"
	"strings"
	"time"
)

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

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

func (s sglJSONScanner[T]) Scan(source any) error {
	var raw []byte
	switch value := source.(type) {
	case nil:
		var zero T
		*s.dest = zero
		return nil
	case []byte:
		raw = value
	case string:
		raw = []byte(value)
	default:
		encoded, err := json.Marshal(value)
		if err != nil {
			return fmt.Errorf("sgl: re-encode %T to JSON: %w", source, err)
		}
		raw = encoded
	}
	if len(raw) == 0 {
		var zero T
		*s.dest = zero
		return nil
	}
	if err := sglDecodeJSONColumn(raw, s.dest); err != nil {
		var zero T
		*s.dest = zero
		return fmt.Errorf("sgl: decode JSON column: %w", err)
	}
	return nil
}

func sglDecodeJSONColumn[T any](raw []byte, dest *T) error {
	fast := !bytes.Contains(raw, []byte("null"))
	var decodeErr error
	if fast {
		decodeErr = json.Unmarshal(raw, dest)
		if decodeErr == nil {
			return nil
		}
	}
	document, err := sglJSONDocument(raw)
	if err != nil {
		return err
	}
	if !fast {
		decodeErr = json.Unmarshal(raw, dest)
	}
	if decodeErr != nil && !sglDecodeEngineJSON(document, dest) {
		return decodeErr
	}
	return sglJSONNulls(document, reflect.TypeFor[T]())
}

func sglJSONDocument(raw []byte) (any, error) {
	var document any
	if !json.Valid(raw) {
		return nil, json.Unmarshal(raw, &document)
	}
	decoder := json.NewDecoder(bytes.NewReader(raw))
	decoder.UseNumber()
	if err := decoder.Decode(&document); err != nil {
		return nil, err
	}
	return document, nil
}

func sglDecodeEngineJSON[T any](document any, dest *T) bool {
	rewritten, err := json.Marshal(sglEngineJSONValue(document, reflect.TypeFor[T]()))
	if err != nil {
		return false
	}
	var zero T
	*dest = zero
	return json.Unmarshal(rewritten, dest) == nil
}

func sglEngineJSONValue(value any, target reflect.Type) any {
	for target.Kind() == reflect.Pointer {
		target = target.Elem()
	}
	switch typed := value.(type) {
	case []any:
		if target.Kind() == reflect.Slice || target.Kind() == reflect.Array {
			return sglEngineJSONElements(typed, target.Elem())
		}
	case map[string]any:
		if target.Kind() == reflect.Map {
			rewritten := make(map[string]any, len(typed))
			for key, element := range typed {
				rewritten[key] = sglEngineJSONValue(element, target.Elem())
			}
			return rewritten
		}
	case json.Number:
		if target.Kind() == reflect.String || sglUnmarshalsText(target) {
			return string(typed)
		}
	case string:
		return sglEngineJSONText(typed, target)
	}
	return value
}

func sglEngineJSONElements(elements []any, target reflect.Type) []any {
	rewritten := make([]any, len(elements))
	for index, element := range elements {
		rewritten[index] = sglEngineJSONValue(element, target)
	}
	return rewritten
}

func sglEngineJSONText(text string, target reflect.Type) any {
	switch {
	case target == reflect.TypeFor[time.Time]():
		for _, layout := range []string{"2006-01-02T15:04:05.999999999", time.DateOnly} {
			if moment, err := time.ParseInLocation(layout, text, time.UTC); err == nil {
				return moment.Format(time.RFC3339Nano)
			}
		}
	case target.Kind() == reflect.Slice && target.Elem().Kind() == reflect.Uint8 && !sglUnmarshalsJSON(target):
		if digits, found := strings.CutPrefix(text, `\x`); found {
			if decoded, err := hex.DecodeString(digits); err == nil {
				return decoded
			}
		}
	}
	return text
}

func sglUnmarshalsText(target reflect.Type) bool {
	return reflect.PointerTo(target).Implements(reflect.TypeFor[encoding.TextUnmarshaler]()) && !sglUnmarshalsJSON(target)
}

func sglUnmarshalsJSON(target reflect.Type) bool {
	return reflect.PointerTo(target).Implements(reflect.TypeFor[json.Unmarshaler]())
}

func sglJSONNulls(document any, target reflect.Type) error {
	for target.Kind() == reflect.Pointer {
		target = target.Elem()
	}
	switch typed := document.(type) {
	case []any:
		if target.Kind() == reflect.Slice || target.Kind() == reflect.Array {
			return sglJSONElementNulls(typed, target.Elem())
		}
	case map[string]any:
		if target.Kind() == reflect.Map {
			return sglJSONValueNulls(typed, target.Elem())
		}
	}
	return nil
}

func sglJSONElementNulls(elements []any, element reflect.Type) error {
	for index, value := range elements {
		if err := sglJSONNull(value, element); err != nil {
			return fmt.Errorf("element %d: %w", index, err)
		}
	}
	return nil
}

func sglJSONValueNulls(values map[string]any, element reflect.Type) error {
	keys := make([]string, 0, len(values))
	for key := range values {
		keys = append(keys, key)
	}
	slices.Sort(keys)
	for _, key := range keys {
		if err := sglJSONNull(values[key], element); err != nil {
			return fmt.Errorf("value of key %s: %w", key, err)
		}
	}
	return nil
}

func sglJSONNull(value any, target reflect.Type) error {
	if value != nil {
		return sglJSONNulls(value, target)
	}
	switch target.Kind() {
	case reflect.Pointer, reflect.Interface, reflect.Slice, reflect.Map:
		return nil
	}
	if reflect.PointerTo(target).Implements(reflect.TypeFor[json.Unmarshaler]()) {
		return nil
	}
	return fmt.Errorf("converting NULL to %s is unsupported", target)
}

internal/store/voyages.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const listVoyagesSQL = `SELECT id, boat, to_json(ports) AS "ports", to_json(catch_kg) AS "catch_kg", to_json(sightings) AS "sightings" FROM voyages ORDER BY id;`

type ListVoyagesRow struct {
	ID        int64     `json:"id"`
	Boat      string    `json:"boat"`
	Ports     []string  `json:"ports"`
	CatchKg   []*int64  `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, sglScanInto(&row.Ports), sglScanInto(&row.CatchKg), sglScanInto(&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   []*int64
	Sightings []*string
}

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

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

type VoyagesCallingAtRow struct {
	Boat      string  `json:"boat"`
	FirstPort *string `json:"first_port"`
	Stops     int32   `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, to_json(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, sglScanInto(&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

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.DuckDBFishing voyages with VARCHAR[] and INTEGER[] list 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().