Rows back from INSERT, UPDATE and DELETE

On SQLite:

• RETURNING on INSERT, UPDATE and DELETE (SQLite 3.35 and later)
• SQLite does not promise an order for RETURNING rows, so the example sorts them
• ? placeholders, numbered ?1, ?2 in the generated SQL; every INTEGER column, the primary key included, maps to int64

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       INTEGER PRIMARY KEY,
    item     TEXT    NOT NULL,
    quantity INTEGER 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 (?, ?)
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)
DELETE FROM orders WHERE id = ? AND status = 'waiting'
RETURNING 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)
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/sqlite/020_returning/internal/store"
)

// run places three orders, cancels one, serves the rest and then tries to cancel an order
// that has already been served. Every write uses RETURNING, so the rows it touched come
// back from the same statement.
//
// 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 SQLite 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.
	// SQLite 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.ServeWaitingOrdersRow) 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)
	if errors.Is(err, sql.ErrNoRows) {
		fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
		return nil
	}
	if err != nil {
		return fmt.Errorf("cancelling order 1: %w", err)
	}
	return errors.New("cancelled an order that had already been served")
}

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

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.

	SQLite	PostgreSQL	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 PostgreSQL, 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/sqlite && go run ./020_returning

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

cd seagull/examples/sqlite && go test ./020_returning

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: 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,
    item     TEXT    NOT NULL,
    quantity INTEGER 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 (?, ?)
RETURNING id, status, quantity * 250 AS price_pence;

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

-- sgl.query(CancelOrder, one)
DELETE FROM orders WHERE id = ? AND status = 'waiting'
RETURNING item, quantity;

example.go

package main

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

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

// run places three orders, cancels one, serves the rest and then tries to cancel an order
// that has already been served. Every write uses RETURNING, so the rows it touched come
// back from the same statement.
//
// 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 SQLite 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.
	// SQLite 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.ServeWaitingOrdersRow) 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)
	if errors.Is(err, sql.ErrNoRows) {
		fmt.Fprintln(output, "cannot cancel #1: it is no longer waiting")
		return nil
	}
	if err != nil {
		return fmt.Errorf("cancelling order 1: %w", err)
	}
	return errors.New("cancelled an order that had already been served")
}

main.go

// Command 020_returning applies its migrations to a SQLite 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 .`, or `go run . -db orders.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 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 item, quantity;`

type CancelOrderRow struct {
	Item     string `json:"item"`
	Quantity int64  `json:"quantity"`
}

func (queries *Queries) CancelOrder(ctx context.Context, id int64) (CancelOrderRow, error) {
	var row CancelOrderRow
	err := queries.writer.QueryRowContext(ctx, cancelOrderSQL, id).Scan(&row.Item, &row.Quantity)
	if err != nil {
		return CancelOrderRow{}, 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 serveWaitingOrdersSQL = `UPDATE orders SET status = 'served' WHERE status = 'waiting'
RETURNING id, item, quantity;`

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

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