Queries

Start a query with a model's scope method, then call a terminal method. Each chain has independent scope, so concurrent requests cannot change each other's filters.

Anatomy of a query

const leads = await Lead
  .where({ user_id: userId })   // scope
  .orderBy('created_at', 'desc') // scope
  .limit(10)                     // scope
  .all()                         // terminal → ModelCollection

Scope methods

Method	Description
where(conditions)	Equality conditions (typed from declared columns)
where(column, operator, value)	Bound comparison on a query or relation
where(callback)	The ORM passes a temporary filter builder to create a parenthesized group
whereIn / whereNotIn	Membership, including empty and null lists
whereBetween	Inclusive range
whereNull / whereNotNull	SQL NULL checks
whereLike / whereILike	SQL LIKE pattern; whereILike folds ASCII case
orderBy(column, direction?)	Sort ('asc' | 'desc', default 'asc')
limit(count)	Maximum rows
offset(count)	Skip rows (often with limit)
clone()	Fork the current filters, ordering, limit, and offset into an independent query

// Equality object
await User.where({ email: 'jane@example.com' }).first()

// Compose freely
await Post
  .where({ published: true })
  .orderBy('created_at', 'desc')
  .offset(20)
  .limit(10)
  .all()

// orderBy without a prior where (queries the whole table)
await Lead.orderBy('created_at', 'desc').all()

// Branch without changing the reusable base query
const base = Post.where({ published: true })
const recent = base.clone().orderBy('created_at', 'desc').limit(10)
const featured = base.clone().where({ featured: true })

Extended filters

Model.where(object) keeps its existing equality signature. Start with an equality object (or {} for an unscoped query), then chain comparison and other filters on the returned query. Relation queries support the same filters.

import { escapeLike } from 'mevn-orm'

const search = await Item.where({})
  .where('price', '>=', 100)
  .whereIn('status', ['available', 'reserved'])
  .where((group) => group
    .whereILike('title', `%${escapeLike('desk')}%`)
    .orWhereILike('description', `%${escapeLike('desk')}%`))
  .all()

const expiring = await Token.where({})
  .where('expires_at', '<', new Date())
  .all()

Comparison operators are =, !=, <>, <, <=, >, and >=. Column names are checked as SQL identifiers; values are bound. Declared model fields determine allowed columns and value types. Models without declared fields retain loose column and value types. Use whereNull or whereNotNull for SQL NULL rather than a comparison operator.

whereIn(column, []) matches no rows; whereNotIn(column, []) matches every row in the existing scope. Lists containing null include or exclude SQL NULL as expected. whereBetween includes both endpoints and requires two non-null values. For where((group) => ...), the ORM creates group and passes it to your callback. The callback adds conditions to one parenthesized group; nested groups are allowed. OR methods are available only inside a group, so an OR on a relation cannot bypass its parent key constraint.

whereLike follows the database's native collation. whereILike uses LOWER() on both sides for consistent ASCII case folding on SQLite and MySQL; Unicode case behavior remains database dependent. % and _ are wildcards. To search for them literally, call escapeLike(text) and wrap it in % if you want a contains search. The helper escapes %, _, and the ! escape character.

Date values in extended filters are converted to UTC text in YYYY-MM-DD HH:mm:ss.SSS format before binding. Store timestamp values in UTC using that representation, or use UTC database sessions for native date columns. Invalid Date values fail before execution. Existing equality objects keep their previous encoding behavior.

The Knex backend and db0's supported SQLite and MySQL connectors support these filters. Third-party backends can opt in through the optional TableQuery.filter capability; equality queries continue to work without it. An extended filter on a backend without that capability throws before changing its query. No migration is needed.

Terminal methods

Method	Returns
first(columns?)	First matching model, or null
firstOrFail(columns?)	First matching model, or throws if missing
all(columns?)	ModelCollection of models
exists()	Whether the current filtered, limited, and offset result contains a row
value(column)	First column value, or undefined if no row matches
pluck(column)	Array of column values in result order
count(column?)	Number of matching rows
paginate(perPage?, page?, columns?)	Page data + metadata
update(properties)	Rows updated (number)
destroy()	Rows deleted (number)

First and all

const user = await User.where({ email: 'jane@example.com' }).first()

const admins = await User.where({ role: 'admin' }).all()
for (const admin of admins) {
  console.log(admin.email)
}

// Column selection
const slim = await User.all(['id', 'email'])

Without a prior scope, first() / all() operate on the whole table:

const anyone = await User.first()
const everyone = await User.all()

Scalar reads

const query = User.where({ active: true }).orderBy('id')
const hasUsers = await query.exists()
const firstName = await query.value('name')
const names = await query.pluck('name')
const firstUser = await query.firstOrFail()

These helpers leave query reusable. value() and pluck() select only the requested column and return its declared TypeScript type when the model declares fields; untyped models return unknown. value() returns undefined for no row and preserves a SQL NULL as null. pluck() returns [] for no rows and preserves null entries. exists() respects offset() and returns false for limit(0). count() still counts the full filtered scope, ignoring limit and offset. Scalar reads return requested database values directly, including columns marked hidden; avoid exposing those values in API responses.

The new helpers are available on ModelQuery objects such as User.where(...) or User.orderBy(...). They add no names to Model itself, so subclasses can retain methods with the same names.

Count

const total = await User.count()
const active = await User.where({ active: true }).count()

Scoped bulk writes

const updated = await User
  .where({ active: false })
  .update({ archived: true })

const deleted = await Session
  .where({ expired: true })
  .destroy()

Pagination

paginate() defaults to 15 items per page on page 1.

const result = await Post.paginate()
const result10 = await Post.paginate(10)
const page2 = await Post
  .where({ published: true })
  .orderBy('created_at', 'desc')
  .paginate(10, 2)

Result shape

{
  data: ModelCollection<Post>, // use .toArray() for plain objects
  total: number,
  per_page: number,
  current_page: number,
  next_page: number | null,
  prev_page: number | null,
  last_page: number,
}

API handler example

// Express / Nitro style
export async function listPosts(req: { query: { page?: string; perPage?: string } }) {
  const page = Number(req.query.page ?? 1)
  const perPage = Number(req.query.perPage ?? 15)

  const result = await Post
    .where({ published: true })
    .orderBy('created_at', 'desc')
    .paginate(perPage, page)

  return {
    data: result.data.toArray(),
    meta: {
      total: result.total,
      per_page: result.per_page,
      current_page: result.current_page,
      next_page: result.next_page,
      prev_page: result.prev_page,
      last_page: result.last_page
    }
  }
}

Pagination edge cases

• Page numbers are clamped into a valid range (never less than 1, never past last_page).
• Empty tables still return a valid structure with total: 0 and last_page: 1.
• The query object can be reused after paginate(); other chains are independent.
• Relation pagination uses the same metadata, with data as an array of related models.

Backend support

Query cloning and scalar helpers use the existing query backend operations. They work with the Knex backend and db0's supported SQLite and MySQL connectors. Relation sorting, counting, and pagination use the same operations. No backend interface methods are required beyond those already used by model queries, and no migration is needed.

ModelCollection

all() and paginate().data return a ModelCollection, which is an Array subclass:

const users = await User.all()

users.length
users.map((u) => u.email)
users.filter((u) => u.active)

// Plain objects for JSON responses
return users.toArray()

Table resolution

Static queries honour override table on subclasses:

class PasswordReset extends Model {
  override table = 'password_reset_tokens'
}

// Uses password_reset_tokens, not password_resets
await PasswordReset.where({ token }).first()

PasswordReset.currentTable // 'password_reset_tokens'
PasswordReset.resolveTable() // same

Query hygiene tips

1. Always end with a terminal — scopes alone do not run a query.
2. Keep query chains local to the request — User.where(...) returns a query object with its own filters.
3. Prefer scoped updates/deletes — bare User.update(...) / User.destroy() affect the whole table.
4. Use toArray() at the boundary — keep models inside the service layer; send plain objects to clients.

Next steps

• Relationships
• Serialization
• Read joined rows for typed, read-only joins
• Raw Knex for advanced SQL