The query front-end

The query package is LiteORM’s explicit, generics-first query builder. You write query.Select[T](sess), chain typed predicates and clauses, and finish with a terminal that runs the SQL and scans rows straight into your struct T. The same code runs against a *liteorm.DB or a transaction, because both satisfy the liteorm.Session interface — see transactions.

If you prefer a declarative, model-driven style with associations, hooks, and migrations, see the orm front-end. The two share one core and interoperate on a single transaction, so picking one here does not lock you out of the other.

For exhaustive API detail, see the reference at pkg.go.dev/liteorm.org/query.

Every query needs a session. Open one with a dialect package; SQLite is the simplest:

import (
  "liteorm.org/dialect/sqlite"
  "liteorm.org/query"
)

db, err := sqlite.Open("app.db")
if err != nil {
  return err
}
defer db.Close()

Other backends open the same way and return the same *liteorm.DB, so nothing below changes when you switch: postgres.Open(ctx, dsn), mysql.Open(ctx, dsn), mssql.Open(ctx, dsn). See getting started for the full setup.

A model is a plain struct. The table name comes from a TableName() string method if you define one, otherwise it’s the snake_case of the type name — singular by default, or pluralized if you opt in with orm.UsePluralTableNames(true) (both front-ends share that one setting).

type Product struct {
  ID       int64
  Name     string
  Category string
  Price    float64
  Stock    int64
  Active   bool
}

func (Product) TableName() string { return "products" }

query.Select[T](sess) starts a typed SELECT over T. Call .All(ctx) to get every matching row as []T:

products, err := query.Select[Product](db).All(ctx)

The heart of the builder is Filter, which takes one or more typed, column-validated predicates. Build a predicate from a typed column token — query.Col[V]("name") — and an operator. The value type V is checked at compile time, and the column name is validated against your model’s schema when the query runs (an unknown column is a clear error, never silent SQL).

hot, err := query.Select[Product](db).
  Filter(
    query.Col[string]("category").Eq("electronics"),
    query.Col[float64]("price").Gt(50),
  ).
  OrderBy("price DESC").
  All(ctx)

Multiple predicates passed to Filter (or stacked across several Filter calls) are joined with AND.

Filter’s top-level AND covers the common case. For anything richer, compose explicitly with query.And, query.Or, and query.Not, which nest to any depth:

mixed, err := query.Select[Product](db).
  Filter(query.Or(
    query.Col[string]("category").In("books", "home"),
    query.Col[string]("name").Like("%Pro%"),
  )).
  OrderBy("name").
  All(ctx)

query.Not(p) negates a single predicate.

Order takes typed terms — query.Asc(col) / query.Desc(col) — each validated against the model and quoted by the dialect. OrderBy is the raw-string escape hatch (so you control collation, functions, NULLS FIRST); the two compose in call order. Limit and Offset page the result.

page, err := query.Select[Product](db).
  Filter(query.Col[bool]("active").Eq(true)).
  Order(query.Desc(query.Col[float64]("price")), query.Asc(query.Col[string]("name"))).
  Limit(20).
  Offset(40).
  All(ctx)

// raw escape hatch (e.g. a collation or a NULLS ordering the typed form can't express):
recent, _ := query.Select[Product](db).OrderBy("created_at DESC NULLS LAST").All(ctx)

Three terminals beyond .All:

• .First(ctx) returns the first matching row, or liteorm.ErrNoRows if there are none. It applies LIMIT 1 for you.
• .Count(ctx) returns the matching row count as int64, ignoring order/limit/offset.
• .Exists(ctx) returns a bool.

cheapest, err := query.Select[Product](db).
  Filter(query.Col[int64]("stock").Gt(0)).
  OrderBy("price").
  First(ctx)
if errors.Is(err, liteorm.ErrNoRows) {
  // nothing in stock
}

inStock, _ := query.Select[Product](db).Filter(query.Col[int64]("stock").Gt(0)).Count(ctx)
anyInactive, _ := query.Select[Product](db).Filter(query.Col[bool]("active").Eq(false)).Exists(ctx)

When a result set is large and you’d rather not hold it all in memory, use .Iter(ctx). It returns an iter.Seq2[T, error] you range over directly; rows are scanned lazily and the underlying rows are closed when you stop — including an early break.

n := 0
for p, err := range query.Select[Product](db).OrderBy("price").Iter(ctx) {
  if err != nil {
    return err
  }
  fmt.Println(p.Name, p.Price)
  if n++; n == 3 {
    break // streaming stops early; rows are closed
  }
}

Distinct() adds SELECT DISTINCT. GroupByCols(cols...) groups by typed, validated, dialect-quoted columns (GroupBy(cols...) is the raw-string escape hatch); Having(frag, args...) adds a raw, AND-joined HAVING condition (the frag carries positional ? markers bound by args).

rows, err := query.Select[Product](db).
  Distinct().
  GroupByCols(query.Col[string]("category").Field()).
  Having("count(*) > ?", 2).
  All(ctx)

For a whole-set aggregate, the typed terminals build SELECT AGG(col) over your filters and return the scalar (a result over no rows comes back as the zero value, not an error):

revenue, _ := query.Sum(ctx, query.Select[Order](db).Filter(paid), query.Col[int64]("total"))
avgPrice, _ := query.Avg(ctx, query.Select[Product](db), query.Col[float64]("price")) // returns float64
cheapest, _ := query.Min(ctx, query.Select[Product](db), query.Col[float64]("price"))

Sum/Min/Max return the column’s type, Avg returns float64, and CountCol returns int64. (.Count(ctx) remains the row-count terminal.)

To pull a single column into a slice — the “give me all the emails / ids” read — use Pluck, which projects one typed column and scans the values directly (no full-row structs):

emails, _ := query.Pluck(ctx, query.Select[User](db).Filter(active), query.Col[string]("email"))
// emails is []string, honoring the builder's filters/order/limit

For a grouped aggregate, project the grouped columns and the aggregate expressions into a result struct with Into — column-validated and dialect-quoted, the typed counterpart of Raw:

type byCategory struct {
  Category string `db:"category"`
  Revenue  int64  `db:"revenue"`
}
stats, err := query.Into[Product, byCategory](ctx,
  query.Select[Product](db).GroupByCols(query.Col[string]("category").Field()),
  query.Col[string]("category").Field(),
  query.SumAs(query.Col[float64]("price"), "revenue"))

The aggregate projection helpers are SumAs/AvgAs/MinAs/MaxAs/CountAs(col, alias); Name("col") projects a plain column and Expr("…") is the raw escape hatch within a projection. The result struct’s columns must match the projection’s names and aliases. For a fully custom shape, Raw (below) stays available.

For a join keyed by a column you control, use the typed helpers. The table identifier is quoted by the dialect; the ON condition is raw SQL (it spans tables) and may carry ? markers:

top, err := query.Select[Product](db).
  Distinct().
  InnerJoin("reviews", "reviews.product_id = products.id").
  Where("reviews.rating >= ?", 5).
  OrderBy("products.id").
  All(ctx)

The full set: InnerJoin(table, on, args...), LeftJoin, RightJoin, CrossJoin(table), and a fully raw Join(clause, args...) escape hatch when you want to write the whole join clause yourself.

Where(frag, args...) seen above is the raw, AND-joined predicate escape hatch — reach for it when a condition spans joined tables or needs SQL that the typed predicates don’t cover; prefer Filter for conditions on your own model’s columns.

By default the full model column set is selected. Project(cols...) overrides the SELECT list with raw column expressions — most often to select a single column for an IN-subquery, or to pull specific columns or aggregates.

Build a subquery like any other Select, then drop it into a predicate.

For an IN-subquery, the inner query must Project exactly one column. Its columns are validated when it’s placed in the predicate, so an error surfaces from the outer query’s terminal before any SQL runs:

fiveStar := query.Select[Review](db).
  Project("product_id").
  Filter(query.Col[int64]("rating").Ge(5))

viaSub, err := query.Select[Product](db).
  Filter(query.Col[int64]("id").InQuery(fiveStar)).
  OrderBy("id").
  All(ctx)

For an EXISTS / NOT EXISTS predicate, use query.Exists(sub) / query.NotExists(sub). The subquery typically correlates to the outer query through a raw Where:

anyReview := query.Select[Review](db).
  Project("1").
  Where("reviews.product_id = products.id")

reviewed, err := query.Select[Product](db).
  Filter(query.Exists(anyReview)).
  OrderBy("id").
  All(ctx)

Combine two compatible selects (same column shape). Union removes duplicate rows; UnionAll keeps them. The receiver’s ORDER BY / LIMIT apply to the whole compound.

cheapElectronics := query.Select[Product](db).Filter(query.And(
  query.Col[string]("category").Eq("electronics"),
  query.Col[float64]("price").Lt(50),
))
allBooks := query.Select[Product](db).Filter(query.Col[string]("category").Eq("books"))

combined, err := cheapElectronics.Union(allBooks).OrderBy("name").All(ctx)

Intersect (rows in both) and Except (rows in the first but not the second) round out the set operators, each with an …All variant that keeps duplicates:

both, _ := a.Intersect(b).All(ctx)
only, _ := a.Except(b).All(ctx)

INTERSECT / EXCEPT are supported on SQLite, Postgres, and SQL Server; on MySQL they raise a clear build error (MySQL only added them in 8.0.31, so LiteORM doesn’t advertise them there).

On Postgres and MySQL, a SELECT can take row locks. ForUpdate() takes exclusive locks, ForShare() shared ones; SkipLocked() skips already-locked rows instead of blocking, and NoWait() errors instead. Take locks inside a transaction.

tx, _ := db.Begin(ctx)
job, err := query.Select[Job](tx).
  Filter(query.Col[string]("status").Eq("queued")).
  OrderBy("id").Limit(1).
  ForUpdate().SkipLocked().      // the classic work-queue claim
  First(ctx)
// … process job, update status, tx.Commit(ctx)

Locking is gated by dialect: SQLite (no row locks) and SQL Server (which uses table hints instead) raise a clear build error rather than emit SQL that wouldn’t mean what you intended.

Distinct() adds plain SELECT DISTINCT. On Postgres, DistinctOn(cols...) keeps the first row of each distinct combination of the given columns — pair it with an Order whose leading terms match to choose which row:

// the latest event per kind
latest, err := query.Select[Event](db).
  DistinctOn(query.Col[string]("kind").Field()).
  Order(query.Asc(query.Col[string]("kind")), query.Desc(query.Col[int64]("seq"))).
  All(ctx)

DistinctOn raises a clear build error on the dialects that don’t support it.

A FROM source can be a base table, a common table expression, or a derived table (subquery). With(name, sub) prepends a CTE; reference it with From(name):

active := query.Select[User](db).Filter(query.Col[bool]("active").Eq(true))
rows, err := query.Select[User](db).
  With("active_users", active).
  From("active_users").
  Filter(query.Col[int64]("age").Gt(18)).
  All(ctx)

WithRecursive(name, sub) builds a recursive CTE — the recursive arm refers back to the CTE name (via a raw Join), and the two arms are combined with UnionAll. This is how you walk a tree or graph in one query:

anchor := query.Select[Category](db).Where("id = ?", rootID)
recurse := query.Select[Category](db).Join("JOIN subtree ON categories.parent_id = subtree.id")
subtree, err := query.Select[Category](db).
  WithRecursive("subtree", anchor.UnionAll(recurse)).
  From("subtree").
  All(ctx) // the root and all its descendants

FromSubquery[T](sess, alias, sub) selects from a derived table, and JoinSub(kind, alias, sub, on) joins one — the subquery’s placeholders renumber into the outer statement automatically:

recent := query.Select[Order](db).Where("created_at > ?", cutoff)
big, err := query.FromSubquery[Order](db, "r", recent).
  Filter(query.Col[int64]("total").Gt(1000)).
  All(ctx)

withOrders, err := query.Select[Customer](db).
  JoinSub("INNER JOIN", "o", recent, "o.customer_id = customers.id").
  All(ctx)

CTEs are gated by FeatCTE (every backend supports them); JoinLateral (a LATERAL join, where the subquery may reference earlier FROM items) is Postgres-only and raises a clear build error elsewhere.

Window functions and per-row scalar subqueries are projection expressions — you select them into a result struct with Into. A window function is built from a function (RowNumber/Rank/DenseRank, Lag/Lead, or a running WindowSum/WindowAvg/WindowCount/WindowMin/WindowMax), an Over(...) spec (PartitionBy + OrderBy), and a result alias:

type Ranked struct {
  Region string
  Amount int64
  Rank   int64 `db:"rank"`
}
ranked, err := query.Into[Sale, Ranked](ctx,
  query.Select[Sale](db),
  query.Col[string]("region").Field(),
  query.Col[int64]("amount").Field(),
  query.RowNumber().Over(
    query.Over().
      PartitionBy(query.Col[string]("region").Field()).
      OrderBy(query.Desc(query.Col[int64]("amount"))),
    "rank"))

ScalarSubquery(alias, sub) puts a subquery in the SELECT list as a single per-row value — the typed answer to a computed column beyond IN/EXISTS. The subquery must select one column and yield at most one row; correlate it with a raw Where referencing the outer table, and its bind parameters renumber into the outer statement automatically:

type WithCount struct {
  Name       string
  OpenOrders int64 `db:"open_orders"`
}
openByUser := query.Select[Order](db).Project("count(*)").
  Where("orders.user_id = users.id AND orders.status = ?", "open")
rows, err := query.Into[User, WithCount](ctx,
  query.Select[User](db),
  query.Col[string]("name").Field(),
  query.ScalarSubquery("open_orders", openByUser))

Window functions need a modern engine (SQLite 3.25+, Postgres, MySQL 8+, SQL Server). For anything more exotic, raw Project plus Raw[T] remains the escape hatch.

query.NewRepo[T](sess) is a typed repository wrapping the common write paths and primary-key lookups. It requires a primary key on T for the keyed operations.

repo := query.NewRepo[Product](db)

// Insert one; the generated primary key is read back into v in place.
p := Product{Name: "Desk Lamp", Category: "home", Price: 24, Stock: 18, Active: true}
err := repo.Insert(ctx, &p)
fmt.Println(p.ID) // populated

// Lookup by primary key (→ liteorm.ErrNoRows when absent).
got, err := repo.Get(ctx, p.ID)

// Find by predicates (same predicates as the builder).
cheapBooks, err := repo.Find(ctx,
  query.Col[string]("category").Eq("books"),
  query.Col[float64]("price").Lt(40),
)

// Update non-key columns of the row identified by its primary key.
p.Price = 19.99
err = repo.Update(ctx, &p)

// Delete by primary key.
err = repo.Delete(ctx, p.ID)

InsertMany(ctx, vs) inserts a slice efficiently — using the backend’s native bulk path when available (Postgres CopyFrom), otherwise chunked multi-row VALUES. It does not read primary keys back, so use Insert per row when you need each generated id.

err := repo.InsertMany(ctx, []Product{
  {Name: "Laptop Pro", Category: "electronics", Price: 1899, Stock: 7, Active: true},
  {Name: "USB Cable", Category: "electronics", Price: 9.99, Stock: 230, Active: true},
})

Upsert(ctx, v, query.OnConflict("col")) inserts v or, on a conflict with the named columns, updates the row. By default every non-conflict column is overwritten; chain .DoUpdate(...) to overwrite only specific columns:

restock := Product{Name: "USB Cable", Category: "electronics", Price: 8.49, Stock: 500, Active: true}
err := repo.Upsert(ctx, &restock, query.OnConflict("name").DoUpdate("stock", "price"))

The Repo writes one row by primary key; query.Update[T] and query.Delete[T] are the builders for writing many rows by condition. Set/SetExpr assign columns, Where/Filter scope the statement, and Exec returns the number of rows affected. A WHERE-less write is refused (add Where("1 = 1") to affect every row on purpose).

deactivated, err := query.Update[Product](db).
  Set("active", false).
  Filter(query.Col[int64]("stock").Eq(0)).
  Exec(ctx) // rows affected

discontinued, err := query.Delete[Product](db).
  Filter(query.Col[string]("category").Eq("legacy")).
  Exec(ctx)

Returning(ctx) runs the write and scans the changed rows back as []T — via RETURNING (Postgres/SQLite) or OUTPUT (SQL Server); it errors on MySQL, which has neither:

restocked, err := query.Update[Product](db).
  SetExpr("stock", "stock + ?", 100). // a raw expression, not just a value
  Filter(query.Col[string]("category").Eq("electronics")).
  Returning(ctx) // []Product, the updated rows

From(source) adds a correlated UPDATE … FROM — set columns from another table (or a VALUES list), which is also how you set many rows to different values in one statement. Gated by FeatUpdateFrom (Postgres / SQLite / SQL Server; MySQL, which uses UPDATE … JOIN, raises a clear build error):

// age += adjustments.delta, joined per row
_, err := query.Update[Person](db).
  SetExpr("age", "age + adj.delta").
  From("adjustments AS adj").
  Where("people.id = adj.person_id").
  Exec(ctx)

For a correlated DELETE, scope it with a subquery predicate (InQuery / Exists) — portable across every dialect.

When you need SQL the builder doesn’t express — window functions, CTEs, hand-tuned aggregates — drop to query.Raw[T]. It runs your SQL with bound args and scans the rows into any result type T (often a small struct shaped to the projection, with db:"..." tags):

type catStat struct {
  Category string `db:"category"`
  Items    int64  `db:"items"`
  Total    int64  `db:"total"`
}

stats, err := query.Raw[catStat](ctx, db,
  `SELECT category, count(*) AS items, sum(stock) AS total
   FROM products GROUP BY category ORDER BY total DESC`)

Raw is the intended path for complex SQL such as window functions and CTEs.

On Postgres, the builder adds typed predicates for JSON/JSONB columns and array columns. JSON path extraction works through query.JSON("col").Key("k")... with Eq/Ne/Like/In and the JSONB containment Contains; array columns use query.Array[E]("col") with Contains/ContainedBy/Overlaps/Has. These operators are Postgres-only and fail loudly at build time on a dialect that doesn’t support them.

admins, err := query.Select[Account](db).
  Filter(query.JSON("profile").Key("role").Eq("admin")).
  All(ctx)

tagged, err := query.Select[Article](db).
  Filter(query.Array[string]("tags").Contains("go", "databases")).
  All(ctx)

See Postgres for the full treatment of these operators.

• The orm front-end — declarative models, migrations, associations.
• Transactions — running these same builders inside a tx.
• Index and getting started.