Querying
QueryRow for one row, Query for many, Scan to read the columns, and the rules for closing rows and handling NULL.
rows, err := db.QueryContext(ctx, query string, args ...any) (*sql.Rows, error)Reading data has two shapes, and picking the wrong one is the usual source of trouble.
One row
db.QueryRowContext returns a *sql.Row. You call Scan on it with a pointer per selected column, and Scan copies the values into them. If no row matched, Scan returns sql.ErrNoRows. That is not a failure of your program, it is an answer, so test for it with errors.Is and turn it into whatever your code calls "not found". Row cleans up after itself; there is nothing to close.
Many rows
db.QueryContext returns *sql.Rows, a cursor over the result. You call Next to move to each row, then Scan to read it. Two steps are easy to forget. First, defer rows.Close(): those rows hold a connection from the pool until they are closed, and an early return inside the loop leaves it held. Next returning false closes them for you, but only if the loop runs to the end. Second, check rows.Err() after the loop. Next returns false both when the rows are finished and when something broke, and Err is the only way to tell those apart.
NULL
A SQL NULL cannot be scanned into a string or an int. Scan one of the nullable types instead: sql.NullString, which has fields String and Valid, or the generic sql.Null[T] added in Go 1.22. A pointer such as *string also works and reads better: Scan sets it to nil for NULL.
Shorthand
db.Query(q, args...) is exactly db.QueryContext(context.Background(), q, args...).
Example
package main
import (
"database/sql"
"fmt"
"log"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.SetMaxOpenConns(1) // one connection, so every statement sees the same in-memory database
if _, err := db.Exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"); err != nil {
log.Fatal(err)
}
if _, err := db.Exec("INSERT INTO users VALUES (1, 'ada', '[email protected]'), (2, 'bo', NULL)"); err != nil {
log.Fatal(err)
}
// ORDER BY makes the output stable; a query without it has no promised order.
rows, err := db.Query("SELECT name, email FROM users ORDER BY id")
if err != nil {
log.Fatal(err)
}
defer rows.Close() // frees the connection even if this function returns early
for rows.Next() {
var name string
var email *string // nil when the column holds NULL
if err := rows.Scan(&name, &email); err != nil {
log.Fatal(err)
}
if email == nil {
fmt.Println(name, "has no email")
continue
}
fmt.Println(name, "->", *email)
}
if err := rows.Err(); err != nil { // the loop can also stop because of an error
log.Fatal(err)
}
}
// Output:
// ada -> [email protected]
// bo has no emailWhen to use it
- Load one record by id and return 404 when the row does not exist.
- Build a list endpoint that reads many rows without leaking pool connections.
- Read a table where optional columns are NULL without crashing on a conversion error.
More examples
One row, with not-found as its own error
An HTTP handler needs to tell 404 from 500, and sql.ErrNoRows is how the database says 404. Translating it at the storage boundary keeps database/sql out of the rest of the program.
package storage
import (
"context"
"database/sql"
"errors"
"fmt"
)
// ErrNotFound lets callers handle a missing row without importing database/sql.
var ErrNotFound = errors.New("not found")
type User struct {
ID int64
Name string
}
func (s *Store) User(ctx context.Context, id int64) (User, error) {
var u User
// Name the columns, and list the Scan targets in the same order.
err := s.db.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = $1", id).Scan(&u.ID, &u.Name)
if errors.Is(err, sql.ErrNoRows) {
return User{}, ErrNotFound // an answer, not a broken query
}
if err != nil {
return User{}, fmt.Errorf("select user %d: %w", id, err)
}
return u, nil
}Collecting rows into a slice
The complete shape of a list query: deferred Close, an error return inside the loop, and rows.Err after it. Missing the last line is how a service silently returns half a page of results.
package storage
import (
"context"
"fmt"
)
func (s *Store) UserNames(ctx context.Context, limit int) ([]string, error) {
rows, err := s.db.QueryContext(ctx, "SELECT name FROM users ORDER BY id LIMIT $1", limit)
if err != nil {
return nil, fmt.Errorf("select names: %w", err)
}
defer rows.Close()
var names []string
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
return nil, fmt.Errorf("scan name: %w", err) // the deferred Close still runs
}
names = append(names, name)
}
// Without this check, a connection dropped halfway looks like a short list.
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("iterate names: %w", err)
}
return names, nil
}Nullable columns three ways
Reach for this when a column is declared without NOT NULL. Scanning NULL straight into a string fails with a conversion error at runtime, which is why an optional column needs one of these three types.
package storage
import (
"context"
"database/sql"
"fmt"
)
func (s *Store) Profile(ctx context.Context, id int64) error {
var nickname sql.NullString // .String and .Valid
var age sql.Null[int64] // Go 1.22 and later: .V and .Valid
var bio *string // nil for NULL
err := s.db.QueryRowContext(ctx,
"SELECT nickname, age, bio FROM profiles WHERE user_id = $1", id,
).Scan(&nickname, &age, &bio)
if err != nil {
return err
}
if nickname.Valid {
fmt.Println("nickname:", nickname.String)
}
if age.Valid {
fmt.Println("age:", age.V)
}
if bio != nil {
fmt.Println("bio:", *bio)
}
return nil
}
Discussion