database/sql Basics

The deploy script runs the service with a typo in DATABASE_URL. The process starts, registers routes, passes the health check — and the first user request fails with a connection error. The surprising part is not the typo; it is that nothing complained earlier, because sql.Open never tried to connect.

sql.Open validates configuration and builds a pool description. It performs no I/O — the first real connection happens on first use, which by then is user-facing traffic. Verifying reachability is a separate, deliberate step:

package repo

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

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

func Open(ctx context.Context, dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn) // parses config; connects to nothing
	if err != nil {
		return nil, fmt.Errorf("open database: %w", err)
	}

	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	db.SetConnMaxIdleTime(5 * time.Minute)

	pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel()
	if err := db.PingContext(pingCtx); err != nil {
		db.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}
	return db, nil
}

Three mechanics hide in those lines. The blank import is how database/sql finds your driver: the driver package's init registers itself under a name ("pgx", "postgres", "mysql"), and calling sql.Open("pgx", ...) with no matching import fails with sql: unknown driver. The import is configuration, not usage — an "unused import" that is anything but.

sql.Open's error covers malformed configuration; everything else waits for PingContext. Ping with a timeout is the startup gate: fail fast at boot instead of at request one. Note which error you got matters too — a Ping failure at boot is fatal and retryable by the orchestrator; a Ping failure mid-flight usually means the database went away, and the pool will re-dial on its own.

And the thing you opened is not one connection — it is a pool. Every QueryContext may check a connection out, use it, and return it; the SetMax* calls above size that pool, which is the whole subject of Connection Pool Tuning. One *sql.DB per process per database is the norm: create it in main, pass it down, close it during graceful shutdown so idle connections drain. Opening a new *sql.DB per request or per repository defeats pooling entirely and eventually exhausts the database's own connection limit.

With the pool open and verified, the next question is how results actually move from the driver into your structs — and the ways that trip people up.

A report job queries 10,000 users and silently returns 843. The loop looked right: rows.Next(), Scan, append. Two omissions did the damage — nothing checked rows.Err(), so when the connection died mid-stream the loop simply saw Next() == false and treated truncation as completion; and nothing closed rows promptly, pinning a pool connection for the life of the surrounding function.

The full pattern, all four pieces:

func (r *UserRepo) ActiveUsers(ctx context.Context) ([]User, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id, name, email FROM users WHERE active = $1 ORDER BY id`, true)
	if err != nil {
		return nil, fmt.Errorf("query active users: %w", err)
	}
	defer rows.Close() // releases the connection when THIS function returns

	var users []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
			return nil, fmt.Errorf("scan user row: %w", err)
		}
		users = append(users, u)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate users: %w", err)
	}
	return users, nil
}

Why each line is load-bearing: rows.Next() returns false both at the end of data and on error — the two are indistinguishable without rows.Err(), which is where driver-level failures (connection reset, context cancelled mid-stream) surface. defer rows.Close() right after the error check means a return on any later failure still releases the connection; putting the defer inside the for loop is a classic bug — each iteration adds another defer and another held connection until the function ends.

The single-row shapes cut the ceremony by encoding it: QueryRowContext returns a Row whose Scan reports sql.ErrNoRows when nothing matched — a state of the world, not a malfunction, which callers translate to 404 (error identity):

var u User
err := r.db.QueryRowContext(ctx,
	`SELECT id, name FROM users WHERE id = $1`, id).Scan(&u.ID, &u.Name)
switch {
case errors.Is(err, sql.ErrNoRows):
	return User{}, ErrNotFound
case err != nil:
	return User{}, fmt.Errorf("get user %d: %w", id, err)
default:
	return u, nil
}

Scan itself converts columns to Go types with strict rules: driver values arrive as int64, float64, []byte, string, time.Time, or nil. Scanning nil into a plain string errors — NULL is not "". Use *string (nil means NULL) or sql.NullString when a column may be null, and think about it per column rather than defensively wrapping everything: a NOT NULL column scanned into a plain type documents itself.

One more discipline: every query takes ctx first. A cancelled request context propagates to the driver and aborts the round trip — without it, a client who left keeps your connection busy (Context and Cancellation). Reading rows is only half of data access; writing brings its own result shapes.

The insert code works in MySQL and returns id 0 in Postgres — with no error. LastInsertId is not part of SQL; it is a MySQL-ism that database/sql surfaces opportunistically, and the Postgres drivers return an error or zero for it. The portable answer is SQL itself: RETURNING, which turns an insert into a query.

Choose the method by what comes back:

// 1. No result set, success/failure only: ExecContext
res, err := db.ExecContext(ctx, `
	UPDATE orders SET status = $1 WHERE id = $2`, status, orderID)
if err != nil {
	return fmt.Errorf("update order %d: %w", orderID, err)
}
n, err := res.RowsAffected()
if err != nil || n == 0 {
	return ErrNotFound // affected-zero rows is a fact, not a failure
}

// 2. A row comes back: QueryRowContext with RETURNING
var created Order
err := db.QueryRowContext(ctx, `
	INSERT INTO orders (user_id, total_cents)
	VALUES ($1, $2)
	RETURNING id, created_at`, userID, cents).
	Scan(&created.ID, &created.CreatedAt)
if err != nil {
	return fmt.Errorf("create order: %w", err)
}

// 3. Many rows: QueryContext — covered in the rows pattern above

RowsAffected deserves the same skepticism as LastInsertId: MySQL counts rows matched by default for UPDATE while Postgres counts rows changed, so the same code means different things on different engines. Treat it as a hint and prefer explicit WHERE clauses whose zero-match result you actually want to detect.

Parameter placeholders are dialect territory: Postgres uses $1, $2; MySQL and SQLite use ?. The value-binding principle is identical everywhere (parameterized queries cover why), so keep dialect-specific SQL in the repository package where it is visible instead of string-replacing placeholders at runtime — a trick that breaks the moment a string literal contains a ?.

Scanning writes has one more shape to know: ExecContext returns sql.Result, not rows — no Close, no Err, and nothing to iterate. Confusing the two produces either leaked result sets or code that tries to Scan an update. The method name encodes the shape: Exec = effect, QueryRow = one row, Query = many rows.

These calls all start with r.db — a *sql.DB held by a repository struct. That struct is where SQL stops leaking into handlers, and it is the boundary that keeps the rest of the service testable.

Handlers are starting to embed SQL strings. Three different handlers each know a column name; a rename becomes a four-file search; and testing a handler now requires a database because *sql.DB sits in its signature. The fix is a boundary — and database/sql's own types give you everything you need to draw it.

package repo

type UserRepo struct {
	db *sql.DB
}

func NewUserRepo(db *sql.DB) *UserRepo { return &UserRepo{db: db} }

func (r *UserRepo) ByID(ctx context.Context, id int64) (User, error) {
	var u User
	err := r.db.QueryRowContext(ctx,
		`SELECT id, name, email FROM users WHERE id = $1`, id).
		Scan(&u.ID, &u.Name, &u.Email)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return User{}, ErrNotFound
	case err != nil:
		return User{}, fmt.Errorf("repo by id %d: %w", id, err)
	default:
		return u, nil
	}
}

// consumer declares what it needs — in the service package, not here
type Users interface {
	ByID(ctx context.Context, id int64) (User, error)
}

The split is deliberate: SQL, column names, and dialect live in repo; the service package declares a Users interface with only the methods it calls — the consumer-side interface rule (Interfaces) — and depends on that. The service compiles without database/sql in sight, and its tests pass a fake in five lines instead of spinning up Postgres (fakes behind interfaces).

What belongs in the repository: queries, row-to-struct mapping, ErrNoRows translation to domain errors (ErrNotFound), and wrapping with query context (fmt.Errorf("repo by id %d: %w", id, err)). What does not: HTTP concerns (status codes belong in handlers), business rules (service layer), or logging every query — return errors with context and let the one place at the top log them (error handling discipline).

Two lifecycle rules finish the boundary. The *sql.DB is created once in main, passed into NewUserRepo, and closed once during shutdown — repositories do not open or close pools; they borrow from one. And every method takes ctx as its first parameter, so callers control how long queries may run: handlers pass r.Context(), background jobs pass a timeout context. A repository method without context is a method that cannot be cancelled, and in a service under load that turns one slow query into a pile of them.

With queries parameterized (never concatenated), rows iterated completely, and SQL confined to its package, the data layer is correct in the small. The next topic attacks correctness in the hostile case — when the values in those parameters are attacker-controlled.