Go Database & Transactions — A Deep, Author-Level Guide

Everything a senior/staff Go engineer should know about database/sql, connection pooling, transaction semantics, isolation levels, error handling, retries, distributed transactions, and testing.

The database/sql Mental Model
Connections & Connection Pooling
Basic Queries: Query, QueryRow, Exec
Prepared Statements
Transactions: Fundamentals
Context & Transaction Lifecycle
Isolation Levels In Depth
Locking, Deadlocks & Retry Strategies
Savepoints & Nested Transactions
Transaction Design Patterns
Distributed Transactions & the Saga Pattern
Error Handling Deep Dive
ORMs & Query Builders: sqlx, GORM, ent, sqlc
Testing Transactions
Performance & Observability
Common Pitfalls Checklist
Production-Grade Reference Implementation

1. The `database/sql` Mental Model

database/sql is not a driver. It’s an abstraction layer that wraps a driver implementing the driver.Driver interface (e.g., pgx, go-sql-driver/mysql, mattn/go-sqlite3). Understanding this separation is critical:

import (
    "database/sql"
    _ "github.com/lib/pq" // driver registers itself via init()
)

db, err := sql.Open("postgres", dsn)

Key insight: sql.Open does not connect to the database. It merely validates the DSN format and prepares a *sql.DB struct. The first actual connection happens lazily, on the first query, or explicitly via db.Ping().

*sql.DB is not a single connection — it’s a connection pool. This is the single most misunderstood fact about Go’s database layer. Every Query, Exec, or QueryRow call may use a different underlying connection unless you explicitly pin one via sql.Tx or sql.Conn.

if err := db.Ping(); err != nil {
    log.Fatalf("cannot reach database: %v", err)
}

Always call Ping (or PingContext) at startup to fail fast if the database is unreachable — don’t let a broken DSN surface as a mysterious runtime error three requests in.

2. Connections & Connection Pooling

*sql.DB exposes four pool-tuning knobs. Getting these wrong is one of the most common causes of production incidents (connection exhaustion, “too many connections” errors, or leaking sockets).

db.SetMaxOpenConns(25)                  // hard cap on total connections (idle + in-use)
db.SetMaxIdleConns(25)                  // connections kept alive in the pool when idle
db.SetConnMaxLifetime(5 * time.Minute)  // force-recycle connections periodically
db.SetConnMaxIdleTime(2 * time.Minute)  // close idle connections after this duration

Why each setting matters

SetMaxOpenConns: Without a cap, a traffic spike can open thousands of connections, exhausting the database’s max_connections (often 100–500 by default in Postgres/MySQL) and taking down the DB for every other service sharing it. Rule of thumb: MaxOpenConns per instance × number of app instances should stay comfortably under the DB’s connection limit.
SetMaxIdleConns: If this is lower than MaxOpenConns, Go will aggressively close and reopen connections under bursty load, adding TCP/TLS handshake latency to your p99. Generally set MaxIdleConns == MaxOpenConns unless you have a strong reason (e.g., cost-sensitive serverless environments where idle connections are billed).
SetConnMaxLifetime: Protects against stale connections behind load balancers/proxies (e.g., PgBouncer, AWS RDS Proxy, cloud NAT timeouts) that silently drop long-lived TCP connections. A value of 3–30 minutes is typical. Without this, you’ll intermittently see driver: bad connection errors that are hard to reproduce.
SetConnMaxIdleTime (Go 1.15+): Closes idle connections that have been sitting unused, freeing DB-side resources without impacting active traffic.

Diagnosing pool health

stats := db.Stats()
fmt.Printf("Open: %d, InUse: %d, Idle: %d, WaitCount: %d, WaitDuration: %s\n",
    stats.OpenConnections, stats.InUse, stats.Idle,
    stats.WaitCount, stats.WaitDuration)

A rising WaitCount/WaitDuration under load is the canonical signal that MaxOpenConns is too low for your throughput — export this to Prometheus/Datadog as a standard SRE dashboard metric.

Sizing formula (rule of thumb, from PostgreSQL’s own guidance)

connections = ((core_count * 2) + effective_spindle_count)

For SSD-backed cloud databases, a common starting point is max_open_conns = 2x CPU cores per application replica, tuned empirically under load testing — never guessed.

3. Basic Queries: Query, QueryRow, Exec

// Exec: for INSERT/UPDATE/DELETE/DDL — no rows returned
res, err := db.ExecContext(ctx, `UPDATE accounts SET balance = balance - $1 WHERE id = $2`, amount, id)
if err != nil {
    return fmt.Errorf("exec: %w", err)
}
rows, _ := res.RowsAffected()

// QueryRow: exactly one row expected
var balance int64
err = db.QueryRowContext(ctx, `SELECT balance FROM accounts WHERE id = $1`, id).Scan(&balance)
if errors.Is(err, sql.ErrNoRows) {
    return ErrAccountNotFound
}

// Query: multiple rows — MUST close rows, always
rows, err := db.QueryContext(ctx, `SELECT id, balance FROM accounts WHERE owner = $1`, owner)
if err != nil {
    return err
}
defer rows.Close() // critical — leaking rows leaks the underlying connection

for rows.Next() {
    var id int64
    var balance int64
    if err := rows.Scan(&id, &balance); err != nil {
        return err
    }
    // process
}
if err := rows.Err(); err != nil { // check iteration error — often forgotten!
    return err
}

Critical rule: Every *sql.Rows must be closed, and every loop must check rows.Err() after the loop. Failing to call rows.Close() (even after breaking early from the loop) leaks the connection back to the pool in a bad state — it will never be reused, silently shrinking your effective pool size until you hit MaxOpenConns and everything blocks.

Always use the *Context variants (QueryContext, ExecContext, QueryRowContext, BeginTx) in production code — never the context-less legacy variants. This lets query cancellation propagate correctly (e.g., when an HTTP request is cancelled by the client).

4. Prepared Statements

stmt, err := db.PrepareContext(ctx, `INSERT INTO logs (msg, ts) VALUES ($1, $2)`)
if err != nil {
    return err
}
defer stmt.Close()

for _, entry := range entries {
    if _, err := stmt.ExecContext(ctx, entry.Msg, entry.Time); err != nil {
        return err
    }
}

Subtlety: Because *sql.DB is a pool, a *sql.Stmt prepared on db is not bound to a single physical connection. Under the hood, Go re-prepares the statement transparently on whichever connection it ends up using, and caches per-connection prepared statements internally. This is convenient but means:

Prepared statements from *sql.DB are safe for concurrent use.
Preparing a statement inside a transaction (tx.PrepareContext) is bound to that transaction’s single connection — this is the correct approach for hot loops inside a transaction (e.g., bulk inserts).
Most modern drivers (pgx, go-sql-driver/mysql) already do statement caching internally, so manual Prepare for one-off queries often isn’t worth the complexity — reserve it for genuinely hot paths (thousands of executions per request, like batch imports).

5. Transactions: Fundamentals

A transaction groups multiple statements into a single atomic unit — either all succeed (commit) or none do (rollback), satisfying the classic ACID properties:

Atomicity — all-or-nothing execution.
Consistency — the DB moves from one valid state to another (constraints, triggers, foreign keys enforced).
Isolation — concurrent transactions don’t see each other’s uncommitted changes (degree depends on isolation level — see §7).
Durability — once committed, changes survive crashes (via WAL/redo logs).

The canonical Go pattern

func TransferFunds(ctx context.Context, db *sql.DB, fromID, toID int64, amount int64) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelReadCommitted, // explicit is better than implicit
        ReadOnly:  false,
    })
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    // The defer + rollback-after-commit-is-a-noop pattern is the idiomatic
    // way to guarantee cleanup on every exit path, including panics.
    defer func() {
        _ = tx.Rollback() // returns sql.ErrTxDone if already committed — safely ignored
    }()

    if _, err := tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1`,
        amount, fromID); err != nil {
        return fmt.Errorf("debit: %w", err)
    }

    res, err := tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1`,
        amount, fromID)
    if err != nil {
        return fmt.Errorf("debit: %w", err)
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrInsufficientFunds // business rule violated — bail before credit
    }

    if _, err := tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance + $1 WHERE id = $2`,
        amount, toID); err != nil {
        return fmt.Errorf("credit: %w", err)
    }

    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit: %w", err)
    }
    return nil
}

Why `defer tx.Rollback()` is always safe

Once tx.Commit() succeeds, the transaction is done. Calling tx.Rollback() afterward returns sql.ErrTxDone, which the deferred call simply discards. This pattern guarantees that any early return, error, or panic results in a rollback — you never have to remember to roll back manually on every branch.

Never mix `db.Query` and `tx.Query` in the same logical operation

Once you call db.BeginTx, all statements that must participate in that transaction must be called on the *sql.Tx object, not on *sql.DB. Calling db.Exec mid-transaction runs on a different pooled connection, entirely outside the transaction — a classic, hard-to-spot bug.

6. Context & Transaction Lifecycle

Transactions hold a database connection checked out of the pool for their entire lifetime. This has real consequences:

ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
defer cancel()

tx, err := db.BeginTx(ctx, nil)

If ctx is cancelled (client disconnects, timeout fires) while the transaction is open, Go’s database/sql automatically rolls back the transaction and returns the connection to the pool — you do not need to manually watch for cancellation. However:

Never hold a transaction open across a network call to another service, a slow external API, or user input. A transaction that spans an HTTP call to a third party will hold a connection (and often row locks) for the duration of that call — a classic way to exhaust your connection pool and cause cascading outages under load.
Keep transactions short-lived: fetch what you need, compute in memory, write in a tight transaction.
If you must coordinate a DB write with an external call, prefer the outbox pattern (write an event to an outbox table in the same transaction, then have a separate worker publish it asynchronously) over holding the transaction open.

7. Isolation Levels In Depth

sql.TxOptions.Isolation lets you request an isolation level, but the driver/DB may only support a subset, and unsupported levels usually silently upgrade to the nearest stricter one (behavior is driver-specific — always verify against your DB’s docs).

const (
    sql.LevelDefault
    sql.LevelReadUncommitted
    sql.LevelReadCommitted
    sql.LevelWriteCommitted
    sql.LevelRepeatableRead
    sql.LevelSnapshot
    sql.LevelSerializable
    sql.LevelLinearizable
)

The three classic anomalies

Anomaly	Description
Dirty Read	Reading another transaction’s uncommitted changes.
Non-Repeatable Read	Re-reading the same row within a transaction yields different values because another transaction committed a change in between.
Phantom Read	Re-running the same range query yields a different set of rows because another transaction inserted/deleted matching rows.

Isolation level matrix

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible (varies by DB)
Serializable	Prevented	Prevented	Prevented

PostgreSQL specifics

PostgreSQL doesn’t implement true “Read Uncommitted” — it silently behaves as Read Committed (dirty reads are never possible in Postgres, by design of MVCC).
Postgres’s Repeatable Read actually prevents phantom reads too (it’s closer to “Snapshot Isolation”), stricter than the SQL standard requires.
Postgres’s Serializable uses Serializable Snapshot Isolation (SSI), which detects dangerous read/write dependency cycles and aborts one transaction with a 40001 serialization failure error — your application must be prepared to retry on this error code.

tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
// ... do work ...
err = tx.Commit()
var pgErr *pq.Error
if errors.As(err, &pgErr) && pgErr.Code == "40001" {
    // serialization_failure — retry the whole transaction from scratch
}

MySQL/InnoDB specifics

Default isolation is Repeatable Read, which (unlike the SQL standard) does prevent phantom reads for locking reads via next-key locking — but plain non-locking SELECTs can still see phantoms across statements within the same transaction in some edge cases.
MySQL’s SELECT ... FOR UPDATE and SELECT ... FOR SHARE acquire row/gap locks explicitly.

Choosing a level

Read Committed (the default in Postgres and most systems): good default for most CRUD operations — cheap, avoids dirty reads.
Repeatable Read / Snapshot: use when you read-then-write based on that read within the same transaction and must guarantee the read doesn’t change underneath you (e.g., reading an inventory count before decrementing).
Serializable: use for genuinely correctness-critical multi-step invariants (e.g., double-booking prevention, financial ledgers) — but budget for retry logic, since SSI aborts are expected, not exceptional.

8. Locking, Deadlocks & Retry Strategies

Optimistic vs. pessimistic locking

Pessimistic locking — acquire a row lock up front:

var balance int64
err := tx.QueryRowContext(ctx,
    `SELECT balance FROM accounts WHERE id = $1 FOR UPDATE`, id).Scan(&balance)

FOR UPDATE blocks other transactions from reading (with FOR UPDATE/FOR SHARE) or writing that row until this transaction commits/rolls back. Use FOR UPDATE NOWAIT (fail immediately if locked) or FOR UPDATE SKIP LOCKED (skip locked rows — extremely useful for building job queues) when blocking is unacceptable.

// Job-queue pattern: SKIP LOCKED lets multiple workers safely pull
// distinct jobs without blocking on each other.
rows, err := tx.QueryContext(ctx, `
    SELECT id, payload FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 10
    FOR UPDATE SKIP LOCKED`)

Optimistic locking — no lock held; detect conflicts at write time via a version column:

res, err := tx.ExecContext(ctx,
    `UPDATE accounts SET balance = $1, version = version + 1
     WHERE id = $2 AND version = $3`,
    newBalance, id, expectedVersion)
n, _ := res.RowsAffected()
if n == 0 {
    return ErrOptimisticLockConflict // someone else updated first — retry from read
}

Optimistic locking scales better under low-contention, high-read workloads (no lock held during business logic computation); pessimistic locking is safer under high contention where retries would be frequent and wasteful.

Deadlocks

A deadlock occurs when transaction A holds a lock B wants, and B holds a lock A wants. Databases detect this cycle and abort one transaction (choosing a “victim”), returning a driver-specific error:

PostgreSQL: SQLSTATE 40P01 (deadlock_detected)
MySQL/InnoDB: error 1213 (ER_LOCK_DEADLOCK)

Deadlocks are a normal, expected occurrence in any system with concurrent writers to overlapping rows — your code must handle them via retry, not treat them as fatal.

A production-grade retry wrapper

package txutil

import (
    "context"
    "database/sql"
    "errors"
    "math/rand"
    "time"

    "github.com/jackc/pgconn"
)

// RetryableTxFunc is the unit of work executed inside a transaction.
type RetryableTxFunc func(ctx context.Context, tx *sql.Tx) error

// WithRetryableTx runs fn inside a transaction, automatically retrying on
// serialization failures and deadlocks with exponential backoff + jitter.
func WithRetryableTx(ctx context.Context, db *sql.DB, opts *sql.TxOptions, fn RetryableTxFunc) error {
    const maxAttempts = 5
    var lastErr error

    for attempt := 0; attempt < maxAttempts; attempt++ {
        if attempt > 0 {
            backoff := time.Duration(1<<uint(attempt)) * 10 * time.Millisecond
            jitter := time.Duration(rand.Int63n(int64(backoff)))
            select {
            case <-time.After(backoff + jitter):
            case <-ctx.Done():
                return ctx.Err()
            }
        }

        err := runOnce(ctx, db, opts, fn)
        if err == nil {
            return nil
        }
        if !isRetryable(err) {
            return err // permanent error — don't waste attempts
        }
        lastErr = err
    }
    return fmt.Errorf("exceeded %d retry attempts: %w", maxAttempts, lastErr)
}

func runOnce(ctx context.Context, db *sql.DB, opts *sql.TxOptions, fn RetryableTxFunc) (err error) {
    tx, err := db.BeginTx(ctx, opts)
    if err != nil {
        return err
    }
    defer func() {
        if p := recover(); p != nil {
            _ = tx.Rollback()
            panic(p) // re-panic after ensuring rollback
        }
        if err != nil {
            _ = tx.Rollback()
            return
        }
        err = tx.Commit()
    }()

    err = fn(ctx, tx)
    return err
}

func isRetryable(err error) bool {
    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) {
        switch pgErr.Code {
        case "40001", // serialization_failure
            "40P01": // deadlock_detected
            return true
        }
    }
    return false
}

Usage:

err := txutil.WithRetryableTx(ctx, db, &sql.TxOptions{Isolation: sql.LevelSerializable},
    func(ctx context.Context, tx *sql.Tx) error {
        return TransferFundsTx(ctx, tx, fromID, toID, amount)
    })

Key design decisions in this wrapper:

Exponential backoff with jitter avoids thundering-herd retries when many transactions conflict simultaneously.
Distinguishing retryable vs. permanent errors — a unique constraint violation (23505) should not be retried; retrying it will fail identically forever.
Re-panicking after rollback ensures panics still propagate to caller-level recovery/logging, while guaranteeing the transaction is never left dangling.

9. Savepoints & Nested Transactions

database/sql has no native support for nested transactions — calling BeginTx again on the same *sql.DB simply checks out a different connection from the pool, creating an entirely independent transaction, not a nested one. This is a frequent source of bugs.

Savepoints are the correct mechanism for partial rollback within a single transaction, issued as raw SQL:

tx, _ := db.BeginTx(ctx, nil)
defer tx.Rollback()

_, _ = tx.ExecContext(ctx, `INSERT INTO orders (...) VALUES (...)`)

_, err := tx.ExecContext(ctx, `SAVEPOINT sp1`)
if err != nil {
    return err
}

if _, err := tx.ExecContext(ctx, `INSERT INTO risky_step (...) VALUES (...)`); err != nil {
    // roll back only to the savepoint — the earlier INSERT survives
    _, _ = tx.ExecContext(ctx, `ROLLBACK TO SAVEPOINT sp1`)
} else {
    _, _ = tx.ExecContext(ctx, `RELEASE SAVEPOINT sp1`)
}

return tx.Commit()

Libraries like sqlx don’t add native nested-transaction support either (by design, matching the underlying SQL semantics), but higher-level frameworks (e.g., GORM’s tx.SavePoint()/tx.RollbackTo()) wrap this pattern for convenience.

10. Transaction Design Patterns

10.1 Repository + Unit of Work

Passing *sql.Tx through layers is verbose. A common pattern is to abstract over “something queryable” so the same repository code works both inside and outside a transaction:

// Querier is satisfied by both *sql.DB and *sql.Tx.
type Querier 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 AccountRepo struct {
    db Querier
}

func (r *AccountRepo) Debit(ctx context.Context, id, amount int64) error {
    _, err := r.db.ExecContext(ctx,
        `UPDATE accounts SET balance = balance - $1 WHERE id = $2`, amount, id)
    return err
}

// UnitOfWork coordinates a transaction across multiple repositories.
type UnitOfWork struct {
    db *sql.DB
}

func (u *UnitOfWork) Execute(ctx context.Context, fn func(repos *Repos) error) error {
    tx, err := u.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    repos := &Repos{
        Accounts: &AccountRepo{db: tx},
        Ledger:   &LedgerRepo{db: tx},
    }
    if err := fn(repos); err != nil {
        return err
    }
    return tx.Commit()
}

This gives you repository code that’s transaction-agnostic — the same AccountRepo works whether backed by *sql.DB (auto-commit per statement) or *sql.Tx (part of a larger unit of work).

10.2 Context-embedded transaction (implicit propagation)

Some codebases stash the active *sql.Tx in context.Context to avoid threading it through every function signature:

type txKey struct{}

func WithTx(ctx context.Context, tx *sql.Tx) context.Context {
    return context.WithValue(ctx, txKey{}, tx)
}

func TxFromContext(ctx context.Context, db *sql.DB) Querier {
    if tx, ok := ctx.Value(txKey{}).(*sql.Tx); ok {
        return tx
    }
    return db // fall back to the pool
}

This is convenient but controversial — it hides a significant side effect (which connection, which transaction) behind an invisible context value. Many senior engineers prefer explicit transaction passing (10.1) precisely because it makes transaction boundaries visible in function signatures and code review. Use context-embedding sparingly, and document it clearly if you do.

10.3 The Outbox Pattern

To atomically combine a DB write with a “send a message” side effect (Kafka, SQS, webhook), never call the external system from inside the transaction. Instead:

func PlaceOrder(ctx context.Context, tx *sql.Tx, order Order) error {
    if _, err := tx.ExecContext(ctx, `INSERT INTO orders (...) VALUES (...)`, ...); err != nil {
        return err
    }
    event, _ := json.Marshal(OrderPlacedEvent{OrderID: order.ID})
    _, err := tx.ExecContext(ctx,
        `INSERT INTO outbox (event_type, payload, created_at) VALUES ($1, $2, now())`,
        "order.placed", event)
    return err
}

A separate background worker polls the outbox table, publishes events to the message broker, and marks them processed — giving you exactly the atomicity guarantee of the DB transaction, without ever holding a transaction open across a network call.

11. Distributed Transactions & the Saga Pattern

Two-Phase Commit (2PC)

database/sql has no built-in 2PC support. True distributed ACID transactions across multiple databases require an external transaction coordinator (e.g., XA transactions). This is rarely used in modern Go microservice architectures because:

It requires all participants to support the XA protocol.
Coordinator failure can leave resources locked indefinitely (“in-doubt” transactions).
It couples services tightly and hurts availability (CAP theorem tradeoffs).

Saga Pattern (the pragmatic alternative)

Instead of a single atomic transaction spanning services, a saga is a sequence of local transactions, each with a corresponding compensating action to undo it if a later step fails.

type SagaStep struct {
    Name       string
    Action     func(ctx context.Context) error
    Compensate func(ctx context.Context) error
}

func RunSaga(ctx context.Context, steps []SagaStep) error {
    completed := make([]SagaStep, 0, len(steps))
    for _, step := range steps {
        if err := step.Action(ctx); err != nil {
            // Unwind in reverse order — undo everything that succeeded.
            for i := len(completed) - 1; i >= 0; i-- {
                if cErr := completed[i].Compensate(ctx); cErr != nil {
                    // Compensation failures need alerting — manual intervention
                    // is often required at this point.
                    log.Printf("CRITICAL: compensation failed for %s: %v",
                        completed[i].Name, cErr)
                }
            }
            return fmt.Errorf("saga failed at step %q: %w", step.Name, err)
        }
        completed = append(completed, step)
    }
    return nil
}

Example: booking a trip = reserve flight + reserve hotel + charge card. If charging the card fails, compensating actions cancel the hotel reservation and the flight reservation, in reverse order.

Choreography vs. Orchestration:

Orchestration (shown above): a central coordinator calls each service and knows the full sequence — easier to reason about and debug, but the orchestrator is a single point of logic (not necessarily failure, if made stateless/restartable).
Choreography: each service reacts to events published by the previous one (fully decentralized), better for loose coupling but much harder to trace and debug — requires strong observability (distributed tracing) to be maintainable.

For sagas that must survive process crashes mid-flight, persist saga state (current step, completed steps) to a database table so an in-progress saga can be resumed after a restart — treat the saga itself as a state machine with durable state transitions.

12. Error Handling Deep Dive

The sentinel errors you must know

sql.ErrNoRows   // returned by QueryRow.Scan when no row matched
sql.ErrTxDone   // operation attempted on an already-committed/rolled-back Tx
sql.ErrConnDone // operation attempted on a released *sql.Conn

err := db.QueryRowContext(ctx, query, id).Scan(&result)
switch {
case errors.Is(err, sql.ErrNoRows):
    return nil, ErrNotFound // translate to a domain error — never leak sql.ErrNoRows upward
case err != nil:
    return nil, fmt.Errorf("query account %d: %w", id, err)
}

Rule: Never let sql.ErrNoRows escape your data-access layer. It’s an implementation detail of database/sql; translate it into a domain-level ErrNotFound so callers (and especially HTTP handlers) don’t need to know about SQL internals.

Driver-specific error inspection

Use errors.As to unwrap driver-specific error types for fine-grained handling (constraint violations, deadlocks, connection failures):

import "github.com/jackc/pgconn" // pgx driver

var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
    switch pgErr.Code {
    case "23505": // unique_violation
        return ErrDuplicateEntry
    case "23503": // foreign_key_violation
        return ErrInvalidReference
    case "23514": // check_violation
        return ErrConstraintViolation
    case "40001":
        return ErrSerializationFailure // caller should retry
    case "40P01":
        return ErrDeadlockDetected // caller should retry
    }
}

For lib/pq (older, maintenance-mode driver), the equivalent type is *pq.Error. For go-sql-driver/mysql, it’s *mysql.MySQLError with numeric .Number codes (e.g., 1062 for duplicate entry, 1213 for deadlock).

Wrapping errors with context

Always wrap errors with %w and enough context to debug without needing to reproduce:

return fmt.Errorf("transferring %d from account %d to %d: %w", amount, fromID, toID, err)

This preserves the error chain for errors.Is/errors.As while giving operators a readable, greppable log line.

13. ORMs & Query Builders: sqlx, GORM, ent, sqlc

Tool	Category	Transaction API	Notes
`database/sql`	stdlib	`BeginTx` / `Tx`	Full control, most verbose.
`sqlx`	thin extension	`db.Beginx()` returns `*sqlx.Tx`	Adds `StructScan`, named queries; transaction semantics identical to stdlib.
`sqlc`	code generator	Generates `*Queries` bound to any `DBTX` interface	You write raw SQL; sqlc generates type-safe Go. Compose with the `Querier` interface pattern from §10.1 for transactions.
`GORM`	full ORM	`db.Transaction(func(tx *gorm.DB) error {...})`	Convenient auto rollback-on-error closure; supports nested transactions via savepoints (`tx.SavePoint("sp1")`).
`ent`	code-gen ORM (Facebook/Meta)	`client.Tx(ctx)` returns a tx-scoped client	Strong type-safety and graph-based schema; good for complex domain models.

GORM transaction example

err := db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Model(&Account{}).Where("id = ?", fromID).
        Update("balance", gorm.Expr("balance - ?", amount)).Error; err != nil {
        return err // GORM automatically rolls back on any returned error
    }
    if err := tx.Model(&Account{}).Where("id = ?", toID).
        Update("balance", gorm.Expr("balance + ?", amount)).Error; err != nil {
        return err
    }
    return nil // commit happens automatically if nil is returned
})

sqlc pattern

// sqlc-generated Queries struct is bound to a db-or-tx interface (DBTX):
type DBTX interface {
    ExecContext(context.Context, string, ...any) (sql.Result, error)
    QueryContext(context.Context, string, ...any) (*sql.Rows, error)
    QueryRowContext(context.Context, string, ...any) *sql.Row
}

func (s *Store) TransferTx(ctx context.Context, arg TransferParams) error {
    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    q := New(tx) // New() wraps DBTX — here, the transaction
    if err := q.DecrementBalance(ctx, arg.FromID, arg.Amount); err != nil {
        return err
    }
    if err := q.IncrementBalance(ctx, arg.ToID, arg.Amount); err != nil {
        return err
    }
    return tx.Commit()
}

sqlc is popular in high-performance Go shops because it keeps SQL as SQL (full control over query plans, no ORM “N+1” surprises) while eliminating the boilerplate of manual Scan calls via code generation.

14. Testing Transactions

14.1 Unit tests with `sqlmock`

import "github.com/DATA-DOG/go-sqlmock"

func TestTransferFunds(t *testing.T) {
    db, mock, err := sqlmock.New()
    require.NoError(t, err)
    defer db.Close()

    mock.ExpectBegin()
    mock.ExpectExec(`UPDATE accounts SET balance = balance - \$1`).
        WithArgs(int64(100), int64(1)).
        WillReturnResult(sqlmock.NewResult(0, 1))
    mock.ExpectExec(`UPDATE accounts SET balance = balance \+ \$1`).
        WithArgs(int64(100), int64(2)).
        WillReturnResult(sqlmock.NewResult(0, 1))
    mock.ExpectCommit()

    err = TransferFunds(context.Background(), db, 1, 2, 100)
    require.NoError(t, err)
    require.NoError(t, mock.ExpectationsWereMet())
}

sqlmock is great for verifying that the right SQL was issued in the right order with the right args, but it doesn’t validate actual SQL correctness (typos, constraint logic) — it’s a contract test, not an integration test.

14.2 Integration tests with `testcontainers-go`

import "github.com/testcontainers/testcontainers-go/modules/postgres"

func setupTestDB(t *testing.T) *sql.DB {
    ctx := context.Background()
    container, err := postgres.Run(ctx, "postgres:16-alpine",
        postgres.WithDatabase("testdb"),
        postgres.WithUsername("test"),
        postgres.WithPassword("test"),
    )
    require.NoError(t, err)
    t.Cleanup(func() { _ = container.Terminate(ctx) })

    connStr, err := container.ConnectionString(ctx, "sslmode=disable")
    require.NoError(t, err)

    db, err := sql.Open("postgres", connStr)
    require.NoError(t, err)
    runMigrations(t, db)
    return db
}

Real integration tests against a real (containerized) Postgres/MySQL instance catch things mocks never will: constraint violations, actual isolation-level behavior, deadlocks, SQL syntax errors specific to the DB dialect.

14.3 Test isolation strategy: transaction rollback per test

A fast, reliable pattern for integration tests: wrap each test in its own transaction and roll it back at the end, so tests never leak state into each other and don’t need explicit cleanup:

func withTestTx(t *testing.T, db *sql.DB) *sql.Tx {
    tx, err := db.Begin()
    require.NoError(t, err)
    t.Cleanup(func() { _ = tx.Rollback() })
    return tx
}

Pass this tx (as your Querier interface from §10.1) into the code under test — every test runs in perfect isolation, and rollback is essentially free.

15. Performance & Observability

Batch inserts: use multi-row INSERT ... VALUES (...), (...), (...) or driver-native bulk copy (pgx.CopyFrom for Postgres) instead of looping single-row inserts inside a transaction — orders of magnitude faster.
Avoid N+1 queries: fetch related data with JOINs or WHERE id = ANY($1) batch lookups rather than looping and querying per item.
Instrument your pool: export db.Stats() on an interval (e.g., every 15s) to your metrics system — WaitCount, WaitDuration, InUse, Idle are your first line of defense against connection exhaustion incidents.
Trace transaction boundaries: wrap BeginTx/Commit with OpenTelemetry spans so slow transactions are visible in distributed traces, not just slow individual queries.
Log slow transactions: track wall-clock time from BeginTx to Commit/Rollback and log/alert on outliers — a transaction held open for 30 seconds is very likely a bug (waiting on a lock, an accidental external call inside the tx, or a missing index causing a slow query).

start := time.Now()
tx, err := db.BeginTx(ctx, nil)
defer func() {
    if d := time.Since(start); d > 500*time.Millisecond {
        log.Printf("WARN: long-running transaction: %s", d)
    }
}()

16. Common Pitfalls Checklist

17. Production-Grade Reference Implementation

package accounts

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

    "github.com/jackc/pgconn"
)

var (
    ErrAccountNotFound   = errors.New("account not found")
    ErrInsufficientFunds = errors.New("insufficient funds")
)

type Store struct {
    db *sql.DB
}

func NewStore(db *sql.DB) *Store {
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(2 * time.Minute)
    return &Store{db: db}
}

func (s *Store) Transfer(ctx context.Context, fromID, toID int64, amount int64) (err error) {
    if amount <= 0 {
        return fmt.Errorf("invalid amount: %d", amount)
    }

    tx, err := s.db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    defer func() {
        if p := recover(); p != nil {
            _ = tx.Rollback()
            panic(p)
        }
        if err != nil {
            _ = tx.Rollback()
        }
    }()

    // Lock the source row to serialize concurrent transfers from the same account.
    var balance int64
    err = tx.QueryRowContext(ctx,
        `SELECT balance FROM accounts WHERE id = $1 FOR UPDATE`, fromID).Scan(&balance)
    if errors.Is(err, sql.ErrNoRows) {
        return ErrAccountNotFound
    }
    if err != nil {
        return fmt.Errorf("lock source account: %w", err)
    }
    if balance < amount {
        return ErrInsufficientFunds
    }

    if _, err = tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance - $1 WHERE id = $2`, amount, fromID); err != nil {
        return fmt.Errorf("debit: %w", err)
    }

    res, execErr := tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance + $1 WHERE id = $2`, amount, toID)
    if execErr != nil {
        err = fmt.Errorf("credit: %w", execErr)
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        err = ErrAccountNotFound
        return err
    }

    if _, err = tx.ExecContext(ctx,
        `INSERT INTO ledger (from_id, to_id, amount, created_at) VALUES ($1, $2, $3, now())`,
        fromID, toID, amount); err != nil {
        return fmt.Errorf("write ledger: %w", err)
    }

    if err = tx.Commit(); err != nil {
        var pgErr *pgconn.PgError
        if errors.As(err, &pgErr) && (pgErr.Code == "40001" || pgErr.Code == "40P01") {
            return fmt.Errorf("commit failed (retryable): %w", err)
        }
        return fmt.Errorf("commit: %w", err)
    }
    return nil
}

This reference implementation demonstrates, in one place: explicit isolation level, row locking for a critical invariant, business-rule validation mid-transaction, panic-safe deferred rollback, structured error wrapping, retryable-error classification, and a durable audit trail (ledger table) written in the same atomic unit as the balance mutation.

Table of Contents

1. The database/sql Mental Model