Working with Databases: database/sql

database/sql is Go's standard interface to SQL databases. It handles connection pooling and a common API; a separate driver package speaks to the specific database.

Opening a database

Install a driver and import it — usually with a blank import, because you only need its init function to register itself:

go get github.com/jackc/pgx/v5

package main

import (
    "context"
    "database/sql"
    "log"
    "os"
    "time"

    _ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver
)

func main() {
    db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Pool settings — tune to your database's connection limit.
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(30 * time.Minute)

    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        log.Fatal(err)
    }
    log.Println("connected")
}

Drivers set their own minimum Go version, and the newest releases may need a newer toolchain than yours; go get will tell you, and you can pin an older release (for example github.com/jackc/pgx/v5@v5.7.1 builds with Go 1.23). Other common drivers: github.com/go-sql-driver/mysql (driver name "mysql") and modernc.org/sqlite ("sqlite", pure Go, no cgo).

Two things surprise newcomers:

• sql.Open doesn't connect; it only validates arguments. PingContext forces a real connection.
• *sql.DB is a pool, not a single connection. Create one at startup and share it everywhere — it's safe for concurrent use.

Querying rows

Always use the ...Context variants so queries respect cancellation and deadlines:

type Product struct {
    ID    int64
    Name  string
    Price int64 // cents
}

func ListProducts(ctx context.Context, db *sql.DB, maxPrice int64) ([]Product, error) {
    rows, err := db.QueryContext(ctx,
        "SELECT id, name, price FROM products WHERE price <= $1 ORDER BY name",
        maxPrice)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var out []Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price); err != nil {
            return nil, err
        }
        out = append(out, p)
    }
    return out, rows.Err()
}

defer rows.Close() returns the connection to the pool; rows.Err() reports an error that ended the loop early. Skipping either is the most common database/sql bug.

Placeholders

Never build SQL with string concatenation. Pass values as arguments and let the driver send them separately from the query text. The placeholder syntax depends on the driver: PostgreSQL uses $1, $2; MySQL and SQLite use ?.

A single row and "not found"

func GetProduct(ctx context.Context, db *sql.DB, id int64) (Product, error) {
    var p Product
    err := db.QueryRowContext(ctx,
        "SELECT id, name, price FROM products WHERE id = $1", id).
        Scan(&p.ID, &p.Name, &p.Price)
    if errors.Is(err, sql.ErrNoRows) {
        return Product{}, ErrNotFound // your own domain error
    }
    return p, err
}

For statements that don't return rows, use ExecContext; its sql.Result gives RowsAffected() (and LastInsertId() on MySQL/SQLite — with PostgreSQL use INSERT ... RETURNING id and QueryRowContext).

NULL columns

Scanning SQL NULL into a plain string fails. Use sql.NullString, sql.NullInt64, the generic sql.Null[T] (Go 1.22+), or a pointer such as *string.

Transactions

func PlaceOrder(ctx context.Context, db *sql.DB, productID int64, qty int) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    // Rollback is a no-op after a successful Commit, so always defer it.
    defer tx.Rollback()

    res, err := tx.ExecContext(ctx,
        "UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1",
        qty, productID)
    if err != nil {
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return errors.New("insufficient stock")
    }

    if _, err := tx.ExecContext(ctx,
        "INSERT INTO orders (product_id, qty) VALUES ($1, $2)",
        productID, qty); err != nil {
        return err
    }
    return tx.Commit()
}

Inside a transaction use tx, not db — queries on db run on a different connection, outside the transaction.

Beyond database/sql

Many teams use sqlc, which generates type-safe Go from SQL files, or pgx's native API for PostgreSQL-specific features. Both still reward understanding the concepts above.

Common mistakes

• Forgetting rows.Close() — connections leak until the pool is exhausted and every query hangs.
• Opening a new *sql.DB per request instead of sharing one.
• Using fmt.Sprintf to insert values into SQL — an SQL injection vulnerability.
• Using db instead of tx inside a transaction.
• Leaving SetMaxOpenConns unlimited (the default) against a database with a small connection cap.

Practice

Write Transfer(ctx, db, fromID, toID, amount int64) error that moves money between two rows of an accounts(id, balance) table in one transaction, failing if the source balance is too low.

package bank

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

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

func Transfer(ctx context.Context, db *sql.DB, fromID, toID, amount int64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    res, err := tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1",
        amount, fromID)
    if err != nil {
        return err
    }
    n, err := res.RowsAffected()
    if err != nil {
        return err
    }
    if n == 0 {
        return ErrInsufficientFunds
    }

    if _, err := tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        amount, toID); err != nil {
        return err
    }
    return tx.Commit()
}