Appearance
ORM Select Guide
This guide explains trusted server-authored ORM select operations in Vona within the Cabloy monorepo. Its property-keyed where, underscore operators, tuple orders, joins, Raw and Ref examples are not public request payloads. For HTTP, MCP and generated frontend query inputs, use the policy-bound Public Query Guide; there is no public raw-ORM fallback.
Why select operations matter
Select operations are where model definitions, relationships, filters, ordering, pagination, and caching behavior begin to interact.
Vona does not treat queries as untyped string fragments. It provides a richer model-aware query surface.
Basic select operations
Representative patterns:
typescript
await this.scope.model.post.select();
await this.scope.model.post.count();
await this.scope.model.post.selectAndCount();
await this.scope.model.post.get({ id });
await this.scope.model.post.mget(ids);These operations show the basic query vocabulary that services can build on.
Rich select parameters
select can combine:
distinctcolumnswherejoinsordersoffsetlimitincludewith
Representative pattern:
typescript
await this.scope.model.post.select(
{
columns: ['id', 'title', 'userId'],
where: {
'id': { _gt_: 1 },
'testVonaUser.id': 1,
},
joins: [['innerJoin', 'testVonaUser', ['userId', 'testVonaUser.id']]],
offset: 0,
limit: 20,
orders: [['createdAt', 'desc']],
},
{
disableDeleted: false,
},
'test-vona:user',
);This matters because Vona ORM encourages structured query building rather than ad hoc query scattering.
Query options
Representative option areas include:
disableDeleteddisableCreateTimedisableUpdateTimedisableCacheQuerydisableCacheEntitydeleted
This is important because select behavior may depend on caching and soft-deletion policy, not only on columns and filters.
joins, include, and with
A useful distinction is:
joinsshapes table-level join behaviorincludeloads declared static relationswithloads dynamic relations declared at the usage site
That means joins are not floating SQL trivia. They are part of a model-aware query system that can move between table-level control and relation-level convenience.
For stable, reusable business relationships, declare the appropriate hasOne, belongsTo, hasMany, or belongsToMany relation on the Model and prefer include when a CRUD read needs its related data. Use with for a situational relation instead of adding every one-off lookup to Model metadata. See Relations Guide. An include loads and attaches related records after the parent query; it is not automatically a SQL JOIN between the parent and related tables. In list reads, the ordinary relation loader batches related lookups, but this still adds queries and may load large collections. Select only the relations and fields needed by that endpoint.
A relation's where filters the related rows that are attached; it does not remove parent rows or change a parent's selectAndCount total. If the presence of a matching child determines which parents belong in a list, put that condition in the parent query, for example with _exists_ or a deliberately chosen joins predicate. Paginate the parent results explicitly and load bounded relation projections. A hasMany relation limit on a multi-parent list applies to the shared child query, not separately to each parent; for per-parent top-N requirements, design a query for that contract rather than assuming include provides it. Direct joins to collections can multiply parent rows and distort pagination or counts, so verify those semantics before using them.
Match the read's selected relations to its $Dto.* projection when inferring an API contract; inference describes the shape, but does not trigger ORM loading or strip extra response fields by itself. See DTO Infer and Generation.
Type-guided joins
A key insight is that joinable tables often come from relationships already declared on the model.
In broader systems, not every useful join is declared on the current model. In those cases, the _modelJoins parameter can provide additional model hints so the join surface remains typed and discoverable.
where operators
The operator model is broad and includes examples like:
_eq__notEq__gt__gte__lt__lte__in__notIn__is__isNot__between__notBetween__startsWith__endsWith__includes_- case-insensitive variants
_ref_
That operator vocabulary belongs to the trusted ORM DSL. Public predicate operators and shapes are deliberately different.
A practical operator-family reading is:
- comparison:
_eq_,_notEq_,_gt_,_gte_,_lt_,_lte_ - membership/range:
_in_,_notIn_,_between_,_notBetween_ - null checks:
_is_,_isNot_ - string matching:
_startsWith_,_endsWith_,_includes_, and case-insensitive variants - composition/subquery:
_and_,_or_,_not_,_exists_,_notExists_ - identifier helper:
_ref_
Literal strings in _eqI_ and _notEqI_
For ordinary string values, _eqI_ / _notEqI_ (EqI / notEqI) match the whole literal value: !, %, and _ are escaped with ! and SQL ESCAPE '!', so caller-supplied wildcard characters do not become patterns. These operators preserve Knex's native dialect-specific LIKE/ILIKE case behavior; they do not provide uniform database-independent case folding or Unicode normalization. In particular, SQLite does not guarantee that é and É compare as equivalent. They do not trim values.
Pattern operators such as _startsWith_, _endsWith_, _includes_ and their case-insensitive variants retain their existing behavior. Raw/ref expression operands are unchanged; the literal-string escaping contract does not reinterpret SQL expressions.
Absent, undefined, and null in where
A direct where object distinguishes omitted conditions from SQL NULL checks:
| Field value | Meaning |
|---|---|
| Field is absent | Do not add a condition |
Own field with undefined | Do not add a condition |
Op.omit | Explicitly do not add a condition |
null | Match SQL NULL with IS NULL |
An own undefined means the property exists on the JavaScript object but its value is undefined, such as { title: undefined }. It has the same query result as an absent field, while Op.omit is the readable way to make that omission intentional in a dynamically composed query.
Representative pattern:
typescript
import { Op } from 'vona-module-a-orm';
await this.scope.model.post.select({
where: {
title: undefined, // omitted
stars: Op.omit, // explicitly omitted
publishedAt: null, // SQL IS NULL
},
});The same direct-where rule applies to filters used by mutation, aggregate, and group operations.
At the public request boundary, use an admitted { field, op: 'isNull' } or { field, op: 'isNotNull' } leaf, without value. Public eq with null is invalid; the trusted { publishedAt: null } shorthand above is not a public AST.
Joint operators and subqueries
The query language also supports joint operators such as:
_and__or__not__exists__notExists_
These operators matter because many real queries are not flat field comparisons.
Representative _exists_ pattern:
typescript
await this.scope.model.post.select({
where: {
_exists_: function (builder: Knex.QueryBuilder) {
builder
.select('*')
.from('testVonaPostContent')
.where('postId', this.scope.model.post.ref('testVonaPost.id'));
} as any,
},
});raw and ref
The structured query language is the default, but Vona still exposes escape hatches when they are truly needed.
Representative raw pattern:
typescript
await this.scope.model.post.select({
where: this.scope.model.post.raw('?? > ?', ['stars', 20]) as any,
});Representative ref pattern:
typescript
await this.scope.model.post.select({
where: {
title: {
_eq_: this.scope.model.post.ref('testVonaPost.title') as any,
},
},
});A practical rule is:
- start with structured operators first
- use
refwhen comparisons need identifier semantics - use
rawonly when the structured surface is not sufficient
selectAndCount and pagination-shaped results
selectAndCount is especially useful when one backend query should return both rows and pagination metadata together.
A practical result shape includes:
listtotalpageCountpageSizepageNo
This matters because page-query contracts often need more than a plain row array. They need a stable list-plus-metadata response shape that can map directly into DTOs and controller response contracts.
Relationship to aggregate/group and DTOs
Read this guide together with:
A practical split is:
- use this guide for row-oriented query structure
- use the aggregate/group guide when the result shape is summary-oriented
- use DTO guidance when query shape must become an explicit API contract
Implementation checks for ORM select changes
When writing or editing select logic:
- start from model relationships and typed query structure
- choose deliberately among
joins,include, andwith - prefer the ORM query surface before dropping to raw SQL
- remember that soft-delete, cache, and datasource behavior may affect query semantics
- think about whether the query shape should also drive DTO or OpenAPI-facing output
That keeps query logic aligned with Vona’s intended abstractions.