Go database/sql Tutorial

The database/sql package is Go’s standard interface to relational databases. It is a thin layer that delegates protocol details to a driver but standardizes the connection pool, the API shape, and the error contract. If you understand a few mechanics, it is genuinely pleasant to use.

What database/sql is and why

The package gives you a uniform API across Postgres, MySQL, SQLite, and others. You import a driver for side effects, open a *sql.DB, and call Query, Exec, and friends. The driver translates calls into protocol messages; the standard library manages connection pooling and lifecycle.

The “why” is portability and battle-tested pooling. You write the same code against any driver, and you get retry-safe pooling, context cancellation, and prepared statement caching for free.

Mental model

A *sql.DB is not a connection. It is a handle to a pool of connections. Each query borrows a connection, runs, and returns it. Transactions pin one connection for the duration. Rows iterators also hold a connection open until you finish reading or call Close().

This is the key insight: leaking rows leaks connections. Eventually the pool drains and every query blocks.

Hands-on example

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib"
)

db, err := sql.Open("pgx", "postgres://user:pass@localhost/app")
if err != nil { log.Fatal(err) }
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

type User struct{ ID int; Name string }

func GetUser(ctx context.Context, db *sql.DB, id int) (User, error) {
    var u User
    err := db.QueryRowContext(ctx,
        "SELECT id, name FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.Name)
    return u, err
}

func ListActive(ctx context.Context, db *sql.DB) ([]User, error) {
    rows, err := db.QueryContext(ctx, "SELECT id, name FROM users WHERE active")
    if err != nil { return nil, err }
    defer rows.Close()
    var out []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name); err != nil { return nil, err }
        out = append(out, u)
    }
    return out, rows.Err()
}

Common pitfalls

The single biggest mistake is forgetting defer rows.Close(). Every Query and QueryContext that returns rows must be closed, or you leak a connection. QueryRow is fine because Scan releases for you.

Another is calling sql.Open once per request. Open it once at startup; it is safe for concurrent use. Re-opening creates new pools each time and exhausts the database’s connection limit.

Tune your pool. The defaults are unbounded MaxOpenConns, which lets bursts overwhelm the database. Set SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime based on your DB’s max_connections and proxy timeouts.

Transactions are easy to mishandle. Always defer tx.Rollback() after Begin; if Commit runs first, the rollback becomes a no-op. Never use a *sql.DB inside a transaction; use the *sql.Tx.

Practical tips

Always pass a context. QueryContext, ExecContext, and BeginTx let your queries cancel when the request does. Without a context, a slow query holds a connection forever.

Use parameter placeholders, never string concatenation. The driver handles quoting and prevents injection. Each driver has its own placeholder style ($1 for pgx, ? for MySQL); the package does not abstract that.

For complex schemas, layer a small helper like sqlx or pgx directly on top. Pure database/sql is great for control, but sqlx’s StructScan removes a lot of boilerplate without hiding the underlying machinery.

Avoid db.Prepare unless you reuse a statement many times in a tight loop. The driver already caches prepared statements per connection, and explicit Stmt objects come with their own lifecycle to manage.

Wrap-up

database/sql rewards a small amount of care with reliable, portable database access. Open the pool once, configure its limits, always close rows, always pass context, and treat transactions as connection-pinned. Once those habits are automatic, the standard library is all you need for most services.