Query, Scan and Rows

## The shape of a read in `database/sql` Go's standard library has no ORM and no row-mapping magic. Reading many rows is always the same four calls, and an interviewer asking this wants to see all four plus the reason each exists. ``` rows, err := db.QueryContext(ctx, "SELECT id, email FROM users WHERE org_id = ?", orgID) if err != nil { return nil, err } defer rows.Close() for rows.Next() { var id int64 var email string if err := rows.Scan(&id, &email); err != nil { return nil, err } // use id, email } return out, rows.Err() ``` ### `QueryContext` — the query, and a checked-out connection `(*sql.DB).QueryContext(ctx, query, args ...any) (*sql.Rows, error)` takes a connection from the pool, sends the statement, and hands you a cursor over the result. The returned error covers only *starting* the query: a syntax error, a dead connection, a cancelled context. Once it returns non-nil rows, a connection is **out of the pool and stays out** until the result set is finished or closed. That single fact explains most of the rules below. `ctx` matters: it is the query's lifetime. If the context is cancelled — a client disconnecting from an HTTP handler that passed `r.Context()`, say — the driver is told to cancel, the rows are closed, and iteration stops. ### `defer rows.Close()` — release the connection on every exit `(*sql.Rows).Close()` releases the connection back to the pool. It is idempotent and safe to call after the result set is already exhausted, which is precisely why the idiomatic form is a `defer` placed immediately after the error check. `Next` does auto-close the rows when it returns `false` at a clean end of data, so on the happy path the deferred `Close` is a no-op — but the happy path is not the one you are defending against. An early `return` from inside the loop, a `break`, or a panic all leave the cursor open, and without the `defer` that connection never goes back to the pool. Enough of those and every subsequent query blocks waiting for a free connection. Note the ordering trap: `defer rows.Close()` must come **after** `if err != nil { return }`. On an error `rows` may be nil, and deferring a method call on it before the check panics. ### `rows.Next()` — advance, and hide errors `(*sql.Rows).Next() bool` advances to the next row, fetching more from the server as needed, and returns `false` when there is nothing more to read. The word "nothing more" is doing dangerous work: `false` is returned both at the natural end of the result set **and** when iteration failed — a broken connection, a cancelled context, a driver-level error mid-stream. `Next` itself returns no error. A loop that treats `false` as "done" and returns whatever it accumulated will happily return a short answer with a nil error. ### `rows.Scan(dest ...any)` — pointers, in order `Scan` copies the current row's column values into the destinations you pass. Each destination must be a **pointer** — `&id`, not `id` — because `Scan` writes through it; passing a value gives you an error about an unsupported destination type. You must pass exactly as many destinations as the query selected columns, or `Scan` returns an error naming the mismatch. This is the reason to write out an explicit select list rather than `SELECT *`: with `*`, someone adding a column to the table breaks every `Scan` at runtime. `Scan` also converts: a driver's `int64` will land in an `*int`, numeric text will parse into a numeric destination, and so on, returning an error when the conversion is impossible. Declare the destination variables **inside** the loop body; reusing one variable declared outside is a classic source of stale values when a `Scan` error is skipped. ### `rows.Err()` — the check that makes the loop honest `(*sql.Rows).Err() error` reports the error that ended iteration, and `nil` if the loop ended because the data ran out. It is the only way to distinguish those two cases, and it must be checked after every `for rows.Next()` loop. `return out, rows.Err()` at the end of a helper is the compact idiom. ### The whole loop, judged A reviewer reads this code looking for: the error check before the defer; the defer itself; pointers in `Scan`; and the `rows.Err()` after the loop. Missing the first is a panic, missing the second is a leaked connection, missing the third is a compile-clean runtime error, and missing the fourth is a silent wrong answer — the worst of the four, because nothing ever tells you.