Nested structs with sgl.embed

On this page

On MySQL:

  • Columns are qualified with table names, which sgl.embed matches on
  • Foreign keys are BIGINT to match the BIGINT AUTO_INCREMENT keys they reference, as InnoDB requires, so they map to int64

A join returns one flat row, but in Go you usually want the joined record as a value of its own: a boat with its harbour inside it, not one flat struct with the harbour's columns beside the boat's own. sgl.embed does that. Name a joined table in the query's header, and the columns the query selects from it are gathered into a nested struct.

The join decides the shape. Through an inner join every row has a match, so the nested struct is a plain value. Through a LEFT JOIN the match may be missing, so the nested struct is a pointer, and nil means there was no row to join.

This example keeps a small fleet: boats, the harbour each is moored at, and the skipper some of them have.

The schema

Every boat has a harbour, so harbour_id is NOT NULL. A boat may have no skipper, so skipper_id can be NULL.

db/migrations/0001_fleet.up.sql

CREATE TABLE harbours (
    id     BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(50) NOT NULL,
    county VARCHAR(50) NOT NULL
);

CREATE TABLE skippers (
    id      BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    licence VARCHAR(20) NOT NULL
);

CREATE TABLE boats (
    id         BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    harbour_id BIGINT      NOT NULL,
    skipper_id BIGINT,
    FOREIGN KEY (harbour_id) REFERENCES harbours (id),
    FOREIGN KEY (skipper_id) REFERENCES skippers (id)
);

An inner join

GetBoat reads one boat with its harbour. sgl.embed(harbours) gathers the harbours columns:

db/queries/boats.sql

-- sgl.query(GetBoat, one)
-- sgl.embed(harbours)
SELECT boats.id, boats.name, harbours.id, harbours.name, harbours.county
FROM boats
INNER JOIN harbours ON harbours.id = boats.harbour_id
WHERE boats.id = ?;

internal/store

type GetBoatRow struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	Harbours GetBoatHarbours
}

type GetBoatHarbours struct {
	ID     int64  `json:"id"`
	Name   string `json:"name"`
	County string `json:"county"`
}

type Harbours struct {
	ID     int64  `json:"id"`
	Name   string `json:"name"`
	County string `json:"county"`
}

func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error) {
	var row GetBoatRow
	err := queries.reader.QueryRowContext(ctx, getBoatSQL, id).Scan(&row.ID, &row.Name, &row.Harbours.ID, &row.Harbours.Name, &row.Harbours.County)
	if err != nil {
		return GetBoatRow{}, err
	}
	return row, nil
}

The row keeps the boat's own ID and Name, and adds a Harbours field, named after the table. Its type, GetBoatHarbours, is named after the query and the table, and holds the harbour's ID, Name and County. The two id and two name columns no longer collide, because each lives in its own struct. An inner join always has a harbour, so Harbours is a value, and so are its fields.

A LEFT JOIN

ListCrews lists every boat with its skipper, if it has one:

db/queries/boats.sql

-- sgl.query(ListCrews, many)
-- sgl.embed(skippers)
SELECT boats.id, boats.name, skippers.id, skippers.name, skippers.licence
FROM boats
LEFT JOIN skippers ON skippers.id = boats.skipper_id
ORDER BY boats.id;

internal/store

type ListCrewsRow struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	Skippers *ListCrewsSkippers
}

type ListCrewsSkippers struct {
	ID      *int64  `json:"id"`
	Name    *string `json:"name"`
	Licence *string `json:"licence"`
}

type Skippers struct {
	ID      int64  `json:"id"`
	Name    string `json:"name"`
	Licence string `json:"licence"`
}

func (queries *Queries) ListCrews(ctx context.Context) ([]ListCrewsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listCrewsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListCrewsRow
	if rows.Next() {
		var row ListCrewsRow
		var embedRows struct {
			Skippers ListCrewsSkippers
		}
		scanArgs := []any{&row.ID, &row.Name, &embedRows.Skippers.ID, &embedRows.Skippers.Name, &embedRows.Skippers.Licence}
		for more := true; more; more = rows.Next() {
			row = ListCrewsRow{}
			embedRows = struct {
				Skippers ListCrewsSkippers
			}{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			if embedRows.Skippers.ID != nil {
				row.Skippers = &ListCrewsSkippers{ID: embedRows.Skippers.ID, Name: embedRows.Skippers.Name, Licence: embedRows.Skippers.Licence}
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Here Skippers is a *ListCrewsSkippers, and the method sets it to nil when the boat has no skipper, so checking for a skipper is one comparison. Inside it, every field is a pointer too: each column on the outer side of a LEFT JOIN can be NULL, as nullability explains.

The columns are qualified with their table names, harbours.name and skippers.licence, which is how sgl.embed knows which columns belong to which table. When a table appears under an alias, or twice in one query, from: names the alias the embed takes its columns from, and as: gives the field a different name; the directive reference covers both.

Using it

The program adds a harbour, a skipper and two boats, only one with a skipper. It reads boat 2 with its harbour, then lists the crews, checking crew.Skippers == nil before reading the skipper's fields.

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/015_embed/internal/store"
)

// run adds a small fleet and reads it back with joined tables as nested structs.
// sgl.embed(table) gathers the columns the query selects from that table into a field
// named after the table, here Harbours and Skippers, with its own struct type named after
// the query and the table, such as GetBoatHarbours.
//
// GetBoat uses an INNER JOIN, so every boat has a harbour and Harbours is a value.
// ListCrews uses a LEFT JOIN, so a boat may have no skipper: Skippers is a pointer that
// is nil when the joined row is missing, and its fields are pointers too, as each column
// of a LEFT JOIN can be NULL.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the boat and the crew list.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	if err := queries.AddHarbour(ctx, store.AddHarbourParams{Name: "Newlyn", County: "Cornwall"}); err != nil {
		return fmt.Errorf("adding a harbour: %w", err)
	}
	if err := queries.AddSkipper(ctx, store.AddSkipperParams{Name: "Morwenna Penrose", Licence: "MCA-1182"}); err != nil {
		return fmt.Errorf("adding a skipper: %w", err)
	}
	boats := []store.AddBoatParams{
		{Name: "Kittiwake", HarbourID: 1, SkipperID: new(int64(1))},
		{Name: "Salty Chip", HarbourID: 1, SkipperID: nil},
	}
	for _, boat := range boats {
		if err := queries.AddBoat(ctx, boat); err != nil {
			return fmt.Errorf("adding %s: %w", boat.Name, err)
		}
	}

	boat, err := queries.GetBoat(ctx, 2)
	if err != nil {
		return fmt.Errorf("getting boat 2: %w", err)
	}
	fmt.Fprintf(output, "%s is moored at %s, %s\n", boat.Name, boat.Harbours.Name, boat.Harbours.County)

	crews, err := queries.ListCrews(ctx)
	if err != nil {
		return fmt.Errorf("listing crews: %w", err)
	}
	for _, crew := range crews {
		if crew.Skippers == nil {
			fmt.Fprintf(output, "%s has no skipper\n", crew.Name)
			continue
		}
		fmt.Fprintf(output, "%s is skippered by %s, licence %s\n", crew.Name, *crew.Skippers.Name, *crew.Skippers.Licence)
	}
	return nil
}

What it prints

Salty Chip is moored at Newlyn, Cornwall
Kittiwake is skippered by Morwenna Penrose, licence MCA-1182
Salty Chip has no skipper

When the joined table has many rows for each parent, such as the lines of an order, Parents with child slices builds on sgl.embed to collect them into a slice.

Across engines

ListCrews has the same Go types on every engine that has this example.

MariaDB runs the MySQL version unchanged.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/mysql && go run ./015_embed

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

cd seagull/examples/mysql && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./015_embed

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

db/migrations/0001_fleet.up.sql

CREATE TABLE harbours (
    id     BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(50) NOT NULL,
    county VARCHAR(50) NOT NULL
);

CREATE TABLE skippers (
    id      BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    licence VARCHAR(20) NOT NULL
);

CREATE TABLE boats (
    id         BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    harbour_id BIGINT      NOT NULL,
    skipper_id BIGINT,
    FOREIGN KEY (harbour_id) REFERENCES harbours (id),
    FOREIGN KEY (skipper_id) REFERENCES skippers (id)
);

db/migrations/0001_fleet.down.sql

DROP TABLE boats;
DROP TABLE skippers;
DROP TABLE harbours;

db/queries/boats.sql

-- sgl.query(AddHarbour, exec)
INSERT INTO harbours (name, county) VALUES (?, ?);

-- sgl.query(AddSkipper, exec)
INSERT INTO skippers (name, licence) VALUES (?, ?);

-- sgl.query(AddBoat, exec)
INSERT INTO boats (name, harbour_id, skipper_id) VALUES (?, ?, ?);

-- sgl.query(GetBoat, one)
-- sgl.embed(harbours)
SELECT boats.id, boats.name, harbours.id, harbours.name, harbours.county
FROM boats
INNER JOIN harbours ON harbours.id = boats.harbour_id
WHERE boats.id = ?;

-- sgl.query(ListCrews, many)
-- sgl.embed(skippers)
SELECT boats.id, boats.name, skippers.id, skippers.name, skippers.licence
FROM boats
LEFT JOIN skippers ON skippers.id = boats.skipper_id
ORDER BY boats.id;

example.go

package main

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

	"seagull.sh/seagull/examples/mysql/015_embed/internal/store"
)

// run adds a small fleet and reads it back with joined tables as nested structs.
// sgl.embed(table) gathers the columns the query selects from that table into a field
// named after the table, here Harbours and Skippers, with its own struct type named after
// the query and the table, such as GetBoatHarbours.
//
// GetBoat uses an INNER JOIN, so every boat has a harbour and Harbours is a value.
// ListCrews uses a LEFT JOIN, so a boat may have no skipper: Skippers is a pointer that
// is nil when the joined row is missing, and its fields are pointers too, as each column
// of a LEFT JOIN can be NULL.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives the boat and the crew list.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	if err := queries.AddHarbour(ctx, store.AddHarbourParams{Name: "Newlyn", County: "Cornwall"}); err != nil {
		return fmt.Errorf("adding a harbour: %w", err)
	}
	if err := queries.AddSkipper(ctx, store.AddSkipperParams{Name: "Morwenna Penrose", Licence: "MCA-1182"}); err != nil {
		return fmt.Errorf("adding a skipper: %w", err)
	}
	boats := []store.AddBoatParams{
		{Name: "Kittiwake", HarbourID: 1, SkipperID: new(int64(1))},
		{Name: "Salty Chip", HarbourID: 1, SkipperID: nil},
	}
	for _, boat := range boats {
		if err := queries.AddBoat(ctx, boat); err != nil {
			return fmt.Errorf("adding %s: %w", boat.Name, err)
		}
	}

	boat, err := queries.GetBoat(ctx, 2)
	if err != nil {
		return fmt.Errorf("getting boat 2: %w", err)
	}
	fmt.Fprintf(output, "%s is moored at %s, %s\n", boat.Name, boat.Harbours.Name, boat.Harbours.County)

	crews, err := queries.ListCrews(ctx)
	if err != nil {
		return fmt.Errorf("listing crews: %w", err)
	}
	for _, crew := range crews {
		if crew.Skippers == nil {
			fmt.Fprintf(output, "%s has no skipper\n", crew.Name)
			continue
		}
		fmt.Fprintf(output, "%s is skippered by %s, licence %s\n", crew.Name, *crew.Skippers.Name, *crew.Skippers.Licence)
	}
	return nil
}

main.go

// Command 015_embed applies its migrations to a MySQL database, then runs the example in
// example.go: boats are read with their harbour and their skipper as nested structs.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/fleet'`, 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/boats.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addBoatSQL = `INSERT INTO boats (name, harbour_id, skipper_id) VALUES (?, ?, ?);`

type AddBoatParams struct {
	Name      string
	HarbourID int64
	SkipperID *int64
}

func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error {
	_, err := queries.writer.ExecContext(ctx, addBoatSQL, params.Name, params.HarbourID, params.SkipperID)
	return err
}

const addHarbourSQL = `INSERT INTO harbours (name, county) VALUES (?, ?);`

type AddHarbourParams struct {
	Name   string
	County string
}

func (queries *Queries) AddHarbour(ctx context.Context, params AddHarbourParams) error {
	_, err := queries.writer.ExecContext(ctx, addHarbourSQL, params.Name, params.County)
	return err
}

const addSkipperSQL = `INSERT INTO skippers (name, licence) VALUES (?, ?);`

type AddSkipperParams struct {
	Name    string
	Licence string
}

func (queries *Queries) AddSkipper(ctx context.Context, params AddSkipperParams) error {
	_, err := queries.writer.ExecContext(ctx, addSkipperSQL, params.Name, params.Licence)
	return err
}

const getBoatSQL = `SELECT boats.id, boats.name, harbours.id, harbours.name, harbours.county
FROM boats
INNER JOIN harbours ON harbours.id = boats.harbour_id
WHERE boats.id = ?;`

type GetBoatHarbours struct {
	ID     int64  `json:"id"`
	Name   string `json:"name"`
	County string `json:"county"`
}

type GetBoatRow struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	Harbours GetBoatHarbours
}

func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error) {
	var row GetBoatRow
	err := queries.reader.QueryRowContext(ctx, getBoatSQL, id).Scan(&row.ID, &row.Name, &row.Harbours.ID, &row.Harbours.Name, &row.Harbours.County)
	if err != nil {
		return GetBoatRow{}, err
	}
	return row, nil
}

const listCrewsSQL = `SELECT boats.id, boats.name, skippers.id, skippers.name, skippers.licence
FROM boats
LEFT JOIN skippers ON skippers.id = boats.skipper_id
ORDER BY boats.id;`

type ListCrewsSkippers struct {
	ID      *int64  `json:"id"`
	Name    *string `json:"name"`
	Licence *string `json:"licence"`
}

type ListCrewsRow struct {
	ID       int64  `json:"id"`
	Name     string `json:"name"`
	Skippers *ListCrewsSkippers
}

func (queries *Queries) ListCrews(ctx context.Context) ([]ListCrewsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listCrewsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListCrewsRow
	if rows.Next() {
		var row ListCrewsRow
		var embedRows struct {
			Skippers ListCrewsSkippers
		}
		scanArgs := []any{&row.ID, &row.Name, &embedRows.Skippers.ID, &embedRows.Skippers.Name, &embedRows.Skippers.Licence}
		for more := true; more; more = rows.Next() {
			row = ListCrewsRow{}
			embedRows = struct {
				Skippers ListCrewsSkippers
			}{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			if embedRows.Skippers.ID != nil {
				row.Skippers = &ListCrewsSkippers{ID: embedRows.Skippers.ID, Name: embedRows.Skippers.Name, Licence: embedRows.Skippers.Licence}
			}
			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

type Boats struct {
	ID        int64  `json:"id"`
	Name      string `json:"name"`
	HarbourID int64  `json:"harbour_id"`
	SkipperID *int64 `json:"skipper_id"`
}

type Harbours struct {
	ID     int64  `json:"id"`
	Name   string `json:"name"`
	County string `json:"county"`
}

type Skippers struct {
	ID      int64  `json:"id"`
	Name    string `json:"name"`
	Licence string `json:"licence"`
}

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

Salty Chip is moored at Newlyn, Cornwall
Kittiwake is skippered by Morwenna Penrose, licence MCA-1182
Salty Chip has no skipper

Also on other engines

SQLiteBoats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.PostgreSQLBoats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.CockroachDBBoats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.TimescaleDBBoats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.MariaDBBoats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.DuckDBBoats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.