A *sql.DB is useful only when you can pull rows out of it. The database/sql package gives you three methods for that: QueryRow for a single row, Query for many rows, and Exec for statements that change data without returning rows. This chapter walks through the mechanics of each one, including the Scan calls that move column values into Go variables and the cleanup rules that keep connections from leaking.

db.QueryRow is the right call when the query returns at most one row, like a primary-key lookup. It takes a SQL string and any number of arguments, and it returns a *sql.Row. You then call Scan on that row to copy column values into Go variables.

The ? in the SQL string is a placeholder. The driver substitutes the second argument (1) for it at execution time, with proper escaping. Never build SQL by string concatenation: writing "SELECT ... WHERE id = " + userInput is how SQL injection happens. Placeholders sidestep that completely.

Scan takes one pointer per selected column, in the same order as the SELECT list. It writes the column value into the variable pointed at. If the column has the wrong type for the destination (a TEXT column scanned into a *int), Scan returns a conversion error rather than panicking.

The order matters. SELECT id, name, price, stock paired with Scan(&id, &name, &price, &stock) is fine; swapping &name and &price would produce a runtime error because the driver can't put "Notebook" into a *float64.

Handling ErrNoRows

When a QueryRow query matches zero rows, Scan returns sql.ErrNoRows. This is the only "expected error" in the package, and you should always check for it explicitly with errors.Is. Treating "no rows" as a generic failure usually leads to bad UX: a 500 page when a 404 was correct.

The function itself returns the error untouched, including sql.ErrNoRows. That's the conventional layering: data-access functions surface the standard error, and the caller decides whether "not found" means a 404, a default value, or a fatal bug.

When a query may return more than one row, use db.Query. It returns a *sql.Rows cursor that you walk with Next and read one row at a time with Scan. The lifecycle has four parts that must all show up: open, iterate, close, check final error.

db.Query itself returns an error: a malformed SQL string or a closed pool surfaces here, not later. Check it before doing anything else. defer rows.Close() runs even if the loop exits early (a return, a break, a panic). Without it, the underlying connection stays checked out of the pool, and after enough leaks the pool runs out. rows.Scan inside the loop reads the current row's columns into the destination pointers; declare a fresh struct each iteration to avoid accidentally sharing state. rows.Err() after the loop reports errors that ended iteration prematurely, like a dropped network connection. rows.Next() returning false doesn't tell you whether iteration finished normally or failed; you need rows.Err() to find out.

The Query Lifecycle

The flow diagram below shows what happens between the moment you call Query and the moment all resources are released. Each step has its own failure path, and skipping any of them leaves the pool in a worse state than when you started.

Next advances the cursor and returns true if there's a row ready. It returns false when the rows are exhausted or when something went wrong. That's why rows.Err() exists: it disambiguates "we finished" from "we crashed". And Close is what hands the connection back to the pool; without it, the connection sits idle in your code's hands even after your function returned.

The example above used a Product struct. That's the common pattern: declare a struct whose fields match the columns, then scan one row into one struct per iteration. The mapping isn't automatic in the standard library, you have to pass each field pointer to Scan yourself. A third-party package can do the mapping by struct tag; for everything else you write the Scan call out.

The function returns ([]Product, error), which is the idiomatic shape for "this might fail". The slice is built with append, starting from nil; callers that get back a nil slice can range over it just fine. Errors are returned at every layer, including from rows.Err() after the loop. A common mistake is to return out along with rows.Err(), which produces a partial slice plus an error; whether that's acceptable depends on the use case. Returning nil, err keeps the contract clean in most situations.

The column order in SELECT must match the argument order in Scan. Some teams write SELECT * for brevity, but that's a bug factory: a column added to the table changes the order silently, and the next time someone runs the code, scans land in the wrong fields. Listing columns explicitly is part of the contract between the SQL and the Go code.

Scanning Nullable Columns

Database NULL doesn't map cleanly to Go's regular types. If a column can be NULL and you scan it into a plain string, Scan returns an error. The standard library ships sql.NullString, sql.NullInt64, and friends, which carry both a value and a Valid flag.

email.Valid is true when the column had a value, false when it was NULL. The email.String field carries the actual text in the valid case, or the zero value when invalid.

Placeholders are how arguments get safely substituted into SQL. Each driver picks its own syntax, but the database/sql API is the same: pass the value as an argument to Query, QueryRow, or Exec, and the driver handles the rest.

The same Go code works with any driver as long as you write the placeholder syntax that matches the chosen driver. Switching from SQLite to PostgreSQL is mostly a search-and-replace from ? to $1, $2, and renumbering. Tools like sqlx and query builders that abstract over both exist, but the standard library leaves it to you.

Note that PostgreSQL placeholders are numbered, so the same argument can be referenced more than once (WHERE name = $1 OR alias = $1 reuses $1), while ? placeholders are positional and need the value passed once per occurrence.

The driver's job is to send the values separately from the SQL text. The database parses the SQL once and treats the values as data, never as code. That's what makes parameterized queries injection-safe: even if the user passes "1; DROP TABLE products" as the argument, the database sees one string, not two statements.

When a statement modifies data but doesn't return rows, use db.Exec. It runs the SQL and returns a sql.Result carrying two useful numbers: the ID of the row just inserted (when applicable) and the count of rows affected.

LastInsertId returns the auto-generated primary key from the most recent INSERT, but only on drivers that support it (SQLite and MySQL do, PostgreSQL doesn't, you use RETURNING id and QueryRow there instead). RowsAffected returns how many rows the statement touched. For the DELETE of a non-existent id, that count is zero, which is how you detect "you tried to delete something that wasn't there" without raising an error.

Both methods on sql.Result return an error because not every driver supports them. In practice you check the error once and move on. If you're writing portable code, plan for LastInsertId returning ErrNoLastInsertID on PostgreSQL.

Comparing QueryRow, Query, and Exec

The three methods cover the full surface of the database/sql API for data access. The choice is mechanical once you know the shape of the result.

QueryRow is a convenience wrapper: internally it calls Query and then reads one row in Scan. The wrapper closes the underlying Rows for you, which is why you don't see a Close call in QueryRow code. A Query that returns zero rows is fine, the loop simply doesn't enter; only QueryRow raises sql.ErrNoRows when nothing matches.

Five mistakes show up repeatedly in database/sql code. They all compile, and most work fine until the system is under real load. Spotting them in code review is faster than debugging them later.

1. Forgetting rows.Close

The function returns, but the rows object still holds a connection. After enough calls, the pool is empty and every subsequent query blocks until one of the leaked connections eventually times out. In tests with a small pool this surfaces as deadlock; in production it surfaces as latency spikes that vanish after a restart.

The fix is one line: defer rows.Close() immediately after the error check on db.Query.

2. Forgetting rows.Err

Next returns false for two different reasons: rows are exhausted, or the cursor errored out mid-iteration (lost network, server crash, query timeout). Without rows.Err(), the loop silently stops on partial data and the caller thinks the result was complete. Always check it after the loop:

3. Scanning the Wrong Number of Columns

Scan returns an error like sql.Scan: expected 3 destination arguments in Scan, not 2. It doesn't panic, but the loop body returns early on the first row, so the function gives up before reading anything. Reviewers should pair the SELECT list and the Scan argument list line by line.

4. Type Mismatches

Scan returns a conversion error because a float can't fit losslessly into an int. Always match the SQL column types to Go types: INTEGER to int64 or int, REAL to float64, TEXT to string, BLOB to []byte. Numeric narrowing (a BIGINT into an int32) returns an overflow error if the value doesn't fit.

5. Reusing a Single Struct Across Iterations

The loop scans new values into the same p every iteration, then appends a pointer to that same p to the slice. By the end, every pointer aliases the last row. The fix is to declare var p Product inside the loop body so each iteration has its own variable, or to append values (append(products, p)) instead of pointers. This bug is easy to spot once you know about it, easy to miss in a quick read.

A query that runs against a real database can hang. The network might be slow, the server might be overloaded, the client might have disconnected and nobody noticed. QueryContext and QueryRowContext take a context.Context as their first argument, and they cancel the in-flight query when the context is cancelled or hits its deadline.

The context here has a 500ms deadline. For a fast in-memory lookup the query finishes well inside that window, so the deadline never fires. In production code you'd use a tighter deadline for user-facing reads (50-200ms) and a looser one for batch jobs (a few seconds). When the deadline does fire, the database driver receives a cancellation signal and the query returns context.DeadlineExceeded instead of dragging on forever.

The *Context methods are the recommended default for any handler that already has a context.Context in scope, which in practice means every HTTP handler, every gRPC handler, and every worker that reads from a queue. Plain db.Query and db.QueryRow use context.Background() internally, which never cancels; on a server, that means a slow query keeps a goroutine and a connection alive even after the client gave up.

ExecContext exists for the same reason. Use it for any INSERT/UPDATE/DELETE in a request path, with the request's context wired in. A multi-second UPDATE on a hot table can pile up if the client retried while the original was still running; cancellation cuts that off.

A short end-to-end example puts the pieces in one place: open the pool, populate the table, count orders for a customer with QueryRow, list products under a price with Query, and update stock with Exec. The whole program runs against an in-memory SQLite database, so it's fully self-contained.

Every external call in this program has an error check. Every multi-row read has defer rows.Close() and rows.Err(). Every query uses placeholders for arguments. Every context-aware call carries a deadline. None of these are optional in production code; they're the baseline that keeps a service stable.