database/sql Usage (Correct Patterns)

Proper use of database/sql prevents connection leaks, transaction bugs, and hidden latency. This lesson covers Query vs Exec, row scanning pitfalls, context-aware calls, NULL handling, and production-safe SQL usage in Go.

On this page

database/sql: Correct Usage Patterns in Production

The database/sql package is deceptively simple. Its API hides a connection pool and concurrency coordination layer. Misusing it leads to connection leaks, transaction inconsistencies, and hard-to-debug production incidents.

This lesson focuses on correct usage patterns — not just API calls.

Opening the Database

db, err := sql.Open("postgres", dsn)
if err != nil {
    log.Fatal(err)
}

Important: sql.Open does not immediately connect to the database. It validates arguments and prepares the pool.

To verify connectivity:

if err := db.Ping(); err != nil {
    log.Fatal(err)
}

Query vs QueryRow vs Exec

Query (Multiple Rows)

rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
if err != nil {
    return err
}
defer rows.Close()

Always close rows. Not closing rows leaks connections.

QueryRow (Single Row)

row := db.QueryRowContext(ctx, "SELECT id FROM users WHERE id = $1", id)

var userID int
err := row.Scan(&userID)
if errors.Is(err, sql.ErrNoRows) {
    return notFound
}

QueryRow does not require Close().

Exec (No Rows Returned)

res, err := db.ExecContext(ctx, "UPDATE users SET name = $1 WHERE id = $2", name, id)

Use Exec for INSERT/UPDATE/DELETE without result sets.

Rows Iteration Pattern

for rows.Next() {
    var u User
    if err := rows.Scan(&u.ID, &u.Name); err != nil {
        return err
    }
}
if err := rows.Err(); err != nil {
    return err
}

Always check rows.Err() after iteration.

NULL Handling

Scanning NULL into non-pointer types causes errors.

Unsafe

var name string
rows.Scan(&name)

Safe Using sql.NullString

var name sql.NullString
rows.Scan(&name)
if name.Valid {
    fmt.Println(name.String)
}

Alternatively use pointers:

var name *string

Common Bug: Forgotten rows.Close()

If rows are not closed, the connection remains in use. Under load, pool exhaustion occurs.

Always:

defer rows.Close()

Context Is Mandatory

Always use Context variants.

db.QueryContext(ctx, ...)
db.ExecContext(ctx, ...)

If request is canceled, DB query is interrupted.

Error Classification

sql.ErrNoRows

if errors.Is(err, sql.ErrNoRows) {
    return notFound
}

Driver-Specific Errors

Inspect error types carefully for constraint violations.

Never compare error strings.

Prepared Statements

Single Use (Prefer This)

Let driver handle preparation internally.

db.QueryContext(ctx, "SELECT * FROM users WHERE id = $1", id)

Explicit Prepare (Advanced Use)

stmt, err := db.PrepareContext(ctx, query)
if err != nil {
    return err
}
defer stmt.Close()

Avoid preparing inside loops repeatedly.

Transactions

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

Always defer Rollback. Commit only if successful.

if err := tx.Commit(); err != nil {
    return err
}

Never mix db.Query and tx.Query inside the same transaction scope.

Common Transaction Pitfalls

Long-running transactions
Performing network calls inside transaction
Not handling commit errors
Ignoring serialization conflicts

Scanning Performance

Scan is reflection-heavy. For high-volume reads:

Select only required columns
Avoid SELECT *
Use indexes properly

Connection Leak Detection

stats := db.Stats()
fmt.Println(stats.InUse)

If InUse remains high under low traffic, investigate leaks.

Graceful Shutdown Considerations

During shutdown:

Stop accepting new HTTP requests
Wait for active DB queries
Call db.Close() last

Testing SQL Usage

Use integration tests with real DB when possible.

go test -run=Integration ./...

Mocking sql.DB rarely captures real behavior.

Common Anti-Patterns

Ignoring rows.Err()
Forgetting rows.Close()
Using string concatenation for SQL (SQL injection risk)
Long transactions spanning multiple layers
Comparing error messages directly

Operational Checklist

Context used everywhere
rows.Close() always deferred
rows.Err() checked
NULL handled explicitly
Transactions short-lived
No SQL string concatenation
Pool stats monitored

Final Perspective

database/sql is powerful but unforgiving when misused. Correct resource handling, explicit context usage, and disciplined transaction management separate stable production systems from those that degrade silently under load. Treat SQL interactions as critical resource operations — not just function calls.

Connection Pooling (Limits & Timeouts) →