Query Builder

Query Builder Chaining

Chain methods to build complex queries. You can start a chain with .where(), .order(), or .limit():

# Start with .where()
results = User
  .where("age >= @age", { "age": 18 })
  .where("active == @active", { "active": true })
  .order("created_at", "desc")
  .limit(10)
  .offset(20)
  .all

# Start with .order() — no filter needed
recent = User.order("created_at", "desc").limit(5).all

# Start with .limit()
sample = User.limit(3).all

# Get first result only
first = User.where("email == @email", { "email": "alice@example.com" }).first

# Count with conditions
count = User.where("role == @role", { "role": "admin" }).count

# Bulk delete — one AQL REMOVE for the whole match (no N+1 loop)
User.where("active == @a", { "a": false }).delete_all
post.comments.delete_all      # via has_many relation

# Bulk update — one AQL UPDATE for the whole match (no N+1 loop)
User.where("active == @a", { "a": false }).update_all({ "archived": true })

QueryBuilder Methods

All chainable methods available on the QueryBuilder. Methods like .where(), .order(), .limit(), and .includes() return a new QueryBuilder, while .all, .first, .count, .delete_all, and .update_all execute the query.

Query Generation (SDBQL)

Under the hood, QueryBuilder methods generate SDBQL (SoliDB Query Language) queries. Use .to_query to inspect the generated output:

Hash filter operators

The hash form of .where is not equality-only. All of the behaviour below is verified per backend — the surprises are where the engines differ.

The three value forms

A scalar means equality, an array means IN, and a hash means comparisons. Several operators on one field are ANDed, and several fields are ANDed.

Order.where({
  "status": "open",                       # equality
  "total":  { "gte": 100, "lt": 500 },    # both, ANDed
  "region": ["eu", "us"],                 # IN
  "or": [{ "rush": true }, { "vip": true }]
})

or and and take an array of hashes (an empty array is an error) and may nest; any hash inside them is a full filter again.

Semantics worth knowing

• Ordering comparisons follow the value's type. A number compares numerically; a string compares as text, which is what makes ISO-8601 dates work: { "issued_at": { "gte": "2026-01-01" } }.
• null and missing keys. { "f": null } is IS NULL; { "f": { "ne": null } } is IS NOT NULL; any other operator against null is an error rather than a clause that silently never matches. A document that never had the key reads as NULL, so { "nope": null } matches every row.
• IN details. An empty list matches nothing (a false predicate, not a syntax error). A null element adds IS NULL, since SQL IN never matches NULL. Each element keeps the semantics equality would give it, so a list may mix strings and numbers.
• Numbers compare differently per backend on document tables: Postgres compares jsonb numerically, so { "v": 10 } matches a stored 10.0; MySQL and SQLite compare the JSON representation, where it does not. Store numbers consistently if you filter on them.
• Index interaction. String equality and string IN compile to the JSON text extract — the expression an index declaration creates — so they can use it. Numeric comparisons use a numeric cast, which that index does not cover.
• On column-aware models (table "orders") the same hash compiles against real columns with typed binds, so each comparison is the column's own type and ordering on a text column follows the database's collation. A field that is not a column is refused by name, listing the columns that exist.
• Operator spellings. Each comparison also answers to its symbol, so { "total": { ">": 100 } } and { "total": { "gt": 100 } } compile to the same predicate: > >= < <= for the four comparisons, == or = for eq, and != or <> for ne. or / and are matched case-insensitively.
• Errors name the vocabulary. An unknown operator reports gt, gte, lt, lte, eq, ne, like, ilike, in and the symbols; a field name that is not an identifier is refused before anything is compiled.

like / ilike case sensitivity

Both need a string pattern (% = any run, _ = one character). Case behaviour is where the backends genuinely differ:

If case behaviour matters, use ilike and get the same answer everywhere.

Batch Iteration (find_each / in_batches)

.all materialises every matching row as Soli values at once, so a job over a large collection is bounded by RAM rather than by anything you chose — and .each is not an escape hatch, because it materialises first and then iterates. find_each walks the whole result set one record at a time, holding only one batch in memory.

# One batch of 1000 in memory at a time, however large the collection is
User.find_each(fn(user) {
  user.recompute_score()
})

# Any filter chain composes, and the batch size is yours to pick
User.where("active == @a", { "a": true }).find_each(fn(user) {
  user.backfill_initials()
}, { "batch_size": 500 })

# in_batches hands the block the whole batch — for work that is itself bulk
Invoice.where("status == @s", { "s": "pending" }).in_batches(fn(batch) {
  Mailer.deliver_reminders(batch)
}, { "batch_size": 200 })

Paging is by key, not by offset. Each batch adds FILTER doc._key > <last key of the previous batch> and sorts by _key ascending. LIMIT offset, n degrades as the offset grows and — worse — skips or repeats rows when the collection is written to mid-scan, which is exactly what a long correction job does to the collection it is correcting. Deleting or updating records inside the block is therefore safe: the cursor is read before the block runs. batch_size defaults to 1000 and is capped at 10,000.

Coalescing Reads (grouped)

An action that reads several unrelated things pays one network round-trip per read. Wrap the reads in grouped(fn() { ... }) and they are deferred and combined into a single request — one LET … RETURN […] statement that computes every subquery server-side and returns them together.

# Without grouped — three round-trips
@posts    = Post.all
@accounts = Account.where({ active: true }).count
@tags     = Tag.all

# With grouped — one round-trip for all three
grouped(fn() {
  @posts    = Post.all
  @accounts = Account.where({ active: true }).count
  @tags     = Tag.all
})

Inside the block each read returns a placeholder instead of hitting the database; the queries fire as one combined statement when the block ends, and the results are then materialised. After the block you use @posts, @accounts, and @tags exactly as before — iterate them, index them, read fields, serialise them to JSON.

Strictly speaking the binding stays a thin placeholder that resolves to its value on use, rather than being replaced by one. That is invisible in normal code, but it is why a new way of consuming a value can occasionally need teaching to unwrap it — if you ever see an error naming a type that the value plainly is (cannot iterate over array), that is the shape of the bug, and it is worth reporting.

What gets coalesced

Read queries are batched: all, where(...).all / .first / .count / .exists, the aggregates (sum / avg / min / max), find, find_by, and first_by. Writes are not — create, save, update, delete run immediately even inside the block (use transaction for atomic writes).

Reading a result inside the block (auto-flush)

If you read one of the deferred results before the block ends, the queries collected so far fire immediately (an "auto-flush"), then collection resumes. This always returns correct data; it just means more than one round-trip when you interleave reads. For maximum coalescing, do the reads first and use the results after the block.

Finding reads that should be grouped

Because grouped is for reads that each run once, an N+1 scan can never point you at them: it fingerprints by query template and only fires on a repeated one. Three unrelated reads are three distinct templates with a count of one each — invisible to assert_no_n_plus_one and to the dev bar's N+1 badge. Two tools cover that blind spot:

• The dev bar query panel shows an amber N READS · N ROUND-TRIPS advisory when a request issues three or more distinct one-off reads outside any grouped block. Amber, not red: it is a suggestion, because reads that feed each other cannot share a round-trip.
• assert_no_ungrouped_reads(response) fails a spec on the same condition.

test("the dashboard loads in one round-trip", fn() {
  let response = get("/dashboard")
  assert_no_ungrouped_reads(response)
  assert_query_count(response, 1)
})

Neither can prove the reads are independent — User.find(id) followed by a query on user._key is genuinely two round-trips. When the reads must be sequential, that advisory is a false positive and the assertion is the wrong tool for that action.

Query timeouts (timeout)

Every read reaches SoliDB over HTTP, and that client gives a request 10 seconds. The limit is deliberately tight — it is the backstop that stops a stalled connection from pinning a worker — but a genuine long-running query outgrows it: a report over a large collection, a multi-aggregate group_by, an import-sized scan. Such a query fails with Error: HTTP error: error sending request for url … after ten seconds no matter how healthy the database is.

.timeout(secs) raises the limit for one query:

# A slow report gets two minutes; every other query on the
# process keeps the 10s default.
let rows = Order
    .where({ "created_at": { "gte": start_of_year } })
    .group_by(["region", "channel"])
    .aggregate({ "revenue": ["sum", "total"], "n": ["count"] })
    .timeout(120)
    .all()

# Also a static entry point, so no .where is needed
Order.timeout(120).all()

• Seconds, Int or Float — .timeout(0.5) is a valid half-second budget. A zero, negative, or non-numeric value raises rather than being ignored, so a typo cannot silently leave the 10s default in place.
• Chainable and position-independent, like .limit.
• Scoped to the one query. The override is installed for that request and reverted the moment it finishes, error included — it never leaks into the next query.
• Lower it too. Nothing stops .timeout(2) on a query you would rather see fail fast than have a user wait on.

Inside a grouped block the reads become a single request, and the batch runs under the largest .timeout any member asked for — one slow member is not capped by the fast reads sharing its round-trip. A raw db.query read against the same host and database joins that batch, so a sibling Model.timeout(120) covers it; a write via db.query still runs immediately, like Model create / update / delete.

grouped(fn() {
  @summary = Order.group_by(["region"]).timeout(120).all   # the slow one
  @tags    = Tag.all                                       # rides along
  @heavy   = db.query("FOR d IN orders COLLECT … RETURN d") # joins the batch
})
# one request, 120s of room

The QueryBuilder form does not apply to a Solidb client. Raise that path with db.timeout(secs).query(sdbql) (persists on the client until you change it) or a per-call options hash:

rows = db.timeout(60).query(sdbql)
rows = db.query(sdbql, binds, { "timeout": 60 })

In --dev, grouped does not coalesce (so the query log stays one row per read). Put .timeout on each slow query, including db.query.

Graph Traversal (traverse)

On models connected by an edge model, record.traverse(EdgeModel, options?) returns a QueryBuilder over the reachable vertices — it chains like any other builder. Vertex filters use the usual doc variable; prefix a string filter with edge. to match edge attributes. direction is "out" (default), "in", or "any"; depth is an Int n (depth 1..n) or a [min, max] array:

friends   = alice.traverse(Follow).all                    # OUTBOUND, depth 1..1
followers = alice.traverse(Follow, direction: "in").all   # INBOUND

# Chain filters, ordering, limits — vertex variable is `doc`
ring = alice.traverse(Follow, depth: [2, 3], direction: "any")
  .where({ "active": true })
  .where("edge.since >= @y", { "y": 2024 })    # edge-attribute filter
  .order("name")
  .limit(10)
  .all

n = alice.traverse(Follow).count

Shortest path

shortest_path is not chainable — it executes immediately and returns the vertices along the shortest path ([] when unconnected). Direction defaults to "any":

path = alice.shortest_path(bob, via: Follow)
path = alice.shortest_path("users/bob-key", via: Follow, direction: "out")

Time Buckets (time_bucket)

On timeseries models, .time_bucket(interval, aggregates?) groups rows into fixed time buckets and computes per-bucket aggregates. Chain it after .where() filters and finish with .all:

rows = Metric.where("device = @d", { "d": "srv1" })
  .time_bucket("5m", { "avg": "value", "max": "value" })
  .all
# => [{ "bucket": "2026-07-05T10:00:00+00:00", "avg": 0.41, "max": 0.9 }, ...]

rows = Metric.time_bucket("1d").all                  # bare form: count per bucket
rows = Metric.time_bucket("1h", avg: "value").all    # keyword style works too

Grouped Aggregation (group_by / aggregate / having)

Group on one or more fields and compute several named aggregates at once. In grouped mode .order() must name a group field or an aggregate alias; limit/offset compose as usual:

rows = Order
  .where({ "status": "paid" })
  .group_by(["country", "plan"])
  .aggregate({ "total": ["sum", "amount"], "n": ["count"] })
  .having("total > @min", { "min": 1000 })
  .order("total", "desc")
  .all
# => [{ "country": "FR", "plan": "pro", "total": 5300, "n": 12 }, ...]

User.group_by("role").all                 # 1-arg form: implicit count per group ("n")
Order.aggregate({ "n": ["count"] }).first # ungrouped: one row
Order.median("amount").first              # also: stddev / variance / count_distinct

Next Steps