Run database transactions with database/sql

Approach

Wrap transactional work in a helper that begins a transaction, defers a rollback (a no-op after commit), and commits at the end. Use context-aware methods, FOR UPDATE row locks, and configure the pool.

Solution

package store

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

var ErrInsufficientFunds = errors.New("insufficient funds")

func Open(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn) // driver registered by importing github.com/jackc/pgx/v5/stdlib
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(20)
	db.SetConnMaxLifetime(30 * time.Minute)
	return db, nil
}

func WithTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // returns sql.ErrTxDone after a successful commit; safe to ignore
	if err := fn(tx); err != nil {
		return err
	}
	return tx.Commit()
}

func Transfer(ctx context.Context, db *sql.DB, from, to int64, cents int64) error {
	return WithTx(ctx, db, func(tx *sql.Tx) error {
		var balance int64
		err := tx.QueryRowContext(ctx, `SELECT balance FROM accounts WHERE id = $1 FOR UPDATE`, from).Scan(&balance)
		if err != nil {
			return fmt.Errorf("load account %d: %w", from, err)
		}
		if balance < cents {
			return ErrInsufficientFunds
		}
		if _, err := tx.ExecContext(ctx, `UPDATE accounts SET balance = balance - $1 WHERE id = $2`, cents, from); err != nil {
			return err
		}
		_, err = tx.ExecContext(ctx, `UPDATE accounts SET balance = balance + $1 WHERE id = $2`, cents, to)
		return err
	})
}

Pitfalls

• Not closing *sql.Rows (use defer rows.Close() and check rows.Err()), exhausting the pool.
• Using db instead of tx inside the transaction function, running statements outside it.
• Building SQL with fmt.Sprintf and user input; always use placeholders.

Variations

• Lock rows in a consistent order (lower ID first) to avoid deadlocks in transfers.
• sqlc generates type-safe Go code from SQL queries; pgx native API is faster for PostgreSQL.

Further reading: database/sql transactions