JSON columns

The same on TimescaleDB. TimescaleDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes.

A JSON column holds a whole document in one value: a basket of items, a set of preferences, a payload you do not want to split into tables. The database can still search inside it. On the Go side you want to keep your own struct for the document, and let the generated code carry it in and out of the database without getting in the way.

Seagull gives every JSON column one generated type, JSON, which holds the raw document as bytes. You fill it with json.Marshal and read it with json.Unmarshal into whatever struct you like. Fragments extracted inside a query come back as JSON too, and values extracted as text come back as plain strings.

This example keeps a chip shop's orders. Each has a basket document, and a delivery document only when the order is delivered.

The schema

db/migrations/0001_orders.up.sql

CREATE TABLE orders (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer TEXT   NOT NULL,
    basket   JSONB  NOT NULL,
    delivery JSONB
);

The JSON type

sgl generate writes the JSON type into its own file in your package, so there is nothing extra to import:

internal/store/sgl_json.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
)

type JSON []byte

func (j *JSON) Scan(source any) error {
	switch value := source.(type) {
	case nil:
		*j = nil
		return nil
	case []byte:
		clone := make(JSON, len(value))
		copy(clone, value)
		*j = clone
		return nil
	case string:
		*j = JSON(value)
		return nil
	default:
		encoded, err := json.Marshal(value)
		if err != nil {
			return fmt.Errorf("sgl: encode %T to JSON: %w", source, err)
		}
		*j = encoded
		return nil
	}
}

func (j JSON) Value() (driver.Value, error) {
	if j == nil {
		return nil, nil
	}
	return string(j), nil
}

func (j JSON) MarshalJSON() ([]byte, error) {
	if j == nil {
		return []byte("null"), nil
	}
	return j, nil
}

func (j *JSON) UnmarshalJSON(data []byte) error {
	clone := make(JSON, len(data))
	copy(clone, data)
	*j = clone
	return nil
}

It is a []byte with the methods database/sql and encoding/json look for. Scan accepts whatever shape the driver returns for a JSON column, and a nil JSON is SQL NULL. That is why the nullable delivery column is a plain JSON, not a pointer: nil already says "no document".

Reading a document

internal/store

type GetOrderRow struct {
	ID       int64  `json:"id,omitempty" db:"id"`
	Customer string `json:"customer,omitempty" db:"customer"`
	Basket   JSON   `json:"basket,omitempty" db:"basket"`
	Delivery JSON   `json:"delivery,omitempty" db:"delivery"`
}

type JSON []byte

func (queries *Queries) GetOrder(ctx context.Context, id int64) (GetOrderRow, error) {
	var row GetOrderRow
	err := queries.reader.QueryRowContext(ctx, getOrderSQL, id).Scan(&row.ID, &row.Customer, &row.Basket, &row.Delivery)
	if err != nil {
		return GetOrderRow{}, err
	}
	return row, nil
}

Searching inside documents

OrdersWithFish finds the orders for one kind of fish, and pulls two things out of each document as it goes.

db/queries/orders.sql

-- sgl.query(OrdersWithFish, many)
-- $1 as sgl.param(fish, type: text)
SELECT
    id,
    customer,
    basket -> 'extras' AS extras,
    delivery ->> 'address' AS delivery_address
FROM orders
WHERE basket ->> 'fish' = $1
ORDER BY id;

->> extracts a value as text, and -> extracts a fragment as JSONB. Keys are plain names, not $. paths. So Extras is JSON again, ready to decode, and Address is text. It is a *string, because ->> gives NULL when the delivery column or its address is missing.

The value compared with ->> could be any type as far as the SQL says, so inference leaves the parameter as any. The binding line sgl.param(fish, type: text) pins it to a string.

internal/store

type OrdersWithFishRow struct {
	ID              int64   `json:"id,omitempty" db:"id"`
	Customer        string  `json:"customer,omitempty" db:"customer"`
	Extras          JSON    `json:"extras,omitempty" db:"extras"`
	DeliveryAddress *string `json:"deliveryAddress,omitempty" db:"delivery_address"`
}

type JSON []byte

func (queries *Queries) OrdersWithFish(ctx context.Context, fish string) ([]OrdersWithFishRow, error) {
	rows, err := queries.reader.QueryContext(ctx, ordersWithFishSQL, fish)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	results := []OrdersWithFishRow{}
	for rows.Next() {
		var row OrdersWithFishRow
		if err := rows.Scan(&row.ID, &row.Customer, &row.Extras, &row.DeliveryAddress); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

OrdersContaining uses @>, which matches a document that contains another one. Casting the parameter with $1::jsonb makes it a JSON in Go, so the program passes a document rather than a string:

db/queries/orders.sql

-- sgl.query(OrdersContaining, many)
-- $1 as sgl.param(subset)
SELECT id, customer
FROM orders
WHERE basket @> $1::jsonb
ORDER BY id;

Using it

The program encodes each basket with json.Marshal and places three orders, only one of them for delivery. It reads the first order back and decodes its basket, then searches for the cod orders.

example.go

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"io"
	"reflect"
	"strings"

	"seagull.sh/seagull/examples/postgres/006_json/internal/store"
)

// basket is the document an order's basket column holds.
type basket struct {
	// Fish is the fish the order is for.
	Fish string `json:"fish"`

	// Portions is how many portions of fish and chips.
	Portions int `json:"portions"`

	// Extras lists the sides.
	Extras []string `json:"extras"`
}

// delivery is the document an order's delivery column holds, when the order is delivered.
type delivery struct {
	// Address is where the order goes.
	Address string `json:"address"`
}

// run places three chip shop orders and reads them back. A JSONB column becomes the
// generated store.JSON type, which holds the raw document as bytes: encode a value into
// it with json.Marshal and decode it with json.Unmarshal. A nil store.JSON is SQL NULL.
//
// JSONB stores a parsed document, not the text it was sent, and writes it back in its own
// form: a space after each comma and colon, keys reordered and duplicate keys dropped, so
// compare documents by decoding them, not by their bytes.
//
// OrdersWithFish filters with ->>, which extracts a value as text, and selects with ->,
// which extracts a JSONB fragment, so Extras is store.JSON again. DeliveryAddress is
// *string because ->> gives NULL when the delivery column or its address is missing. The
// ->> comparison's parameter is pinned with sgl.param(fish, type: text), as inference
// leaves it as any. OrdersContaining uses @>, which matches a document containing the one
// bound to $1::jsonb.
//
// The rows go straight out as JSON, as an HTTP handler would send them, so sgl.yaml
// shapes the generated structs for that: go.json_tags writes each field's json tag in
// camel case with omitempty, so delivery_address is "deliveryAddress" and a nil one is
// left out; go.struct_tags adds a db tag holding the column's name, {name}, for libraries
// that map columns by tag; and go.empty_slices makes a many method return an empty slice
// when no row matches, which encodes as [] rather than null.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the decoded orders and the searches.
//
// Returns error when a query fails or a document does not encode or decode.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	orders := []struct {
		customer string
		basket   basket
		delivery *delivery
	}{
		{customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
		{customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
		{customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
	}
	for _, order := range orders {
		encodedBasket, err := json.Marshal(order.basket)
		if err != nil {
			return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
		}
		parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
		if order.delivery != nil {
			encodedDelivery, err := json.Marshal(order.delivery)
			if err != nil {
				return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
			}
			parameters.Delivery = encodedDelivery
		}
		if err := queries.PlaceOrder(ctx, parameters); err != nil {
			return fmt.Errorf("placing %s's order: %w", order.customer, err)
		}
	}

	first, err := queries.GetOrder(ctx, 1)
	if err != nil {
		return fmt.Errorf("getting order 1: %w", err)
	}
	var decoded basket
	if err := json.Unmarshal(first.Basket, &decoded); err != nil {
		return fmt.Errorf("decoding order 1's basket: %w", err)
	}
	fmt.Fprintf(output, "order 1 decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)

	cod, err := queries.OrdersWithFish(ctx, "cod")
	if err != nil {
		return fmt.Errorf("finding cod orders: %w", err)
	}
	fmt.Fprintln(output, "\ncod orders:")
	for _, order := range cod {
		address := "collecting"
		if order.DeliveryAddress != nil {
			address = "to " + *order.DeliveryAddress
		}
		fmt.Fprintf(output, "  order %d  %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
	}
	if err := writeJSON(output, "cod orders as JSON", cod); err != nil {
		return err
	}

	plaice, err := queries.OrdersWithFish(ctx, "plaice")
	if err != nil {
		return fmt.Errorf("finding plaice orders: %w", err)
	}
	if err := writeJSON(output, "plaice orders as JSON", plaice); err != nil {
		return err
	}
	field, _ := reflect.TypeFor[store.OrdersWithFishRow]().FieldByName("DeliveryAddress")
	fmt.Fprintf(output, "DeliveryAddress is tagged %s\n", field.Tag)

	subset := store.JSON(`{"extras": ["pickled egg"]}`)
	containing, err := queries.OrdersContaining(ctx, subset)
	if err != nil {
		return fmt.Errorf("finding orders containing %s: %w", subset, err)
	}
	customers := make([]string, 0, len(containing))
	for _, order := range containing {
		customers = append(customers, order.Customer)
	}
	fmt.Fprintf(output, "\norders containing %s: %s\n", subset, strings.Join(customers, ", "))
	return nil
}

// writeJSON writes rows encoded as JSON, as an HTTP handler would send them, after a
// label.
//
// Takes output (io.Writer) which receives the label and the document.
// Takes label (string) which says what the rows are.
// Takes rows (any) which are the rows to encode.
//
// Returns error when the rows do not encode.
func writeJSON(output io.Writer, label string, rows any) error {
	encoded, err := json.Marshal(rows)
	if err != nil {
		return fmt.Errorf("encoding %s: %w", label, err)
	}
	fmt.Fprintf(output, "%s: %s\n", label, encoded)
	return nil
}

What it prints

JSONB stores a parsed document, not the text it was sent, and writes it back in its own form, with a space after each comma and colon and the keys reordered. The program prints the decoded basket rather than the stored text for that reason: compare documents by decoding them, not by their bytes.

order 1 decoded: 2 x cod with [mushy peas curry sauce], delivery is NULL: true

cod orders:
  order 1  Steven    extras ["mushy peas", "curry sauce"]  collecting
  order 3  Morwenna  extras ["pickled egg"]                to 4 Harbour Row
cod orders as JSON: [{"id":1,"customer":"Steven","extras":["mushy peas","curry sauce"]},{"id":3,"customer":"Morwenna","extras":["pickled egg"],"deliveryAddress":"4 Harbour Row"}]
plaice orders as JSON: []
DeliveryAddress is tagged json:"deliveryAddress,omitempty" db:"delivery_address"

orders containing {"extras": ["pickled egg"]}: Morwenna

Across engines

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

	PostgreSQL	SQLite	MySQL	MariaDB	DuckDB	ClickHouse
id column	int64	int64	int64	int64	int64	uint64
extras column	JSON	JSON	JSON	JSON	JSON	string
address column	·	*string	*string	*string	*string	*string
delivery_address column	*string	·	·	·	·	·

A · marks a parameter or column that version of the example does not have.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with SQLite, MySQL, MariaDB, DuckDB and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./006_json

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=timescaledb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./006_json

Needs a running TimescaleDB 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: orders
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
      json_tags:
        case: camel
        omitempty: true
      struct_tags:
        db: "{name}"
      empty_slices: true

db/migrations/0001_orders.up.sql

CREATE TABLE orders (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer TEXT   NOT NULL,
    basket   JSONB  NOT NULL,
    delivery JSONB
);

db/migrations/0001_orders.down.sql

DROP TABLE orders;

db/queries/orders.sql

-- sgl.query(PlaceOrder, exec)
INSERT INTO orders (customer, basket, delivery) VALUES ($1, $2, $3);

-- sgl.query(GetOrder, one)
SELECT id, customer, basket, delivery FROM orders WHERE id = $1;

-- sgl.query(OrdersWithFish, many)
-- $1 as sgl.param(fish, type: text)
SELECT
    id,
    customer,
    basket -> 'extras' AS extras,
    delivery ->> 'address' AS delivery_address
FROM orders
WHERE basket ->> 'fish' = $1
ORDER BY id;

-- sgl.query(OrdersContaining, many)
-- $1 as sgl.param(subset)
SELECT id, customer
FROM orders
WHERE basket @> $1::jsonb
ORDER BY id;

example.go

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"io"
	"reflect"
	"strings"

	"seagull.sh/seagull/examples/postgres/006_json/internal/store"
)

// basket is the document an order's basket column holds.
type basket struct {
	// Fish is the fish the order is for.
	Fish string `json:"fish"`

	// Portions is how many portions of fish and chips.
	Portions int `json:"portions"`

	// Extras lists the sides.
	Extras []string `json:"extras"`
}

// delivery is the document an order's delivery column holds, when the order is delivered.
type delivery struct {
	// Address is where the order goes.
	Address string `json:"address"`
}

// run places three chip shop orders and reads them back. A JSONB column becomes the
// generated store.JSON type, which holds the raw document as bytes: encode a value into
// it with json.Marshal and decode it with json.Unmarshal. A nil store.JSON is SQL NULL.
//
// JSONB stores a parsed document, not the text it was sent, and writes it back in its own
// form: a space after each comma and colon, keys reordered and duplicate keys dropped, so
// compare documents by decoding them, not by their bytes.
//
// OrdersWithFish filters with ->>, which extracts a value as text, and selects with ->,
// which extracts a JSONB fragment, so Extras is store.JSON again. DeliveryAddress is
// *string because ->> gives NULL when the delivery column or its address is missing. The
// ->> comparison's parameter is pinned with sgl.param(fish, type: text), as inference
// leaves it as any. OrdersContaining uses @>, which matches a document containing the one
// bound to $1::jsonb.
//
// The rows go straight out as JSON, as an HTTP handler would send them, so sgl.yaml
// shapes the generated structs for that: go.json_tags writes each field's json tag in
// camel case with omitempty, so delivery_address is "deliveryAddress" and a nil one is
// left out; go.struct_tags adds a db tag holding the column's name, {name}, for libraries
// that map columns by tag; and go.empty_slices makes a many method return an empty slice
// when no row matches, which encodes as [] rather than null.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the decoded orders and the searches.
//
// Returns error when a query fails or a document does not encode or decode.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	orders := []struct {
		customer string
		basket   basket
		delivery *delivery
	}{
		{customer: "Steven", basket: basket{Fish: "cod", Portions: 2, Extras: []string{"mushy peas", "curry sauce"}}},
		{customer: "Gwen", basket: basket{Fish: "haddock", Portions: 1, Extras: []string{}}},
		{customer: "Morwenna", basket: basket{Fish: "cod", Portions: 1, Extras: []string{"pickled egg"}}, delivery: &delivery{Address: "4 Harbour Row"}},
	}
	for _, order := range orders {
		encodedBasket, err := json.Marshal(order.basket)
		if err != nil {
			return fmt.Errorf("encoding %s's basket: %w", order.customer, err)
		}
		parameters := store.PlaceOrderParams{Customer: order.customer, Basket: encodedBasket, Delivery: nil}
		if order.delivery != nil {
			encodedDelivery, err := json.Marshal(order.delivery)
			if err != nil {
				return fmt.Errorf("encoding %s's delivery: %w", order.customer, err)
			}
			parameters.Delivery = encodedDelivery
		}
		if err := queries.PlaceOrder(ctx, parameters); err != nil {
			return fmt.Errorf("placing %s's order: %w", order.customer, err)
		}
	}

	first, err := queries.GetOrder(ctx, 1)
	if err != nil {
		return fmt.Errorf("getting order 1: %w", err)
	}
	var decoded basket
	if err := json.Unmarshal(first.Basket, &decoded); err != nil {
		return fmt.Errorf("decoding order 1's basket: %w", err)
	}
	fmt.Fprintf(output, "order 1 decoded: %d x %s with %v, delivery is NULL: %t\n", decoded.Portions, decoded.Fish, decoded.Extras, first.Delivery == nil)

	cod, err := queries.OrdersWithFish(ctx, "cod")
	if err != nil {
		return fmt.Errorf("finding cod orders: %w", err)
	}
	fmt.Fprintln(output, "\ncod orders:")
	for _, order := range cod {
		address := "collecting"
		if order.DeliveryAddress != nil {
			address = "to " + *order.DeliveryAddress
		}
		fmt.Fprintf(output, "  order %d  %-9s extras %-30s %s\n", order.ID, order.Customer, order.Extras, address)
	}
	if err := writeJSON(output, "cod orders as JSON", cod); err != nil {
		return err
	}

	plaice, err := queries.OrdersWithFish(ctx, "plaice")
	if err != nil {
		return fmt.Errorf("finding plaice orders: %w", err)
	}
	if err := writeJSON(output, "plaice orders as JSON", plaice); err != nil {
		return err
	}
	field, _ := reflect.TypeFor[store.OrdersWithFishRow]().FieldByName("DeliveryAddress")
	fmt.Fprintf(output, "DeliveryAddress is tagged %s\n", field.Tag)

	subset := store.JSON(`{"extras": ["pickled egg"]}`)
	containing, err := queries.OrdersContaining(ctx, subset)
	if err != nil {
		return fmt.Errorf("finding orders containing %s: %w", subset, err)
	}
	customers := make([]string, 0, len(containing))
	for _, order := range containing {
		customers = append(customers, order.Customer)
	}
	fmt.Fprintf(output, "\norders containing %s: %s\n", subset, strings.Join(customers, ", "))
	return nil
}

// writeJSON writes rows encoded as JSON, as an HTTP handler would send them, after a
// label.
//
// Takes output (io.Writer) which receives the label and the document.
// Takes label (string) which says what the rows are.
// Takes rows (any) which are the rows to encode.
//
// Returns error when the rows do not encode.
func writeJSON(output io.Writer, label string, rows any) error {
	encoded, err := json.Marshal(rows)
	if err != nil {
		return fmt.Errorf("encoding %s: %w", label, err)
	}
	fmt.Fprintf(output, "%s: %s\n", label, encoded)
	return nil
}

main.go

// Command 006_json applies its migrations to a PostgreSQL database, then runs the example
// in example.go: chip shop orders are stored as JSONB documents and searched with ->, ->>
// and @>.
//
// 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 Orders struct {
	ID       int64  `json:"id,omitempty" db:"id"`
	Customer string `json:"customer,omitempty" db:"customer"`
	Basket   JSON   `json:"basket,omitempty" db:"basket"`
	Delivery JSON   `json:"delivery,omitempty" db:"delivery"`
}

internal/store/orders.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const getOrderSQL = `SELECT id, customer, basket, delivery FROM orders WHERE id = $1;`

type GetOrderRow struct {
	ID       int64  `json:"id,omitempty" db:"id"`
	Customer string `json:"customer,omitempty" db:"customer"`
	Basket   JSON   `json:"basket,omitempty" db:"basket"`
	Delivery JSON   `json:"delivery,omitempty" db:"delivery"`
}

func (queries *Queries) GetOrder(ctx context.Context, id int64) (GetOrderRow, error) {
	var row GetOrderRow
	err := queries.reader.QueryRowContext(ctx, getOrderSQL, id).Scan(&row.ID, &row.Customer, &row.Basket, &row.Delivery)
	if err != nil {
		return GetOrderRow{}, err
	}
	return row, nil
}

const ordersContainingSQL = `SELECT id, customer
FROM orders
WHERE basket @> $1::jsonb
ORDER BY id;`

type OrdersContainingRow struct {
	ID       int64  `json:"id,omitempty" db:"id"`
	Customer string `json:"customer,omitempty" db:"customer"`
}

func (queries *Queries) OrdersContaining(ctx context.Context, subset JSON) ([]OrdersContainingRow, error) {
	rows, err := queries.reader.QueryContext(ctx, ordersContainingSQL, subset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	results := []OrdersContainingRow{}
	if rows.Next() {
		var row OrdersContainingRow
		scanArgs := []any{&row.ID, &row.Customer}
		for more := true; more; more = rows.Next() {
			row = OrdersContainingRow{}
			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
}

const ordersWithFishSQL = `SELECT
    id,
    customer,
    basket -> 'extras' AS extras,
    delivery ->> 'address' AS delivery_address
FROM orders
WHERE basket ->> 'fish' = $1
ORDER BY id;`

type OrdersWithFishRow struct {
	ID              int64   `json:"id,omitempty" db:"id"`
	Customer        string  `json:"customer,omitempty" db:"customer"`
	Extras          JSON    `json:"extras,omitempty" db:"extras"`
	DeliveryAddress *string `json:"deliveryAddress,omitempty" db:"delivery_address"`
}

func (queries *Queries) OrdersWithFish(ctx context.Context, fish string) ([]OrdersWithFishRow, error) {
	rows, err := queries.reader.QueryContext(ctx, ordersWithFishSQL, fish)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	results := []OrdersWithFishRow{}
	for rows.Next() {
		var row OrdersWithFishRow
		if err := rows.Scan(&row.ID, &row.Customer, &row.Extras, &row.DeliveryAddress); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const placeOrderSQL = `INSERT INTO orders (customer, basket, delivery) VALUES ($1, $2, $3);`

type PlaceOrderParams struct {
	Customer string
	Basket   JSON
	Delivery JSON
}

func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) error {
	_, err := queries.writer.ExecContext(ctx, placeOrderSQL, params.Customer, params.Basket, params.Delivery)
	return err
}

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 (
	"database/sql/driver"
	"encoding/json"
	"fmt"
)

type JSON []byte

func (j *JSON) Scan(source any) error {
	switch value := source.(type) {
	case nil:
		*j = nil
		return nil
	case []byte:
		clone := make(JSON, len(value))
		copy(clone, value)
		*j = clone
		return nil
	case string:
		*j = JSON(value)
		return nil
	default:
		encoded, err := json.Marshal(value)
		if err != nil {
			return fmt.Errorf("sgl: encode %T to JSON: %w", source, err)
		}
		*j = encoded
		return nil
	}
}

func (j JSON) Value() (driver.Value, error) {
	if j == nil {
		return nil, nil
	}
	return string(j), nil
}

func (j JSON) MarshalJSON() ([]byte, error) {
	if j == nil {
		return []byte("null"), nil
	}
	return j, nil
}

func (j *JSON) UnmarshalJSON(data []byte) error {
	clone := make(JSON, len(data))
	copy(clone, data)
	*j = clone
	return nil
}

output.txt

order 1 decoded: 2 x cod with [mushy peas curry sauce], delivery is NULL: true

cod orders:
  order 1  Steven    extras ["mushy peas", "curry sauce"]  collecting
  order 3  Morwenna  extras ["pickled egg"]                to 4 Harbour Row
cod orders as JSON: [{"id":1,"customer":"Steven","extras":["mushy peas","curry sauce"]},{"id":3,"customer":"Morwenna","extras":["pickled egg"],"deliveryAddress":"4 Harbour Row"}]
plaice orders as JSON: []
DeliveryAddress is tagged json:"deliveryAddress,omitempty" db:"delivery_address"

orders containing {"extras": ["pickled egg"]}: Morwenna