Week 12: Working with Databases: database/sql & Migrations

1. Connection Pools & database/sql

database/sql is a generic interface — it works with any database that has a driver implementing it (Postgres, MySQL, SQLite). sql.Open doesn't actually connect immediately; it configures a connection pool that connects lazily, on first real use.

go get github.com/lib/pq  # a Postgres driver

db, err := sql.Open("postgres", "postgres://user:pass@localhost/mydb?sslmode=disable")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

if err := db.Ping(); err != nil { // this is what actually connects
    log.Fatal(err)
}

db.Ping() is the idiomatic way to fail fast at startup if the database is unreachable, rather than discovering it on the first real request.

2. Scanning Rows into Structs & sql.ErrNoRows

func GetTask(db *sql.DB, id int) (*Task, error) {
    var t Task
    err := db.QueryRow(
        "SELECT id, title, done FROM tasks WHERE id = $1", id,
    ).Scan(&t.ID, &t.Title, &t.Done)

    if errors.Is(err, sql.ErrNoRows) {
        return nil, fmt.Errorf("task %d: %w", id, ErrNotFound)
    }
    if err != nil {
        return nil, fmt.Errorf("querying task %d: %w", id, err)
    }
    return &t, nil
}

sql.ErrNoRows isn't a real error condition — it's how database/sql reports "the query succeeded, it just matched nothing," which every call site needs to check for explicitly (using Week 13's errors.Is) and translate into whatever the application's own not-found semantics are, exactly like ErrNotFound above feeding directly into Week 10's 404 handling.

func ListTasks(db *sql.DB) ([]Task, error) {
    rows, err := db.Query("SELECT id, title, done FROM tasks ORDER BY id")
    if err != nil {
        return nil, err
    }
    defer rows.Close() // always — a leaked rows.Rows leaks a connection

    var tasks []Task
    for rows.Next() {
        var t Task
        if err := rows.Scan(&t.ID, &t.Title, &t.Done); err != nil {
            return nil, err
        }
        tasks = append(tasks, t)
    }
    return tasks, rows.Err() // check for an error that occurred during iteration
}

3. Prepared Statements

Every query above already uses $1/parameter placeholders rather than building SQL with string concatenation — this isn't just style, it's the difference between safe and SQL-injectable code.

// DANGEROUS — never build SQL by concatenating user input
query := "SELECT * FROM tasks WHERE title = '" + userInput + "'"

db.QueryRow("SELECT * FROM tasks WHERE title = $1", userInput)

Every db.Query/QueryRow/Exec call with placeholders is already a prepared statement under the hood, sent as parameters separately from the query text — the database engine itself, not string interpolation, is what safely substitutes the value, which is exactly what makes user-controlled input impossible to misinterpret as SQL syntax.

4. Schema Migrations

A schema needs to evolve alongside code, in a way every environment (a teammate's laptop, staging, production) can apply identically and in order — hand-run SQL scripts don't scale past a single developer.

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
migrate create -ext sql -dir migrations -seq add_tasks_table

CREATE TABLE tasks (
    id    SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    done  BOOLEAN NOT NULL DEFAULT FALSE
);

DROP TABLE tasks;

migrate -database "postgres://user:pass@localhost/mydb?sslmode=disable" -path migrations up

Every migration ships as a numbered pair — up to apply it, down to reverse it — committed to the repository alongside the code that depends on it, so the schema's history is versioned exactly like everything else Week 8 already put under go.mod.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.