Select Queries

Everything about the shape of a SELECT, apart from conditions (Where Clauses), Joins and Subqueries, Unions and CTEs.

Examples show PostgreSQL SQL. ToSQL() returns (sql string, args []any, err error).

On this page: Columns · From · Grouping and having · Ordering · Limit and offset · Locks and index hints · Conditional building · Inspecting SQL

Columns

q := conn.Select("id", "name as n", "u.email").From("users")
// SELECT "id", "name" AS "n", "u"."email" FROM "users"

q.AddSelect("age")                                             // append a column
q.Distinct()                                                   // SELECT DISTINCT
q.SelectRaw("count(*) filter (where active) as active_count")  // raw expression
q.SelectSub(
    conn.Select().From("posts").SelectRaw("count(*)").WhereColumn("posts.user_id", "users.id"),
    "post_count",
)                                                              // subquery column

• With no columns, * is selected.
• conn.Select(cols...) and the builder's Select replace the list. AddSelect, SelectRaw and SelectSub append.
• "name as n" becomes an alias. "u.email" becomes a qualified column. Each part is quoted.

From

conn.Select().From("users")                 // FROM "users"
conn.Select().From("users as u")            // FROM "users" AS "u"
conn.Select().From("app.users")             // FROM "app"."users"
conn.Select().FromSub(sub, "t")             // FROM (SELECT ...) AS "t"
conn.Select().FromRaw("generate_series(?, ?) g", 1, 3)

conn.Select() gives a select builder with no table yet. Name one with From, FromSub or FromRaw, otherwise you get ErrNoTable.

To prefix table names, either set db.WithPrefix once on the DB, or call Prefix("p_") on a single builder.

Grouping and having

conn.Select().From("orders").
    Select("user_id").SelectRaw("sum(total) as s").
    GroupBy("user_id").
    HavingRaw("sum(total) > ?", 100)

Having("count", ">", 1)       // OrHaving
HavingNull("x")               // HavingNotNull, OrHavingNull, OrHavingNotNull
HavingBetween("n", 1, 5)      // HavingNotBetween
HavingGroup(func(q *db.HavingClause) { q.Having("a", 1).OrHavingRaw("b > ?", 2) })
GroupByRaw("date(created_at)")

Gotcha: use HavingRaw with the full expression, not an output alias. PostgreSQL does not allow aliases in HAVING.

Ordering

OrderBy("name")             // ascending
OrderBy("name", "desc")     // direction must be "asc" or "desc"
OrderByDesc("created_at")
OrderByRaw("random()")
Latest()                    // ORDER BY "created_at" DESC  (Latest("col") for another column)
Oldest("updated_at")
InRandomOrder()             // RANDOM() / RAND(); InRandomOrder(42) seeds MySQL
Reorder()                   // drop all ordering
ReorderBy("id", "desc")     // drop, then order

Any other direction returns ErrInvalidDirection.

Keyset helpers

ForPageAfterID(perPage, lastID, column...) and ForPageBeforeID(...) build keyset paging:

WHERE "id" > $1 ORDER BY "id" ASC LIMIT n

A nil last id selects IS NOT NULL. Existing conditions that contain an OR or a raw fragment are wrapped in parentheses first, so Where("a", 1).OrWhere("b", 2) becomes ("a" = $1 OR "b" = $2) AND "id" > $3.

Keyset paging on a query with Union returns ErrInvalidArgs, because the key condition would apply to the first member only. Wrap the union with FromSub and page the wrapper. For a full pagination guide see Pagination and Chunking.

Limit and offset

Limit(10)          // alias: Take(10)
Offset(20)         // alias: Skip(20)
ForPage(3, 15)     // OFFSET 30 LIMIT 15

• A negative limit is ignored. A negative offset becomes 0.
• An offset with no limit works on every dialect (MySQL and SQLite get the unbounded limit they require).

Locks and index hints

LockForUpdate()               // FOR UPDATE                      (PostgreSQL, MySQL)
SharedLock()                  // FOR SHARE / LOCK IN SHARE MODE
Lock("for no key update")     // caller-trusted raw clause
UseIndex("idx_a, idx_b")      // MySQL
ForceIndex("idx_a")           // MySQL; SQLite too
IgnoreIndex("idx_a")          // MySQL

• SQLite ignores row locks. PostgreSQL ignores index hints.
• Index names are emitted unquoted and validated against [a-zA-Z0-9_$]. Anything else is ErrInvalidArgs.
• Run locks inside a transaction.

Conditional building

When, Unless and Tap keep a chain readable when parts are optional:

q := conn.Select().From("users").
    When(onlyActive, func(q *db.SelectBuilder) { q.Where("active", true) }).
    Unless(includeDeleted, func(q *db.SelectBuilder) { q.WhereNull("deleted_at") }).
    Tap(func(q *db.SelectBuilder) { log.Println("building users query") })

When runs the callback if the condition is true. Unless runs it if false. Tap always runs it (handy for logging or debugging).

Inspecting SQL

q := conn.Select().From("users").Where("id", 7)

sql, args, err := q.ToSQL()      // statement with placeholders + ordered arguments
raw, err       := q.ToRawSQL()   // arguments interpolated. DEBUG ONLY
sql, args       = q.MustSQL()    // panics instead of returning the error
sql, args, err  = q.ExistsSQL()  // SELECT EXISTS(...) AS "exists"
sql, args, err  = q.AggregateSQL("sum", "total")

Warning: never execute or log ToRawSQL() output in production. It is not injection-safe and it contains the data. See Security.

The write builders have their own ToSQL(); see Writing Data.