Query Builder
The query builder is the object you get from Post.query(): you chain methods onto it (where, orderBy, limit…) to describe the rows you want, and a final method (get, first, count…) runs one SQL statement and gives you the result. It’s fully typed from your migrations: a wrong column name, a value of the wrong type or a misspelled enum value is a compile error.
This page is the reference: every method, what it produces, and how the three databases differ. For a guided tour with the blog example, start with ORM: Querying.
The smallest query
Section titled “The smallest query”import { Post } from '../../models/post.ts'
const drafts = await Post.query().where('status', 'draft').orderBy('created_at', 'desc').limit(10).get()console.log(drafts.length, drafts[0]?.title)What you’ll see:
2 A draftdrafts is a Post[]: each item is a model with typed columns.
How a query becomes SQL
Section titled “How a query becomes SQL”- You build. Each method adds to a plain description of the query. Nothing runs yet, and column names are checked against your schema as you go (a typo fails right here, with a suggestion).
- The model adds its own rules. Soft-deleted rows are left out, the model’s global scopes are added, and on team tables the team condition is added. Your conditions are wrapped in brackets first, so an
orWhereof yours can never escape them. - The grammar writes SQL for your database, with placeholders instead of values.
- A final check refuses the statement if it could reach another team’s rows.
- The connection runs it, sending the values separately, and the rows become models (booleans, dates and decimals converted).
The builder is mutable: each method changes the query and returns it. To branch one base query into two, use clone().
Every method
Section titled “Every method”Conditions
Section titled “Conditions”| Method | Example | SQL (PostgreSQL) |
|---|---|---|
where(column, value) |
where('status', 'draft') |
"posts"."status" = $1 |
where(column, operator, value) |
where('created_at', '>=', since) |
"posts"."created_at" >= $1 |
where(column, null) |
where('body', null) |
"posts"."body" IS NULL |
where((q) => …) |
where((q) => q.where('a', 1).orWhere('b', 2)) |
("posts"."a" = $1 OR "posts"."b" = $2) |
orWhere(…) |
same forms as where |
… OR … |
whereIn(column, values) |
whereIn('status', ['draft', 'published']) |
"posts"."status" IN ($1, $2) |
whereNotIn(column, values) |
whereNotIn('status', ['draft']) |
NULL-safe: rows where status is empty also match |
whereNull(column) / whereNotNull(column) |
whereNull('published_on') |
"posts"."published_on" IS NULL |
whereContains(column, text) |
whereContains('title', '100%') |
case-insensitive “contains”; % and _ are plain text |
whereStartsWith(column, text) |
whereStartsWith('slug', 'how-to') |
case-insensitive “starts with” |
whereHas(relation, (q) => …) |
whereHas('posts', (q) => q.where('status', 'published')) |
EXISTS (SELECT …) |
whereRaw(fragment) |
whereRaw(sql`length(${sql.id('title')}) > ${60}`) |
your SQL, values bound |
Operators are =, !=, <>, <, <=, > and >=; anything else (like, ilike) is refused with a hint. != and <> are NULL-safe: where('status', '!=', 'draft') also returns rows whose status is empty, the way !== works in JavaScript. Plain SQL != would silently skip them.
Requests: search, filter and sort
Section titled “Requests: search, filter and sort”These take strings from a request and check every column name, so they’re safe to feed with query-string values (the generated API uses them).
| Method | Example | What it does |
|---|---|---|
search(term, columns) |
search('hello world', ['title', 'body']) |
Case-insensitive search: every word must appear, each in any of the columns (at most 10 columns). A term that is an id or a UUID also matches that row exactly. Blank terms are ignored; at most 200 characters. |
filter(column, operator, value) |
filter('status', 'in', ['draft', 'published']) |
One filter. Operators: eq, ne, gt, gte, lt, lte, in, contains, startsWith, null (true = is empty, false = isn’t). |
sort(spec) |
sort('-created_at,title') |
Comma-separated columns, - for descending. |
The “every word” and “id or UUID” rules are decided (2026-11-15); the ORM prototype still looks for the whole term as one piece of text. A column that exists isn’t automatically one users may filter on: the generated API also checks each name against the allow-lists in .strutvane/resources.json. Do the same in your own controllers (see ORM: Querying).
Order and size
Section titled “Order and size”| Method | Example | Notes |
|---|---|---|
orderBy(column, direction = 'asc') |
orderBy('created_at', 'desc') |
The direction is checked at runtime too, because it often comes from a URL. |
latest(column = 'created_at') |
latest() |
Newest first. |
limit(count) |
limit(10) |
At most this many rows. |
offset(count) |
offset(20) |
Skip this many rows. |
Relations, scopes and soft deletes
Section titled “Relations, scopes and soft deletes”| Method | Example | Guide |
|---|---|---|
with(...paths) |
with('author', 'tags'), with('posts.tags'), with({ posts: (q) => q.where('status', 'published') }) |
Relationships |
withCount(...relations) |
withCount('tags') → post.tags_count |
Relationships |
| your scopes | published(), ofStatus('draft') |
Scopes |
withoutGlobalScope(name) |
withoutGlobalScope('visible') |
Scopes |
withTrashed() / onlyTrashed() |
withTrashed().find(7) |
Soft Deletes |
unsafeWithoutTenancy(reason) |
unsafeWithoutTenancy('nightly usage report') |
ORM: Tenancy |
Running the query
Section titled “Running the query”| Method | Returns | Notes |
|---|---|---|
get() |
Post[] |
Every matching row. |
first() |
Post | undefined |
Adds LIMIT 1. |
firstOrFail() |
Post |
Throws ModelNotFoundError (a 404 in a controller). |
find(id) / findOrFail(id) |
Post | undefined / Post |
By primary key, within the query’s conditions. |
count() |
number |
SELECT COUNT(*), ignoring order, limit and offset. |
exists() |
boolean |
Whether at least one row matches. |
paginate(perPage?, page?) |
{ data, total, perPage, currentPage, lastPage } |
Numbered pages. See Pagination. |
cursorPaginate(perPage?, cursor?) |
{ data, perPage, nextCursor } |
Fast pages for long lists. |
toSql() |
{ sql, params } |
The SQL and its values, without running it. |
clone() |
a new builder | An independent copy of the query so far. |
SQL on each database
Section titled “SQL on each database”The same query produces slightly different SQL per database. You never write these differences yourself; they matter when you read toSql() output or a slow-query log.
| MySQL 8.4+ | PostgreSQL | SQLite | |
|---|---|---|---|
| Quoting names | `posts`.`title` |
"posts"."title" |
"posts"."title" |
| Placeholders | ? |
$1, $2, … |
? |
NULL-safe != |
NOT (a <=> b) |
a IS DISTINCT FROM b |
a IS NOT b |
| Contains / starts with | a position check on lower-cased text (never LIKE) |
same | same |
offset() without limit() |
adds a huge LIMIT |
fine | adds LIMIT -1 |
| Reading back a new row | an extra SELECT by id (MySQL has no RETURNING) |
RETURNING * |
RETURNING * |
const { sql: text, params } = Post.query().where('status', '!=', 'draft').limit(5).toSql()console.log(text)console.log(params)What you’ll see on MySQL (something like this):
SELECT `posts`.* FROM `posts` WHERE NOT (`posts`.`status` <=> ?) AND `posts`.`deleted_at` IS NULL LIMIT ?[ 'draft', 5 ]On PostgreSQL the same query uses "posts"."status" IS DISTINCT FROM $1 and LIMIT $2.
Raw SQL, safely
Section titled “Raw SQL, safely”Use whereRaw for one hand-written condition inside a model query, and the connection for a whole statement:
import { defaultConnection, sql } from '@strutvane/framework'import { Post } from '../models/post.ts'
// 1. One raw condition inside a model query: you still get Post models back.const longTitles = await Post.query().whereRaw(sql`length(${sql.id('title')}) > ${60}`).get()
// 2. A whole statement: you get plain rows back.const { rows } = await defaultConnection().run( sql`SELECT ${sql.id('author_id')}, COUNT(*) AS total FROM ${sql.id('posts')} GROUP BY ${sql.id('author_id')}`,)console.log(longTitles.length, rows)What you’ll see (on MySQL):
1 [ { author_id: 1, total: 4 }, { author_id: 2, total: 1 } ]The rules of the sql tag:
- every
${value}is bound (sent separately), never pasted into the SQL; ${sql.id('name')}is a checked, quoted column or table name;- a
${fragment}made with the tag is spliced in, so you can build SQL from parts; sql.join(fragments, ' AND ')joins several fragments;- plain strings are refused everywhere.
Raw SQL that names a team’s table is refused inside a team context; see Database: Getting Started.
Three queries, from small to advanced
Section titled “Three queries, from small to advanced”Small: one condition.
const published = await Post.where('status', 'published').get()Typical: a list page with search, a filter, eager loading and pages.
const page = await Post.query() .search('launch', ['title', 'body']) .filter('status', 'eq', 'published') .with('author') .withCount('tags') .latest() .paginate(25, 1)console.log(page.total, page.data[0]?.author?.name, page.data[0]?.tags_count)// → 3 Ada 2Advanced: a reusable base query, branched, with a grouped OR and a relation condition.
import { User } from '../../models/user.ts'import { Post } from '../../models/post.ts'
const mine = (userId: number) => Post.query().where((q) => q.where('author_id', userId).orWhere('status', 'published'))
const me = await User.findOrFail(1)const recent = await mine(me.id).clone().latest().limit(5).get()const draftCount = await mine(me.id).clone().where('status', 'draft').count()const activeAuthors = await User.query().whereHas('posts', (q) => q.where('status', 'published')).count()console.log(recent.length, draftCount, activeAuthors)// → 5 2 3Coming later
Section titled “Coming later”These are part of Laravel’s query builder and designed for Strutvane, but not built yet. Until then, use raw SQL through the connection.
| Feature | Laravel | Status |
|---|---|---|
| Queries without a model | DB::table('posts') |
Coming in 0.2 |
| Choosing columns | select('id', 'title') |
Coming in 0.2 |
| Joins | join, leftJoin |
Coming in 0.2 |
| Grouping and aggregates | groupBy, having, sum, avg, min, max |
Coming in 0.2 |
| Counting and summing relations | withSum, withExists |
Coming in 0.2 |
| Processing big tables in pieces | chunk, chunkById, cursor, lazy |
Coming in 0.2 (see Collections) |
| Insert or update in one statement | upsert (the conflict columns must include the team column) |
Coming in 0.2 |
| Changing many rows at once | update, delete, increment on a query |
Coming in 0.2 |
| Full-text search | whereFullText |
Opt-in, coming later |
Common mistakes
Section titled “Common mistakes”“is not a supported operator”
Section titled ““is not a supported operator””OrmUsageError: "like" is not a supported operator. Use one of: = != <> < <= > >=. For text search use whereRaw with the sql tag.Why: LIKE treats % and _ in user input as wildcards, which surprises users and can be slow.
Fix: use whereContains('title', text) or whereStartsWith('slug', text).
“has no column … Did you mean …?”
Section titled ““has no column … Did you mean …?””OrmUsageError: "posts" has no column "titel". Did you mean "title"? Columns come from your migrations.Why: a typo, often in a value that came from a URL (?sort=titel), so TypeScript couldn’t catch it.
Fix: correct the name. In a controller, check request names against resources.json first and answer 400.
“whereRaw() needs a sql fragment, not a string”
Section titled ““whereRaw() needs a sql fragment, not a string””OrmUsageError: whereRaw() needs a sql`…` fragment, not a string. Strings would let user input become SQL; put values in ${…} inside the sql tag.Fix: whereRaw(sql`length(${sql.id('title')}) > ${60}`).
My != query returns rows with empty values
Section titled “My != query returns rows with empty values”Why: != is NULL-safe on purpose: status != 'draft' includes rows where status is empty, because an empty status isn’t 'draft'.
Fix: if you want only rows that have a value, add .whereNotNull('status').
Two queries share conditions by accident
Section titled “Two queries share conditions by accident”const base = Post.query().where('status', 'published')const recent = base.latest().limit(5) // changes base too!const total = await base.count() // still counts, but `base` now has the order and limitWhy: the builder is mutable; each method changes the same query.
Fix: branch with clone(): const recent = base.clone().latest().limit(5).
“needs a whole number of 0 or more”
Section titled ““needs a whole number of 0 or more””OrmUsageError: limit() needs a whole number of 0 or more; got -1.Fix: check numbers from a URL before passing them, or use paginate(), which validates page and size for you.
Next steps
Section titled “Next steps”- ORM: Querying: the same methods, walked through with the blog example.
- Pagination: numbered and cursor pages, in the ORM, the API and the screens.
- Relationships:
with,withCountandwhereHasin depth. - Database: Getting Started: connections, raw SQL and seeing every query.