Types through CTEs, window functions and views

On this page

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

Reporting queries are rarely a plain SELECT from one table. They build intermediate results with WITH, walk hierarchies with a recursive CTE, rank and total rows with window functions, and read from views. Seagull follows each column's type and nullability through all of these, so a report gets the same typed row struct as a simple lookup, and you do not have to restate what the SQL already says.

This example keeps a colony of gulls, some of them the chicks of others, and a record of the snacks each has stolen. It reads the colony back three ways.

The schema

The migration creates two tables and a view. Seagull reads the view's own SELECT when it replays the migration, so it knows the type of every column the view returns, and a query can read from the view as if it were a table.

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name      TEXT   NOT NULL,
    parent_id BIGINT REFERENCES gulls (id),
    beach     TEXT   NOT NULL
);

CREATE TABLE thefts (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    gull_id   BIGINT NOT NULL REFERENCES gulls (id),
    snack     TEXT   NOT NULL,
    stolen_on DATE   NOT NULL
);

CREATE VIEW beach_records AS
SELECT
    gulls.beach,
    COUNT(DISTINCT gulls.id) AS gulls,
    COUNT(thefts.id) AS thefts,
    MAX(thefts.stolen_on) AS last_theft
FROM gulls
LEFT JOIN thefts ON thefts.gull_id = gulls.id
GROUP BY gulls.beach;

A recursive CTE

FamilyTree starts from one gull and finds its descendants. The first part of the CTE picks the starting gull, at generation 0; the second joins each gull's chicks to the rows found so far, one generation further down, until there are none left.

db/queries/gulls.sql

-- sgl.query(FamilyTree, many)
WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id, 0::BIGINT AS generation
    FROM gulls
    WHERE id = $1
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, descendants.generation + 1
    FROM gulls
    JOIN descendants ON gulls.parent_id = descendants.id
)
SELECT descendants.name, descendants.generation, parents.name AS parent
FROM descendants
LEFT JOIN gulls AS parents ON parents.id = descendants.parent_id
ORDER BY descendants.generation, descendants.id;

internal/store

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation int64   `json:"generation"`
	Parent     *string `json:"parent"`
}

func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error) {
	rows, err := queries.reader.QueryContext(ctx, familyTreeSQL, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FamilyTreeRow
	for rows.Next() {
		var row FamilyTreeRow
		if err := rows.Scan(&row.Name, &row.Generation, &row.Parent); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

generation starts as 0::BIGINT and grows by generation + 1, so it is an int64. The cast keeps the type the same on CockroachDB, where integers are 64-bit. parent comes from the LEFT JOIN back to gulls, which finds no parent for the gull the tree starts from, so it is a *string.

A CTE and window functions

TheftLeague counts each gull's thefts in a CTE, then ranks the gulls on each beach and totals the beach, with two window functions over the CTE's rows:

db/queries/gulls.sql

-- sgl.query(TheftLeague, many)
WITH totals AS (
    SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id
)
SELECT
    name,
    beach,
    thefts,
    RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
    SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name;

internal/store

type TheftLeagueRow struct {
	Name        string       `json:"name"`
	Beach       string       `json:"beach"`
	Thefts      int64        `json:"thefts"`
	BeachRank   int64        `json:"beach_rank"`
	BeachThefts *apd.Decimal `json:"beach_thefts"`
}

type Thefts struct {
	ID       int64     `json:"id"`
	GullID   int64     `json:"gull_id"`
	Snack    string    `json:"snack"`
	StolenOn time.Time `json:"stolen_on"`
}

func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error) {
	rows, err := queries.reader.QueryContext(ctx, theftLeagueSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []TheftLeagueRow
	for rows.Next() {
		var row TheftLeagueRow
		if err := rows.Scan(&row.Name, &row.Beach, &row.Thefts, &row.BeachRank, &row.BeachThefts); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

The count carries through the CTE as a bigint, so int64, and RANK() is a bigint that is never NULL. SUM() of a bigint is a numeric, so BeachThefts is an apd.Decimal, and a pointer, as SQL sums no rows to NULL.

A view

BeachRecords reads the view like a table:

db/queries/gulls.sql

-- sgl.query(BeachRecords, many)
SELECT * FROM beach_records ORDER BY beach;

internal/store

type BeachRecordsRow struct {
	Beach     string     `json:"beach"`
	Gulls     int64      `json:"gulls"`
	Thefts    int64      `json:"thefts"`
	LastTheft *time.Time `json:"last_theft"`
}

type Gulls struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	ParentID *int64 `json:"parent_id"`
	Beach    string `json:"beach"`
}

type Thefts struct {
	ID       int64     `json:"id"`
	GullID   int64     `json:"gull_id"`
	Snack    string    `json:"snack"`
	StolenOn time.Time `json:"stolen_on"`
}

func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, beachRecordsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BeachRecordsRow
	for rows.Next() {
		var row BeachRecordsRow
		if err := rows.Scan(&row.Beach, &row.Gulls, &row.Thefts, &row.LastTheft); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Its columns keep the types the view's own SELECT gives them. The counts are int64, and MAX(stolen_on) over a DATE column is a *time.Time, nil for a beach with no thefts.

Using it

The program hatches six gulls across three beaches, records seven thefts, and prints the family tree of the first gull, the theft league and the beach records.

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/019_ctes_and_windows/internal/store"
)

// run hatches a colony of gulls, records their snack thefts and reads them back three
// ways. Seagull follows each column's type and nullability through the SQL:
//
//   - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
//     generation; generation is an int64 from 0::BIGINT AS generation and generation + 1,
//     and parent is *string because the LEFT JOIN finds no parent for the first gull;
//   - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
//     totals the beach with SUM() OVER; COUNT and RANK are bigint, so int64, while SUM of
//     a bigint is numeric in PostgreSQL, so BeachThefts is a nullable *apd.Decimal;
//   - BeachRecords reads the beach_records view from the migration as if it were a table,
//     and its columns keep the types the view's own SELECT gives them: MAX over a DATE
//     column is a *time.Time, nil for a beach with no thefts. The query is written as
//     SELECT *, and the generated SQL names the view's four columns instead, so a
//     migration that later adds a column to the view does not break the code already
//     deployed, which scans exactly those four. go.keep_stars: true in sgl.yaml keeps the
//     star as written.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the three listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.HatchGullParams{
		{Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
		{Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
		{Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
		{Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
		{Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
		{Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
	}
	for _, gull := range gulls {
		if err := queries.HatchGull(ctx, gull); err != nil {
			return fmt.Errorf("hatching %s: %w", gull.Name, err)
		}
	}
	thefts := []store.RecordTheftParams{
		{GullID: 1, Snack: "chips", StolenOn: july(1)},
		{GullID: 1, Snack: "pasty", StolenOn: july(3)},
		{GullID: 1, Snack: "ice cream", StolenOn: july(4)},
		{GullID: 2, Snack: "chips", StolenOn: july(2)},
		{GullID: 3, Snack: "pasty", StolenOn: july(2)},
		{GullID: 3, Snack: "chips", StolenOn: july(5)},
		{GullID: 5, Snack: "sandwich", StolenOn: july(3)},
	}
	for _, theft := range thefts {
		if err := queries.RecordTheft(ctx, theft); err != nil {
			return fmt.Errorf("recording a theft: %w", err)
		}
	}

	family, err := queries.FamilyTree(ctx, 1)
	if err != nil {
		return fmt.Errorf("tracing Steven's family: %w", err)
	}
	fmt.Fprintln(output, "Steven's family:")
	for _, member := range family {
		fmt.Fprintf(output, "  generation %d  %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
	}

	league, err := queries.TheftLeague(ctx)
	if err != nil {
		return fmt.Errorf("ranking thieves: %w", err)
	}
	fmt.Fprintln(output, "theft league:")
	for _, entry := range league {
		beachThefts := "NULL"
		if entry.BeachThefts != nil {
			beachThefts = entry.BeachThefts.String()
		}
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, beachThefts)
	}

	records, err := queries.BeachRecords(ctx)
	if err != nil {
		return fmt.Errorf("reading beach records: %w", err)
	}
	fmt.Fprintln(output, "beach records:")
	for _, record := range records {
		lastTheft := "NULL"
		if record.LastTheft != nil {
			lastTheft = record.LastTheft.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, lastTheft)
	}
	return nil
}

// july returns a day in July 2026, the month the thefts happen in.
//
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is midnight UTC on that day, as a DATE column holds it.
func july(day int) time.Time {
	return time.Date(2026, time.July, day, 0, 0, 0, 0, time.UTC)
}

// orNull formats a nullable field, or returns NULL when the field is nil.
//
// Takes value (*T) which is a nullable field.
//
// Returns string which is the text to print.
func orNull[T any](value *T) string {
	if value == nil {
		return "NULL"
	}
	return fmt.Sprint(*value)
}

What it prints

Steven's family:
  generation 0  Steven   parent NULL
  generation 1  Gwen     parent Steven
  generation 1  Jago     parent Steven
  generation 2  Kerensa  parent Gwen
theft league:
  Gwithian      #1 Morwenna 0 of 0
  Porthmeor     #1 Steven   3 of 4
  Porthmeor     #2 Gwen     1 of 4
  Porthmeor     #3 Kerensa  0 of 4
  Porthminster  #1 Jago     2 of 3
  Porthminster  #2 Tristan  1 of 3
beach records:
  Gwithian      gulls 1, thefts 0, last theft NULL
  Porthmeor     gulls 3, thefts 4, last theft 2026-07-04
  Porthminster  gulls 2, thefts 3, last theft 2026-07-05

When Seagull cannot type an expression, its field is any. An sgl.column directive with type: pins it to the type you know it has; Type overrides shows how.

Across engines

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

PostgreSQLSQLiteMySQLDuckDBClickHouse
thefts columnint64int64int64int64·
beach_rank columnint64int64int64int64uint64
beach_thefts column*apd.Decimal*int64*apd.Decimal*big.Intuint64
stolen column····uint64

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

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./019_ctes_and_windows

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

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

Needs a running CockroachDB server: set DATABASE_URL, or pass -dsn, to the address of an empty database.

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: colony
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
    migrate:
      dsn: ${env:DATABASE_URL}

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name      TEXT   NOT NULL,
    parent_id BIGINT REFERENCES gulls (id),
    beach     TEXT   NOT NULL
);

CREATE TABLE thefts (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    gull_id   BIGINT NOT NULL REFERENCES gulls (id),
    snack     TEXT   NOT NULL,
    stolen_on DATE   NOT NULL
);

CREATE VIEW beach_records AS
SELECT
    gulls.beach,
    COUNT(DISTINCT gulls.id) AS gulls,
    COUNT(thefts.id) AS thefts,
    MAX(thefts.stolen_on) AS last_theft
FROM gulls
LEFT JOIN thefts ON thefts.gull_id = gulls.id
GROUP BY gulls.beach;

db/migrations/0001_gulls.down.sql

DROP VIEW beach_records;
DROP TABLE thefts;
DROP TABLE gulls;

db/queries/gulls.sql

-- sgl.query(HatchGull, exec)
INSERT INTO gulls (name, parent_id, beach) VALUES ($1, $2, $3);

-- sgl.query(RecordTheft, exec)
INSERT INTO thefts (gull_id, snack, stolen_on) VALUES ($1, $2, $3);

-- sgl.query(FamilyTree, many)
WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id, 0::BIGINT AS generation
    FROM gulls
    WHERE id = $1
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, descendants.generation + 1
    FROM gulls
    JOIN descendants ON gulls.parent_id = descendants.id
)
SELECT descendants.name, descendants.generation, parents.name AS parent
FROM descendants
LEFT JOIN gulls AS parents ON parents.id = descendants.parent_id
ORDER BY descendants.generation, descendants.id;

-- sgl.query(TheftLeague, many)
WITH totals AS (
    SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id
)
SELECT
    name,
    beach,
    thefts,
    RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
    SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name;

-- sgl.query(BeachRecords, many)
SELECT * FROM beach_records ORDER BY beach;

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/019_ctes_and_windows/internal/store"
)

// run hatches a colony of gulls, records their snack thefts and reads them back three
// ways. Seagull follows each column's type and nullability through the SQL:
//
//   - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
//     generation; generation is an int64 from 0::BIGINT AS generation and generation + 1,
//     and parent is *string because the LEFT JOIN finds no parent for the first gull;
//   - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
//     totals the beach with SUM() OVER; COUNT and RANK are bigint, so int64, while SUM of
//     a bigint is numeric in PostgreSQL, so BeachThefts is a nullable *apd.Decimal;
//   - BeachRecords reads the beach_records view from the migration as if it were a table,
//     and its columns keep the types the view's own SELECT gives them: MAX over a DATE
//     column is a *time.Time, nil for a beach with no thefts. The query is written as
//     SELECT *, and the generated SQL names the view's four columns instead, so a
//     migration that later adds a column to the view does not break the code already
//     deployed, which scans exactly those four. go.keep_stars: true in sgl.yaml keeps the
//     star as written.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the three listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.HatchGullParams{
		{Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
		{Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
		{Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
		{Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
		{Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
		{Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
	}
	for _, gull := range gulls {
		if err := queries.HatchGull(ctx, gull); err != nil {
			return fmt.Errorf("hatching %s: %w", gull.Name, err)
		}
	}
	thefts := []store.RecordTheftParams{
		{GullID: 1, Snack: "chips", StolenOn: july(1)},
		{GullID: 1, Snack: "pasty", StolenOn: july(3)},
		{GullID: 1, Snack: "ice cream", StolenOn: july(4)},
		{GullID: 2, Snack: "chips", StolenOn: july(2)},
		{GullID: 3, Snack: "pasty", StolenOn: july(2)},
		{GullID: 3, Snack: "chips", StolenOn: july(5)},
		{GullID: 5, Snack: "sandwich", StolenOn: july(3)},
	}
	for _, theft := range thefts {
		if err := queries.RecordTheft(ctx, theft); err != nil {
			return fmt.Errorf("recording a theft: %w", err)
		}
	}

	family, err := queries.FamilyTree(ctx, 1)
	if err != nil {
		return fmt.Errorf("tracing Steven's family: %w", err)
	}
	fmt.Fprintln(output, "Steven's family:")
	for _, member := range family {
		fmt.Fprintf(output, "  generation %d  %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
	}

	league, err := queries.TheftLeague(ctx)
	if err != nil {
		return fmt.Errorf("ranking thieves: %w", err)
	}
	fmt.Fprintln(output, "theft league:")
	for _, entry := range league {
		beachThefts := "NULL"
		if entry.BeachThefts != nil {
			beachThefts = entry.BeachThefts.String()
		}
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, beachThefts)
	}

	records, err := queries.BeachRecords(ctx)
	if err != nil {
		return fmt.Errorf("reading beach records: %w", err)
	}
	fmt.Fprintln(output, "beach records:")
	for _, record := range records {
		lastTheft := "NULL"
		if record.LastTheft != nil {
			lastTheft = record.LastTheft.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, lastTheft)
	}
	return nil
}

// july returns a day in July 2026, the month the thefts happen in.
//
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is midnight UTC on that day, as a DATE column holds it.
func july(day int) time.Time {
	return time.Date(2026, time.July, day, 0, 0, 0, 0, time.UTC)
}

// orNull formats a nullable field, or returns NULL when the field is nil.
//
// Takes value (*T) which is a nullable field.
//
// Returns string which is the text to print.
func orNull[T any](value *T) string {
	if value == nil {
		return "NULL"
	}
	return fmt.Sprint(*value)
}

main.go

// Command 019_ctes_and_windows applies its migrations to a PostgreSQL database, then runs
// the example in example.go: a gull colony's family tree, theft league and beach records
// are read through a recursive CTE, window functions and a view.
//
// 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/gulls.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"

	"github.com/cockroachdb/apd/v3"
)

const beachRecordsSQL = `SELECT "beach_records"."beach", "beach_records"."gulls", "beach_records"."thefts", "beach_records"."last_theft" FROM beach_records ORDER BY beach;`

type BeachRecordsRow struct {
	Beach     string     `json:"beach"`
	Gulls     int64      `json:"gulls"`
	Thefts    int64      `json:"thefts"`
	LastTheft *time.Time `json:"last_theft"`
}

func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, beachRecordsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BeachRecordsRow
	for rows.Next() {
		var row BeachRecordsRow
		if err := rows.Scan(&row.Beach, &row.Gulls, &row.Thefts, &row.LastTheft); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const familyTreeSQL = `WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id, 0::BIGINT AS generation
    FROM gulls
    WHERE id = $1
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, descendants.generation + 1
    FROM gulls
    JOIN descendants ON gulls.parent_id = descendants.id
)
SELECT descendants.name, descendants.generation, parents.name AS parent
FROM descendants
LEFT JOIN gulls AS parents ON parents.id = descendants.parent_id
ORDER BY descendants.generation, descendants.id;`

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation int64   `json:"generation"`
	Parent     *string `json:"parent"`
}

func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error) {
	rows, err := queries.reader.QueryContext(ctx, familyTreeSQL, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []FamilyTreeRow
	for rows.Next() {
		var row FamilyTreeRow
		if err := rows.Scan(&row.Name, &row.Generation, &row.Parent); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const hatchGullSQL = `INSERT INTO gulls (name, parent_id, beach) VALUES ($1, $2, $3);`

type HatchGullParams struct {
	Name     string
	ParentID *int64
	Beach    string
}

func (queries *Queries) HatchGull(ctx context.Context, params HatchGullParams) error {
	_, err := queries.writer.ExecContext(ctx, hatchGullSQL, params.Name, params.ParentID, params.Beach)
	return err
}

const recordTheftSQL = `INSERT INTO thefts (gull_id, snack, stolen_on) VALUES ($1, $2, $3);`

type RecordTheftParams struct {
	GullID   int64
	Snack    string
	StolenOn time.Time
}

func (queries *Queries) RecordTheft(ctx context.Context, params RecordTheftParams) error {
	_, err := queries.writer.ExecContext(ctx, recordTheftSQL, params.GullID, params.Snack, params.StolenOn)
	return err
}

const theftLeagueSQL = `WITH totals AS (
    SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
    FROM gulls
    LEFT JOIN thefts ON thefts.gull_id = gulls.id
    GROUP BY gulls.id
)
SELECT
    name,
    beach,
    thefts,
    RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
    SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
FROM totals
ORDER BY beach, beach_rank, name;`

type TheftLeagueRow struct {
	Name        string       `json:"name"`
	Beach       string       `json:"beach"`
	Thefts      int64        `json:"thefts"`
	BeachRank   int64        `json:"beach_rank"`
	BeachThefts *apd.Decimal `json:"beach_thefts"`
}

func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error) {
	rows, err := queries.reader.QueryContext(ctx, theftLeagueSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []TheftLeagueRow
	for rows.Next() {
		var row TheftLeagueRow
		if err := rows.Scan(&row.Name, &row.Beach, &row.Thefts, &row.BeachRank, &row.BeachThefts); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

import "time"

type Gulls struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	ParentID *int64 `json:"parent_id"`
	Beach    string `json:"beach"`
}

type Thefts struct {
	ID       int64     `json:"id"`
	GullID   int64     `json:"gull_id"`
	Snack    string    `json:"snack"`
	StolenOn time.Time `json:"stolen_on"`
}

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()
}

output.txt

Steven's family:
  generation 0  Steven   parent NULL
  generation 1  Gwen     parent Steven
  generation 1  Jago     parent Steven
  generation 2  Kerensa  parent Gwen
theft league:
  Gwithian      #1 Morwenna 0 of 0
  Porthmeor     #1 Steven   3 of 4
  Porthmeor     #2 Gwen     1 of 4
  Porthmeor     #3 Kerensa  0 of 4
  Porthminster  #1 Jago     2 of 3
  Porthminster  #2 Tristan  1 of 3
beach records:
  Gwithian      gulls 1, thefts 0, last theft NULL
  Porthmeor     gulls 3, thefts 4, last theft 2026-07-04
  Porthminster  gulls 2, thefts 3, last theft 2026-07-05

Also on other engines

SQLiteA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.PostgreSQLA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.TimescaleDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.MySQLA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.MariaDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.DuckDBA gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each.ClickHouseA gull colony read through a recursive CTE, a CTE feeding rank() and sum() window functions, and a view, with types and nullability carried through each.