Queries

The shape#

users, err := db.Users.Query().
    Where(Users.Active.Eq(true)).
    OrderBy(Users.CreatedAt.Desc()).
    Limit(50).
    All(ctx)

A Query is mutable and single-use. Clone branches one when you want a base:

base := db.Users.Query().Where(Users.Active.Eq(true))
recent := base.Clone().Where(Users.CreatedAt.Gte(cutoff))
count, _ := base.Clone().Count(ctx)

Terminals#

Method	Returns
`All(ctx)`	`[]E`
`One(ctx)`	`E`, or `ErrNotFound`
`Count(ctx)`	`int64`
`Exists(ctx)`	`bool`
`Rows(ctx)`	`iter.Seq2[E, error]` — streams
`SQL()`	the statement and its arguments, without running it

Builder mistakes accumulate and surface together from the terminal, so a query that cannot be built never reaches PostgreSQL:

_, err := db.Users.Query().Where(broken).OrderBy(alsoBroken).All(ctx)
// err reports both, not just the first

Where#

Multiple Where calls are ANDed. That is the common case and it keeps dynamic filtering readable:

q := db.Users.Query()
if email != "" {
    q = q.Where(Users.Email.ILike("%" + email + "%"))
}
if onlyActive {
    q = q.Where(Users.Active.Eq(true))
}
users, err := q.All(ctx)

For OR, combine explicitly:

db.Users.Query().Where(orm.Or(
    Users.Email.Eq("a@example.com"),
    Users.Email.Eq("b@example.com"),
))

orm.And() over an empty slice produces a query with no WHERE at all rather than WHERE TRUE.

Ordering and paging#

db.Users.Query().
    OrderBy(Users.CreatedAt.Desc(), Users.ID.Asc()).
    Limit(20).
    Offset(40)

Limit(0) is a legal query that returns nothing. Only a negative value is a mistake.

Keyset paging beats OFFSET on large tables, and the typed API expresses it directly:

db.Users.Query().
    Where(orm.Or(
        Users.CreatedAt.Lt(lastSeenAt),
        orm.And(Users.CreatedAt.Eq(lastSeenAt), Users.ID.Lt(lastSeenID)),
    )).
    OrderBy(Users.CreatedAt.Desc(), Users.ID.Desc()).
    Limit(20)

Streaming#

Rows yields as the server sends, so a large result never all exists at once:

for user, err := range db.Users.Query().Rows(ctx) {
    if err != nil {
        return err
    }
    if err := handle(user); err != nil {
        return err
    }
}

A relation needing a statement of its own would have to see every row before it could run — which is the one thing streaming exists to avoid — so Rows refuses With rather than quietly buffering.

Locking#

db.Users.Query().Where(Users.ID.Eq(id)).ForUpdate()
db.Users.Query().Lock(orm.ForUpdateStrong, orm.SkipLocked())
db.Users.Query().Lock(orm.ForShare, orm.NoWait())

Locking the nullable side of an outer join is something PostgreSQL refuses, so when the statement has joins the lock names the root table explicitly.

Seeing the SQL#

sql, args, err := db.Users.Query().Where(Users.Active.Eq(true)).SQL()
// SELECT "users"."id", ... FROM "public"."users" WHERE "users"."active" = $1
// args: [true]

Values are never in the SQL. Every one is a bind parameter, including the ones inside Expr fragments.

Worked examples#

Three different schemas, because a filter reads differently depending on what it is filtering.

A parcel tracker#

Shipments that left a warehouse but have not arrived, oldest first — the queue a dispatcher works through.

stuck, err := db.Shipments.Query().
    Where(Shipments.DepartedAt.IsNotNull()).
    Where(Shipments.ArrivedAt.IsNull()).
    Where(Shipments.DepartedAt.Lt(time.Now().Add(-48*time.Hour))).
    OrderBy(Shipments.DepartedAt.Asc()).
    Limit(100).
    All(ctx)

The two NULL checks are the whole query: departed is set, arrived is not. On a nullable column that reads as plainly as the sentence, and on a NOT NULL one neither method exists to be misused.

A ledger#

The last statement line for one account, and whether there are any at all.

latest, err := db.Entries.Query().
    Where(Entries.AccountID.Eq(accountID)).
    OrderBy(Entries.PostedAt.Desc(), Entries.ID.Desc()).
    One(ctx)
if errors.Is(err, orm.ErrNotFound) {
    // a new account, not a broken one
}

any, err := db.Entries.Query().Where(Entries.AccountID.Eq(accountID)).Exists(ctx)

One returning ErrNotFound is a normal answer to a normal question. Exists selects a constant rather than a row, so asking costs no decoding.

A telemetry table#

Readings from a set of devices, in a window, streamed because there are too many to hold.

for reading, err := range db.Readings.Query().
    Where(Readings.DeviceID.In(deviceIDs...)).
    Where(Readings.At.Between(from, to)).
    OrderBy(Readings.At.Asc()).
    Rows(ctx) {
    if err != nil {
        return err
    }
    if err := accumulate(reading); err != nil {
        return err
    }
}

Rows yields as the server sends. The whole window never exists in memory at once, which is the difference between a report that runs and one that is killed.