Summaries
A summary aggregates the whole filtered set: every row that matches the request, not just the page. It runs as one SQL statement and only when the request asks for it.
| Request | rows.length | summary.rows |
|---|---|---|
25 per page, status shipped, 1,204 shipped orders | 25 | 1,204 |
| page 49 of the same request | 4 | 1,204 |
| a page past the end | 0 | 1,204 |
Declare a summary
The callback returns an object of Drizzle aggregate expressions. It receives the root table's columns and virtual fields, but not private fields.
import { count, sql, sum } from "drizzle-orm";
import { createQueryEngine, virtual } from "drizzle-resource";
import { db } from "./db";
import { schema } from "./schema";
import { relations } from "./relations";
const engine = createQueryEngine({
db,
schema,
relations,
models: {
orders: { private: ["deletedAt"] },
customers: {
hidden: ["orgId"],
virtual: (customer) => ({
displayName: virtual.text(sql`upper(${customer.name})`),
}),
},
},
}).withContext<{ orgId: string }>();
const orders = engine.defineResource("orders", {
relations: { customer: true },
views: {
list: {
select: {
id: true,
reference: true,
totalAmount: true,
customer: { select: { id: true, displayName: true } },
},
},
internal: {
select: {
id: true,
customer: { select: { id: true, name: true, orgId: true } },
},
},
},
query: {
scope: (f, context) => f.is("customer.orgId", context.orgId),
search: { allowed: ["reference", "customer.displayName"] },
},
summary: (order) => ({
rows: count(),
totalAmount: sum(order.totalAmount),
}),
});
type Summary = typeof orders.$infer.summary;
const result = await orders.query({
context: { orgId: "acme" },
view: "list",
summary: true,
request: {
pagination: { pageIndex: 1, pageSize: 25 },
filters: [],
sorting: [],
search: { value: "", fields: [] },
},
});
result.summary.totalAmount;
What it counts
- The scope, filters and search of the request apply before aggregating.
- Page size, offset, cursor and sorting don't. An empty page can have a full summary.
- Rows repeated by to-many joins are not counted twice. Aggregates through to-many paths are not supported yet.
- All metrics come from one statement, without loading the matching rows into memory.
When the request also asks for an exact count, the same statement supplies it, so the page doesn't run a separate count.
Types
Drizzle decides the types: count() is a number, sum() a decimal string or null, which keeps money out of floating point. An empty sum stays null; use coalesce if you need 0.
A summary is its own output, independent of the view. It may aggregate a hidden field on purpose.
Refresh only the totals
querySummary({ request, context, db? }) returns just the summary with the same definition, so a client can refresh a totals footer when filters change without reloading the page.
Cost
- Totals read every matching row, so they cost more as the filtered set grows.
- The page and the summary are separate statements. Pass a repeatable-read transaction as
dbwhen both must see the same snapshot. - Summaries need the built-in query and ids steps. A custom rows step is fine.
scan does not compute summaries.