Rows back from INSERT, UPDATE and DELETE

On MariaDB:

• RETURNING on INSERT, REPLACE and DELETE (MariaDB 10.5 and later), but not on UPDATE
• The update is execrows followed by a SELECT of the rows it changed
• ? placeholders; BIGINT AUTO_INCREMENT maps to int64 and INT to int32
• MySQL has no RETURNING at all; see concept 2 for execresult and LastInsertId

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      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item     VARCHAR(50) NOT NULL,
    quantity INT         NOT NULL DEFAULT 1,
    status   VARCHAR(20) 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 int32
}

type PlaceOrderRow struct {
	ID         int64  `json:"id"`
	Status     string `json:"status"`
	PricePence int32  `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

MariaDB has RETURNING on INSERT, REPLACE and DELETE, but not on UPDATE, and Seagull reports an UPDATE ... RETURNING as Q052. So serving the orders takes two queries: ServeWaitingOrders is execrows, which returns how many rows changed, and ServedOrders is an ordinary SELECT that reads the served orders back, sorted by id.

db/queries/orders.sql

-- sgl.query(ServeWaitingOrders, execrows)
UPDATE orders SET status = 'served' WHERE status = 'waiting';

db/queries/orders.sql

-- sgl.query(ServedOrders, many)
SELECT id, item, quantity FROM orders WHERE status = 'served' ORDER BY id;

ServedOrders returns every served order, not only the ones this update changed. In this program they are the same rows; in a busy shop you would narrow the SELECT to the rows you meant.

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 (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/mariadb/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. The INSERT and the DELETE use RETURNING, so the rows they
// 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 MariaDB 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)

	// MariaDB has RETURNING on INSERT, REPLACE and DELETE, but not on UPDATE, so
	// ServeWaitingOrders is execrows and ServedOrders reads the changed rows back.
	if _, err := queries.ServeWaitingOrders(ctx); err != nil {
		return fmt.Errorf("serving orders: %w", err)
	}
	served, err := queries.ServedOrders(ctx)
	if err != nil {
		return fmt.Errorf("listing served orders: %w", err)
	}
	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.

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

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with SQLite, PostgreSQL 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/mariadb && go run ./020_returning

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

cd seagull/examples/mariadb && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./020_returning

Needs a running MariaDB 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: mariadb
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_orders.up.sql

CREATE TABLE orders (
    id       BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item     VARCHAR(50) NOT NULL,
    quantity INT         NOT NULL DEFAULT 1,
    status   VARCHAR(20) 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, execrows)
UPDATE orders SET status = 'served' WHERE status = 'waiting';

-- sgl.query(ServedOrders, many)
SELECT id, item, quantity FROM orders WHERE status = 'served' ORDER BY id;

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

example.go

package main

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

	"seagull.sh/seagull/examples/mariadb/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. The INSERT and the DELETE use RETURNING, so the rows they
// 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 MariaDB 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)

	// MariaDB has RETURNING on INSERT, REPLACE and DELETE, but not on UPDATE, so
	// ServeWaitingOrders is execrows and ServedOrders reads the changed rows back.
	if _, err := queries.ServeWaitingOrders(ctx); err != nil {
		return fmt.Errorf("serving orders: %w", err)
	}
	served, err := queries.ServedOrders(ctx)
	if err != nil {
		return fmt.Errorf("listing served orders: %w", err)
	}
	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 MariaDB database, then runs the
// example in example.go: orders are placed, served and cancelled, and the INSERT and
// DELETE hand back the rows they touched through RETURNING.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/orders'`, 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"), "MariaDB 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 MariaDB 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/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Orders struct {
	ID       int64  `json:"id"`
	Item     string `json:"item"`
	Quantity int32  `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 = ? AND status = 'waiting'
RETURNING item, quantity;`

type CancelOrderRow struct {
	Item     string `json:"item"`
	Quantity int32  `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 (?, ?)
RETURNING id, status, quantity * 250 AS price_pence;`

type PlaceOrderParams struct {
	Item     string
	Quantity int32
}

type PlaceOrderRow struct {
	ID         int64  `json:"id"`
	Status     string `json:"status"`
	PricePence int32  `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';`

func (queries *Queries) ServeWaitingOrders(ctx context.Context) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, serveWaitingOrdersSQL)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const servedOrdersSQL = `SELECT id, item, quantity FROM orders WHERE status = 'served' ORDER BY id;`

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

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

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