016 Parents with child slices on SQLite

On SQLite:
Columns are qualified with table names, which sgl.embed matches on

An order has many lines. Join the two tables and the database returns one row per line, repeating the order's columns on every one of them. What the program wants is the other shape: one value per order, with its lines in a slice. Without help, that means a loop over the rows, a map keyed by order id, and care to keep the orders in sequence. group_by on the query header has the generated method do all of that.

It builds on sgl.embed: each table's columns go into a nested struct, and group_by names the column that identifies a parent, so that rows sharing it are collapsed into one, with the child structs collected into a slice.

This example lists a chip shop's orders with the dishes on each.

The query

db/queries/orders.sql

-- sgl.query(ListOrders, many, group_by: orders.id)
-- sgl.embed(orders)
-- sgl.embed(order_lines)
SELECT orders.id, orders.customer, order_lines.id, order_lines.dish, order_lines.quantity
FROM orders
LEFT JOIN order_lines ON order_lines.order_id = orders.id
ORDER BY orders.id, order_lines.id;

Three directives work together:

group_by: orders.id on the header says that rows with the same orders.id belong to one order. It names the column by its table, not by an embed's name.
sgl.embed(orders) gathers the order's columns into an Orders struct.
sgl.embed(order_lines) gathers each line's columns. Because the query groups, this embed becomes a slice.

It is a LEFT JOIN, so an order with no lines still appears.

What sgl generates

internal/store

type ListOrdersRow struct {
	Orders     ListOrdersOrders
	OrderLines []ListOrdersOrderLines
}

type ListOrdersOrderLines struct {
	ID       *int64  `json:"id"`
	Dish     *string `json:"dish"`
	Quantity *int64  `json:"quantity"`
}

type ListOrdersOrders struct {
	ID       int64  `json:"id"`
	Customer string `json:"customer"`
}

type OrderLines struct {
	ID       int64  `json:"id"`
	OrderID  int64  `json:"order_id"`
	Dish     string `json:"dish"`
	Quantity int64  `json:"quantity"`
}

type Orders struct {
	ID       int64  `json:"id"`
	Customer string `json:"customer"`
}

func (queries *Queries) ListOrders(ctx context.Context) ([]ListOrdersRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listOrdersSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	groupIndex := make(map[int64]int)
	results := []ListOrdersRow{}
	for rows.Next() {
		var ordersID int64
		var ordersCustomer string
		var orderLinesID *int64
		var orderLinesDish *string
		var orderLinesQuantity *int64
		if err := rows.Scan(&ordersID, &ordersCustomer, &orderLinesID, &orderLinesDish, &orderLinesQuantity); err != nil {
			return nil, err
		}
		if entry, exists := groupIndex[ordersID]; exists {
			if orderLinesID != nil {
				results[entry].OrderLines = append(results[entry].OrderLines, ListOrdersOrderLines{ID: orderLinesID, Dish: orderLinesDish, Quantity: orderLinesQuantity})
			}
		} else {
			entry := len(results)
			groupIndex[ordersID] = entry
			results = append(results, ListOrdersRow{Orders: ListOrdersOrders{ID: ordersID, Customer: ordersCustomer}})
			if orderLinesID != nil {
				results[entry].OrderLines = append(results[entry].OrderLines, ListOrdersOrderLines{ID: orderLinesID, Dish: orderLinesDish, Quantity: orderLinesQuantity})
			}
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Each ListOrdersRow is one order: Orders holds its id and customer, and OrderLines holds a ListOrdersOrderLines for every line. The line's fields are pointers, because they come from the outer side of a LEFT JOIN, as nullability explains. An order with no lines gets an empty slice rather than one line full of nils.

The method does the grouping in Go as it scans. It keeps a map from each order id to its row, appends every line to the row it belongs to, and returns the orders in the order their first row arrived. So the query's ORDER BY still decides the result: here, orders by id, and each order's lines by line id.

Using it

The program opens three orders and adds four lines between the first two, leaving the third empty, then prints each order with its lines.

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/016_one_to_many/internal/store"
)

// run takes three chip shop orders and lists them with their lines. The join in
// ListOrders returns one row per order line, repeating the order's columns each time.
// group_by: orders.id collapses those rows: each distinct orders.id becomes one
// ListOrdersRow, holding the order's columns in Orders and every matching line in the
// OrderLines slice. Groups keep the order the rows arrive in.
//
// It is a LEFT JOIN, so an order with no lines still appears, with an empty slice, and
// the line fields are pointers. sgl vet checks the directive: group_by must name a column
// the query returns (Q014), the other tables need sgl.embed (Q015), and the command must
// be many (Q016).
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each order and its lines.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	for _, customer := range []string{"Steven", "Gwen", "Jago"} {
		if err := queries.OpenOrder(ctx, customer); err != nil {
			return fmt.Errorf("opening %s's order: %w", customer, err)
		}
	}
	lines := []store.AddLineParams{
		{OrderID: 1, Dish: "cod and chips", Quantity: 2},
		{OrderID: 1, Dish: "mushy peas", Quantity: 1},
		{OrderID: 1, Dish: "pickled egg", Quantity: 3},
		{OrderID: 2, Dish: "chips", Quantity: 1},
	}
	for _, line := range lines {
		if err := queries.AddLine(ctx, line); err != nil {
			return fmt.Errorf("adding %s to order %d: %w", line.Dish, line.OrderID, err)
		}
	}

	orders, err := queries.ListOrders(ctx)
	if err != nil {
		return fmt.Errorf("listing orders: %w", err)
	}
	for _, order := range orders {
		fmt.Fprintf(output, "order %d for %s\n", order.Orders.ID, order.Orders.Customer)
		if len(order.OrderLines) == 0 {
			fmt.Fprintln(output, "  nothing ordered yet")
		}
		for _, line := range order.OrderLines {
			fmt.Fprintf(output, "  %d x %s\n", *line.Quantity, *line.Dish)
		}
	}
	return nil
}

What it prints

order 1 for Steven
  2 x cod and chips
  1 x mushy peas
  3 x pickled egg
order 2 for Gwen
  1 x chips
order 3 for Jago
  nothing ordered yet

When the directives do not fit

sgl vet checks that the grouping makes sense, and warns when it does not:

Q014: group_by names a column the query does not select;
Q015: the query groups, but no sgl.embed gathers the other tables' columns, so there is nothing to collect;
Q016: the command is not many; collapsing rows only makes sense for a list.

Try it

Change the command from many to one and run sgl vet to see Q016, or point group_by at a column the query does not select to see Q014.

Across engines

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

	SQLite	PostgreSQL	MySQL	DuckDB
`quantity` column	`*int64`	`*int64`	`*int32`	`*int32`

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

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

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/sqlite && go run ./016_one_to_many

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

cd seagull/examples/sqlite && go test ./016_one_to_many

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

db/migrations/0001_orders.up.sql

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT    NOT NULL
);

CREATE TABLE order_lines (
    id       INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders (id),
    dish     TEXT    NOT NULL,
    quantity INTEGER NOT NULL
);

db/migrations/0001_orders.down.sql

DROP TABLE order_lines;
DROP TABLE orders;

db/queries/orders.sql

-- sgl.query(OpenOrder, exec)
INSERT INTO orders (customer) VALUES (?);

-- sgl.query(AddLine, exec)
INSERT INTO order_lines (order_id, dish, quantity) VALUES (?, ?, ?);

-- sgl.query(ListOrders, many, group_by: orders.id)
-- sgl.embed(orders)
-- sgl.embed(order_lines)
SELECT orders.id, orders.customer, order_lines.id, order_lines.dish, order_lines.quantity
FROM orders
LEFT JOIN order_lines ON order_lines.order_id = orders.id
ORDER BY orders.id, order_lines.id;

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/016_one_to_many/internal/store"
)

// run takes three chip shop orders and lists them with their lines. The join in
// ListOrders returns one row per order line, repeating the order's columns each time.
// group_by: orders.id collapses those rows: each distinct orders.id becomes one
// ListOrdersRow, holding the order's columns in Orders and every matching line in the
// OrderLines slice. Groups keep the order the rows arrive in.
//
// It is a LEFT JOIN, so an order with no lines still appears, with an empty slice, and
// the line fields are pointers. sgl vet checks the directive: group_by must name a column
// the query returns (Q014), the other tables need sgl.embed (Q015), and the command must
// be many (Q016).
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each order and its lines.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	for _, customer := range []string{"Steven", "Gwen", "Jago"} {
		if err := queries.OpenOrder(ctx, customer); err != nil {
			return fmt.Errorf("opening %s's order: %w", customer, err)
		}
	}
	lines := []store.AddLineParams{
		{OrderID: 1, Dish: "cod and chips", Quantity: 2},
		{OrderID: 1, Dish: "mushy peas", Quantity: 1},
		{OrderID: 1, Dish: "pickled egg", Quantity: 3},
		{OrderID: 2, Dish: "chips", Quantity: 1},
	}
	for _, line := range lines {
		if err := queries.AddLine(ctx, line); err != nil {
			return fmt.Errorf("adding %s to order %d: %w", line.Dish, line.OrderID, err)
		}
	}

	orders, err := queries.ListOrders(ctx)
	if err != nil {
		return fmt.Errorf("listing orders: %w", err)
	}
	for _, order := range orders {
		fmt.Fprintf(output, "order %d for %s\n", order.Orders.ID, order.Orders.Customer)
		if len(order.OrderLines) == 0 {
			fmt.Fprintln(output, "  nothing ordered yet")
		}
		for _, line := range order.OrderLines {
			fmt.Fprintf(output, "  %d x %s\n", *line.Quantity, *line.Dish)
		}
	}
	return nil
}

main.go

// Command 016_one_to_many applies its migrations to a SQLite database, then runs the
// example in example.go: chip shop orders are listed once each, with their lines as a
// slice, from a join that repeats every order.
//
// Run it with `go run .`, or `go run . -db chippy.db` to keep the database.
package main

import (
	"context"
	"database/sql"
	"embed"
	"flag"
	"fmt"
	"log"
	"os"
	"path/filepath"

	_ "modernc.org/sqlite"

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

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	path := flag.String("db", "", "SQLite database file; a temporary one when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at path, or a temporary 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 {
	if path == "" {
		directory, err := os.MkdirTemp("", "seagull-example-")
		if err != nil {
			return err
		}
		defer func() { _ = os.RemoveAll(directory) }()
		path = filepath.Join(directory, "example.db")
	}
	database, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_sqlite.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/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type OrderLines struct {
	ID       int64  `json:"id"`
	OrderID  int64  `json:"order_id"`
	Dish     string `json:"dish"`
	Quantity int64  `json:"quantity"`
}

type Orders struct {
	ID       int64  `json:"id"`
	Customer string `json:"customer"`
}

internal/store/orders.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addLineSQL = `INSERT INTO order_lines (order_id, dish, quantity) VALUES (?1, ?2, ?3);`

type AddLineParams struct {
	OrderID  int64
	Dish     string
	Quantity int64
}

func (queries *Queries) AddLine(ctx context.Context, params AddLineParams) error {
	_, err := queries.writer.ExecContext(ctx, addLineSQL, params.OrderID, params.Dish, params.Quantity)
	return err
}

const listOrdersSQL = `SELECT orders.id, orders.customer, order_lines.id, order_lines.dish, order_lines.quantity
FROM orders
LEFT JOIN order_lines ON order_lines.order_id = orders.id
ORDER BY orders.id, order_lines.id;`

type ListOrdersOrders struct {
	ID       int64  `json:"id"`
	Customer string `json:"customer"`
}

type ListOrdersOrderLines struct {
	ID       *int64  `json:"id"`
	Dish     *string `json:"dish"`
	Quantity *int64  `json:"quantity"`
}

type ListOrdersRow struct {
	Orders     ListOrdersOrders
	OrderLines []ListOrdersOrderLines
}

func (queries *Queries) ListOrders(ctx context.Context) ([]ListOrdersRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listOrdersSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	groupIndex := make(map[int64]int)
	results := []ListOrdersRow{}
	for rows.Next() {
		var ordersID int64
		var ordersCustomer string
		var orderLinesID *int64
		var orderLinesDish *string
		var orderLinesQuantity *int64
		if err := rows.Scan(&ordersID, &ordersCustomer, &orderLinesID, &orderLinesDish, &orderLinesQuantity); err != nil {
			return nil, err
		}
		if entry, exists := groupIndex[ordersID]; exists {
			if orderLinesID != nil {
				results[entry].OrderLines = append(results[entry].OrderLines, ListOrdersOrderLines{ID: orderLinesID, Dish: orderLinesDish, Quantity: orderLinesQuantity})
			}
		} else {
			entry := len(results)
			groupIndex[ordersID] = entry
			results = append(results, ListOrdersRow{Orders: ListOrdersOrders{ID: ordersID, Customer: ordersCustomer}})
			if orderLinesID != nil {
				results[entry].OrderLines = append(results[entry].OrderLines, ListOrdersOrderLines{ID: orderLinesID, Dish: orderLinesDish, Quantity: orderLinesQuantity})
			}
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const openOrderSQL = `INSERT INTO orders (customer) VALUES (?1);`

func (queries *Queries) OpenOrder(ctx context.Context, customer string) error {
	_, err := queries.writer.ExecContext(ctx, openOrderSQL, customer)
	return err
}

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

order 1 for Steven
  2 x cod and chips
  1 x mushy peas
  3 x pickled egg
order 2 for Gwen
  1 x chips
order 3 for Jago
  nothing ordered yet

Parents with child slices