8. Databases & Transactions

Use database/sql correctly: pools, deadlines, transactions, scanning, and resiliency under load.

Question: What is the role of *sql.DB, and how should it be configured?

Answer: *sql.DB is not a single database connection. It is a handle that manages a pool of underlying connections. It is safe for concurrent use and should be created once (as a global or long-lived object) and shared across your application.

Explanation: You should configure the connection pool parameters to match your application's needs:

• SetMaxOpenConns: The maximum number of open connections to the database.

• SetMaxIdleConns: The maximum number of connections that can be left idle in the pool.

• SetConnMaxLifetime: The maximum amount of time a connection may be reused.

Properly tuning these values is critical for database performance and resilience.

Question: How do you safely manage a database transaction in Go?

Answer: A safe transaction pattern involves starting a transaction with db.BeginTx, using defer with a recovery block to ensure tx.Rollback() is called on failure, and then explicitly calling tx.Commit() at the end.

Explanation: The defer is crucial. If any part of the transaction logic panics or returns an error, the defer guarantees that the transaction is rolled back, preventing it from being left open. You should only call Commit if all operations within the transaction have succeeded.

func withTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) (err error) {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
    if err != nil { return err }

    defer func() {
        if p := recover(); p != nil {
            _ = tx.Rollback()
            panic(p)
        } else if err != nil {
            _ = tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    err = fn(tx)
    return
}

Question: What are best practices for handling *sql.Rows and scanning?

Answer: Always defer rows.Close(), iterate with for rows.Next() and check rows.Err() after the loop.

Explanation: Use nullable types (sql.NullString, custom wrappers) to handle NULLs. Scan into pointers only when necessary.

Question: How do you enforce timeouts on DB operations?

Answer: Pass Context with deadlines to QueryContext/ExecContext/BeginTx.

Explanation: Timeouts prevent stuck connections and integrate with HTTP request cancellation. Map DB errors to retriable vs permanent categories.

Question: When should you use prepared statements?

Answer: Use prepared statements for repeated queries to reduce parse/plan overhead and for strong SQL injection prevention.

Explanation: Manage statement lifecycle carefully; some drivers tie statements to specific connections.

Question: How do you detect and retry serialization/deadlock errors?

Answer: Match driver-specific codes (e.g., Postgres 40001/40P01) and retry the transaction with backoff.

Explanation: Keep transactions small and idempotent to enable safe retries.