Getting started

How a request is handled

The five steps every request goes through, the SQL each one runs, and where the cost is.

Every request goes through the same steps in the same order. Pick a request below to follow it: a normal one, one that uses a field the resource doesn't allow, and one that tries to read another tenant's data.

Client requesta normal request
{ search: { value: "halv" },
  filters: [{ key: "status", operator: "is", value: "shipped" }],
  facets: [{ key: "status" }], summary: true }
Responsewaiting…
 
Engine5 steps, always in this order
01scope
02validate
03page ids
04rows
05facets & totals

1. Scope

No database work. The engine adds the resource's query.scope to the request's top-level filters and combines it with AND. A client filter can only narrow the result; an OR group from the client stays inside the scope.

2. Validate and apply defaults

Still no database work. The engine:

  • uses sort.defaults when sorting is empty;
  • uses search.defaults when search.fields is empty;
  • fills in the default page size;
  • checks every sort key, filter key, search field and facet key against what the resource allows.

A key that isn't allowed is rejected here, before any SQL runs.

3. Page ids

One query selects the ids of the requested page, with the scope, filters and search applied:

SELECT orders.id
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
WHERE [scope + client filters + search]
ORDER BY [sorting]
LIMIT [pageSize] OFFSET [(pageIndex - 1) * pageSize]

On PostgreSQL the engine reads primary keys and unique constraints from your schema. When they prove a join can't duplicate root rows, it selects ids directly instead of deduplicating them.

Cursor pagination replaces the offset with a boundary over the sort keys and fetches one extra id to know whether there is a next page. An exact count only runs when the request asks for count: "exact".

This step is usually the most expensive one: sorting through relations, searching many-to-many paths and deep offsets all add cost here.

4. Rows

The rows for those ids are loaded with Drizzle's relational query API, then put back in page order:

db
.
query
.
orders
.
findMany
({
where
:
inArray
(
orders
.
id
,
ids
),
with
: {
customer
: true,
orderLines
: {
with
: {
product
: true } },
}, });

When the request names a view, only the view's fields are selected.

5. Facets and totals

Only when the request asks for them. Each facet groups the filtered rows by one field:

SELECT orders.status AS value, count(*) AS count
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
WHERE [same scope and filters, minus this facet's own filter in exclude-self mode]
GROUP BY orders.status
ORDER BY count DESC
LIMIT [facet.limit]

On PostgreSQL, facets with the same filters and joins share one GROUPING SETS statement, which can also supply the exact page count. A summary totals the whole filtered set in one aggregate statement.

exclude-self removes the facet's own client filter, never the scope.

Where the cost is

SituationMain costWhat to do
Large table, deep pagesOFFSET and the exact countUse cursor pagination; ask for counts only when needed
Sorting or filtering via relationsJoins in the page-ids queryIndex the join and sort columns; see Performance
Search on many-to-many pathsOne EXISTS subquery per pathKeep those paths out of search.defaults
Several facetsOne aggregate per incompatible groupKeep facet paths on the same joins
Facet on a many-to-many pathcount(distinct) and a junction joinRemove it from facets.allowed or write a custom stage

The engine keeps schema metadata in weak references and does not build a growing cache of request shapes.

Copyright © 2026