Types through CTEs, window functions and views

On this page

On MySQL:

  • WITH RECURSIVE and window functions need MySQL 8.0 or later
  • SUM over integers returns DECIMAL in MySQL, so the beach total is *apd.Decimal
  • stolen_on is a DATE, so the view's MAX(stolen_on) arrives as *time.Time

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      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    parent_id BIGINT,
    beach     VARCHAR(50) NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES gulls (id)
);

CREATE TABLE thefts (
    id        BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    gull_id   BIGINT      NOT NULL,
    snack     VARCHAR(50) NOT NULL,
    stolen_on DATE        NOT NULL,
    FOREIGN KEY (gull_id) REFERENCES gulls (id)
);

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 family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = ?
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id;

internal/store

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation int32   `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 the integer 0 and grows by generation + 1, so it is an int32. 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 an int64, and RANK() is never NULL. SUM() over integers returns an exact DECIMAL, 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 beach, gulls, thefts, last_theft 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/mysql/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 integer from 0 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; RANK is never NULL, while SUM is nullable, as SQL
//     sums no rows to NULL, and it is a DECIMAL, as MySQL sums integers exactly into a
//     DECIMAL, so BeachThefts is *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.
//
// 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: day(1)},
		{GullID: 1, Snack: "pasty", StolenOn: day(3)},
		{GullID: 1, Snack: "ice cream", StolenOn: day(4)},
		{GullID: 2, Snack: "chips", StolenOn: day(2)},
		{GullID: 3, Snack: "pasty", StolenOn: day(2)},
		{GullID: 3, Snack: "chips", StolenOn: day(5)},
		{GullID: 5, Snack: "sandwich", StolenOn: day(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 {
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, orNull(entry.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 {
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, orNull(record.LastTheft))
	}
	return nil
}

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

// orNull formats a nullable field, or returns NULL when the field is nil. A date prints
// as YYYY-MM-DD, and a decimal through its String method, which apd.Decimal declares on
// its pointer.
//
// 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"
	}
	switch field := any(value).(type) {
	case *time.Time:
		return field.Format(time.DateOnly)
	case fmt.Stringer:
		return field.String()
	default:
		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.

MySQLSQLitePostgreSQLDuckDBClickHouse
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.

MariaDB runs the MySQL version unchanged.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/mysql && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./019_ctes_and_windows

Needs a running MySQL 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: mysql
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id        BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    parent_id BIGINT,
    beach     VARCHAR(50) NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES gulls (id)
);

CREATE TABLE thefts (
    id        BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    gull_id   BIGINT      NOT NULL,
    snack     VARCHAR(50) NOT NULL,
    stolen_on DATE        NOT NULL,
    FOREIGN KEY (gull_id) REFERENCES gulls (id)
);

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 (?, ?, ?);

-- sgl.query(RecordTheft, exec)
INSERT INTO thefts (gull_id, snack, stolen_on) VALUES (?, ?, ?);

-- sgl.query(FamilyTree, many)
WITH RECURSIVE family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = ?
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.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 beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/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 integer from 0 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; RANK is never NULL, while SUM is nullable, as SQL
//     sums no rows to NULL, and it is a DECIMAL, as MySQL sums integers exactly into a
//     DECIMAL, so BeachThefts is *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.
//
// 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: day(1)},
		{GullID: 1, Snack: "pasty", StolenOn: day(3)},
		{GullID: 1, Snack: "ice cream", StolenOn: day(4)},
		{GullID: 2, Snack: "chips", StolenOn: day(2)},
		{GullID: 3, Snack: "pasty", StolenOn: day(2)},
		{GullID: 3, Snack: "chips", StolenOn: day(5)},
		{GullID: 5, Snack: "sandwich", StolenOn: day(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 {
		fmt.Fprintf(output, "  %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, orNull(entry.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 {
		fmt.Fprintf(output, "  %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, orNull(record.LastTheft))
	}
	return nil
}

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

// orNull formats a nullable field, or returns NULL when the field is nil. A date prints
// as YYYY-MM-DD, and a decimal through its String method, which apd.Decimal declares on
// its pointer.
//
// 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"
	}
	switch field := any(value).(type) {
	case *time.Time:
		return field.Format(time.DateOnly)
	case fmt.Stringer:
		return field.String()
	default:
		return fmt.Sprint(*value)
	}
}

main.go

// Command 019_ctes_and_windows applies its migrations to a MySQL 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 'user:password@tcp(localhost:3306)/colony'`, 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/go-sql-driver/mysql"

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

// 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"), "MySQL data source name; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database dsn names, and runs the example against it.
//
// Takes dsn (string) which is the database's data source name.
//
// 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 MySQL data source name")
	}
	config, err := mysql.ParseDSN(dsn)
	if err != nil {
		return err
	}
	// Seagull maps DATETIME and TIMESTAMP columns to time.Time, which the driver returns
	// only when parseTime is set.
	config.ParseTime = true
	dsn = config.FormatDSN()

	database, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_mysql.New(migrate.WithDriverMultiStatements(config.MultiStatements)), 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, gulls, thefts, 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 family AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM gulls
    WHERE id = ?
    UNION ALL
    SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
    FROM gulls
    JOIN family ON gulls.parent_id = family.id
)
SELECT family.name, family.generation, parents.name AS parent
FROM family
LEFT JOIN gulls AS parents ON parents.id = family.parent_id
ORDER BY family.generation, family.id;`

type FamilyTreeRow struct {
	Name       string  `json:"name"`
	Generation int32   `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 (?, ?, ?);`

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 (?, ?, ?);`

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 reads DATETIME, DATE and TIMESTAMP columns into time.Time, so the DSN it connects with must set parseTime=true.
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.CockroachDBA 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.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.