Analytics in DuckDB
On this page
On DuckDB:
- QUALIFY filters on a window function and keeps the table's column types
- SUM of an INTEGER is a HUGEINT in DuckDB, so the FILTER totals arrive as *big.Int
- A scalar macro is typed through the CAST in its body; a table macro's columns are typed from the SELECT in its body
- read_parquet takes its path as a bound parameter; Seagull cannot see the file's columns, so sgl vet warns Q001 and CASTs give the types
- PIVOT ... ON species IN (...) gives one typed column per listed value, the same columns as the FILTER query
- run takes the Parquet file's path, which main.go and the test write with COPY ... TO
DuckDB is built for analysis, and its SQL has conveniences for it that save subqueries and glue code: QUALIFY to filter on a window function, FILTER and PIVOT to aggregate a subset of rows into its own column, macros to name a calculation once, and read_parquet to query a file as if it were a table. Seagull types the results of all of them.
The example analyses a week of fish landings at two harbours, then values them against an auction house's price list held in a Parquet file.
The schema
The migration creates the table and two macros. tonnes is a scalar macro, a named expression. harbour_landings is a table macro, a named query that takes a parameter.
db/migrations/0001_landings.up.sql
CREATE SEQUENCE landing_ids;
CREATE TABLE landings (
id BIGINT PRIMARY KEY DEFAULT nextval('landing_ids'),
harbour VARCHAR NOT NULL,
boat VARCHAR NOT NULL,
species VARCHAR NOT NULL,
kg INTEGER NOT NULL,
landed_on DATE NOT NULL
);
CREATE MACRO tonnes(kg) AS CAST(kg AS DOUBLE) / 1000;
CREATE MACRO harbour_landings(name) AS TABLE
SELECT boat, species, kg FROM landings WHERE harbour = name;
Top row per group: QUALIFY
QUALIFY filters on a window function the way HAVING filters on an aggregate. Here it keeps the biggest landing in each harbour, without the subquery other databases need:
db/queries/landings.sql
-- sgl.query(BiggestLandingPerHarbour, many)
SELECT harbour, boat, species, kg, landed_on
FROM landings
QUALIFY row_number() OVER (PARTITION BY harbour ORDER BY kg DESC) = 1
ORDER BY harbour;
The rows are still the table's rows, so they keep its types: nothing is a pointer.
internal/store
type BiggestLandingPerHarbourRow struct {
Harbour string `json:"harbour"`
Boat string `json:"boat"`
Species string `json:"species"`
Kg int32 `json:"kg"`
LandedOn time.Time `json:"landed_on"`
}
func (queries *Queries) BiggestLandingPerHarbour(ctx context.Context) ([]BiggestLandingPerHarbourRow, error) {
rows, err := queries.reader.QueryContext(ctx, biggestLandingPerHarbourSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []BiggestLandingPerHarbourRow
if rows.Next() {
var row BiggestLandingPerHarbourRow
scanArgs := []any{&row.Harbour, &row.Boat, &row.Species, &row.Kg, &row.LandedOn}
for more := true; more; more = rows.Next() {
row = BiggestLandingPerHarbourRow{}
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
}
A column per species: FILTER
sum(kg) FILTER (WHERE species = 'cod') sums only the cod landings, so one pass over the table gives a column per species:
db/queries/landings.sql
-- sgl.query(CatchBySpecies, many)
SELECT
harbour,
coalesce(sum(kg) FILTER (WHERE species = 'cod'), 0) AS cod_kg,
coalesce(sum(kg) FILTER (WHERE species = 'hake'), 0) AS hake_kg,
coalesce(sum(kg) FILTER (WHERE species = 'mackerel'), 0) AS mackerel_kg
FROM landings
GROUP BY harbour
ORDER BY harbour;
In DuckDB, SUM of an INTEGER is a HUGEINT, a 128-bit integer, so the totals arrive as *big.Int.
The same columns: PIVOT
PIVOT builds those columns for you. ON species IN (...) makes one column for each species listed, and USING sum(kg) fills each with that species' total:
db/queries/landings.sql
-- sgl.query(CatchPivot, many)
SELECT *
FROM (PIVOT landings ON species IN ('cod', 'hake', 'mackerel') USING sum(kg) GROUP BY harbour)
ORDER BY harbour;
Seagull gives the pivoted query a column for each value in the IN list, typed from the USING aggregate, so the row has the same *big.Int totals as CatchBySpecies, under the species' own names:
internal/store
type CatchPivotRow struct {
Harbour string `json:"harbour"`
Cod *big.Int `json:"cod"`
Hake *big.Int `json:"hake"`
Mackerel *big.Int `json:"mackerel"`
}
func (queries *Queries) CatchPivot(ctx context.Context) ([]CatchPivotRow, error) {
rows, err := queries.reader.QueryContext(ctx, catchPivotSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []CatchPivotRow
for rows.Next() {
var row CatchPivotRow
if err := rows.Scan(&row.Harbour, &row.Cod, &row.Hake, &row.Mackerel); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
The list is what makes the columns knowable. Without it, PIVOT makes a column for every species the data happens to hold, which cannot be known when the code is generated.
Macros
A call to the scalar macro tonnes is typed through the CAST in its body, so the result is a *float64:
db/queries/landings.sql
-- sgl.query(TonnesPerBoat, many)
SELECT boat, tonnes(sum(kg)) AS tonnes
FROM landings
GROUP BY boat
ORDER BY boat;
internal/store
type TonnesPerBoatRow struct {
Boat string `json:"boat"`
Tonnes *float64 `json:"tonnes"`
}
func (queries *Queries) TonnesPerBoat(ctx context.Context) ([]TonnesPerBoatRow, error) {
rows, err := queries.reader.QueryContext(ctx, tonnesPerBoatSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []TonnesPerBoatRow
for rows.Next() {
var row TonnesPerBoatRow
if err := rows.Scan(&row.Boat, &row.Tonnes); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
The method runs on queries.reader: Seagull reads the macro's body and sees that it only computes, so the query could be sent to a read replica.
A table macro is used in FROM like a table, and Seagull types its columns from the SELECT in its body. species comes straight from the landings column, so it is a NOT NULL string. HarbourCatch casts only the total, to a BIGINT, so it arrives as an *int64 rather than DuckDB's 128-bit HUGEINT. The macro's parameter has no column to take a type from, so sgl.param gives it one.
db/queries/landings.sql
-- sgl.query(HarbourCatch, many)
-- $1 as sgl.param(harbour, type: varchar)
SELECT species, CAST(sum(kg) AS BIGINT) AS kg
FROM harbour_landings($1)
GROUP BY species
ORDER BY species;
Reading a Parquet file
read_parquet reads a Parquet file as a table, and its path here is a bound parameter, so the same query can read any file. Seagull cannot look inside a file when it generates code, so it does not know the columns: sgl vet warns Q001 for a column it cannot find, and a CAST gives each column its type. Nothing says those columns are NOT NULL, so they are pointers.
db/queries/landings.sql
-- sgl.query(AuctionPrices, many)
-- $1 as sgl.param(price_file, type: varchar)
SELECT CAST(species AS VARCHAR) AS species, CAST(pence_per_kg AS INTEGER) AS pence_per_kg
FROM read_parquet($1)
ORDER BY species;
The file can be joined to a table like any other source. LandingValues prices every landing from the auction list and totals each boat:
db/queries/landings.sql
-- sgl.query(LandingValues, many)
-- $1 as sgl.param(price_file, type: varchar)
SELECT landings.boat, CAST(sum(landings.kg * prices.pence_per_kg) AS BIGINT) AS value_pence
FROM landings
JOIN read_parquet($1) AS prices ON prices.species = landings.species
GROUP BY landings.boat
ORDER BY landings.boat;
The program does not ship a Parquet file. main.go writes one before run starts, with DuckDB's COPY ... TO $1, binding the file's path like any other parameter, and passes the same path to run.
Using it
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/duckdb/038_duckdb_analytics/internal/store"
)
// run lands a week of fish at two harbours and analyses it with DuckDB's own SQL, every
// result typed by Seagull:
//
// - BiggestLandingPerHarbour filters on a window function with QUALIFY, which keeps the
// table's types, so no subquery is needed to pick the top row per harbour;
// - CatchBySpecies builds one column per species with sum(...) FILTER (WHERE ...); SUM
// of an INTEGER is a HUGEINT in DuckDB, so each arrives as *big.Int;
// - CatchPivot builds the same columns with PIVOT ... ON species IN (...), one column
// per listed species, each a nullable HUGEINT, as a harbour may land none of one;
// - TonnesPerBoat calls tonnes, a macro from the migration, and its CAST gives the
// result a type, *float64;
// - HarbourCatch reads from harbour_landings, a table macro, whose columns Seagull
// types from the macro's body, so species is a string, NOT NULL like the landings
// column it comes from, and only the total is cast, to a BIGINT, for an *int64;
// - AuctionPrices and LandingValues read the auction house's Parquet file with
// read_parquet, and the path is a bound parameter. Seagull cannot look inside the
// file when it generates, so sgl vet warns Q001 for a column it cannot find, and a
// CAST gives each column its type; such columns are pointers, as nothing says they
// are NOT NULL.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes priceList (string) which is the path of the auction house's Parquet price list.
// Takes output (io.Writer) which receives each analysis.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, priceList string, output io.Writer) error {
queries := store.New(database)
landings := []store.LandFishParams{
{Harbour: "Newlyn", Boat: "Kittiwake", Species: "cod", Kg: 120, LandedOn: day(1)},
{Harbour: "Newlyn", Boat: "Kittiwake", Species: "mackerel", Kg: 300, LandedOn: day(2)},
{Harbour: "Newlyn", Boat: "Salty Chip", Species: "hake", Kg: 80, LandedOn: day(1)},
{Harbour: "Newlyn", Boat: "Salty Chip", Species: "cod", Kg: 60, LandedOn: day(3)},
{Harbour: "Brixham", Boat: "Gull Wing", Species: "cod", Kg: 150, LandedOn: day(2)},
{Harbour: "Brixham", Boat: "Gull Wing", Species: "hake", Kg: 40, LandedOn: day(3)},
{Harbour: "Brixham", Boat: "Driftwood", Species: "mackerel", Kg: 220, LandedOn: day(1)},
}
for _, landing := range landings {
if err := queries.LandFish(ctx, landing); err != nil {
return fmt.Errorf("landing %s's %s: %w", landing.Boat, landing.Species, err)
}
}
biggest, err := queries.BiggestLandingPerHarbour(ctx)
if err != nil {
return fmt.Errorf("finding the biggest landings: %w", err)
}
fmt.Fprintln(output, "biggest landing per harbour (QUALIFY):")
for _, landing := range biggest {
fmt.Fprintf(output, " %-8s %-10s %4dkg of %s on %s\n", landing.Harbour, landing.Boat, landing.Kg, landing.Species, landing.LandedOn.Format(time.DateOnly))
}
bySpecies, err := queries.CatchBySpecies(ctx)
if err != nil {
return fmt.Errorf("totalling the catch by species: %w", err)
}
fmt.Fprintln(output, "kg by species (FILTER):")
for _, harbour := range bySpecies {
fmt.Fprintf(output, " %-8s cod %v, hake %v, mackerel %v\n", harbour.Harbour, harbour.CodKg, harbour.HakeKg, harbour.MackerelKg)
}
pivoted, err := queries.CatchPivot(ctx)
if err != nil {
return fmt.Errorf("pivoting the catch by species: %w", err)
}
fmt.Fprintln(output, "kg by species (PIVOT):")
for _, harbour := range pivoted {
fmt.Fprintf(output, " %-8s cod %v, hake %v, mackerel %v\n", harbour.Harbour, harbour.Cod, harbour.Hake, harbour.Mackerel)
}
perBoat, err := queries.TonnesPerBoat(ctx)
if err != nil {
return fmt.Errorf("totalling each boat: %w", err)
}
fmt.Fprintln(output, "tonnes per boat (macro):")
for _, boat := range perBoat {
fmt.Fprintf(output, " %-10s %.3f t\n", boat.Boat, *boat.Tonnes)
}
newlyn, err := queries.HarbourCatch(ctx, "Newlyn")
if err != nil {
return fmt.Errorf("reading Newlyn's catch: %w", err)
}
fmt.Fprintln(output, "Newlyn's catch (table macro):")
for _, catch := range newlyn {
fmt.Fprintf(output, " %-8s %dkg\n", catch.Species, *catch.Kg)
}
prices, err := queries.AuctionPrices(ctx, priceList)
if err != nil {
return fmt.Errorf("reading the price list: %w", err)
}
fmt.Fprintln(output, "auction prices (read_parquet):")
for _, price := range prices {
fmt.Fprintf(output, " %-8s %dp a kg\n", *price.Species, *price.PencePerKg)
}
values, err := queries.LandingValues(ctx, priceList)
if err != nil {
return fmt.Errorf("valuing the landings: %w", err)
}
fmt.Fprintln(output, "value per boat (landings joined to the Parquet file):")
for _, boat := range values {
fmt.Fprintf(output, " %-10s £%d.%02d\n", boat.Boat, *boat.ValuePence/100, *boat.ValuePence%100)
}
return nil
}
// day returns a date in May 2026, the week the landings were made.
//
// Takes dayOfMonth (int) which is the day.
//
// Returns time.Time which is midnight UTC on that day.
func day(dayOfMonth int) time.Time {
return time.Date(2026, time.May, dayOfMonth, 0, 0, 0, 0, time.UTC)
}
What it prints
biggest landing per harbour (QUALIFY):
Brixham Driftwood 220kg of mackerel on 2026-05-01
Newlyn Kittiwake 300kg of mackerel on 2026-05-02
kg by species (FILTER):
Brixham cod 150, hake 40, mackerel 220
Newlyn cod 180, hake 80, mackerel 300
kg by species (PIVOT):
Brixham cod 150, hake 40, mackerel 220
Newlyn cod 180, hake 80, mackerel 300
tonnes per boat (macro):
Driftwood 0.220 t
Gull Wing 0.190 t
Kittiwake 0.420 t
Salty Chip 0.140 t
Newlyn's catch (table macro):
cod 180kg
hake 80kg
mackerel 300kg
auction prices (read_parquet):
cod 900p a kg
hake 650p a kg
mackerel 350p a kg
value per boat (landings joined to the Parquet file):
Driftwood £770.00
Gull Wing £1610.00
Kittiwake £2130.00
Salty Chip £1060.00
Try it
Remove the IN (...) list from CatchPivot, leaving PIVOT landings ON species USING sum(kg) GROUP BY harbour, and run sgl vet. It reports Q010: without the list, the pivot makes one column for each species in the data, so its columns cannot be known when generating code. Put the list back, add 'pollock' to it, and run sgl generate: CatchPivotRow gains a Pollock field, nil for every harbour, as no pollock was landed.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/duckdb && go run ./038_duckdb_analytics
Its test runs it too, and checks what it prints against output.txt:
cd seagull/examples/duckdb && go test ./038_duckdb_analytics
Needs cgo and a C compiler (the DuckDB driver).
The files
Every file of the example, as it is in the Seagull repository.
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: market
dialect: duckdb
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_landings.up.sql
CREATE SEQUENCE landing_ids;
CREATE TABLE landings (
id BIGINT PRIMARY KEY DEFAULT nextval('landing_ids'),
harbour VARCHAR NOT NULL,
boat VARCHAR NOT NULL,
species VARCHAR NOT NULL,
kg INTEGER NOT NULL,
landed_on DATE NOT NULL
);
CREATE MACRO tonnes(kg) AS CAST(kg AS DOUBLE) / 1000;
CREATE MACRO harbour_landings(name) AS TABLE
SELECT boat, species, kg FROM landings WHERE harbour = name;
db/migrations/0001_landings.down.sql
DROP MACRO TABLE harbour_landings;
DROP MACRO tonnes;
DROP TABLE landings;
DROP SEQUENCE landing_ids;
db/queries/landings.sql
-- sgl.query(LandFish, exec)
INSERT INTO landings (harbour, boat, species, kg, landed_on) VALUES ($1, $2, $3, $4, $5);
-- sgl.query(BiggestLandingPerHarbour, many)
SELECT harbour, boat, species, kg, landed_on
FROM landings
QUALIFY row_number() OVER (PARTITION BY harbour ORDER BY kg DESC) = 1
ORDER BY harbour;
-- sgl.query(CatchBySpecies, many)
SELECT
harbour,
coalesce(sum(kg) FILTER (WHERE species = 'cod'), 0) AS cod_kg,
coalesce(sum(kg) FILTER (WHERE species = 'hake'), 0) AS hake_kg,
coalesce(sum(kg) FILTER (WHERE species = 'mackerel'), 0) AS mackerel_kg
FROM landings
GROUP BY harbour
ORDER BY harbour;
-- sgl.query(CatchPivot, many)
SELECT *
FROM (PIVOT landings ON species IN ('cod', 'hake', 'mackerel') USING sum(kg) GROUP BY harbour)
ORDER BY harbour;
-- sgl.query(TonnesPerBoat, many)
SELECT boat, tonnes(sum(kg)) AS tonnes
FROM landings
GROUP BY boat
ORDER BY boat;
-- sgl.query(HarbourCatch, many)
-- $1 as sgl.param(harbour, type: varchar)
SELECT species, CAST(sum(kg) AS BIGINT) AS kg
FROM harbour_landings($1)
GROUP BY species
ORDER BY species;
-- sgl.query(LandingValues, many)
-- $1 as sgl.param(price_file, type: varchar)
SELECT landings.boat, CAST(sum(landings.kg * prices.pence_per_kg) AS BIGINT) AS value_pence
FROM landings
JOIN read_parquet($1) AS prices ON prices.species = landings.species
GROUP BY landings.boat
ORDER BY landings.boat;
-- sgl.query(AuctionPrices, many)
-- $1 as sgl.param(price_file, type: varchar)
SELECT CAST(species AS VARCHAR) AS species, CAST(pence_per_kg AS INTEGER) AS pence_per_kg
FROM read_parquet($1)
ORDER BY species;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/duckdb/038_duckdb_analytics/internal/store"
)
// run lands a week of fish at two harbours and analyses it with DuckDB's own SQL, every
// result typed by Seagull:
//
// - BiggestLandingPerHarbour filters on a window function with QUALIFY, which keeps the
// table's types, so no subquery is needed to pick the top row per harbour;
// - CatchBySpecies builds one column per species with sum(...) FILTER (WHERE ...); SUM
// of an INTEGER is a HUGEINT in DuckDB, so each arrives as *big.Int;
// - CatchPivot builds the same columns with PIVOT ... ON species IN (...), one column
// per listed species, each a nullable HUGEINT, as a harbour may land none of one;
// - TonnesPerBoat calls tonnes, a macro from the migration, and its CAST gives the
// result a type, *float64;
// - HarbourCatch reads from harbour_landings, a table macro, whose columns Seagull
// types from the macro's body, so species is a string, NOT NULL like the landings
// column it comes from, and only the total is cast, to a BIGINT, for an *int64;
// - AuctionPrices and LandingValues read the auction house's Parquet file with
// read_parquet, and the path is a bound parameter. Seagull cannot look inside the
// file when it generates, so sgl vet warns Q001 for a column it cannot find, and a
// CAST gives each column its type; such columns are pointers, as nothing says they
// are NOT NULL.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes priceList (string) which is the path of the auction house's Parquet price list.
// Takes output (io.Writer) which receives each analysis.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, priceList string, output io.Writer) error {
queries := store.New(database)
landings := []store.LandFishParams{
{Harbour: "Newlyn", Boat: "Kittiwake", Species: "cod", Kg: 120, LandedOn: day(1)},
{Harbour: "Newlyn", Boat: "Kittiwake", Species: "mackerel", Kg: 300, LandedOn: day(2)},
{Harbour: "Newlyn", Boat: "Salty Chip", Species: "hake", Kg: 80, LandedOn: day(1)},
{Harbour: "Newlyn", Boat: "Salty Chip", Species: "cod", Kg: 60, LandedOn: day(3)},
{Harbour: "Brixham", Boat: "Gull Wing", Species: "cod", Kg: 150, LandedOn: day(2)},
{Harbour: "Brixham", Boat: "Gull Wing", Species: "hake", Kg: 40, LandedOn: day(3)},
{Harbour: "Brixham", Boat: "Driftwood", Species: "mackerel", Kg: 220, LandedOn: day(1)},
}
for _, landing := range landings {
if err := queries.LandFish(ctx, landing); err != nil {
return fmt.Errorf("landing %s's %s: %w", landing.Boat, landing.Species, err)
}
}
biggest, err := queries.BiggestLandingPerHarbour(ctx)
if err != nil {
return fmt.Errorf("finding the biggest landings: %w", err)
}
fmt.Fprintln(output, "biggest landing per harbour (QUALIFY):")
for _, landing := range biggest {
fmt.Fprintf(output, " %-8s %-10s %4dkg of %s on %s\n", landing.Harbour, landing.Boat, landing.Kg, landing.Species, landing.LandedOn.Format(time.DateOnly))
}
bySpecies, err := queries.CatchBySpecies(ctx)
if err != nil {
return fmt.Errorf("totalling the catch by species: %w", err)
}
fmt.Fprintln(output, "kg by species (FILTER):")
for _, harbour := range bySpecies {
fmt.Fprintf(output, " %-8s cod %v, hake %v, mackerel %v\n", harbour.Harbour, harbour.CodKg, harbour.HakeKg, harbour.MackerelKg)
}
pivoted, err := queries.CatchPivot(ctx)
if err != nil {
return fmt.Errorf("pivoting the catch by species: %w", err)
}
fmt.Fprintln(output, "kg by species (PIVOT):")
for _, harbour := range pivoted {
fmt.Fprintf(output, " %-8s cod %v, hake %v, mackerel %v\n", harbour.Harbour, harbour.Cod, harbour.Hake, harbour.Mackerel)
}
perBoat, err := queries.TonnesPerBoat(ctx)
if err != nil {
return fmt.Errorf("totalling each boat: %w", err)
}
fmt.Fprintln(output, "tonnes per boat (macro):")
for _, boat := range perBoat {
fmt.Fprintf(output, " %-10s %.3f t\n", boat.Boat, *boat.Tonnes)
}
newlyn, err := queries.HarbourCatch(ctx, "Newlyn")
if err != nil {
return fmt.Errorf("reading Newlyn's catch: %w", err)
}
fmt.Fprintln(output, "Newlyn's catch (table macro):")
for _, catch := range newlyn {
fmt.Fprintf(output, " %-8s %dkg\n", catch.Species, *catch.Kg)
}
prices, err := queries.AuctionPrices(ctx, priceList)
if err != nil {
return fmt.Errorf("reading the price list: %w", err)
}
fmt.Fprintln(output, "auction prices (read_parquet):")
for _, price := range prices {
fmt.Fprintf(output, " %-8s %dp a kg\n", *price.Species, *price.PencePerKg)
}
values, err := queries.LandingValues(ctx, priceList)
if err != nil {
return fmt.Errorf("valuing the landings: %w", err)
}
fmt.Fprintln(output, "value per boat (landings joined to the Parquet file):")
for _, boat := range values {
fmt.Fprintf(output, " %-10s £%d.%02d\n", boat.Boat, *boat.ValuePence/100, *boat.ValuePence%100)
}
return nil
}
// day returns a date in May 2026, the week the landings were made.
//
// Takes dayOfMonth (int) which is the day.
//
// Returns time.Time which is midnight UTC on that day.
func day(dayOfMonth int) time.Time {
return time.Date(2026, time.May, dayOfMonth, 0, 0, 0, 0, time.UTC)
}
main.go
// Command 038_duckdb_analytics applies its migrations to an in-memory DuckDB database,
// writes the fish market's price list to a Parquet file with COPY ... TO, then runs the
// example in example.go: a week of landings is analysed with QUALIFY, FILTER, macros and
// read_parquet.
//
// Run it with `go run .`, or `go run . -db example.duckdb` to keep the database in a
// file.
package main
import (
"context"
"database/sql"
"embed"
"flag"
"fmt"
"log"
"os"
"path/filepath"
_ "github.com/duckdb/duckdb-go/v2"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_duckdb"
)
// priceListName is the Parquet file the auction house's prices are written to.
const priceListName = "auction-prices.parquet"
// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS
func main() {
path := flag.String("db", "", "DuckDB database file; an in-memory database when empty")
flag.Parse()
if err := execute(context.Background(), *path); err != nil {
log.Fatal(err)
}
}
// execute opens and migrates the DuckDB database at path, or an in-memory one when path
// is empty, writes the price list to a temporary directory, and runs the example.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database or the price list cannot be prepared or the example
// fails.
func execute(ctx context.Context, path string) error {
database, err := sql.Open("duckdb", path)
if err != nil {
return err
}
defer func() { _ = database.Close() }()
migrator, err := migrate.NewMigrator(database, migrate_backend_duckdb.New(), migrations, "db/migrations")
if err != nil {
return err
}
if _, err := migrator.Up(ctx); err != nil {
return fmt.Errorf("migrating: %w", err)
}
directory, err := os.MkdirTemp("", "seagull-example-")
if err != nil {
return err
}
defer func() { _ = os.RemoveAll(directory) }()
priceList := filepath.Join(directory, priceListName)
if err := writePriceList(ctx, database, priceList); err != nil {
return fmt.Errorf("writing the price list: %w", err)
}
return run(ctx, database, priceList, os.Stdout)
}
// writePriceList writes the auction house's prices, in pence per kilogram, to a Parquet
// file with DuckDB's COPY ... TO. The file name is a bound parameter, like any other
// value, so the path needs no quoting.
//
// Takes database (*sql.DB) which runs the COPY.
// Takes path (string) which is the Parquet file to write.
//
// Returns error when the file cannot be written.
func writePriceList(ctx context.Context, database *sql.DB, path string) error {
_, err := database.ExecContext(ctx, `COPY (
SELECT * FROM (VALUES ('cod', 900), ('hake', 650), ('mackerel', 350)) AS prices (species, pence_per_kg)
) TO $1 (FORMAT parquet)`, path)
return err
}
internal/store/landings.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"math/big"
"time"
)
const auctionPricesSQL = `SELECT CAST(species AS VARCHAR) AS species, CAST(pence_per_kg AS INTEGER) AS pence_per_kg
FROM read_parquet($1)
ORDER BY species;`
type AuctionPricesRow struct {
Species *string `json:"species"`
PencePerKg *int32 `json:"pence_per_kg"`
}
func (queries *Queries) AuctionPrices(ctx context.Context, priceFile string) ([]AuctionPricesRow, error) {
rows, err := queries.reader.QueryContext(ctx, auctionPricesSQL, priceFile)
if err != nil {
return nil, err
}
defer rows.Close()
var results []AuctionPricesRow
for rows.Next() {
var row AuctionPricesRow
if err := rows.Scan(&row.Species, &row.PencePerKg); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const biggestLandingPerHarbourSQL = `SELECT harbour, boat, species, kg, landed_on
FROM landings
QUALIFY row_number() OVER (PARTITION BY harbour ORDER BY kg DESC) = 1
ORDER BY harbour;`
type BiggestLandingPerHarbourRow struct {
Harbour string `json:"harbour"`
Boat string `json:"boat"`
Species string `json:"species"`
Kg int32 `json:"kg"`
LandedOn time.Time `json:"landed_on"`
}
func (queries *Queries) BiggestLandingPerHarbour(ctx context.Context) ([]BiggestLandingPerHarbourRow, error) {
rows, err := queries.reader.QueryContext(ctx, biggestLandingPerHarbourSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []BiggestLandingPerHarbourRow
if rows.Next() {
var row BiggestLandingPerHarbourRow
scanArgs := []any{&row.Harbour, &row.Boat, &row.Species, &row.Kg, &row.LandedOn}
for more := true; more; more = rows.Next() {
row = BiggestLandingPerHarbourRow{}
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 catchBySpeciesSQL = `SELECT
harbour,
coalesce(sum(kg) FILTER (WHERE species = 'cod'), 0) AS cod_kg,
coalesce(sum(kg) FILTER (WHERE species = 'hake'), 0) AS hake_kg,
coalesce(sum(kg) FILTER (WHERE species = 'mackerel'), 0) AS mackerel_kg
FROM landings
GROUP BY harbour
ORDER BY harbour;`
type CatchBySpeciesRow struct {
Harbour string `json:"harbour"`
CodKg *big.Int `json:"cod_kg"`
HakeKg *big.Int `json:"hake_kg"`
MackerelKg *big.Int `json:"mackerel_kg"`
}
func (queries *Queries) CatchBySpecies(ctx context.Context) ([]CatchBySpeciesRow, error) {
rows, err := queries.reader.QueryContext(ctx, catchBySpeciesSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []CatchBySpeciesRow
for rows.Next() {
var row CatchBySpeciesRow
if err := rows.Scan(&row.Harbour, &row.CodKg, &row.HakeKg, &row.MackerelKg); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const catchPivotSQL = `SELECT *
FROM (PIVOT landings ON species IN ('cod', 'hake', 'mackerel') USING sum(kg) GROUP BY harbour)
ORDER BY harbour;`
type CatchPivotRow struct {
Harbour string `json:"harbour"`
Cod *big.Int `json:"cod"`
Hake *big.Int `json:"hake"`
Mackerel *big.Int `json:"mackerel"`
}
func (queries *Queries) CatchPivot(ctx context.Context) ([]CatchPivotRow, error) {
rows, err := queries.reader.QueryContext(ctx, catchPivotSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []CatchPivotRow
for rows.Next() {
var row CatchPivotRow
if err := rows.Scan(&row.Harbour, &row.Cod, &row.Hake, &row.Mackerel); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const harbourCatchSQL = `SELECT species, CAST(sum(kg) AS BIGINT) AS kg
FROM harbour_landings($1)
GROUP BY species
ORDER BY species;`
type HarbourCatchRow struct {
Species string `json:"species"`
Kg *int64 `json:"kg"`
}
func (queries *Queries) HarbourCatch(ctx context.Context, harbour string) ([]HarbourCatchRow, error) {
rows, err := queries.reader.QueryContext(ctx, harbourCatchSQL, harbour)
if err != nil {
return nil, err
}
defer rows.Close()
var results []HarbourCatchRow
for rows.Next() {
var row HarbourCatchRow
if err := rows.Scan(&row.Species, &row.Kg); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const landFishSQL = `INSERT INTO landings (harbour, boat, species, kg, landed_on) VALUES ($1, $2, $3, $4, $5);`
type LandFishParams struct {
Harbour string
Boat string
Species string
Kg int32
LandedOn time.Time
}
func (queries *Queries) LandFish(ctx context.Context, params LandFishParams) error {
_, err := queries.writer.ExecContext(ctx, landFishSQL, params.Harbour, params.Boat, params.Species, params.Kg, params.LandedOn)
return err
}
const landingValuesSQL = `SELECT landings.boat, CAST(sum(landings.kg * prices.pence_per_kg) AS BIGINT) AS value_pence
FROM landings
JOIN read_parquet($1) AS prices ON prices.species = landings.species
GROUP BY landings.boat
ORDER BY landings.boat;`
type LandingValuesRow struct {
Boat string `json:"boat"`
ValuePence *int64 `json:"value_pence"`
}
func (queries *Queries) LandingValues(ctx context.Context, priceFile string) ([]LandingValuesRow, error) {
rows, err := queries.reader.QueryContext(ctx, landingValuesSQL, priceFile)
if err != nil {
return nil, err
}
defer rows.Close()
var results []LandingValuesRow
for rows.Next() {
var row LandingValuesRow
if err := rows.Scan(&row.Boat, &row.ValuePence); err != nil {
return nil, err
}
results = append(results, row)
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const tonnesPerBoatSQL = `SELECT boat, tonnes(sum(kg)) AS tonnes
FROM landings
GROUP BY boat
ORDER BY boat;`
type TonnesPerBoatRow struct {
Boat string `json:"boat"`
Tonnes *float64 `json:"tonnes"`
}
func (queries *Queries) TonnesPerBoat(ctx context.Context) ([]TonnesPerBoatRow, error) {
rows, err := queries.reader.QueryContext(ctx, tonnesPerBoatSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []TonnesPerBoatRow
for rows.Next() {
var row TonnesPerBoatRow
if err := rows.Scan(&row.Boat, &row.Tonnes); 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 Landings struct {
ID int64 `json:"id"`
Harbour string `json:"harbour"`
Boat string `json:"boat"`
Species string `json:"species"`
Kg int32 `json:"kg"`
LandedOn time.Time `json:"landed_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
biggest landing per harbour (QUALIFY):
Brixham Driftwood 220kg of mackerel on 2026-05-01
Newlyn Kittiwake 300kg of mackerel on 2026-05-02
kg by species (FILTER):
Brixham cod 150, hake 40, mackerel 220
Newlyn cod 180, hake 80, mackerel 300
kg by species (PIVOT):
Brixham cod 150, hake 40, mackerel 220
Newlyn cod 180, hake 80, mackerel 300
tonnes per boat (macro):
Driftwood 0.220 t
Gull Wing 0.190 t
Kittiwake 0.420 t
Salty Chip 0.140 t
Newlyn's catch (table macro):
cod 180kg
hake 80kg
mackerel 300kg
auction prices (read_parquet):
cod 900p a kg
hake 650p a kg
mackerel 350p a kg
value per boat (landings joined to the Parquet file):
Driftwood £770.00
Gull Wing £1610.00
Kittiwake £2130.00
Salty Chip £1060.00