Rows back from INSERT, UPDATE and DELETE

The same on CockroachDB. CockroachDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes, and the program migrates with migrate_backend_cockroachdb.New().

A write often produces values you need straight away: the id the database assigned, a default it filled in, a figure it worked out. RETURNING hands them back from the statement that wrote them, so there is no second query to fetch them and no gap in which another connection could change the row first. Seagull types the returned columns exactly as it types a SELECT's, and the command in the header says how many rows to expect: one for a single row, many for a list.

The example is a chip shop's order book. It places three orders, cancels one, serves the rest, and then tries to cancel an order that has already been served.

The schema

The insert gives neither the id nor the status: the database assigns the id, and status defaults to waiting. Those are the values the program wants back.

db/migrations/0001_orders.up.sql

CREATE TABLE orders (
    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    item     TEXT   NOT NULL,
    quantity BIGINT NOT NULL DEFAULT 1,
    status   TEXT   NOT NULL DEFAULT 'waiting'
);

An INSERT that returns a row

PlaceOrder inserts one row and returns three columns: the new id, the default status, and a price worked out in the statement itself. One row goes in and one comes out, so the command is one.

db/queries/orders.sql

-- sgl.query(PlaceOrder, one)
INSERT INTO orders (item, quantity) VALUES ($1, $2)
RETURNING id, status, quantity * 250 AS price_pence;

The generated method returns a PlaceOrderRow with a field for each returned column. price_pence is an expression rather than a column, so it is typed from its operands: quantity times an integer, which comes out as the same Go integer type as quantity.

internal/store

type PlaceOrderParams struct {
	Item     string
	Quantity int64
}

type PlaceOrderRow struct {
	ID         int64  `json:"id"`
	Status     string `json:"status"`
	PricePence int64  `json:"price_pence"`
}

func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) (PlaceOrderRow, error) {
	var row PlaceOrderRow
	err := queries.writer.QueryRowContext(ctx, placeOrderSQL, params.Item, params.Quantity).Scan(&row.ID, &row.Status, &row.PricePence)
	if err != nil {
		return PlaceOrderRow{}, err
	}
	return row, nil
}

The method runs on queries.writer, not queries.reader, because the statement writes even though it reads rows back. With a single connection the two are the same; it matters once reads go to a replica, as in read replicas.

A DELETE that returns what it removed

CancelOrder deletes an order only while it is still waiting, and returns the item and quantity it removed. It is also one, and like a one SELECT, it returns sql.ErrNoRows when its WHERE clause matches nothing. The program uses that to tell "cancelled" from "too late to cancel" without a separate lookup.

db/queries/orders.sql

-- sgl.query(CancelOrder, one, row: OrderLine)
DELETE FROM orders WHERE id = $1 AND status = 'waiting'
RETURNING id, item, quantity;

An UPDATE that returns many rows

ServeWaitingOrders marks every waiting order as served and returns one row for each order it changed. Any number of rows can come back, so the command is many and the method returns a slice.

db/queries/orders.sql

-- sgl.query(ServeWaitingOrders, many, row: OrderLine)
UPDATE orders SET status = 'served' WHERE status = 'waiting'
RETURNING id, item, quantity;

The database does not promise an order for the rows RETURNING produces, so the program sorts them by id before printing.

Using it

The program never asks for an id after inserting, and never reads an order before deleting it: every value it prints came back from the write that produced it.

example.go

package main

import (
	"cmp"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"
	"slices"

	"seagull.sh/seagull/examples/postgres/020_returning/internal/store"
)

// run places three orders, cancels one, serves the rest, tries to cancel an order that
// has already been served and reopens one. Every write uses RETURNING, so the rows it
// touched come back from the same statement.
//
// A query reads its rows into a struct of its own, such as PlaceOrderRow, unless its
// header names another. ServeWaitingOrders and CancelOrder return the same three columns,
// so both name the row type OrderLine with row: OrderLine, and the generated code
// declares that struct once for the two. ReopenOrder returns the whole order with
// RETURNING *, so model: orders reads it into Orders, the table's model in models.go,
// rather than a ReopenOrderRow with the same fields.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each write returned.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	// PlaceOrder is an INSERT declared as one: the id the identity column assigned, the
	// default status and a computed price all come back in a PlaceOrderRow.
	orders := []store.PlaceOrderParams{
		{Item: "chips", Quantity: 2},
		{Item: "battered cod", Quantity: 1},
		{Item: "mushy peas", Quantity: 1},
	}
	for _, order := range orders {
		placed, err := queries.PlaceOrder(ctx, order)
		if err != nil {
			return fmt.Errorf("placing an order for %s: %w", order.Item, err)
		}
		fmt.Fprintf(output, "placed    #%d %-12s %s, %dp\n", placed.ID, order.Item, placed.Status, placed.PricePence)
	}

	// CancelOrder is a DELETE declared as one, so it returns the row it removed.
	cancelled, err := queries.CancelOrder(ctx, 2)
	if err != nil {
		return fmt.Errorf("cancelling order 2: %w", err)
	}
	fmt.Fprintf(output, "cancelled #2 %-12s x%d\n", cancelled.Item, cancelled.Quantity)

	// ServeWaitingOrders is an UPDATE declared as many: one row for every order it changed.
	// PostgreSQL does not promise an order for RETURNING rows, so sort them.
	served, err := queries.ServeWaitingOrders(ctx)
	if err != nil {
		return fmt.Errorf("serving orders: %w", err)
	}
	slices.SortFunc(served, func(a, b store.OrderLine) int { return cmp.Compare(a.ID, b.ID) })
	for _, order := range served {
		fmt.Fprintf(output, "served    #%d %-12s x%d\n", order.ID, order.Item, order.Quantity)
	}

	// A one query that matches no row returns sql.ErrNoRows, writes included: order 1 has
	// been served, so the DELETE's WHERE clause no longer matches it.
	_, err = queries.CancelOrder(ctx, 1)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
	case err != nil:
		return fmt.Errorf("cancelling order 1: %w", err)
	default:
		return errors.New("cancelled an order that had already been served")
	}

	reopened, err := queries.ReopenOrder(ctx, 3)
	if err != nil {
		return fmt.Errorf("reopening order 3: %w", err)
	}
	fmt.Fprintf(output, "reopened  #%d %-12s x%d, %s\n", reopened.ID, reopened.Item, reopened.Quantity, reopened.Status)
	return nil
}

What it prints

The prices are computed by the database (two portions of chips at 250p each), and the last line is the sql.ErrNoRows from cancelling an order that has already been served.

placed    #1 chips        waiting, 500p
placed    #2 battered cod waiting, 250p
placed    #3 mushy peas   waiting, 250p
cancelled #2 battered cod x1
served    #1 chips        x2
served    #3 mushy peas   x1
cannot cancel #1: it is no longer waiting
reopened  #3 mushy peas   x1, waiting

Try it

Delete the RETURNING line from PlaceOrder but leave it declared as one, then run sgl vet. Without RETURNING the statement produces no columns, so there is nothing for one to return, and Seagull reports Q009. Changing the command to exec fixes it, at the cost of the id.

Across engines

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

	PostgreSQL	SQLite	MariaDB	DuckDB
quantity parameter	int64	int64	int32	int32
price_pence column	int64	int64	int32	int32

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

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

Where the example is missing:

• Not on MySQL. MySQL has no RETURNING; use execresult and LastInsertId.
• Not on ClickHouse. ClickHouse has no RETURNING.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./020_returning

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=cockroachdb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./020_returning

Needs a running CockroachDB 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: orders
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
    migrate:
      dsn: ${env:DATABASE_URL}

db/migrations/0001_orders.up.sql

CREATE TABLE orders (
    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    item     TEXT   NOT NULL,
    quantity BIGINT NOT NULL DEFAULT 1,
    status   TEXT   NOT NULL DEFAULT 'waiting'
);

db/migrations/0001_orders.down.sql

DROP TABLE orders;

db/queries/orders.sql

-- sgl.query(PlaceOrder, one)
INSERT INTO orders (item, quantity) VALUES ($1, $2)
RETURNING id, status, quantity * 250 AS price_pence;

-- sgl.query(ServeWaitingOrders, many, row: OrderLine)
UPDATE orders SET status = 'served' WHERE status = 'waiting'
RETURNING id, item, quantity;

-- sgl.query(CancelOrder, one, row: OrderLine)
DELETE FROM orders WHERE id = $1 AND status = 'waiting'
RETURNING id, item, quantity;

-- sgl.query(ReopenOrder, one, model: orders)
UPDATE orders SET status = 'waiting' WHERE id = $1
RETURNING *;

example.go

package main

import (
	"cmp"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"
	"slices"

	"seagull.sh/seagull/examples/postgres/020_returning/internal/store"
)

// run places three orders, cancels one, serves the rest, tries to cancel an order that
// has already been served and reopens one. Every write uses RETURNING, so the rows it
// touched come back from the same statement.
//
// A query reads its rows into a struct of its own, such as PlaceOrderRow, unless its
// header names another. ServeWaitingOrders and CancelOrder return the same three columns,
// so both name the row type OrderLine with row: OrderLine, and the generated code
// declares that struct once for the two. ReopenOrder returns the whole order with
// RETURNING *, so model: orders reads it into Orders, the table's model in models.go,
// rather than a ReopenOrderRow with the same fields.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each write returned.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	// PlaceOrder is an INSERT declared as one: the id the identity column assigned, the
	// default status and a computed price all come back in a PlaceOrderRow.
	orders := []store.PlaceOrderParams{
		{Item: "chips", Quantity: 2},
		{Item: "battered cod", Quantity: 1},
		{Item: "mushy peas", Quantity: 1},
	}
	for _, order := range orders {
		placed, err := queries.PlaceOrder(ctx, order)
		if err != nil {
			return fmt.Errorf("placing an order for %s: %w", order.Item, err)
		}
		fmt.Fprintf(output, "placed    #%d %-12s %s, %dp\n", placed.ID, order.Item, placed.Status, placed.PricePence)
	}

	// CancelOrder is a DELETE declared as one, so it returns the row it removed.
	cancelled, err := queries.CancelOrder(ctx, 2)
	if err != nil {
		return fmt.Errorf("cancelling order 2: %w", err)
	}
	fmt.Fprintf(output, "cancelled #2 %-12s x%d\n", cancelled.Item, cancelled.Quantity)

	// ServeWaitingOrders is an UPDATE declared as many: one row for every order it changed.
	// PostgreSQL does not promise an order for RETURNING rows, so sort them.
	served, err := queries.ServeWaitingOrders(ctx)
	if err != nil {
		return fmt.Errorf("serving orders: %w", err)
	}
	slices.SortFunc(served, func(a, b store.OrderLine) int { return cmp.Compare(a.ID, b.ID) })
	for _, order := range served {
		fmt.Fprintf(output, "served    #%d %-12s x%d\n", order.ID, order.Item, order.Quantity)
	}

	// A one query that matches no row returns sql.ErrNoRows, writes included: order 1 has
	// been served, so the DELETE's WHERE clause no longer matches it.
	_, err = queries.CancelOrder(ctx, 1)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
	case err != nil:
		return fmt.Errorf("cancelling order 1: %w", err)
	default:
		return errors.New("cancelled an order that had already been served")
	}

	reopened, err := queries.ReopenOrder(ctx, 3)
	if err != nil {
		return fmt.Errorf("reopening order 3: %w", err)
	}
	fmt.Fprintf(output, "reopened  #%d %-12s x%d, %s\n", reopened.ID, reopened.Item, reopened.Quantity, reopened.Status)
	return nil
}

main.go

// Command 020_returning applies its migrations to a PostgreSQL database, then runs the
// example in example.go: orders are placed, cancelled and served, and each write hands
// back the rows it touched through RETURNING.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, 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/jackc/pgx/v5/stdlib"

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

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

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// 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 PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.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 Orders struct {
	ID       int64  `json:"id"`
	Item     string `json:"item"`
	Quantity int64  `json:"quantity"`
	Status   string `json:"status"`
}

internal/store/orders.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const cancelOrderSQL = `DELETE FROM orders WHERE id = $1 AND status = 'waiting'
RETURNING id, item, quantity;`

type OrderLine struct {
	ID       int64  `json:"id"`
	Item     string `json:"item"`
	Quantity int64  `json:"quantity"`
}

func (queries *Queries) CancelOrder(ctx context.Context, id int64) (OrderLine, error) {
	var row OrderLine
	err := queries.writer.QueryRowContext(ctx, cancelOrderSQL, id).Scan(&row.ID, &row.Item, &row.Quantity)
	if err != nil {
		return OrderLine{}, err
	}
	return row, nil
}

const placeOrderSQL = `INSERT INTO orders (item, quantity) VALUES ($1, $2)
RETURNING id, status, quantity * 250 AS price_pence;`

type PlaceOrderParams struct {
	Item     string
	Quantity int64
}

type PlaceOrderRow struct {
	ID         int64  `json:"id"`
	Status     string `json:"status"`
	PricePence int64  `json:"price_pence"`
}

func (queries *Queries) PlaceOrder(ctx context.Context, params PlaceOrderParams) (PlaceOrderRow, error) {
	var row PlaceOrderRow
	err := queries.writer.QueryRowContext(ctx, placeOrderSQL, params.Item, params.Quantity).Scan(&row.ID, &row.Status, &row.PricePence)
	if err != nil {
		return PlaceOrderRow{}, err
	}
	return row, nil
}

const reopenOrderSQL = `UPDATE orders SET status = 'waiting' WHERE id = $1
RETURNING "orders"."id", "orders"."item", "orders"."quantity", "orders"."status";`

func (queries *Queries) ReopenOrder(ctx context.Context, id int64) (Orders, error) {
	var row Orders
	err := queries.writer.QueryRowContext(ctx, reopenOrderSQL, id).Scan(&row.ID, &row.Item, &row.Quantity, &row.Status)
	if err != nil {
		return Orders{}, err
	}
	return row, nil
}

const serveWaitingOrdersSQL = `UPDATE orders SET status = 'served' WHERE status = 'waiting'
RETURNING id, item, quantity;`

func (queries *Queries) ServeWaitingOrders(ctx context.Context) ([]OrderLine, error) {
	rows, err := queries.writer.QueryContext(ctx, serveWaitingOrdersSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []OrderLine
	if rows.Next() {
		var row OrderLine
		scanArgs := []any{&row.ID, &row.Item, &row.Quantity}
		for more := true; more; more = rows.Next() {
			row = OrderLine{}
			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
}

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

placed    #1 chips        waiting, 500p
placed    #2 battered cod waiting, 250p
placed    #3 mushy peas   waiting, 250p
cancelled #2 battered cod x1
served    #1 chips        x2
served    #3 mushy peas   x1
cannot cancel #1: it is no longer waiting
reopened  #3 mushy peas   x1, waiting