Nullability through joins, COALESCE and aggregates

On DuckDB:

• COUNT maps to int64, and MAX keeps its column's type: a DATE arrives as *time.Time
• GROUP BY lists every selected gulls column, as DuckDB does not infer them from the primary key
• The ids come from sequences, as DuckDB has no auto-incrementing key

A Go string cannot be NULL, so a field that receives a column which might be NULL has to be able to say so. Seagull makes such a field a pointer, where nil means NULL, and leaves every other field a plain value. The work is in knowing which is which. A column's own NOT NULL is where it starts, but a query can take that guarantee away, with an outer join or an aggregate over no rows, or give it back, with a fallback that is never NULL. Seagull follows the SQL to decide each field, so you do not have to remember which columns to check for nil.

This example rings gulls and records where they were seen. One gull has no nickname and another has never been seen, so both kinds of NULL turn up.

The schema

nickname has no NOT NULL, so it may be NULL. Every other column is NOT NULL.

db/migrations/0001_gulls.up.sql

CREATE SEQUENCE gull_ids;

CREATE SEQUENCE sighting_ids;

CREATE TABLE gulls (
    id       BIGINT  PRIMARY KEY DEFAULT nextval('gull_ids'),
    ring     VARCHAR NOT NULL UNIQUE,
    nickname VARCHAR
);

CREATE TABLE sightings (
    id      BIGINT  PRIMARY KEY DEFAULT nextval('sighting_ids'),
    gull_id BIGINT  NOT NULL REFERENCES gulls (id),
    seen_on DATE    NOT NULL,
    place   VARCHAR NOT NULL
);

A nullable column

RingGull inserts a gull. Because nickname may be NULL, its parameter is a *string, and the program passes nil for the gull without one.

internal/store

type RingGullParams struct {
	Ring     string
	Nickname *string
}

func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error {
	_, err := queries.writer.ExecContext(ctx, ringGullSQL, params.Ring, params.Nickname)
	return err
}

Through a LEFT JOIN

ListSightings lists every gull with each of its sightings. sightings.place cannot be NULL in its own table, but a LEFT JOIN keeps a gull that has no sightings and gives it NULL for every sighting column. So in this query Place is a *string too, alongside Nickname, which was nullable from the start.

db/queries/gulls.sql

-- sgl.query(ListSightings, many)
SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;

internal/store

type ListSightingsRow struct {
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
	Place    *string `json:"place"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	for rows.Next() {
		var row ListSightingsRow
		if err := rows.Scan(&row.Ring, &row.Nickname, &row.Place); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

COALESCE and aggregates

SummariseGulls gives one line per gull: what to call it, how often it was seen, and when it was last seen. Each of those three columns comes out differently.

db/queries/gulls.sql

-- sgl.query(SummariseGulls, many)
SELECT
    gulls.ring,
    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
    COUNT(sightings.id) AS times_seen,
    MAX(sightings.seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id, gulls.ring, gulls.nickname
ORDER BY gulls.id;

• COALESCE falls back to 'ring ' || gulls.ring, which is never NULL, so KnownAs is a plain string.
• COUNT is never NULL, since no rows counts as 0, so TimesSeen is a plain int64.
• MAX over no rows is NULL, so LastSeen is a pointer. It keeps the column's type, and seen_on is a DATE, so it is a *time.Time.

internal/store

type SummariseGullsRow struct {
	Ring      string     `json:"ring"`
	KnownAs   string     `json:"known_as"`
	TimesSeen int64      `json:"times_seen"`
	LastSeen  *time.Time `json:"last_seen"`
}

func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, summariseGullsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SummariseGullsRow
	for rows.Next() {
		var row SummariseGullsRow
		if err := rows.Scan(&row.Ring, &row.KnownAs, &row.TimesSeen, &row.LastSeen); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Using it

The program reads every pointer through a small orNull helper, which prints NULL for nil. The types say which values can be missing: row.Ring is a string to print as it is, while row.Place has to be dereferenced, and may be nil.

example.go

package main

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

	"seagull.sh/seagull/examples/duckdb/004_nullability/internal/store"
)

// run rings three gulls, records where two of them were seen, and lists them twice.
// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
// a pointer:
//
//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
//     column;
//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
//     NULL place, so Place is *string;
//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
//     *time.Time, as seen_on is a DATE.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives both 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.RingGullParams{
		{Ring: "GB1001", Nickname: new("Steven")},
		{Ring: "GB1002", Nickname: nil},
		{Ring: "GB1003", Nickname: new("Gwen")},
	}
	for _, gull := range gulls {
		if err := queries.RingGull(ctx, gull); err != nil {
			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
		}
	}
	sightings := []store.RecordSightingParams{
		{GullID: 1, SeenOn: day(1), Place: "the pier"},
		{GullID: 1, SeenOn: day(3), Place: "the chip shop"},
		{GullID: 2, SeenOn: day(2), Place: "the harbour wall"},
	}
	for _, sighting := range sightings {
		if err := queries.RecordSighting(ctx, sighting); err != nil {
			return fmt.Errorf("recording a sighting: %w", err)
		}
	}

	rows, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	fmt.Fprintln(output, "ring    nickname  place")
	for _, row := range rows {
		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
	}

	summaries, err := queries.SummariseGulls(ctx)
	if err != nil {
		return fmt.Errorf("summarising gulls: %w", err)
	}
	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
	for _, summary := range summaries {
		lastSeen := "NULL"
		if summary.LastSeen != nil {
			lastSeen = summary.LastSeen.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
	}
	return nil
}

// day returns a date in May 2026, the month the sightings 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)
}

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

What it prints

ring    nickname  place
GB1001  Steven    the pier
GB1001  Steven    the chip shop
GB1002  NULL      the harbour wall
GB1003  Gwen      NULL

ring    known as     times seen  last seen
GB1001  Steven       2           2026-05-03
GB1002  ring GB1002  1           2026-05-02
GB1003  Gwen         0           NULL

Try it

Change the LEFT JOIN in ListSightings to a plain JOIN and run sgl generate. Place becomes a string, because an inner join never invents a row, and the program prints one line fewer: the gull that was never seen drops out.

Inference follows the SQL, not your data. When you know a column cannot be NULL in practice, sgl.column(name, nullable: false) in the query's header overrides it for that query; Type overrides covers the directive.

Across engines

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

	DuckDB	SQLite	PostgreSQL	MySQL	ClickHouse
times_seen column	int64	int64	int64	int64	uint64
last_seen column	*time.Time	*string	*time.Time	*time.Time	*time.Time

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/duckdb && go run ./004_nullability

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

cd seagull/examples/duckdb && go test ./004_nullability

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: colony
    dialect: duckdb
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_gulls.up.sql

CREATE SEQUENCE gull_ids;

CREATE SEQUENCE sighting_ids;

CREATE TABLE gulls (
    id       BIGINT  PRIMARY KEY DEFAULT nextval('gull_ids'),
    ring     VARCHAR NOT NULL UNIQUE,
    nickname VARCHAR
);

CREATE TABLE sightings (
    id      BIGINT  PRIMARY KEY DEFAULT nextval('sighting_ids'),
    gull_id BIGINT  NOT NULL REFERENCES gulls (id),
    seen_on DATE    NOT NULL,
    place   VARCHAR NOT NULL
);

db/migrations/0001_gulls.down.sql

DROP TABLE sightings;
DROP TABLE gulls;
DROP SEQUENCE sighting_ids;
DROP SEQUENCE gull_ids;

db/queries/gulls.sql

-- sgl.query(RingGull, exec)
INSERT INTO gulls (ring, nickname) VALUES ($1, $2);

-- sgl.query(RecordSighting, exec)
INSERT INTO sightings (gull_id, seen_on, place) VALUES ($1, $2, $3);

-- sgl.query(ListSightings, many)
SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;

-- sgl.query(SummariseGulls, many)
SELECT
    gulls.ring,
    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
    COUNT(sightings.id) AS times_seen,
    MAX(sightings.seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id, gulls.ring, gulls.nickname
ORDER BY gulls.id;

example.go

package main

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

	"seagull.sh/seagull/examples/duckdb/004_nullability/internal/store"
)

// run rings three gulls, records where two of them were seen, and lists them twice.
// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
// a pointer:
//
//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
//     column;
//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
//     NULL place, so Place is *string;
//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
//     *time.Time, as seen_on is a DATE.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives both 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.RingGullParams{
		{Ring: "GB1001", Nickname: new("Steven")},
		{Ring: "GB1002", Nickname: nil},
		{Ring: "GB1003", Nickname: new("Gwen")},
	}
	for _, gull := range gulls {
		if err := queries.RingGull(ctx, gull); err != nil {
			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
		}
	}
	sightings := []store.RecordSightingParams{
		{GullID: 1, SeenOn: day(1), Place: "the pier"},
		{GullID: 1, SeenOn: day(3), Place: "the chip shop"},
		{GullID: 2, SeenOn: day(2), Place: "the harbour wall"},
	}
	for _, sighting := range sightings {
		if err := queries.RecordSighting(ctx, sighting); err != nil {
			return fmt.Errorf("recording a sighting: %w", err)
		}
	}

	rows, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	fmt.Fprintln(output, "ring    nickname  place")
	for _, row := range rows {
		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
	}

	summaries, err := queries.SummariseGulls(ctx)
	if err != nil {
		return fmt.Errorf("summarising gulls: %w", err)
	}
	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
	for _, summary := range summaries {
		lastSeen := "NULL"
		if summary.LastSeen != nil {
			lastSeen = summary.LastSeen.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
	}
	return nil
}

// day returns a date in May 2026, the month the sightings 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)
}

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

main.go

// Command 004_nullability applies its migrations to an in-memory DuckDB database, then
// runs the example in example.go: three gulls are ringed, their sightings recorded, and
// both listings show which fields can be NULL.
//
// 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"

	_ "github.com/duckdb/duckdb-go/v2"

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

// 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, and runs the example against it.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database 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)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/gulls.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"
)

const listSightingsSQL = `SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;`

type ListSightingsRow struct {
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
	Place    *string `json:"place"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	for rows.Next() {
		var row ListSightingsRow
		if err := rows.Scan(&row.Ring, &row.Nickname, &row.Place); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const recordSightingSQL = `INSERT INTO sightings (gull_id, seen_on, place) VALUES ($1, $2, $3);`

type RecordSightingParams struct {
	GullID int64
	SeenOn time.Time
	Place  string
}

func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error {
	_, err := queries.writer.ExecContext(ctx, recordSightingSQL, params.GullID, params.SeenOn, params.Place)
	return err
}

const ringGullSQL = `INSERT INTO gulls (ring, nickname) VALUES ($1, $2);`

type RingGullParams struct {
	Ring     string
	Nickname *string
}

func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error {
	_, err := queries.writer.ExecContext(ctx, ringGullSQL, params.Ring, params.Nickname)
	return err
}

const summariseGullsSQL = `SELECT
    gulls.ring,
    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
    COUNT(sightings.id) AS times_seen,
    MAX(sightings.seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id, gulls.ring, gulls.nickname
ORDER BY gulls.id;`

type SummariseGullsRow struct {
	Ring      string     `json:"ring"`
	KnownAs   string     `json:"known_as"`
	TimesSeen int64      `json:"times_seen"`
	LastSeen  *time.Time `json:"last_seen"`
}

func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, summariseGullsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SummariseGullsRow
	for rows.Next() {
		var row SummariseGullsRow
		if err := rows.Scan(&row.Ring, &row.KnownAs, &row.TimesSeen, &row.LastSeen); 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"`
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
}

type Sightings struct {
	ID     int64     `json:"id"`
	GullID int64     `json:"gull_id"`
	SeenOn time.Time `json:"seen_on"`
	Place  string    `json:"place"`
}

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

ring    nickname  place
GB1001  Steven    the pier
GB1001  Steven    the chip shop
GB1002  NULL      the harbour wall
GB1003  Gwen      NULL

ring    known as     times seen  last seen
GB1001  Steven       2           2026-05-03
GB1002  ring GB1002  1           2026-05-02
GB1003  Gwen         0           NULL