Transaction Rollback Tests for Go Database Code That Is Fast

The slowest part of a database test is rarely the query. It is getting the database back to a known state: truncating tables, reloading fixtures, or starting a fresh container. A transaction that is never committed skips all of that. Open it at the top of the test, run the code inside it, and let Rollback throw the changes away. The next test begins with the database exactly as it was.

The technique is old and it works well, with one hard limit that decides whether you can use it at all. This article builds it on Go 1.25.6 against SQLite so the lab runs anywhere, then shows the limit with a test that fails to clean up after itself. The same code works unchanged on PostgreSQL and MySQL.

The shape of the store

package main

import (
	"context"
	"database/sql"
)

// DBTX is what both *sql.DB and *sql.Tx can do, so a store written against it
// runs inside a test transaction without knowing.
type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type Store struct{ db DBTX }

func NewStore(db DBTX) *Store { return &Store{db: db} }

func (s *Store) AddAccount(ctx context.Context, id int, name string) error {
	_, err := s.db.ExecContext(ctx, `INSERT INTO accounts (id, name) VALUES (?, ?)`, id, name)
	return err
}

func (s *Store) Count(ctx context.Context) (int, error) {
	var n int
	err := s.db.QueryRowContext(ctx, `SELECT count(*) FROM accounts`).Scan(&n)
	return n, err
}

// Archive opens its own transaction and commits it. A test transaction cannot wrap it.
func Archive(ctx context.Context, db *sql.DB, id int) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.ExecContext(ctx, `INSERT INTO archive (account_id) VALUES (?)`, id); err != nil {
		return err
	}
	return tx.Commit()
}

func main() {}

The only unusual line is the DBTX interface. *sql.DB and *sql.Tx both have ExecContext and QueryRowContext with identical signatures, so a store that depends on the two methods it uses accepts either one. Production code passes the pool, and a test passes a transaction. Nothing in AddAccount or Count knows which it got. This is the same idea as the small interface that makes mocking possible, pointed at a different goal: the point here is not to fake the database but to choose which real connection the real SQL runs on.

If your store already depends on a concrete *sql.DB, the change is mechanical. Replace the field type with an interface listing only the methods you call, and add methods as the compiler asks for them. Libraries follow the same convention: sqlc generates a DBTX interface exactly like this one, and pgx has an equivalent in its Tx and Pool types.

One transaction per test

package main

import (
	"context"
	"database/sql"
	"testing"

	_ "modernc.org/sqlite"
)

const schema = `
CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE archive (account_id INTEGER NOT NULL);`

var shared *sql.DB

func TestMain(m *testing.M) {
	var err error
	shared, err = sql.Open("sqlite", "file:shared?mode=memory&cache=shared")
	if err != nil {
		panic(err)
	}
	shared.SetMaxOpenConns(1)
	if _, err := shared.Exec(schema); err != nil {
		panic(err)
	}
	m.Run()
}

// begin starts a transaction that the test never commits.
func begin(t *testing.T) *sql.Tx {
	t.Helper()
	tx, err := shared.BeginTx(context.Background(), nil)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = tx.Rollback() })
	return tx
}

func TestAddAccountA(t *testing.T) {
	s := NewStore(begin(t))
	if err := s.AddAccount(t.Context(), 1, "ada"); err != nil {
		t.Fatal(err)
	}
	n, _ := s.Count(t.Context())
	t.Logf("inside the transaction: %d row", n)
}

func TestAddAccountB(t *testing.T) {
	s := NewStore(begin(t))
	// Same primary key as the test above: it only succeeds because that row was rolled back.
	if err := s.AddAccount(t.Context(), 1, "grace"); err != nil {
		t.Fatal(err)
	}
	n, _ := s.Count(t.Context())
	t.Logf("inside the transaction: %d row", n)
}

func TestNothingLeaked(t *testing.T) {
	n, err := NewStore(shared).Count(t.Context())
	if err != nil {
		t.Fatal(err)
	}
	t.Logf("rows left in the shared database: %d", n)
}

func TestSelfCommittingCodeLeaks(t *testing.T) {
	if err := Archive(t.Context(), shared, 7); err != nil {
		t.Fatal(err)
	}
	var n int
	if err := shared.QueryRow(`SELECT count(*) FROM archive`).Scan(&n); err != nil {
		t.Fatal(err)
	}
	t.Logf("archive rows after the test returns: %d (nothing rolls this back)", n)
}

func TestLeakIsVisibleToTheNextTest(t *testing.T) {
	var n int
	if err := shared.QueryRow(`SELECT count(*) FROM archive`).Scan(&n); err != nil {
		t.Fatal(err)
	}
	t.Logf("archive rows seen by a later test: %d", n)
}

TestMain opens one database and creates the schema once. Opening a database is the expensive step, so it happens a single time for the package rather than once per test, which is what TestMain is for. The schema is applied before m.Run, and nothing else touches it.

begin is the technique. It starts a transaction, registers Rollback with t.Cleanup, and returns the transaction. Because the cleanup runs when the test ends, whether the test passed, failed or called t.Fatal, no path forgets it. A test that wants the isolation writes s := NewStore(begin(t)) and carries on as normal. There is no teardown to write and none to forget. The ordering rules for cleanups are in the article on t.Cleanup.

The first two tests prove the isolation. Both insert an account with id = 1, which is a primary key. If the first test had committed its row, the second would fail with a constraint error. It does not, because the first test's transaction was rolled back during its cleanup.

go test -count=1 -v -run 'AddAccount|NothingLeaked|SelfCommitting|LeakIs' -shuffle=off . 2>&1 | grep -vE '^(=== RUN|=== PAUSE|=== CONT|PASS|ok)' | sed -E 's/ \([0-9.]+s\)//; s#^ +[a-z_]+\.go:[0-9]+: #    #'

    inside the transaction: 1 row
--- PASS: TestAddAccountA
    inside the transaction: 1 row
--- PASS: TestAddAccountB
    rows left in the shared database: 0
--- PASS: TestNothingLeaked
    archive rows after the test returns: 1 (nothing rolls this back)
--- PASS: TestSelfCommittingCodeLeaks
    archive rows seen by a later test: 1
--- PASS: TestLeakIsVisibleToTheNextTest

Read the log lines in order. Inside each test the count is 1: the row is visible to the transaction that wrote it. TestNothingLeaked then opens no transaction and counts rows on the shared database directly, and finds 0. That one assertion is the reason to trust the approach. Everything the first three tests wrote is gone, and the cost of getting there was a ROLLBACK per test, which is far cheaper than DELETE or TRUNCATE because the database discards the transaction's pending changes instead of reversing them statement by statement.

What a rollback cannot wrap

The last two tests are the limit. Archive is a perfectly ordinary function: it takes the pool, begins its own transaction, inserts a row and commits. A function written this way cannot be handed a test transaction, because it creates the transaction itself. The test calls it, the commit succeeds, and the row is permanent.

The log shows both consequences. After TestSelfCommittingCodeLeaks returns, the archive table has one row and nothing will remove it. TestLeakIsVisibleToTheNextTest reads that row from a different test, which is how an order dependent suite is born: it passes when run in order and fails under -shuffle or when run alone.

There are four ways out, and none of them is free.

• Let the caller own the transaction. Change Archive to accept a DBTX and do its work on that, with the commit decided one layer up. This is often better design anyway, because a function that begins its own transaction cannot be composed with another function inside a larger unit of work. It does not fit code whose job is to define the transaction boundary.
• Use savepoints. A function that starts a transaction can be given a wrapper whose Begin creates a SAVEPOINT inside the test's transaction, and whose Commit is a RELEASE SAVEPOINT and Rollback is a ROLLBACK TO SAVEPOINT. PostgreSQL, MySQL and SQLite all support them. The cost is a wrapper type that implements your Beginner interface and a small chance that your test now hides a bug in how real commits behave.
• Clean up explicitly. For the few tests that must commit, delete what they wrote in t.Cleanup, or truncate the tables they touched. Keep these in a clearly named group so the exception stays visible.
• Use a database per test. A template database or an in-memory database with a unique name, as in the SQLite in memory article, gives every test a private copy and makes the question of commits irrelevant. It is slower to set up and the right tool when isolation matters more than the last millisecond.

Pick one for the tests that need it and leave the rest on the rollback. A suite where ninety percent of tests use a plain transaction and ten percent use a private database is common and reasonable.

Concurrency and the pool

SQLite and the single connection pool in this lab hide a trap that bites on larger databases. A transaction pins one connection until it ends. With SetMaxOpenConns(1), a test that holds a transaction and then queries the shared *sql.DB would wait forever for a connection that its own transaction is using. In the lab this is the reason TestNothingLeaked does not open a transaction at all.

With a bigger pool the deadlock goes away, but a different problem takes its place: a second connection does not see the first transaction's uncommitted rows. A test that writes through the transaction and then reads through the pool will find nothing, and it will look like the insert failed. The rule is that everything a single test does goes through the same DBTX value. If a helper builds its own connection, it is outside the test.

Parallel tests are another consideration. Each parallel test gets its own transaction, and the database isolates them from each other at its default level. That is good for correctness and bad for any test that locks the same row, because the second test blocks until the first rolls back. If two parallel tests insert the same unique key, PostgreSQL will make the second wait for the first's outcome rather than fail immediately. Give parallel tests different keys, or accept that they serialise. The parallel test caveats are covered in the t.Parallel article.

What a rolled back test does not exercise

Be honest about what the green run proves. A transaction that never commits never runs the commit path, so these are invisible to it:

• Deferred constraints. PostgreSQL checks DEFERRABLE INITIALLY DEFERRED constraints at commit time, so a violation surfaces only when the transaction ends, which a test that rolls back never does. Run SET CONSTRAINTS ALL IMMEDIATE at the start of the test, or call the check yourself.
• Commit time errors in general: serialization failures under stricter isolation levels are raised at commit in several databases.
• Sequences and auto increment counters. A rollback does not rewind them, so a test that asserts id = 1 passes the first time and fails in a database that was used before. Assert on the returned ID instead, or reset the sequence deliberately.
• Triggers and NOTIFY messages that fire on commit.

The SQLite documentation on transactions and the PostgreSQL documentation on transaction isolation describe each of these. The Go side is in the database/sql package documentation.

When to choose it

Use rollback tests for the large body of repository tests that read and write through one store and never manage their own transactions: inserts, lookups, filters, joins, uniqueness. They are fast enough that the suite can run on every save, and they need no cleanup code. Keep a smaller set of tests that commit for real, against the production database engine, to cover the transaction boundaries, and run those with a database per test. The first PostgreSQL version of that setup is in testing against a real Postgres, and the container that supplies it is in testcontainers.