How a request is handled
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.
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.defaultswhensortingis empty; - uses
search.defaultswhensearch.fieldsis 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
| Situation | Main cost | What to do |
|---|---|---|
| Large table, deep pages | OFFSET and the exact count | Use cursor pagination; ask for counts only when needed |
| Sorting or filtering via relations | Joins in the page-ids query | Index the join and sort columns; see Performance |
| Search on many-to-many paths | One EXISTS subquery per path | Keep those paths out of search.defaults |
| Several facets | One aggregate per incompatible group | Keep facet paths on the same joins |
| Facet on a many-to-many path | count(distinct) and a junction join | Remove 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.