Every Go developer building a backend with a relational database eventually faces a classic engineering fork in the road: how do you test the data access layer (Repository / DAO) quickly, reliably, and without headaches?
The first path is using SQL mocks like the popular go-sqlmock library. Tests fly by in microseconds right in memory, but that convenience has a dark side. Mocks only verify that your code called a function with a specific query string. They don't know your DBMS's dialect, are blind to type-casting subtleties, and don't check foreign keys, triggers, composite unique indexes, or CTE behavior. As a result, your tests are green—and in production the service crashes with a SQL syntax error under the very first real load.
The second path is integration tests against a real database (PostgreSQL, MySQL, or SQLite running locally in Docker or via Testcontainers). Here the verification is 100% honest, but a painful test-isolation problem appears. To keep one test from affecting another's results, data must be cleaned up between runs. Yet calling TRUNCATE across dozens of related tables or recreating the schema via migrations turns the test suite into a sluggish monster: instead of seconds, test suites take minutes, killing engineers' motivation to run checks before committing.
The DATA-DOG team offered a fundamentally different and technically elegant solution to this dilemma by creating go-txdb. It is a single-transaction database driver that turns testing against a real DBMS into a fast, completely isolated process.
How txdb Works: Interception Magic at the database/sql Level
The secret to go-txdb's efficiency lies in the architecture of Go's standard database/sql/driver package. Rather than writing complex proxy servers or patching bytecode, the library implements Go's official driver interfaces (driver.Driver, driver.Conn, driver.Tx), acting as a transparent wrapper around your production driver—such as pgx, lib/pq, go-sql-driver/mysql, or modernc.org/sqlite.
When your test scenario requests a database connection, txdb intercepts control and executes the following sequence:
- Opens a real physical connection to the DBMS through the specified underlying driver;
- Immediately runs a
BEGINcommand, putting the session into an active transaction; - All subsequent business-logic operations—
Exec,Query,Preparestatements—execute strictly inside this single transaction; - The service layer works with the standard
*sql.DBinterface, completely unaware that it is inside a test sandbox; - When the test finishes and the connection closes (
db.Close()), txdb runsROLLBACK.
The database instantly rolls back every single created, modified, or deleted row to the state at the start of the test. In PostgreSQL, the rollback takes fractions of a millisecond, requiring neither disk cleanup nor system catalog resets.
Emulating Nested Transactions via Savepoints
You're probably thinking skeptically right now: "Wait—in real service code we open transactions ourselves with db.Begin() and call tx.Commit(). If code inside a test commits a transaction, the data will be persisted to disk and break isolation!"
That's a completely fair question, and it's also where the real beauty of txdb's implementation lies. The driver's authors taught it to virtualize nested transactions using Savepoints, supported by most modern DBMSs.
Look at what happens under the hood when your business code makes calls:
- When your service calls
tx, err := db.Begin(), the driver does not send a secondBEGINto the database (which would trigger an error or warning); instead it generates a unique savepoint:SAVEPOINT tx_1; - If the operation succeeds and the service calls
tx.Commit(), txdb translates that into the SQL commandRELEASE SAVEPOINT tx_1, confirming the intermediate result inside the outer scope; - If something fails in the logic and the code calls
tx.Rollback(), the driver rolls the database back to that exact checkpoint:ROLLBACK TO SAVEPOINT tx_1.
Meanwhile, the physical parent transaction stays open until the very end of the test, guaranteeing that not a single byte of data leaks into permanent database storage.
Practical Pattern: sql.OpenDB and t.Cleanup
Previously, txdb required registering in the global driver registry via txdb.Register. In modern Go (since version 1.10), the best practice is to use sql.OpenDB together with the txdb.New connector, completely eliminating global state:
package repository_test
import (
"context"
"database/sql"
"testing"
"time"
"github.com/DATA-DOG/go-txdb"
_ "github.com/jackc/pgx/v5/stdlib" // PostgreSQL pgx driver
)
const testDSN = "postgres://postgres:secret@localhost:5432/testdb?sslmode=disable"
// setupTestDB creates an isolated connection pool with guaranteed rollback
func setupTestDB(t *testing.T) *sql.DB {
t.Helper()
// Initialize the txdb connector on top of pgx
connector := txdb.New("pgx", testDSN)
db := sql.OpenDB(connector)
// Register mandatory transaction rollback when the test finishes
t.Cleanup(func() {
if err := db.Close(); err != nil {
t.Errorf("failed to close txdb connection: %v", err)
}
})
return db
}
func TestCreateAndFetchUser(t *testing.T) {
db := setupTestDB(t)
ctx := context.Background()
// Insert a row into real PostgreSQL
insertQuery := `INSERT INTO users (email, created_at) VALUES ($1, $2) RETURNING id`
var newID int
err := db.QueryRowContext(ctx, insertQuery, "developer@example.local", time.Now()).Scan(&newID)
if err != nil {
t.Fatalf("failed to create record: %v", err)
}
// Read data within the same transaction
var fetchedEmail string
selectQuery := `SELECT email FROM users WHERE id = $1`
err = db.QueryRowContext(ctx, selectQuery, newID).Scan(&fetchedEmail)
if err != nil {
t.Fatalf("failed to read user: %v", err)
}
if fetchedEmail != "developer@example.local" {
t.Errorf("expected email developer@example.local, got: %s", fetchedEmail)
}
// On completion, t.Cleanup calls db.Close() -> ROLLBACK.
// The users table in testdb remains crystal clean!
}
The second example shows how txdb transparently handles nested transaction calls in services:
func TestTransferBalanceWithNestedTransaction(t *testing.T) {
db := setupTestDB(t)
ctx := context.Background()
// Business logic opens a transaction (txdb turns it into SAVEPOINT tx_1)
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
if err != nil {
t.Fatalf("failed to open transaction: %v", err)
}
_, err = tx.ExecContext(ctx, `UPDATE accounts SET balance = balance - 100 WHERE id = 1`)
if err != nil {
_ = tx.Rollback()
t.Fatalf("failed to debit balance: %v", err)
}
// Commit the inner transaction (RELEASE SAVEPOINT tx_1)
if err := tx.Commit(); err != nil {
t.Fatalf("failed to commit intermediate transaction: %v", err)
}
}
Pitfalls and Applicability Boundaries
Despite its clear advantages, txdb has architectural boundaries you need to understand clearly:
- Non-transactional DDL: in DBMSs like MySQL or MariaDB, running DDL statements (
ALTER TABLE,DROP TABLE,CREATE TABLE) triggers an implicit commit of the current transaction. In PostgreSQL most DDL operations are transactional, though maintenance commands (VACUUM,CREATE DATABASE) cannot run inside transactions; - Concurrency within a single test: because the entire test is bound to one physical transaction, txdb serializes queries through a mutex. If your code under test spawns dozens of parallel goroutines actively writing to the database, they won't be able to run on separate independent pool connections.
Paired with modern practices like fuzz testing in Go, the txdb driver strikes an ideal balance: the fidelity of a real production database with microsecond test execution speed.

