Day 092 — database/sql Basics

Month 4 · Week 2 · ⬅ Day 091 · Day 093 ➡ · Journal index

🎯 Learning Objective

Use the standard database/sql package correctly: open a pooled *sql.DB, run context-aware queries with placeholders, and scan rows without leaking connections.

📚 Topics

• sql.Open vs Ping; drivers and blank imports
• *sql.DB as a connection pool; QueryContext/QueryRowContext/ExecContext
• rows.Scan, rows.Err, sql.ErrNoRows, NULL handling, placeholder dialects

📖 Reading / Sources

• database/sql package docs
• Go wiki — SQLDrivers
• Tutorial — Accessing a relational database
• Jason Moiron — "Go database/sql tutorial"

📝 Notes

• database/sql is a database-agnostic interface, not a driver. You import a driver for its side effect to register it: import _ "github.com/jackc/pgx/v5/stdlib". The blank _ runs the driver's init() which calls sql.Register. See [[sql-drivers]].
• sql.Open(driver, dsn) does not connect — it just validates arguments and returns a *sql.DB. The first real connection happens lazily on first use. Verify connectivity eagerly with db.PingContext(ctx).
• A *sql.DB is a pool of connections, safe for concurrent use, meant to be long-lived. Open it once at startup and pass it around; never sql.Open per request → [[connection-pooling]].
• Always use the context variants (QueryContext, QueryRowContext, ExecContext). Context-first is the convention and lets a slow query be cancelled by a request timeout → [[context-propagation]].
• Iterating rows has a fixed shape: defer rows.Close(), loop for rows.Next() { rows.Scan(&...) }, then check rows.Err() after the loop (a network error ends the loop with Next()==false, and only rows.Err() reveals it).
• QueryRowContext(...).Scan(...) returns sql.ErrNoRows when nothing matched — match it with errors.Is.
• Placeholders are driver-specific: Postgres uses $1, $2, MySQL/SQLite use ?. Pass args as parameters — never fmt.Sprintf values into SQL, which is a SQL-injection hole.
• NULLs don't scan into plain string/int. Use sql.NullString, sql.NullInt64, the generic sql.Null[T] (Go 1.22), or a pointer (*string).
• Exec returns a sql.Result: LastInsertId() (not supported by Postgres — use RETURNING id + QueryRow instead) and RowsAffected().

💻 Code Examples

database/sql needs a real driver (third-party), so this is a snippet, not a runnable repo example. The pooling concept is modelled stdlib-only in examples/month-04/pool.

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

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

func openDB(ctx context.Context, dsn string) (*sql.DB, error) {
    db, err := sql.Open("pgx", dsn) // does NOT connect yet
    if err != nil {
        return nil, err
    }
    // Pool tuning — sensible defaults are unbounded MaxOpenConns, which can melt
    // a database under load. Always cap it.
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(time.Hour)

    pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
    defer cancel()
    if err := db.PingContext(pingCtx); err != nil { // force a real connection
        return nil, err
    }
    return db, nil
}

func userEmail(ctx context.Context, db *sql.DB, id int) (string, error) {
    var email string
    // $1 is the Postgres placeholder; args go as parameters, never concatenated.
    err := db.QueryRowContext(ctx, `SELECT email FROM users WHERE id = $1`, id).Scan(&email)
    if errors.Is(err, sql.ErrNoRows) {
        return "", errors.New("user not found")
    }
    return email, err
}

🏋️ Exercises / Practice

Exercise	Status	Link
Repository over an in-memory store (mirrors *sql.DB access)	✅	exercises/month-04/week-2/inmemrepo

🐛 Mistakes Made

• Assumed sql.Open connects — my bad DSN only failed later at first query. Added an eager PingContext.
• Forgot rows.Err() after the loop; a truncated result set looked "successful". Always check it.
• Used ? placeholders against Postgres and got a syntax error — placeholder dialect is per-driver.

❓ Open Questions

• When is a Stmt (prepared statement) worth it vs passing args to QueryContext each time? (Per-statement prepare cost vs reuse; the pool re-prepares across connections.)

🧠 Active Recall (answer without looking)

1. Q: Does sql.Open establish a connection?

A

No — it validates arguments and returns a `*sql.DB`. The first connection is lazy; call `db.PingContext` to connect eagerly.

1. Q: After a rows.Next() loop ends, what must you still check and why?

A

`rows.Err()` — `Next()` returns `false` both at normal end-of-rows AND on an error mid-iteration; only `rows.Err()` distinguishes them.

🪶 Feynman Reflection

database/sql is a universal socket: it defines what a query looks like, and a driver plugs the specific database in behind it. The *sql.DB you get back isn't one connection — it's a self-managing pool. Your job is to feed it a context, use parameter placeholders (never string-built SQL), scan each row, and close the rows.

🕳️ Knowledge Gaps

• Prepared-statement lifecycle across pooled connections — revisit with pgx tomorrow.

✅ Summary

I can open a pooled *sql.DB, ping it, run context-aware parameterised queries, scan rows safely (including rows.Err() and sql.ErrNoRows), and handle NULLs.

⏭️ Next Steps / Prep for Tomorrow

• Day 093: pgx, the native Postgres driver, and pgxpool connection pooling.

---

Time spent	Difficulty	Confidence
90 min	🟦🟦⬜⬜⬜	🟦🟦🟦⬜⬜

Suggested commit: docs(journal): database/sql basics (day 092)