Query Syntax

ObjectQL queries are expressed as Abstract Syntax Trees (AST) in JSON format. This enables database-agnostic querying—write once, compile to PostgreSQL, MongoDB, SQLite, or any supported driver.

All query syntax in this document follows the canonical QuerySchema defined in @objectstack/spec (packages/spec/src/data/query.zod.ts). Filtering uses the where + MongoDB-style $op object syntax from FilterConditionSchema (packages/spec/src/data/filter.zod.ts).

Query Philosophy

Traditional SQL:

-- Tightly coupled to PostgreSQL
SELECT c.name, c.email, a.company_name
FROM contact c
LEFT JOIN account a ON c.account_id = a.id
WHERE c.is_active = true AND a.industry = 'tech'
ORDER BY c.created_at DESC
LIMIT 10;

ObjectQL (Canonical Spec Format):

import type { QueryAST } from '@objectstack/spec/data';

const query: QueryAST = {
  object: 'contact',
  fields: ['name', 'email', 'account'],
  where: { is_active: true },
  // Related records are loaded through `expand` — not through a JOIN and not
  // through a dotted `account.industry` path (see §2 and §4).
  expand: {
    account: { object: 'account', fields: ['company_name'] },
  },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 10,
};

Runtime compilation:

• PostgreSQL / MySQL → parameterised single-table SQL (@objectstack/driver-sql); related records are a second, batched $in read, not a JOIN
• MongoDB → Native queries + aggregation pipeline (@objectstack/driver-mongodb)
• SQLite → Portable SQL, in-process or in-browser via @objectstack/driver-sqlite-wasm
• In-Memory → In-process evaluation, no external database (@objectstack/driver-memory)

Query Structure

The QueryAST (Canonical)

The canonical query structure is defined by QuerySchema in @objectstack/spec:

import type { QueryAST } from '@objectstack/spec/data';

// QueryAST — full structure
interface QueryAST {
  object: string;                          // Target object (required)
  fields?: FieldNode[];                    // Projection (SELECT) — field names
  where?: FilterCondition;                 // Filtering (WHERE) — MongoDB-style $op
  search?: string | FullTextSearch;        // Full-text search — the query text (canonical), or the structured form
  searchFields?: string[];                 // Narrow the search (server-intersected — narrows only, never widens)
  orderBy?: SortNode[];                    // Ordering (ORDER BY)
  limit?: number;                          // Max records (LIMIT)
  offset?: number;                         // Skip records (OFFSET)
  top?: number;                            // Alias for limit (OData compat)
  aggregations?: AggregationNode[];        // Aggregation functions
  groupBy?: GroupByNode[];                 // GROUP BY targets (string | object)
  having?: FilterCondition;                // HAVING — engine-enforced after aggregation
  expand?: Record<string, QueryAST>;       // Recursive relation loading
}

Key Types

// SortNode — ORDER BY element
interface SortNode {
  field: string;
  order: 'asc' | 'desc';   // default: 'asc'
}

// AggregationNode — aggregation definition
interface AggregationNode {
  function: 'count' | 'sum' | 'avg' | 'min' | 'max' | 'count_distinct';
  field?: string;        // optional for COUNT(*)
  alias: string;         // result column alias
  filter?: FilterCondition; // [EXPERIMENTAL — not enforced] FILTER WHERE clause — never applied
}
// `distinct?: boolean` was REMOVED in protocol 17 (#6815) — see the callout above.

// FieldNode — one entry of the select list. One of the queried object's OWN
// column names. The type is `string`, so a dotted path ('owner.name') still
// PARSES, but it resolves nothing: no driver ever implemented dotted
// projection, and the ingress refuses it (400 INVALID_FIELD, #7532). Related
// data — whole records and single related columns alike — comes from `expand`,
// not from inside this list.
//
// The `{ field, fields, alias }` nested-select member this union used to carry
// was REMOVED in protocol 17 (#4196): nothing produced it and nothing read
// `.fields`/`.alias`, so it was dropped by the SQL and memory drivers,
// projected as a column named "[object Object]" by MongoDB, and refused as an
// unknown field by the REST ingress. `expand` is the one spelling.
type FieldNode = string;

// GroupByNode — GROUP BY target
type GroupByNode = string | {
  field: string;
  dateGranularity?: 'day' | 'week' | 'month' | 'quarter' | 'year';
  alias?: string;        // defaults to `field`
};

1. Basic Queries

Select All Records

const customers = await engine.find('customer');

// SQL: SELECT * FROM customer;
// MongoDB: db.customer.find({})

Select Specific Fields

const customers = await engine.find('customer', {
  fields: ['company_name', 'industry', 'annual_revenue'],
});

// SQL: SELECT company_name, industry, annual_revenue FROM customer;
// MongoDB: db.customer.find({}, { company_name: 1, industry: 1, annual_revenue: 1 })

Limit and Offset

const customers = await engine.find('customer', {
  limit: 10,
  offset: 20,  // Skip first 20, get next 10
});

// SQL: SELECT * FROM customer LIMIT 10 OFFSET 20;
// MongoDB: db.customer.find().skip(20).limit(10)

2. Filtering

Filters use the where clause with MongoDB-style $op operators (object syntax).

Implicit Equality

The simplest filter — a field-value pair implies $eq:

const query: QueryAST = {
  object: 'customer',
  where: {
    industry: 'tech',       // Implicit: { $eq: 'tech' }
  },
};

// SQL: WHERE industry = 'tech'

Explicit Operators

Use $op keys for non-equality comparisons:

// Not equal
const query: QueryAST = {
  object: 'customer',
  where: {
    status: { $ne: 'inactive' },
  },
};
// SQL: WHERE status != 'inactive'

// Greater than
const query: QueryAST = {
  object: 'customer',
  where: {
    annual_revenue: { $gt: 1000000 },
  },
};
// SQL: WHERE annual_revenue > 1000000

Supported Operators

$like is not a spelling of $contains

The two take different things and picking the wrong one is a silently wrong answer rather than an error:

• $contains takes TEXT. It is matched literally as a substring, and %, _ and regex metacharacters in your comparand are ordinary characters — escaped on your behalf.
• $like takes a PATTERN. % matches any sequence, _ matches exactly one character, and a backslash escapes either. The pattern is matched against the whole value, so a pattern with no wildcards is an exact comparison, not a substring search.

{ name: { $contains: 'Industries' } }  // Acme Industries, Industries Ltd, Industries
{ name: { $like: 'Industries' } }      // Industries          — exact
{ name: { $like: '%Industries' } }     // Acme Industries, Industries — ends with

A pattern ending in a lone unpaired backslash is refused (INVALID_FILTER): no reading of it survives every backend, so it is rejected rather than guessed.

Case Sensitivity

The string operators compare case-sensitively. $icontains is the one that does not, and the case it ignores is ASCII case only — A-Z against a-z, and nothing else.

// Case-sensitive: matches "acme corp", NOT "ACME Corp"
{ name: { $contains: 'acme' } }

// ASCII case-insensitive: matches BOTH "acme corp" and "ACME Corp"
{ name: { $icontains: 'acme' } }

The comparand is always matched literally. % and _ are ordinary characters, not LIKE wildcards, and . / * / + are ordinary characters, not regex metacharacters — { name: { $icontains: 'a.b' } } matches a.b and not axb.

$regex — removed

$regex (and its $options companion) was never a declared operator and is retired (#4706). It could not mean one thing across the backends: driver-sql compiled it to a LIKE-escaped substring match, so a.b matched only the literal a.b, while driver-memory evaluated it as a real RegExp, so the same filter also matched axb — and an invalid pattern was caught and answered zero rows, in silence. A real regex is not implementable on all five backends: driver-turso's remote transport speaks a wire protocol with no way to register a SQLite REGEXP function.

A pattern that genuinely needs a regular expression has no filter-level replacement — narrow the query with the declared operators and match in application code. The prescriptions above are declared as data in RETIRED_FILTER_OPERATORS (@objectstack/spec/data), so every backend's refusal quotes the same sentence.

Multiple Conditions (Implicit AND)

Multiple keys in where are combined with AND logic:

const query: QueryAST = {
  object: 'opportunity',
  where: {
    stage: 'Closed Won',
    amount: { $gt: 50000 },
    close_date: { $gte: '2024-01-01' },
  },
};

// SQL: WHERE stage = 'Closed Won' AND amount > 50000 AND close_date >= '2024-01-01'

Logical OR ($or)

const query: QueryAST = {
  object: 'contact',
  where: {
    $or: [
      { title: { $contains: 'CEO' } },
      { title: { $contains: 'President' } },
      { title: { $contains: 'Founder' } },
    ],
  },
};

// SQL: WHERE (title LIKE '%CEO%' OR title LIKE '%President%' OR title LIKE '%Founder%')

Logical AND ($and)

Explicit $and is useful when you need multiple conditions on the same field:

const query: QueryAST = {
  object: 'product',
  where: {
    $and: [
      { price: { $gte: 10 } },
      { price: { $lte: 100 } },
    ],
  },
};

// SQL: WHERE price >= 10 AND price <= 100

Logical NOT ($not)

const query: QueryAST = {
  object: 'customer',
  where: {
    $not: {
      status: { $in: ['deleted', 'suspended'] },
    },
  },
};

// SQL: WHERE NOT (status IN ('deleted', 'suspended'))

Complex Logic (AND + OR)

const query: QueryAST = {
  object: 'opportunity',
  where: {
    type: 'new_business',                  // AND (type = new_business)
    $or: [                                 // AND (
      { amount: { $gt: 100000 } },         //   amount > 100000
      { is_strategic: true },              //   OR is_strategic = true
    ],                                     // )
  },
};

// SQL: WHERE type = 'new_business'
//      AND (amount > 100000 OR is_strategic = true)

Date, Datetime, and Time Filters

Before a comparison is built, the driver puts the comparand into the same canonical form the column is stored in (SqlDriver.coerceFilterValue) — the identical function the write path uses, on every dialect, so the two sides of a comparison can never be decided by their shapes disagreeing:

import type { FilterCondition } from '@objectstack/spec/data';

// `date` field — a bare calendar day matches that day
const onDay: FilterCondition = { close_date: '2024-01-15' };

// `date` range — $between is inclusive on both ends
const inYear: FilterCondition = {
  close_date: { $between: ['2024-01-01', '2024-12-31'] },
};

// `datetime` field — a bare `YYYY-MM-DD` is completed to midnight UTC
// (`2024-01-15T00:00:00.000Z`), so this is an exact-instant match, not "that day"
const atMidnight: FilterCondition = { created_at: '2024-01-15' };

// A whole UTC day on a `datetime` needs a half-open range
const duringDay: FilterCondition = {
  created_at: { $gte: '2024-01-15', $lt: '2024-01-16' },
};

// `time` field — `'09:00'` is completed to `'09:00:00'`
const businessHours: FilterCondition = {
  start_time: { $gte: '09:00', $lte: '18:00' },
};

A bare YYYY-MM-DD bound is a calendar day. As a lower bound ($gte) it means the start of that day (midnight UTC); as an upper bound ($lte, or the max of a $between) it covers the whole day — on a datetime column the driver compiles it half-open (< next day), so the $between above includes everything that happened on Dec 31. A full ISO timestamp keeps exact-instant semantics on every operator.

Null Checks

// Field IS NULL
where: { manager_id: { $null: true } }

// Field IS NOT NULL
where: { phone: { $null: false } }

// Field exists (NoSQL)
where: { metadata: { $exists: true } }

Filtering Across Relationships

Filter on the local foreign key, or run two queries:

const techAccounts = await engine.find('account', {
  where: { industry: 'tech', annual_revenue: { $gt: 1000000 } },
  fields: ['id'],
});

const opportunities = await engine.find('opportunity', {
  where: { account_id: { $in: techAccounts.map((a) => a.id) } },
});

3. Sorting

Sorting uses the orderBy array of SortNode objects.

Single Field Sort

const query: QueryAST = {
  object: 'customer',
  orderBy: [{ field: 'company_name', order: 'asc' }],
};

// SQL: ORDER BY company_name ASC

Multiple Fields

const query: QueryAST = {
  object: 'opportunity',
  orderBy: [
    { field: 'priority', order: 'desc' },
    { field: 'created_at', order: 'asc' },
  ],
};

// SQL: ORDER BY priority DESC, created_at ASC

Sorting on Related Fields

4. Relationships (Expand)

The expand property enables recursive loading of related records through the reference field types — lookup, master_detail, user and tree (REFERENCE_VALUE_TYPES). Each key is a relationship field name; the value is a nested QueryAST. Over the REST/protocol ingress, a key that is not one of those is 400 INVALID_FIELD rather than a silently absent relation.

Basic Expand

const query: QueryAST = {
  object: 'opportunity',
  fields: ['name', 'amount'],
  expand: {
    account: {
      object: 'account',
      fields: ['company_name'],
    },
  },
};

// Result:
// [
//   {
//     name: 'Big Deal',
//     amount: 100000,
//     account: { company_name: 'Acme Corp' }
//   }
// ]

Multiple Relationships

const query: QueryAST = {
  object: 'opportunity',
  fields: ['name'],
  expand: {
    account: { object: 'account', fields: ['company_name'] },
    owner: { object: 'user', fields: ['name', 'email'] },
  },
};

Nested Expand (Deep Loading)

const query: QueryAST = {
  object: 'task',
  fields: ['title', 'assignee'],
  expand: {
    assignee: { object: 'user', fields: ['name', 'email'] },
    project: {
      object: 'project',
      expand: {
        org: { object: 'org', fields: ['name'] },
      },
    },
  },
};

Filtered Expand

Expansion follows the same reference field types as above — lookup, master_detail, user and tree — i.e. the foreign key lives on the object you are querying. The nested QueryAST can filter (where) and select (fields) the related records:

const query: QueryAST = {
  object: 'task',
  fields: ['title', 'assignee'],
  expand: {
    // assignee is a lookup → user; only resolve assignees that are still active.
    assignee: {
      object: 'user',
      where: { active: { $eq: true } },
      fields: ['name', 'email'],
    },
  },
};

The nested where is AND-merged with the batch $in the engine uses to load related records, so a related record is attached only when it also matches your filter. A foreign key whose target is filtered out is left as the raw id (unresolved) rather than dropped.

The nested fields is a projection of the related record, and you do not have to name id in it to make the expansion work. id is the join key the batch $in re-attaches by, so the engine adds it to its own sub-read and strips it back out when you did not ask for it — the attached record carries exactly the columns you listed. Name id explicitly when you want it (e.g. to link to the related record).

5. Aggregations

Aggregations use the aggregations array with AggregationNode objects, combined with groupBy for grouping.

Count

const count = await engine.count('customer', {
  where: { industry: 'tech' },
});

// SQL: SELECT COUNT(*) FROM customer WHERE industry = 'tech'
// Result: 42

Group By with Aggregations

const query: QueryAST = {
  object: 'opportunity',
  fields: ['stage'],
  groupBy: ['stage'],
  aggregations: [
    { function: 'count', alias: 'count' },
    { function: 'sum', field: 'amount', alias: 'total_amount' },
  ],
};

// Result:
// [
//   { stage: 'Prospecting', count: 10, total_amount: 500000 },
//   { stage: 'Qualification', count: 5, total_amount: 250000 }
// ]

SQL compilation:

SELECT
  stage,
  COUNT(*) AS count,
  SUM(amount) AS total_amount
FROM opportunity
GROUP BY stage

Aggregation Functions

const query: QueryAST = {
  object: 'opportunity',
  aggregations: [
    { function: 'count', alias: 'count' },
    { function: 'sum', field: 'amount', alias: 'total' },
    { function: 'avg', field: 'amount', alias: 'average' },
    { function: 'min', field: 'amount', alias: 'min_amount' },
    { function: 'max', field: 'amount', alias: 'max_amount' },
  ],
};

// Result:
// { count: 100, total: 5000000, average: 50000, min_amount: 10000, max_amount: 500000 }

Schema enum: count, sum, avg, min, max, count_distinct.

Group By Multiple Fields

const query: QueryAST = {
  object: 'opportunity',
  fields: ['stage', 'owner_name'],
  groupBy: ['stage', 'owner_name'],
  aggregations: [
    { function: 'count', alias: 'count' },
    { function: 'sum', field: 'amount', alias: 'total' },
  ],
};

HAVING Clause

Enforced since #4286 (ADR-0049, resolved to enforce). The engine applies having itself, AFTER aggregation, identically on the native-driver path and the in-memory fallback (packages/objectql/src/having-filter.ts) — the same correct-first / optimize-later two-tier shape date bucketing uses; native SQL HAVING pushdown can come later behind a driver capability flag without changing these semantics. The REST findData() aggregate branch forwards the clause.

having references the aggregated row's own columns — aggregation aliases and groupBy projections — with the ordinary FilterCondition operators and $and / $or / $not. An unknown operator rejects the query loudly rather than being ignored (an ignored operator would silently return unfiltered aggregates — the ADR-0078 failure mode enforcement exists to end).

// Only accounts with > $1M pipeline
const rows = await engine.aggregate('opportunity', {
  groupBy: ['account_id'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total' },
  ],
  having: { total: { $gt: 1_000_000 } },
});

Date Bucketing

A groupBy entry may be an object carrying dateGranularity, which buckets a date/datetime column into uniform periods. The bucketed value is projected under the field name (or alias, on the in-memory path):

const revenueByMonth = await engine.aggregate('order', {
  where: { status: 'completed' },
  groupBy: [{ field: 'created_at', dateGranularity: 'month' }],
  aggregations: [
    { function: 'sum', field: 'total_amount', alias: 'revenue' },
  ],
});

Granularities: day, week, month, quarter, year. The engine pushes the bucket down to the driver only when it advertises native support for that granularity (supports.queryDateGranularity); otherwise it falls back to in-memory bucketing over the driver's raw rows.

6. Advanced Queries

Distinct

query.distinct was removed in @objectstack/spec 17 (#4286): no driver ever rendered SELECT DISTINCT, and the flag's only observable effect was mis-wired — it silently suppressed the REST list count (total/hasMore degraded to a page-local estimate) while still returning duplicate rows. The key is tombstoned and QueryBuilder.distinct() was removed with it; the count suppression is gone, so total is truthful again. Unique combinations come from groupBy, deduplicated counts from the count_distinct aggregation, and one column's distinct values from the driver's own distinct() method (implemented by the SQL and in-memory drivers; it is not part of the IDataDriver contract):

const industries = await driver.distinct('account', 'industry');

SqlDriver.distinct() presents each value exactly the way find() presents that column — a date as YYYY-MM-DD and a time as HH:MM:SS[.fff] on every dialect, a datetime folded to canonical UTC ISO on SQLite (the one dialect where storage differs from presentation; Postgres and MySQL hand back their own native temporal value) — and then re-deduplicates the presented values, because SQL DISTINCT compares the stored form.

It is tenant-scoped on the same terms as find() — the organization_id predicate is applied when the call carries options.tenantId (the fourth argument), and the example above omits it, so it returns the column's values across every tenant. Until #6792 the predicate was dropped even when tenantId was supplied, which made a scoped call disclose every other tenant's values for that column.

Full-Text Search

The search parameter does not reach a full-text index. The engine expands it into an $or of $contains predicates across the object's server-resolved searchable fields (ADR-0061) and deletes search from the AST before the driver sees it — every driver already runs $or/$contains, so no driver support is needed (which is also why the old supports.fullTextSearch capability bit had no reader and was retired in 17.0.0, #4634).

search takes the query text itself — that is the canonical spelling (ADR-0061 D1: the client says what to search for, the server decides which fields), and it is what every surface sends. The structured form is equivalent for the two members that drive the expansion, and carries the experimental knobs below:

// Canonical — the server resolves the fields from object metadata
const query: QueryAST = {
  object: 'article',
  search: 'ObjectStack tutorial',
  searchFields: ['title', 'content'],   // optional narrowing
  limit: 10,
};

// Structured form — `query` + `fields` mean exactly the same thing
const structured: QueryAST = {
  object: 'article',
  search: { query: 'ObjectStack tutorial', fields: ['title', 'content'] },
  limit: 10,
};

Field resolution is server-side and never client-trusted: the requested fields (searchFields, or search.fields in the structured form) are intersected with the object's declared searchableFields (or, absent those, an auto-default of the name field plus short-text/enum fields), so naming a field outside that set can never widen the search — and over the REST/protocol ingress it is 400 INVALID_FIELD outright (#4254), because the engine-side intersection alone used to drop the unknown name and fall back to scanning the full searchable set. Internal callers reaching engine.find() directly keep the tolerant intersection. Multiple whitespace-separated terms are AND-ed and fields are OR-ed. Case sensitivity comes from the operator the expansion emits, not from the expansion: it emits a plain $contains, which is case-sensitive by the rule in Case Sensitivity above. Note what that means for search — a user typing acme does not find ACME Corp. Only select / status option labels are matched case-insensitively by the expansion itself.

fuzzy, boost, operator, minScore, language, and highlight carry [EXPERIMENTAL — not enforced] markers (#4286): the schema accepts them, the expansion ignores them.

Searching by a related record's title — mirror the value

Search targets are this object's own columns. A dotted path is not one of them: searchFields: ['project_id.name'] is refused at the ingress rather than dropped, because the search axis does not resolve traversal the way fields, sort and filters do:

Unknown field 'project_id.name' on object 'task'. '$searchFields' narrows which
columns 'search' scans, so a name the object does not declare cannot narrow
anything — and the engine used to drop it and scan the default columns instead,
answering a NARROWER search with a WIDER one. 'search' scans this object's own
columns; a related record's column cannot be a search target.

The declarative answer is a mirror field: copy the related record's title into a stored field on this object and declare that field searchable — a task list searched by project name carries a project_name text column on task, maintained on write and listed in task.searchableFields, so the expansion stays a single-table $or of $contains. The mirror must be stored: a formula field is virtual, no driver materializes a column for it, and a $contains against one has nothing to scan. Cross-object search paths are rejected by design, not pending — see Schema Design → Searching by a related record's title for the maintenance hooks.

Joins — removed (#4286)

query.joins was removed in @objectstack/spec 17 (#4286, ADR-0049 enforce-or-remove): no driver ever read it, so a query carrying joins silently ran as a single-table query. The key is tombstoned — authoring it is a tsc error, and a query that still carries it (even as an empty array) fails to parse with the upgrade prescription. The JoinNode / JoinType / JoinStrategy exports left with it.

Use expand (§4) for relationship loading — the live spelling for related records, and for single related columns too, since its nested QueryAST both filters (where) and selects (fields) the related record's columns. Otherwise, two queries joined in application code.

A dotted fields path is not the alternative. 'owner.name' still parses — FieldNode is string, a shape check — but no driver ever resolved one, and the ingress refuses it with 400 INVALID_FIELD (#7532). Where the value is wanted on the queried object itself, denormalise it onto that object (a stored field, written when the source changes) — the same remedy the sort axis prescribes (#6924).

{ fields: ['title'],                expand: { project_id: { object: 'project' } } }
// -> nothing to resolve; no related record comes back

{ fields: ['title', 'project_id'],  expand: { project_id: { object: 'project' } } }
// -> works

Window Functions — removed from the request surface (#4286)

query.windowFunctions was removed in @objectstack/spec 17 (#4286): find() never applied it, so every OVER clause it declared was silently dropped. The key is tombstoned, and the WindowFunction / WindowSpec / WindowFunctionNode exports left with it — they declared field / over / frame members that no executor ever read.

Window functions remain a SQL-driver door: findWithWindowFunctions(), which is not on the IDataDriver contract and is not surfaced by IDataEngine. Its input is the driver's own flat shape — function name, alias, and optional flat partitionBy / orderBy:

const ranked = await driver.findWithWindowFunctions('order', {
  windowFunctions: [
    {
      function: 'row_number',
      alias: 'rank',
      partitionBy: ['customer_id'],
      orderBy: [{ field: 'amount', order: 'desc' }],
    },
  ],
});

// SQL: SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank
//      FROM orders

That method always selects * plus the window columns — it does not honor a fields projection, and niladic rendering means argument-taking functions (LAG(field)) emit without their argument. For request-level analytics use aggregations + groupBy (§5).

Tenancy works exactly as it does on find(): the driver applies its organization_id predicate only when the call carries options.tenantId. The example above omits it and therefore reads across every tenant — the intended unscoped/admin path, but a deliberate choice rather than a default to inherit. (Until #6792 this door dropped options.tenantId even when it was supplied, so a scoped call still returned every tenant's rows.)

7. Pagination

Offset-Based Pagination

// Page 1 (records 0-9)
const page1 = await engine.find('customer', {
  limit: 10,
  offset: 0,
});

// Page 2 (records 10-19)
const page2 = await engine.find('customer', {
  limit: 10,
  offset: 10,
});

Drawback: Slow for large offsets (database still scans all skipped rows).

Determinism: the walk above visits every customer exactly once even though it names no orderBy. A driver orders any paged read by a unique column of its own — appended to your sort keys, or standing alone when you gave none — because an unordered LIMIT/OFFSET slices an arrangement nothing holds steady: SQL leaves row order to the plan, and MongoDB's natural order moves when a document does. Page 2 would otherwise repeat a row from page 1 and drop another, with every page full and every row real (objectui#3106, #4363). A query with no limit/offset is untouched — nothing is being sliced, so no order is imposed.

Keyset Pagination

// First page
const page = await engine.find('customer', {
  limit: 10,
  orderBy: [{ field: 'created_at', order: 'asc' }],
});

// Next page — seek past the last row instead of offsetting.
// The comparand is canonicalised by the same function that wrote the column
// (`coerceFilterValue` → `storageDatetimeValue`), so the range compare is an
// ordinary indexable comparison on every dialect: canonical UTC ISO text on
// SQLite, `timestamptz` on Postgres, `DATETIME(3)` on MySQL.
const next = await engine.find('customer', {
  where: { created_at: { $gt: page[page.length - 1].created_at } },
  limit: 10,
  orderBy: [{ field: 'created_at', order: 'asc' }],
});

Advantage: Consistent performance regardless of page depth.

8. Real-World Examples

CRM: Open Opportunities

const openOpportunities = await engine.find('opportunity', {
  where: {
    stage: { $nin: ['Closed Won', 'Closed Lost'] },
    owner_id: currentUser.id,
  },
  orderBy: [{ field: 'amount', order: 'desc' }],
  fields: ['name', 'amount', 'close_date'],
  expand: {
    account: { object: 'account', fields: ['company_name'] },
  },
});

E-Commerce: Product Search

const products = await engine.find('product', {
  where: {
    is_active: true,
    inventory_qty: { $gt: 0 },
    category_id: { $in: selectedCategories },
    price: { $between: [minPrice, maxPrice] },
  },
  search: {
    query: searchTerm,
    fields: ['name', 'description'],
  },
  orderBy: [{ field: 'popularity_score', order: 'desc' }],
  limit: 20,
});

Analytics: Revenue by Month

month is not a column — bucket the created_at instant with dateGranularity. Note that engine.aggregate() accepts only where / groupBy / aggregations (plus a timezone for bucketing): there is no orderBy or limit on this path, so sort the returned rows yourself.

const monthlyRevenue = await engine.aggregate('order', {
  where: {
    status: 'completed',
    // `created_at` is a datetime — a bare date is read as midnight UTC
    created_at: { $gte: '2024-01-01' },
  },
  groupBy: [{ field: 'created_at', dateGranularity: 'month' }],
  aggregations: [
    { function: 'sum', field: 'total_amount', alias: 'revenue' },
    { function: 'count', alias: 'order_count' },
    { function: 'avg', field: 'total_amount', alias: 'avg_order' },
  ],
});

const sorted = monthlyRevenue.sort((a, b) =>
  String(a.created_at).localeCompare(String(b.created_at)),
);

9. Error Handling

Unknown Fields Are Tolerated

Unknown field names are not rejected by engine.find() — a projected field that doesn't exist on the object is silently dropped (matching OData / SELECT * tolerance), so a stale field reference never fails the whole query:

const rows = await engine.find('customer', {
  fields: ['name', 'nonexistent'],  // `nonexistent` is not on the schema
});
// Returns each row with `name`; `nonexistent` is omitted — no error thrown.

Security Violations

Row-level scoping is applied by narrowing the query — the security middleware AND-merges its read filter into where, so an over-broad filter returns fewer rows rather than throwing. What throws is an operation the caller is not permitted to run at all, or a predicate that references a field the caller cannot read:

try {
  await engine.find('account', { where: { owner_id: currentUser.id } });
} catch (error) {
  // PermissionDeniedError:
  //   [Security] Access denied: operation 'find' on object 'account' ...
  //   [Security] Access denied: query on 'account' references field(s) not
  //              readable by the caller: ...
}

Legacy Compatibility

import { parseFilterAST } from '@objectstack/spec/data';

// UI Builder output (tuple format)
const uiFilter = ['and', ['status', '=', 'active'], ['priority', '>', 3]];

// Convert to canonical format
const where = parseFilterAST(uiFilter);
// → { $and: [{ status: 'active' }, { priority: { $gt: 3 } }] }

Next Steps