sqlc Transactions and the WithTx Pattern in Go Services

The reason sqlc code works with transactions at all is one small interface. Every generated Queries value wraps a DBTX, which is nothing more than the three methods the queries call. A connection pool has them, a single connection has them, and a transaction has them. So "run these queries in a transaction" is not a feature of sqlc: it is New(tx) instead of New(pool).

This article builds the pattern most services settle on, a helper that runs a function in a transaction, and then breaks it six ways to show what each failure leaves behind. The checks run on PostgreSQL 18.6 with sqlc v1.30.0 and pgx v5.11.0, on Go 1.25.6. It builds on the sqlc tutorial and sits next to the pgx transactions article and the unit of work pattern, both of which cover the same idea without code generation.

The setup

A two row accounts table with a rule that a balance cannot go negative:

create table accounts (
    id      int primary key,
    balance int not null check (balance >= 0)
);
insert into accounts values (1, 100), (2, 50);

Three queries: debit, credit and list. Credit is :execrows, so it can report that the target account did not exist:

-- name: Debit :exec
update accounts set balance = balance - $2 where id = $1;

-- name: Credit :execrows
update accounts set balance = balance + $2 where id = $1;

-- name: Balances :many
select * from accounts order by id;

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5

The helper

package main

import (
	"context"
	"errors"

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

var ErrNoAccount = errors.New("no such account")

// inTx runs fn in a transaction and rolls back on any error or panic.
func inTx(ctx context.Context, pool *pgxpool.Pool, fn func(*Queries) error) (err error) {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback(ctx)
			panic(p)
		}
		if err != nil {
			_ = tx.Rollback(ctx)
		}
	}()
	if err = fn(New(tx)); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

func transfer(ctx context.Context, pool *pgxpool.Pool, from, to, amount int32) error {
	return inTx(ctx, pool, func(q *Queries) error {
		if err := q.Debit(ctx, DebitParams{ID: from, Balance: amount}); err != nil {
			return err
		}
		n, err := q.Credit(ctx, CreditParams{ID: to, Balance: amount})
		if err != nil {
			return err
		}
		if n == 0 {
			return ErrNoAccount
		}
		return nil
	})
}

var _ = pgx.ErrNoRows

inTx begins a transaction on the pool, builds a Queries over it with New(tx), and hands that to the caller's function. The deferred function does the cleanup that must never be forgotten: on a panic it rolls back and re-panics so the crash is still visible, and on a returned error it rolls back. If the function returns nil, Commit is the last call and its error is returned, since a commit can fail (a deferred constraint, a serialisation failure, a lost connection).

Two details are worth copying. The rollback uses the same context as the rest, but ignores its error, because the first error is the one the caller needs and a failed rollback after a failed statement is noise. And Queries.WithTx(tx), which sqlc also generates, does the same thing as New(tx) on an existing value. Use it when you already hold a *Queries with other state, and New when you have only the transaction.

The transfer function shows how a business operation reads once the helper exists. Debit, credit, and an explicit check that the credit touched a row. Returning an error from the callback is the whole rollback API.

What each failure leaves behind

package main

import (
	"context"
	"fmt"
	"log"

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

const dsn = "postgres://postgres:[email protected]:55432/sqlc0693?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func show(ctx context.Context, q *Queries, label string) {
	b, err := q.Balances(ctx)
	must(err)
	fmt.Printf("   %-34s %v\n", label, b)
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)
	show(ctx, q, "start")

	// 1. Without a transaction every call commits alone: the debit stays when the credit finds no account.
	must(q.Debit(ctx, DebitParams{ID: 1, Balance: 30}))
	n, _ := q.Credit(ctx, CreditParams{ID: 99, Balance: 30})
	fmt.Println("1. no transaction: credit to account 99 touched", n, "rows")
	show(ctx, q, "30 has vanished:")

	// 2. The same steps in one transaction.
	err = transfer(ctx, pool, 1, 99, 30)
	fmt.Println("2. in a transaction: error =", err)
	show(ctx, q, "balances unchanged:")

	// 3. A constraint failure aborts it as well.
	err = transfer(ctx, pool, 2, 1, 500)
	fmt.Println("3. overdraft:", err)
	show(ctx, q, "unchanged:")

	// 4. Success.
	fmt.Println("4. transfer of 20 from 2 to 1: error =", transfer(ctx, pool, 2, 1, 20))
	show(ctx, q, "after:")

	// 5. WithTx on a hand-managed transaction, and what happens to a query after Rollback.
	tx, err := pool.Begin(ctx)
	must(err)
	qt := q.WithTx(tx)
	must(qt.Debit(ctx, DebitParams{ID: 1, Balance: 10}))
	must(tx.Rollback(ctx))
	_, err = qt.Balances(ctx)
	fmt.Println("5. query on a finished transaction:", err, "| is pgx.ErrTxClosed:", err == pgx.ErrTxClosed)
	show(ctx, q, "the rolled back debit is gone:")

	// 6. A panic inside the callback still rolls back.
	func() {
		defer func() { fmt.Println("6. recovered panic:", recover()) }()
		_ = inTx(ctx, pool, func(q *Queries) error {
			must(q.Debit(ctx, DebitParams{ID: 1, Balance: 5}))
			panic("boom")
		})
	}()
	show(ctx, q, "balances after the panic:")
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0693
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

   start                              [{1 100} {2 50}]
1. no transaction: credit to account 99 touched 0 rows
   30 has vanished:                   [{1 70} {2 50}]
2. in a transaction: error = no such account
   balances unchanged:                [{1 70} {2 50}]
3. overdraft: ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check" (SQLSTATE 23514)
   unchanged:                         [{1 70} {2 50}]
4. transfer of 20 from 2 to 1: error = <nil>
   after:                             [{1 90} {2 30}]
5. query on a finished transaction: tx is closed | is pgx.ErrTxClosed: true
   the rolled back debit is gone:     [{1 90} {2 30}]
6. recovered panic: boom
   balances after the panic:          [{1 90} {2 30}]

1. No transaction, money disappears. The program debited account 1 by 30, then tried to credit account 99, which does not exist. The credit is :execrows, so it returned 0 and no error: an update that matches nothing is a success in SQL. The debit had already committed on its own connection, so the balances read [{1 70} {2 50}]. Thirty units were gone. This is the case the rest of the article exists to prevent, and notice that nothing failed. No error, no log line, a wrong ledger.

2. The same steps in a transaction. transfer(1, 99, 30) ran the debit, saw the credit touch 0 rows, and returned ErrNoAccount. The helper rolled back, and the balances stayed [{1 70} {2 50}]. The explicit check on the row count matters: without it the transaction would have committed the lost money exactly as scenario one did, only now inside a transaction. A transaction makes the steps atomic, and it cannot know that one of them did nothing.

3. A constraint stops it. Transferring 500 from account 2 (balance 50) violated accounts_balance_check (SQLSTATE 23514) on the debit. The statement failed, the error came back through the callback, and the helper rolled back. Both balances were unchanged. Notice where the rule lives: in the database, not in Go. That is the right place for an invariant, since every writer is subject to it. The pgx error codes article shows how to turn a code like 23514 into a typed error.

4. A good transfer commits. Moving 20 from account 2 to account 1 returned nil and the balances became [{1 90} {2 30}].

5. Using a finished transaction. The program began a transaction by hand, built qt := q.WithTx(tx), debited, and rolled back. A later call through qt returned tx is closed, equal to pgx.ErrTxClosed. The rolled back debit did not appear in the balances. That error is how a bug of this kind shows itself: a Queries captured in a closure that outlives its transaction. Do not store a transaction's Queries anywhere that lives longer than the callback.

6. A panic. The callback debited 5 and then panicked. The deferred function rolled back and re-raised the panic, the program's own recover printed boom, and the balances were unchanged at [{1 90} {2 30}]. A panic during a request is rare and still real (a nil map, an index out of range), and a transaction that stays open after one holds its locks until the connection is reaped.

Patterns that pay off

Keep the transaction short. A transaction holds locks and a connection. Do the network calls (an HTTP request, a queue publish) before it begins or after it ends, not inside. The outbox pattern is the answer when a write and a message must succeed together.

Take locks in a consistent order. Two transfers in opposite directions between the same accounts can deadlock if each locks its own source first. Sort the ids, or lock with select ... for update in id order, as the SELECT FOR UPDATE article covers.

Choose the isolation level where it matters. pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable}) gives the helper stronger guarantees at the cost of retries on serialisation failures. Add a retry loop around inTx for the error code 40001, and nothing else.

Return the business error, not the database error. transfer returns ErrNoAccount, a sentinel the HTTP layer can map to 404, and lets the constraint error pass through for the layer above to classify.

Test it against a real database. A fake DBTX cannot tell you that the rollback worked, only that the code called Rollback. The testing article sets up a database per test.

Mistakes seen in practice

• A helper that commits when the callback returned an error because a deferred function ignored the named return.
• A defer tx.Rollback(ctx) placed after the Commit call path, so the happy path is rolled back once and commits once. The pgx rollback after a commit is a harmless no-op, but write it deliberately.
• Reading with the pool inside a transaction callback (q instead of the callback's Queries), which runs the read on another connection, outside the transaction, and does not see its own writes. The lab's scenarios use the callback's argument for that reason.
• Using :batchexec inside a transaction and expecting each statement to commit alone: they all belong to the transaction.

The DBTX interface and WithTx are described in sqlc's transactions how-to, and the transaction API in the pgx Tx documentation and PostgreSQL's transaction isolation chapter.