Reading Data

These methods need a DB created with db.New(conn, dialect). All take a context.Context first, honour cancellation, and always close their rows.

The readers live on *db.SelectBuilder. Write builders have their own executors (Exec, and Get for RETURNING), see Writing Data.

On this page: Reading rows · How rows are scanned · Aggregates · Raw queries

Reading rows

The destination is a pointer. Its type decides how rows are read.

var users []User
err := conn.Select().From("users").Where("active", true).OrderBy("id").Get(ctx, &users)   // every row

var u User
err = conn.Select().From("users").Where("email", e).First(ctx, &u)   // first row; db.ErrNotFound if none
err = conn.Select().From("users").Find(ctx, 7, &u)                   // WHERE "id" = 7, then First
err = conn.Select().From("users").Where("email", e).Sole(ctx, &u)    // exactly one: ErrNotFound or ErrMultipleRecords

Method	Destination	Result
Get(ctx, &dest)	*[]T	Every row
First(ctx, &dest)	*T	First row, ErrNotFound if none
Find(ctx, id, &dest)	*T	The row whose id is id
Sole(ctx, &dest)	*T	The only row. ErrMultipleRecords for several
Value(ctx, "col", &dest)	*scalar	One column of the first row
Pluck(ctx, "col", &dest)	*[]scalar	One column of every row
PluckMap(ctx, "col", "key", &dest)	*map[K]V	key -> col
Exists(ctx) / DoesntExist(ctx)	none	bool

var name string
err := conn.Select().From("users").Where("id", 2).Value(ctx, "name", &name)

var names []string
err = conn.Select().From("users").OrderBy("name").Pluck(ctx, "name", &names)

var byID map[int64]string
err = conn.Select().From("users").PluckMap(ctx, "name", "id", &byID)

First, Find, Sole and Value add LIMIT 1 (or 2 for Sole) on a copy. Your builder is unchanged.

Prefer returned values over destination pointers? See the Typed API.

How rows are scanned

Destination types

Element type	Result
struct, *struct	Columns map onto fields
map[string]any	One entry per column. []byte becomes string, NULL stays nil
scalar (int, string, time.Time, ...)	The query must return exactly one column

An empty result gives an empty, non-nil slice.

Struct field matching

• A field matches its db:"name" tag, otherwise the snake_case of its name (UserName is user_name, HTTPServer is http_server). Matching falls back to case-insensitive.
• db:"-" ignores a field. Unexported fields are ignored. Columns with no field are ignored.
• Embedded structs are flattened. An outer field wins over an embedded one of the same name.
• sql.Scanner types (sql.NullString, time.Time, your own) are scanned as one value.

See Struct Mapping for tags and writes.

NULL handling

• Into a pointer or a sql.Null* field, you can tell NULL apart.
• Into a plain field (string, int, bool, ...), NULL leaves the zero value instead of failing, so a nullable column never aborts a scan.

type User struct {
    ID    int64
    Email *string        // nil when NULL
    Bio   string         // "" when NULL
    Nick  sql.NullString // Valid=false when NULL
}

Aggregates

n, err   := conn.Select().From("users").Count(ctx)               // count(*)
n, err    = conn.Select().From("users").Count(ctx, "age")         // count("age"): skips NULLs
sum, err := conn.Select().From("orders").Sum(ctx, "total")        // float64, 0 when no rows
avg, err := conn.Select().From("orders").Avg(ctx, "total")

var oldest time.Time
err = conn.Select().From("users").Min(ctx, "created_at", &oldest) // Max likewise
err = conn.Select().From("orders").Aggregate(ctx, "sum", "total", &f)   // any function

• Ordering is dropped from the aggregate.
• Queries with GROUP BY, HAVING, UNION, DISTINCT, LIMIT or OFFSET are wrapped so the aggregate covers the final row set:

SELECT count(*) AS "aggregate" FROM (SELECT "user_id" FROM "posts" GROUP BY "user_id") AS "aggregate_table"

• Strict servers (PostgreSQL, MySQL with ONLY_FULL_GROUP_BY) need the grouped column selected: conn.Select("user_id").From("posts").GroupBy("user_id").Count(ctx).
• Min, Max and Sum over no rows give NULL, which leaves a plain destination at its zero value.

Raw queries

For SQL the builder cannot express, use your dialect's placeholders:

var rows []map[string]any
err := conn.Query(ctx, &rows, "select * from users where age > $1", 30)

n, err := conn.Exec(ctx, "update users set active = $1 where last_seen < $2", false, cutoff)

Query scans like Get. Exec returns rows affected. Raw text is never escaped, see Raw SQL.