Transactions

On PostgreSQL:

• RunInTx and WithTx take a *sql.DB and a *sql.Tx from database/sql, opened with pgx's stdlib driver
• the CHECK constraint failure is a *pgconn.PgError; the example matches its SQLSTATE, 23514, as the message wording varies between servers
• PostgreSQL takes row locks as each UPDATE runs, so other transactions can write to other accounts meanwhile
• :amount and :id named placeholders become $1 and $2 in the generated SQL; without a name, the first would be inferred as balance
• go.interface generates store.Querier, which printBalances takes, so one function reads through the database's queries and the transaction's alike

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      BIGINT  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    owner   TEXT    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)
UPDATE accounts SET balance = balance - :amount WHERE id = :id;

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 int32
	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.

The "With pgx" section below has the same bank on go.driver: pgx. There, RunInTx takes any DBTX that can Begin, such as a *pgxpool.Pool. Given a pgx.Tx, Begin starts a savepoint, so the transfer nests inside the outer transaction.

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.

Using it

example.go

package main

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

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

// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
// does.
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.
//
// sgl.yaml sets go.interface, so the generated package also declares store.Querier, an
// interface of every query method, which *store.Queries implements whether it runs on the
// database or inside a transaction. printBalances takes a store.Querier, so the same
// function reads the committed balances and, inside each transfer, the balances the
// transaction sees before it commits or rolls back; a test could pass it a fake instead.
//
// 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, "balances"); err != nil {
		return err
	}

	if err := transfer(ctx, database, queries, output, 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, "balances"); 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, output, gull, pip, 9000)
	if err == nil {
		return errors.New("an overdrawing transfer committed")
	}

	// pgx reports a server error as a *pgconn.PgError, whose SQLState method gives the
	// standard five-character code: 23514 for a failed CHECK constraint. Match on the code,
	// not the message, as the wording differs between servers and versions.
	failure, hasState := errors.AsType[sqlStateError](err)
	if !hasState {
		return fmt.Errorf("an overdrawing transfer failed without a SQLSTATE: %w", err)
	}
	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (SQLSTATE %s)\n", failure.SQLState())
	return printBalances(ctx, queries, output, "balances")
}

// 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. After the credit it prints the balances the transaction sees, through
// the same printBalances the caller uses.
//
// Takes database (*sql.DB) which begins the transaction.
// Takes queries (*store.Queries) which run the transfer.
// Takes output (io.Writer) which receives the balances inside the transaction.
// Takes from (int64) which is the account to debit.
// Takes to (int64) which is the account to credit.
// Takes amount (int32) 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, output io.Writer, from, to int64, amount int32) 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
		}
		if err := printBalances(ctx, inTransaction, output, "inside the transaction"); err != nil {
			return err
		}
		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
	})
}

// printBalances writes every account's balance on one line. It takes the generated
// store.Querier rather than *store.Queries, so it reads through whatever it is given: the
// queries on the database, the queries bound to a transaction, or a fake in a test.
//
// Takes queries (store.Querier) which read the accounts.
// Takes output (io.Writer) which receives the line.
// Takes label (string) which says whose view of the balances the line shows.
//
// Returns error when the accounts cannot be listed.
func printBalances(ctx context.Context, queries store.Querier, output io.Writer, label string) error {
	accounts, err := queries.ListAccounts(ctx)
	if err != nil {
		return fmt.Errorf("listing accounts: %w", err)
	}
	fmt.Fprintf(output, "  %s:", label)
	for _, account := range accounts {
		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
	}
	fmt.Fprintln(output)
	return nil
}

What it prints

pgx reports the failed CHECK as a *pgconn.PgError, and the program matches its SQLSTATE, 23514, rather than its message, whose wording differs between servers and versions. The balances after it are the same as before it: the credit to Pip was undone.

opened accounts for Pip and Gull: committed
  balances: Pip 5000 Gull 2000
  inside the transaction: Pip 5000 Gull 3500
transferred 1500 from Pip to Gull: committed
  balances: Pip 3500 Gull 3500
  inside the transaction: Pip 12500 Gull 3500
transferred 9000 from Gull to Pip: rolled back (SQLSTATE 23514)
  balances: Pip 3500 Gull 3500

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.

With pgx

RunInTx begins a pgx transaction on the pool, commits a transfer when its function returns nil and rolls it back on an error; WithTx binds the queries to a pgx.Tx you begin yourself.

What changes:

• RunInTx takes any store.DBTX that can Begin, such as the *pgxpool.Pool; on a pgx.Tx, Begin starts a savepoint
• WithTx takes a pgx.Tx, and Commit and Rollback take a context
• the CHECK constraint failure is a *pgconn.PgError; the example matches its SQLSTATE, 23514, as the message wording varies between servers
• :amount and :id named placeholders become $1 and $2 in the generated SQL; without a name, the first would be inferred as balance

db/queries/accounts.sql

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

-- sgl.query(Deposit, exec)
UPDATE accounts SET balance = balance + :amount WHERE id = :id;

-- sgl.query(Withdraw, exec)
UPDATE accounts SET balance = balance - :amount WHERE id = :id;

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

internal/store

type WithdrawParams struct {
	Amount int32
	ID     int64
}

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

example.go

package main

import (
	"context"
	"errors"
	"fmt"
	"io"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	"seagull.sh/seagull/examples/postgres/023_transactions_pgx/internal/store"
)

// 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.
//
// Takes pool (*pgxpool.Pool) which is a pool on 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, pool *pgxpool.Pool, output io.Writer) error {
	queries := store.New(pool)

	pip, gull, err := openAccounts(ctx, pool, 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, pool, 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, pool, queries, gull, pip, 9000)
	if err == nil {
		return errors.New("an overdrawing transfer committed")
	}

	// pgx reports a server error as a *pgconn.PgError. Its Code is the standard
	// five-character SQLSTATE: 23514 for a failed CHECK constraint. Match on the code, not
	// the message, as the wording differs between servers and versions.
	failure, isServerError := errors.AsType[*pgconn.PgError](err)
	if !isServerError {
		return fmt.Errorf("an overdrawing transfer failed without a SQLSTATE: %w", err)
	}
	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (SQLSTATE %s)\n", failure.Code)
	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 pgx.Tx, which suits a transaction begun
// with pgx.TxOptions, such as a serialisable one, or shared with code that does not use
// Seagull.
//
// Takes pool (*pgxpool.Pool) 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, pool *pgxpool.Pool, queries *store.Queries) (pipID, gullID int64, err error) {
	transaction, err := pool.Begin(ctx)
	if err != nil {
		return 0, 0, err
	}
	// Rollback after a successful Commit does nothing, so deferring it covers every early
	// return. pgx's Rollback takes a context.
	defer func() { _ = transaction.Rollback(ctx) }()

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

// transfer moves an amount between two accounts with RunInTx, which calls Begin on pool,
// passes fn a Queries bound to the transaction, and commits when fn returns nil or rolls
// back when it returns an error. pool is a store.DBTX, so a pgx.Tx works too: Begin on a
// pgx.Tx starts a savepoint, and the transfer nests inside it.
//
// Takes pool (*pgxpool.Pool) 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 (int32) 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, pool *pgxpool.Pool, queries *store.Queries, from, to int64, amount int32) error {
	return queries.RunInTx(ctx, pool, 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})
	})
}

// 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
}

Across engines

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

	PostgreSQL	SQLite	CockroachDB	MySQL	DuckDB
amount parameter	int32	int64	int64	int32	int32

TimescaleDB runs the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

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

Where the example is missing:

• Not on ClickHouse. ClickHouse has no transactions.

Run it locally

From the directory you cloned the Seagull repository into:

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

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

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

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

db/migrations/0001_accounts.up.sql

CREATE TABLE accounts (
    id      BIGINT  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    owner   TEXT    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)
UPDATE accounts SET balance = balance + :amount WHERE id = :id;

-- sgl.query(Withdraw, exec)
UPDATE accounts SET balance = balance - :amount WHERE id = :id;

-- 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/postgres/023_transactions/internal/store"
)

// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
// does.
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.
//
// sgl.yaml sets go.interface, so the generated package also declares store.Querier, an
// interface of every query method, which *store.Queries implements whether it runs on the
// database or inside a transaction. printBalances takes a store.Querier, so the same
// function reads the committed balances and, inside each transfer, the balances the
// transaction sees before it commits or rolls back; a test could pass it a fake instead.
//
// 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, "balances"); err != nil {
		return err
	}

	if err := transfer(ctx, database, queries, output, 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, "balances"); 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, output, gull, pip, 9000)
	if err == nil {
		return errors.New("an overdrawing transfer committed")
	}

	// pgx reports a server error as a *pgconn.PgError, whose SQLState method gives the
	// standard five-character code: 23514 for a failed CHECK constraint. Match on the code,
	// not the message, as the wording differs between servers and versions.
	failure, hasState := errors.AsType[sqlStateError](err)
	if !hasState {
		return fmt.Errorf("an overdrawing transfer failed without a SQLSTATE: %w", err)
	}
	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (SQLSTATE %s)\n", failure.SQLState())
	return printBalances(ctx, queries, output, "balances")
}

// 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. After the credit it prints the balances the transaction sees, through
// the same printBalances the caller uses.
//
// Takes database (*sql.DB) which begins the transaction.
// Takes queries (*store.Queries) which run the transfer.
// Takes output (io.Writer) which receives the balances inside the transaction.
// Takes from (int64) which is the account to debit.
// Takes to (int64) which is the account to credit.
// Takes amount (int32) 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, output io.Writer, from, to int64, amount int32) 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
		}
		if err := printBalances(ctx, inTransaction, output, "inside the transaction"); err != nil {
			return err
		}
		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
	})
}

// printBalances writes every account's balance on one line. It takes the generated
// store.Querier rather than *store.Queries, so it reads through whatever it is given: the
// queries on the database, the queries bound to a transaction, or a fake in a test.
//
// Takes queries (store.Querier) which read the accounts.
// Takes output (io.Writer) which receives the line.
// Takes label (string) which says whose view of the balances the line shows.
//
// Returns error when the accounts cannot be listed.
func printBalances(ctx context.Context, queries store.Querier, output io.Writer, label string) error {
	accounts, err := queries.ListAccounts(ctx)
	if err != nil {
		return fmt.Errorf("listing accounts: %w", err)
	}
	fmt.Fprintf(output, "  %s:", label)
	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 PostgreSQL database, then runs the
// example in example.go: two accounts are opened in one transaction, and two transfers
// run with RunInTx, one committing and one rolled back by a CHECK constraint.
//
// 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/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 int32
	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 listAccountsSQL = `SELECT id, owner, balance FROM accounts ORDER BY id;`

type ListAccountsRow struct {
	ID      int64  `json:"id"`
	Owner   string `json:"owner"`
	Balance int32  `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 int32
}

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 withdrawSQL = `UPDATE accounts SET balance = balance - $1 WHERE id = $2;`

type WithdrawParams struct {
	Amount int32
	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 int32  `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()
}

internal/store/querier_interface.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
)

// Querier lists the method of every query, which Queries implements, so code can depend
// on the methods it calls and a test can stand in for the database.
type Querier interface {
	Deposit(ctx context.Context, params DepositParams) error
	ListAccounts(ctx context.Context) ([]ListAccountsRow, error)
	OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error)
	Withdraw(ctx context.Context, params WithdrawParams) error
}

var _ Querier = (*Queries)(nil)

output.txt

opened accounts for Pip and Gull: committed
  balances: Pip 5000 Gull 2000
  inside the transaction: Pip 5000 Gull 3500
transferred 1500 from Pip to Gull: committed
  balances: Pip 3500 Gull 3500
  inside the transaction: Pip 12500 Gull 3500
transferred 9000 from Gull to Pip: rolled back (SQLSTATE 23514)
  balances: Pip 3500 Gull 3500