Skip to content

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:

  • distinct
  • columns
  • where
  • joins
  • orders
  • offset
  • limit
  • include
  • with

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:

  • disableDeleted
  • disableCreateTime
  • disableUpdateTime
  • disableCacheQuery
  • disableCacheEntity
  • deleted

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:

  • joins shapes table-level join behavior
  • include loads declared static relations
  • with loads 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 valueMeaning
Field is absentDo not add a condition
Own field with undefinedDo not add a condition
Op.omitExplicitly do not add a condition
nullMatch 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 ref when comparisons need identifier semantics
  • use raw only 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:

  • list
  • total
  • pageCount
  • pageSize
  • pageNo

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:

  1. start from model relationships and typed query structure
  2. choose deliberately among joins, include, and with
  3. prefer the ORM query surface before dropping to raw SQL
  4. remember that soft-delete, cache, and datasource behavior may affect query semantics
  5. think about whether the query shape should also drive DTO or OpenAPI-facing output

That keeps query logic aligned with Vona’s intended abstractions.

Released under the MIT License.