Transactions & Patterns

Transactions ensure a group of database operations either all succeed or all fail. Go's database/sql provides a clean transaction API, and a single pattern -- defer tx.Rollback() -- handles the tricky cleanup. This topic also covers the repository pattern, when to use an ORM (almost never), and alternatives like sqlc.

sql.Tx: Begin, Commit, Rollback

A transaction starts with BeginTx and ends with either Commit or Rollback:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return fmt.Errorf("beginning transaction: %w", err)
}
defer tx.Rollback() // no-op if already committed

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

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

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

The defer tx.Rollback() Pattern

This is the most important pattern in Go database code:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

// ... do work with tx ...

return tx.Commit()

Why this works:

If any operation fails and you return early, defer tx.Rollback() runs and rolls back
If you reach tx.Commit() and it succeeds, defer tx.Rollback() still runs but is a no-op on an already-committed transaction
If tx.Commit() fails, defer tx.Rollback() cleans up

You never need to manually call Rollback in error paths. The defer handles every case.

Transaction Options

BeginTx accepts *sql.TxOptions for isolation level and read-only mode:

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
    ReadOnly:  true,
})

Available isolation levels:

Level                   Dirty Read  Non-Repeatable  Phantom
------------------------------------------------------------
LevelDefault            driver default
LevelReadUncommitted    possible    possible        possible
LevelReadCommitted      no          possible        possible
LevelRepeatableRead     no          no              possible
LevelSerializable       no          no              no

Most applications use LevelDefault (which is ReadCommitted in PostgreSQL). Use Serializable only when you need it -- it causes more transaction retries under contention.

Context-Aware Queries

Always use the Context variants. If the context is cancelled (client disconnects, timeout), the query is cancelled on the database side too:

func (s *UserStore) GetByEmail(ctx context.Context, email string) (*User, error) {
    var u User
    err := s.db.QueryRowContext(ctx,
        "SELECT id, name, email FROM users WHERE email = $1",
        email,
    ).Scan(&u.ID, &u.Name, &u.Email)

    if err == sql.ErrNoRows {
        return nil, nil
    }
    if err != nil {
        return nil, fmt.Errorf("querying user by email: %w", err)
    }
    return &u, nil
}

Without context, a query against a slow or unreachable database blocks forever.

The Repository Pattern in Go

The repository pattern wraps database access behind an interface. This makes testing and swapping implementations straightforward:

// Define the interface in the package that uses it
type UserRepository interface {
    Create(ctx context.Context, user *User) error
    GetByID(ctx context.Context, id int) (*User, error)
    List(ctx context.Context) ([]User, error)
    Delete(ctx context.Context, id int) error
}

Implement it with database/sql:

type PostgresUserRepo struct {
    db *sql.DB
}

func NewPostgresUserRepo(db *sql.DB) *PostgresUserRepo {
    return &PostgresUserRepo{db: db}
}

func (r *PostgresUserRepo) Create(ctx context.Context, user *User) error {
    err := r.db.QueryRowContext(ctx,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at",
        user.Name, user.Email,
    ).Scan(&user.ID, &user.CreatedAt)
    if err != nil {
        return fmt.Errorf("inserting user: %w", err)
    }
    return nil
}

func (r *PostgresUserRepo) GetByID(ctx context.Context, id int) (*User, error) {
    var u User
    err := r.db.QueryRowContext(ctx,
        "SELECT id, name, email, created_at FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
    if err == sql.ErrNoRows {
        return nil, nil
    }
    if err != nil {
        return nil, fmt.Errorf("querying user %d: %w", id, err)
    }
    return &u, nil
}

func (r *PostgresUserRepo) List(ctx context.Context) ([]User, error) {
    rows, err := r.db.QueryContext(ctx,
        "SELECT id, name, email, created_at FROM users ORDER BY id",
    )
    if err != nil {
        return nil, fmt.Errorf("querying users: %w", err)
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt); err != nil {
            return nil, fmt.Errorf("scanning user: %w", err)
        }
        users = append(users, u)
    }
    return users, rows.Err()
}

func (r *PostgresUserRepo) Delete(ctx context.Context, id int) error {
    result, err := r.db.ExecContext(ctx, "DELETE FROM users WHERE id = $1", id)
    if err != nil {
        return fmt.Errorf("deleting user %d: %w", id, err)
    }
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return fmt.Errorf("user %d not found", id)
    }
    return nil
}

Transactions Across Repositories

When a business operation spans multiple repositories, pass the transaction:

func TransferFunds(ctx context.Context, db *sql.DB, from, to int, amount float64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    if err := debitAccount(ctx, tx, from, amount); err != nil {
        return err
    }
    if err := creditAccount(ctx, tx, to, amount); err != nil {
        return err
    }
    if err := recordTransfer(ctx, tx, from, to, amount); err != nil {
        return err
    }

    return tx.Commit()
}

func debitAccount(ctx context.Context, tx *sql.Tx, id int, amount float64) error {
    _, err := tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1",
        amount, id,
    )
    return err
}

A common pattern is to define an interface that both *sql.DB and *sql.Tx satisfy:

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
}

Then your repository methods accept DBTX and work with or without a transaction.

ORM vs Raw SQL vs sqlc

Raw SQL (Recommended Default)

Write SQL directly. You control every query. No magic, no surprises:

rows, err := db.QueryContext(ctx, "SELECT id, name FROM users WHERE active = $1", true)

sqlc: Generated Type-Safe Code

sqlc reads your SQL queries and generates Go code with proper types:

-- queries.sql
-- name: GetUser :one
SELECT id, name, email FROM users WHERE id = $1;

Running sqlc generate produces type-safe Go functions. You write SQL, sqlc writes the Go boilerplate.

GORM / ent (ORMs)

ORMs map structs to tables and generate SQL. They add a layer of abstraction that often leaks:

// GORM example
db.Where("active = ?", true).Find(&users)

When to Use What

Approach       Pros                          Cons
-----------------------------------------------------------------
Raw SQL        Full control, no deps         Manual scanning boilerplate
sqlc           Type-safe, fast, SQL-first    Extra build step
GORM/ent       Less boilerplate for CRUD     Hides SQL, hard to debug

The Go community strongly favors raw SQL or sqlc. ORMs are used less often in Go than in Python or Ruby because Go's explicit style and database/sql make raw SQL comfortable.

Common Pitfalls

Forgetting defer tx.Rollback(). Without it, a failed transaction holds a connection and leaves the transaction open until the connection times out.
Using db instead of tx inside a transaction. If you start a transaction but query through db instead of tx, those queries run outside the transaction.
Not checking RowsAffected for updates and deletes. An UPDATE that matches zero rows is not an error from the database perspective, but it is usually a bug (user not found).
Choosing an ORM because it is familiar from other languages. Go's database ecosystem is built around explicit SQL. Fighting that leads to pain.
Using Serializable isolation without retry logic. Serializable transactions can fail with serialization errors under contention. You need retry logic.
Not passing context through transaction operations. If the client disconnects, you want the transaction to abort, not continue running expensive queries.

Key Takeaways

Use defer tx.Rollback() after every BeginTx. It handles all cleanup paths.
Always use Context variants: QueryContext, ExecContext, BeginTx with a context.
The repository pattern in Go uses an interface defined by the consumer and implemented with database/sql.
For transactions across repositories, pass *sql.Tx or use a DBTX interface.
Prefer raw SQL or sqlc over ORMs. The Go ecosystem is built around explicit SQL.
Check RowsAffected after UPDATE and DELETE to detect "not found" cases.