Transactions

On SQLite:

• RunInTx and WithTx take a *sql.DB and a *sql.Tx from database/sql
• SQLite allows one writer at a time, so a transaction holds the write lock from its first write until it ends
• a CHECK constraint failure is the error that triggers the rollback here

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

Using it

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/023_transactions/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 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)
	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})
	})
}

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

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 (constraint failed: CHECK constraint failed: balance >= 0 (275))
  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.

Across engines

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

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

TimescaleDB runs the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with PostgreSQL, 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/sqlite && go run ./023_transactions

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

cd seagull/examples/sqlite && go test ./023_transactions

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

db/migrations/0001_accounts.up.sql

CREATE TABLE accounts (
    id      INTEGER 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 (?, ?) 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/sqlite/023_transactions/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 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)
	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})
	})
}

// 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 SQLite 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 .`, or `go run . -db bank.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/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 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 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 (constraint failed: CHECK constraint failed: balance >= 0 (275))
  balances: Pip 3500 Gull 3500