Transactions

On CockroachDB:

• INTEGER is INT8 on CockroachDB, so balances and amounts are int64 rather than int32
• the CHECK failure reads failed to satisfy CHECK constraint (balance >= 0:::INT8), SQLSTATE 23514
• transactions use SERIALIZABLE isolation by default, and one whose read went stale before its write fails with SQLSTATE 40001 instead of overwriting the other write
• the generated RunInTx does not retry 40001, so retrySerialisable runs the function again in a new transaction, up to three attempts, reading the code through the error's SQLState method
• a 40001 message names timestamps and transaction IDs, so the output prints only its SQLSTATE
• $1 and $2 placeholders, with $1 as sgl.param(amount) naming the first; account IDs come from unique_rowid() and are not printed

A transaction groups several writes so that they all happen or none do. A transfer between two accounts is the classic case: it credits one account and debits the other, and if the debit fails, the credit must not survive on its own. The generated package gives you two ways to run queries in a transaction: RunInTx begins one, runs your function and commits or rolls back, and WithTx binds the queries to a transaction you began yourself.

The example is a small bank. It opens two accounts, makes a transfer that succeeds, then one that fails part-way through and leaves no trace.

The schema

The CHECK on balance is the rule the second transfer breaks. The database enforces it on every write, so a debit that would overdraw an account fails with an error.

db/migrations/0001_accounts.up.sql

CREATE TABLE accounts (
    id      INT8    NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
    owner   STRING  NOT NULL,
    balance INTEGER NOT NULL CHECK (balance >= 0)
);

The queries

A transfer is a Deposit into one account and a Withdraw from the other. Each is an ordinary exec query; nothing in the SQL says it will run in a transaction, because the same method works inside one or outside.

db/queries/accounts.sql

-- sgl.query(Withdraw, exec)
-- $1 as sgl.param(amount)
UPDATE accounts SET balance = balance - $1 WHERE id = $2;

The parameters are called amount and id, so Withdraw takes a WithdrawParams with Amount and ID fields. Left to itself, Seagull infers a parameter's name from the column it is used with, which would call the amount balance, so the query names it.

internal/store

type WithdrawParams struct {
	Amount int64
	ID     int64
}

func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error {
	_, err := queries.writer.ExecContext(ctx, withdrawSQL, params.Amount, params.ID)
	return err
}

RunInTx: commit or roll back

RunInTx is generated alongside New. It begins a transaction, hands your function a copy of the queries bound to it, and commits only if the function returns nil. The rollback is deferred, and a rollback after a commit does nothing, so every early return is covered.

internal/store

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

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
}

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()
}

The transfer is a function of the bound queries. It deposits first and withdraws second, so the second transfer, which asks Gull for more than Gull holds, has already credited Pip when the CHECK fails. The function returns the error, RunInTx returns it without committing, and the deferred rollback takes the credit back out.

WithTx: a transaction you begin

Opening the two accounts uses the other way. The program begins the transaction itself, calls WithTx to get queries bound to it, and commits when both accounts are open. Reach for WithTx when you need to begin the transaction with options, such as an isolation level, or when it is shared with code that does not use Seagull.

Retrying a serialisation failure

CockroachDB runs every transaction at SERIALIZABLE, and enforces it by aborting a transaction that would otherwise act on stale reads. If another client commits a change to a row after your transaction has read it, your write fails with SQLSTATE 40001, "restart transaction", rather than overwrite a change it never saw. The cure is to run the whole transaction again, from its first read, which is why CockroachDB has its own version of this example.

The generated RunInTx does not retry: it commits or rolls back once and returns the error, so on CockroachDB the caller retries. That is where the decision belongs, because a retry runs your whole function again: each attempt repeats every read, and anything the function does outside the transaction, such as sending an email, happens once per attempt. The example adds a small helper, retrySerialisable, that calls RunInTx again for as long as the error is a 40001, up to three attempts. It reads the code through the error's SQLState method, so the program does not need to import pgx to check it.

The transaction it retries pays interest by reading a balance into Go and writing back a tenth more:

db/queries/accounts.sql

-- sgl.query(GetBalance, one)
SELECT balance FROM accounts WHERE id = $1;

db/queries/accounts.sql

-- sgl.query(SetBalance, exec)
UPDATE accounts SET balance = $1 WHERE id = $2;

To make the conflict happen on demand, the first attempt deposits 100 into the same account between its read and its write, on the pool rather than in the transaction, standing in for another client. That deposit commits first, the interest write fails with 40001, and the second attempt reads the balance with the deposit in it and commits. Under real contention, wait a short, jittered while between attempts as well.

Using it

example.go

package main

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

	"seagull.sh/seagull/examples/cockroachdb/023_transactions/internal/store"
)

const (
	// serialisationFailure is the SQLSTATE CockroachDB reports when it aborts a transaction
	// that conflicted with another, so that the client can run it again.
	serialisationFailure = "40001"

	// maxAttempts bounds how many times retrySerialisable runs a transaction.
	maxAttempts = 3
)

// sqlStateError is the method a driver's error offers to report its SQLSTATE. pgx's
// *pgconn.PgError has it, so code can read the state without importing pgx.
type sqlStateError interface {
	error

	// SQLState returns the five-character SQLSTATE code.
	SQLState() string
}

// run opens two accounts in a transaction it manages itself, then makes two transfers
// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
// Last, it pays interest with a read-then-write transaction that another client's deposit
// interrupts, which CockroachDB aborts and retrySerialisable runs again.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each step and the balances.
//
// Returns error when a query fails other than as the example expects.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	pip, gull, err := openAccounts(ctx, database, queries)
	if err != nil {
		return fmt.Errorf("opening accounts: %w", err)
	}
	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
	if err := printBalances(ctx, queries, output); err != nil {
		return err
	}

	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
		return fmt.Errorf("first transfer: %w", err)
	}
	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
	if err := printBalances(ctx, queries, output); err != nil {
		return err
	}

	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
	// undone with it.
	err = transfer(ctx, database, queries, gull, pip, 9000)
	if err == nil {
		return errors.New("an overdrawing transfer committed")
	}
	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (%v)\n", err)
	if err := printBalances(ctx, queries, output); err != nil {
		return err
	}

	aborted, err := payInterest(ctx, database, queries, gull)
	if err != nil {
		return fmt.Errorf("paying interest: %w", err)
	}
	fmt.Fprintln(output, "paid Gull 10% interest while a deposit of 100 landed:")
	// A 40001 message names timestamps and transaction IDs that change on every run, so only
	// its SQLSTATE is printed.
	for index, abort := range aborted {
		fmt.Fprintf(output, "  attempt %d: aborted with SQLSTATE %s, retried\n", index+1, sqlState(abort))
	}
	fmt.Fprintf(output, "  attempt %d: committed\n", len(aborted)+1)
	return printBalances(ctx, queries, output)
}

// openAccounts opens both accounts in one transaction that it begins and commits itself.
// WithTx returns a copy of queries bound to that transaction, which suits a transaction
// begun with options or shared with code that does not use Seagull.
//
// Takes database (*sql.DB) which begins the transaction.
// Takes queries (*store.Queries) which are bound to it.
//
// Returns pipID (int64) which is Pip's account ID.
// Returns gullID (int64) which is Gull's account ID.
// Returns err (error) when an account cannot be opened or the commit fails.
func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
	transaction, err := database.BeginTx(ctx, nil)
	if err != nil {
		return 0, 0, err
	}
	// Rollback after a successful Commit does nothing, so deferring it covers every early
	// return.
	defer func() { _ = transaction.Rollback() }()

	inTransaction := queries.WithTx(transaction)
	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
	if err != nil {
		return 0, 0, err
	}
	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
	if err != nil {
		return 0, 0, err
	}
	return pip.ID, gull.ID, transaction.Commit()
}

// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
// returns an error.
//
// Takes database (*sql.DB) which begins the transaction.
// Takes queries (*store.Queries) which run the transfer.
// Takes from (int64) which is the account to debit.
// Takes to (int64) which is the account to credit.
// Takes amount (int64) which is the sum to move, in pence.
//
// Returns error when either update fails; the transaction is then rolled back.
func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
			return err
		}
		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
	})
}

// payInterest adds a tenth to an account's balance by reading the balance into Go and
// writing back the result, under retrySerialisable.
//
// To show a conflict on demand, the first attempt deposits 100 into the same account on
// the pool between its read and its write, standing in for another client. That deposit
// commits first, so the balance this transaction read is stale. CockroachDB uses
// SERIALIZABLE isolation by default, so instead of letting the write overwrite the
// deposit it fails the write with SQLSTATE 40001, "restart transaction", and RunInTx
// rolls back. The second attempt reads the balance with the deposit in it, and commits.
//
// Takes database (*sql.DB) which begins each attempt's transaction.
// Takes queries (*store.Queries) which run the reads and writes.
// Takes account (int64) which is the account to pay.
//
// Returns []error which holds the error of each attempt CockroachDB aborted.
// Returns error when an attempt fails other than with 40001, or every attempt does.
func payInterest(ctx context.Context, database *sql.DB, queries *store.Queries, account int64) ([]error, error) {
	interrupted := false
	return retrySerialisable(ctx, database, queries, func(inTransaction *store.Queries) error {
		current, err := inTransaction.GetBalance(ctx, account)
		if err != nil {
			return err
		}
		if !interrupted {
			interrupted = true
			if err := queries.Deposit(ctx, store.DepositParams{Amount: 100, ID: account}); err != nil {
				return fmt.Errorf("depositing from another client: %w", err)
			}
		}
		return inTransaction.SetBalance(ctx, store.SetBalanceParams{Balance: current.Balance + current.Balance/10, ID: account})
	})
}

// retrySerialisable runs work with RunInTx, and runs it again in a new transaction each
// time CockroachDB aborts the transaction with SQLSTATE 40001, up to maxAttempts.
//
// The generated RunInTx commits or rolls back once and returns the error, so on
// CockroachDB a caller retries itself. work must be safe to run more than once: each
// attempt starts again from its first read, and anything work does outside the
// transaction happens once per attempt. Under heavy contention, wait a short, jittered
// while between attempts as well.
//
// Takes database (*sql.DB) which begins each transaction.
// Takes queries (*store.Queries) which RunInTx binds to it.
// Takes work (func(*store.Queries) error) which is the transaction's work.
//
// Returns []error which holds the error of each attempt CockroachDB aborted, so the
// caller can log them.
// Returns error which is nil once an attempt commits, work's own error, or the 40001 of
// the last attempt.
func retrySerialisable(ctx context.Context, database *sql.DB, queries *store.Queries, work func(*store.Queries) error) ([]error, error) {
	var aborted []error
	for range maxAttempts {
		err := queries.RunInTx(ctx, database, work)
		if sqlState(err) != serialisationFailure {
			return aborted, err
		}
		aborted = append(aborted, err)
	}
	return aborted, fmt.Errorf("still conflicting after %d attempts: %w", maxAttempts, aborted[len(aborted)-1])
}

// sqlState returns the SQLSTATE that err, or an error it wraps, carries.
//
// Takes err (error) which is the error to inspect, or nil.
//
// Returns string which is the five-character code, or empty when err has none.
func sqlState(err error) string {
	stateError, hasState := errors.AsType[sqlStateError](err)
	if !hasState {
		return ""
	}
	return stateError.SQLState()
}

// printBalances writes every account's balance on one line.
//
// Takes queries (*store.Queries) which read the accounts.
// Takes output (io.Writer) which receives the line.
//
// Returns error when the accounts cannot be listed.
func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
	accounts, err := queries.ListAccounts(ctx)
	if err != nil {
		return fmt.Errorf("listing accounts: %w", err)
	}
	fmt.Fprint(output, "  balances:")
	for _, account := range accounts {
		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
	}
	fmt.Fprintln(output)
	return nil
}

What it prints

The rolled-back line carries the database's own error for the failed CHECK, and the balances after it are the same as before it: the credit to Pip was undone.

A 40001 message names timestamps and transaction ids that change on every run, so the program prints only its SQLSTATE. Gull ends with 3960: the 3500 it held, the 100 deposited during the first attempt, and a tenth of 3600.

opened accounts for Pip and Gull: committed
  balances: Pip 5000 Gull 2000
transferred 1500 from Pip to Gull: committed
  balances: Pip 3500 Gull 3500
transferred 9000 from Gull to Pip: rolled back (ERROR: failed to satisfy CHECK constraint (balance >= 0:::INT8) (SQLSTATE 23514))
  balances: Pip 3500 Gull 3500
paid Gull 10% interest while a deposit of 100 landed:
  attempt 1: aborted with SQLSTATE 40001, retried
  attempt 2: committed
  balances: Pip 3500 Gull 3960

Try it

In transfer, call queries.Deposit and queries.Withdraw directly instead of going through RunInTx, and run the example again. The second transfer still fails, but the deposit has already committed on its own, and Pip ends up 9000 richer than the bank can account for. Or set maxAttempts to 1, and the interest payment fails with the 40001 instead of committing on the second attempt.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/cockroachdb && go run ./023_transactions

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

cd seagull/examples/cockroachdb && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./023_transactions

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

db/migrations/0001_accounts.up.sql

CREATE TABLE accounts (
    id      INT8    NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
    owner   STRING  NOT NULL,
    balance INTEGER NOT NULL CHECK (balance >= 0)
);

db/migrations/0001_accounts.down.sql

DROP TABLE accounts;

db/queries/accounts.sql

-- sgl.query(OpenAccount, one)
INSERT INTO accounts (owner, balance) VALUES ($1, $2) RETURNING id;

-- sgl.query(Deposit, exec)
-- $1 as sgl.param(amount)
UPDATE accounts SET balance = balance + $1 WHERE id = $2;

-- sgl.query(Withdraw, exec)
-- $1 as sgl.param(amount)
UPDATE accounts SET balance = balance - $1 WHERE id = $2;

-- sgl.query(GetBalance, one)
SELECT balance FROM accounts WHERE id = $1;

-- sgl.query(SetBalance, exec)
UPDATE accounts SET balance = $1 WHERE id = $2;

-- sgl.query(ListAccounts, many)
SELECT id, owner, balance FROM accounts ORDER BY id;

example.go

package main

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

	"seagull.sh/seagull/examples/cockroachdb/023_transactions/internal/store"
)

const (
	// serialisationFailure is the SQLSTATE CockroachDB reports when it aborts a transaction
	// that conflicted with another, so that the client can run it again.
	serialisationFailure = "40001"

	// maxAttempts bounds how many times retrySerialisable runs a transaction.
	maxAttempts = 3
)

// sqlStateError is the method a driver's error offers to report its SQLSTATE. pgx's
// *pgconn.PgError has it, so code can read the state without importing pgx.
type sqlStateError interface {
	error

	// SQLState returns the five-character SQLSTATE code.
	SQLState() string
}

// run opens two accounts in a transaction it manages itself, then makes two transfers
// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
// Last, it pays interest with a read-then-write transaction that another client's deposit
// interrupts, which CockroachDB aborts and retrySerialisable runs again.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each step and the balances.
//
// Returns error when a query fails other than as the example expects.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	pip, gull, err := openAccounts(ctx, database, queries)
	if err != nil {
		return fmt.Errorf("opening accounts: %w", err)
	}
	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
	if err := printBalances(ctx, queries, output); err != nil {
		return err
	}

	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
		return fmt.Errorf("first transfer: %w", err)
	}
	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
	if err := printBalances(ctx, queries, output); err != nil {
		return err
	}

	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
	// undone with it.
	err = transfer(ctx, database, queries, gull, pip, 9000)
	if err == nil {
		return errors.New("an overdrawing transfer committed")
	}
	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (%v)\n", err)
	if err := printBalances(ctx, queries, output); err != nil {
		return err
	}

	aborted, err := payInterest(ctx, database, queries, gull)
	if err != nil {
		return fmt.Errorf("paying interest: %w", err)
	}
	fmt.Fprintln(output, "paid Gull 10% interest while a deposit of 100 landed:")
	// A 40001 message names timestamps and transaction IDs that change on every run, so only
	// its SQLSTATE is printed.
	for index, abort := range aborted {
		fmt.Fprintf(output, "  attempt %d: aborted with SQLSTATE %s, retried\n", index+1, sqlState(abort))
	}
	fmt.Fprintf(output, "  attempt %d: committed\n", len(aborted)+1)
	return printBalances(ctx, queries, output)
}

// openAccounts opens both accounts in one transaction that it begins and commits itself.
// WithTx returns a copy of queries bound to that transaction, which suits a transaction
// begun with options or shared with code that does not use Seagull.
//
// Takes database (*sql.DB) which begins the transaction.
// Takes queries (*store.Queries) which are bound to it.
//
// Returns pipID (int64) which is Pip's account ID.
// Returns gullID (int64) which is Gull's account ID.
// Returns err (error) when an account cannot be opened or the commit fails.
func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
	transaction, err := database.BeginTx(ctx, nil)
	if err != nil {
		return 0, 0, err
	}
	// Rollback after a successful Commit does nothing, so deferring it covers every early
	// return.
	defer func() { _ = transaction.Rollback() }()

	inTransaction := queries.WithTx(transaction)
	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
	if err != nil {
		return 0, 0, err
	}
	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
	if err != nil {
		return 0, 0, err
	}
	return pip.ID, gull.ID, transaction.Commit()
}

// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
// returns an error.
//
// Takes database (*sql.DB) which begins the transaction.
// Takes queries (*store.Queries) which run the transfer.
// Takes from (int64) which is the account to debit.
// Takes to (int64) which is the account to credit.
// Takes amount (int64) which is the sum to move, in pence.
//
// Returns error when either update fails; the transaction is then rolled back.
func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
			return err
		}
		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
	})
}

// payInterest adds a tenth to an account's balance by reading the balance into Go and
// writing back the result, under retrySerialisable.
//
// To show a conflict on demand, the first attempt deposits 100 into the same account on
// the pool between its read and its write, standing in for another client. That deposit
// commits first, so the balance this transaction read is stale. CockroachDB uses
// SERIALIZABLE isolation by default, so instead of letting the write overwrite the
// deposit it fails the write with SQLSTATE 40001, "restart transaction", and RunInTx
// rolls back. The second attempt reads the balance with the deposit in it, and commits.
//
// Takes database (*sql.DB) which begins each attempt's transaction.
// Takes queries (*store.Queries) which run the reads and writes.
// Takes account (int64) which is the account to pay.
//
// Returns []error which holds the error of each attempt CockroachDB aborted.
// Returns error when an attempt fails other than with 40001, or every attempt does.
func payInterest(ctx context.Context, database *sql.DB, queries *store.Queries, account int64) ([]error, error) {
	interrupted := false
	return retrySerialisable(ctx, database, queries, func(inTransaction *store.Queries) error {
		current, err := inTransaction.GetBalance(ctx, account)
		if err != nil {
			return err
		}
		if !interrupted {
			interrupted = true
			if err := queries.Deposit(ctx, store.DepositParams{Amount: 100, ID: account}); err != nil {
				return fmt.Errorf("depositing from another client: %w", err)
			}
		}
		return inTransaction.SetBalance(ctx, store.SetBalanceParams{Balance: current.Balance + current.Balance/10, ID: account})
	})
}

// retrySerialisable runs work with RunInTx, and runs it again in a new transaction each
// time CockroachDB aborts the transaction with SQLSTATE 40001, up to maxAttempts.
//
// The generated RunInTx commits or rolls back once and returns the error, so on
// CockroachDB a caller retries itself. work must be safe to run more than once: each
// attempt starts again from its first read, and anything work does outside the
// transaction happens once per attempt. Under heavy contention, wait a short, jittered
// while between attempts as well.
//
// Takes database (*sql.DB) which begins each transaction.
// Takes queries (*store.Queries) which RunInTx binds to it.
// Takes work (func(*store.Queries) error) which is the transaction's work.
//
// Returns []error which holds the error of each attempt CockroachDB aborted, so the
// caller can log them.
// Returns error which is nil once an attempt commits, work's own error, or the 40001 of
// the last attempt.
func retrySerialisable(ctx context.Context, database *sql.DB, queries *store.Queries, work func(*store.Queries) error) ([]error, error) {
	var aborted []error
	for range maxAttempts {
		err := queries.RunInTx(ctx, database, work)
		if sqlState(err) != serialisationFailure {
			return aborted, err
		}
		aborted = append(aborted, err)
	}
	return aborted, fmt.Errorf("still conflicting after %d attempts: %w", maxAttempts, aborted[len(aborted)-1])
}

// sqlState returns the SQLSTATE that err, or an error it wraps, carries.
//
// Takes err (error) which is the error to inspect, or nil.
//
// Returns string which is the five-character code, or empty when err has none.
func sqlState(err error) string {
	stateError, hasState := errors.AsType[sqlStateError](err)
	if !hasState {
		return ""
	}
	return stateError.SQLState()
}

// printBalances writes every account's balance on one line.
//
// Takes queries (*store.Queries) which read the accounts.
// Takes output (io.Writer) which receives the line.
//
// Returns error when the accounts cannot be listed.
func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
	accounts, err := queries.ListAccounts(ctx)
	if err != nil {
		return fmt.Errorf("listing accounts: %w", err)
	}
	fmt.Fprint(output, "  balances:")
	for _, account := range accounts {
		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
	}
	fmt.Fprintln(output)
	return nil
}

main.go

// Command 023_transactions applies its migrations to a CockroachDB database, then runs
// the example in example.go: two accounts are opened in one transaction, two transfers
// run with RunInTx, one committing and one rolled back by a CHECK constraint, and an
// interest payment is retried after CockroachDB aborts it with a serialisation failure.
//
// Run it with `go run .` against a local insecure node, which `docker run -d -p
// 26257:26257 cockroachdb/cockroach:latest-v25.1 start-single-node --insecure` starts, or
// point it elsewhere with `go run . -dsn <connection string>` or DATABASE_URL.
package main

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

	_ "github.com/jackc/pgx/v5/stdlib"

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

// localDSN reaches an insecure single node on this machine.
const localDSN = "postgresql://root@localhost:26257/defaultdb?sslmode=disable"

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

func main() {
	dsn := flag.String("dsn", cmp.Or(os.Getenv("DATABASE_URL"), localDSN), "CockroachDB connection string")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute connects to the database at dsn, migrates it with the CockroachDB backend and
// runs the example against it.
//
// Takes dsn (string) which is the connection string.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

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

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const depositSQL = `UPDATE accounts SET balance = balance + $1 WHERE id = $2;`

type DepositParams struct {
	Amount int64
	ID     int64
}

func (queries *Queries) Deposit(ctx context.Context, params DepositParams) error {
	_, err := queries.writer.ExecContext(ctx, depositSQL, params.Amount, params.ID)
	return err
}

const getBalanceSQL = `SELECT balance FROM accounts WHERE id = $1;`

type GetBalanceRow struct {
	Balance int64 `json:"balance"`
}

func (queries *Queries) GetBalance(ctx context.Context, id int64) (GetBalanceRow, error) {
	var row GetBalanceRow
	err := queries.reader.QueryRowContext(ctx, getBalanceSQL, id).Scan(&row.Balance)
	if err != nil {
		return GetBalanceRow{}, err
	}
	return row, nil
}

const listAccountsSQL = `SELECT id, owner, balance FROM accounts ORDER BY id;`

type ListAccountsRow struct {
	ID      int64  `json:"id"`
	Owner   string `json:"owner"`
	Balance int64  `json:"balance"`
}

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

const openAccountSQL = `INSERT INTO accounts (owner, balance) VALUES ($1, $2) RETURNING id;`

type OpenAccountParams struct {
	Owner   string
	Balance int64
}

type OpenAccountRow struct {
	ID int64 `json:"id"`
}

func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error) {
	var row OpenAccountRow
	err := queries.writer.QueryRowContext(ctx, openAccountSQL, params.Owner, params.Balance).Scan(&row.ID)
	if err != nil {
		return OpenAccountRow{}, err
	}
	return row, nil
}

const setBalanceSQL = `UPDATE accounts SET balance = $1 WHERE id = $2;`

type SetBalanceParams struct {
	Balance int64
	ID      int64
}

func (queries *Queries) SetBalance(ctx context.Context, params SetBalanceParams) error {
	_, err := queries.writer.ExecContext(ctx, setBalanceSQL, params.Balance, params.ID)
	return err
}

const withdrawSQL = `UPDATE accounts SET balance = balance - $1 WHERE id = $2;`

type WithdrawParams struct {
	Amount int64
	ID     int64
}

func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error {
	_, err := queries.writer.ExecContext(ctx, withdrawSQL, params.Amount, params.ID)
	return err
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Accounts struct {
	ID      int64  `json:"id"`
	Owner   string `json:"owner"`
	Balance int64  `json:"balance"`
}

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

opened accounts for Pip and Gull: committed
  balances: Pip 5000 Gull 2000
transferred 1500 from Pip to Gull: committed
  balances: Pip 3500 Gull 3500
transferred 9000 from Gull to Pip: rolled back (ERROR: failed to satisfy CHECK constraint (balance >= 0:::INT8) (SQLSTATE 23514))
  balances: Pip 3500 Gull 3500
paid Gull 10% interest while a deposit of 100 landed:
  attempt 1: aborted with SQLSTATE 40001, retried
  attempt 2: committed
  balances: Pip 3500 Gull 3960