forge-orm

Appearance

Aggregations ​

count, sum, avg, min, max, groupBy, having, distinct — the aggregation surface, what forge emits per dialect (GROUP BY for SQL, $group pipeline for Mongo), nullable handling, decimal precision, and the patterns for dashboard-shaped queries.

The README chapter Grouping and aggregates covers the call shape — groupBy({ by, where, _count, _sum, _avg, _min, _max, having, orderBy }). This doc is the companion that goes one layer down: every aggregate's per-dialect emit, the way _count differs from count, how having interleaves with where, the nullable and precision gotchas that show up on big tables, and the dashboard-shaped queries that exercise the whole surface.

If you have not read QUERIES.md yet, start there — this doc assumes you already know findMany, where, select, and orderBy.

Contents ​

The aggregation surface ​

Five entry points, picked by what the caller wants back.

  • count({ where, distinct? }) → number. "How many rows match."
  • aggregate({ where, _avg, _sum, _min, _max, _count }) → one { _avg, _sum, _min, _max, _count } payload. "Overall sum / avg / min / max across these rows." Equivalent to groupBy with by: [].
  • groupBy({ by, _avg, _sum, _min, _max, _count, where, having, orderBy, take, skip }) → array of { <by-col>: value, _count?, _sum?, _avg?, _min?, _max? }. "Per-group aggregates."
  • findMany({ include: { _count: { select: { <relation>: true } } } }) → rows with _count.<relation> attached. "Related-row counts per parent."
  • aggregate({ pipeline }) (Mongo-only) → raw documents from the pipeline. Escape hatch into the native $lookup / $facet / $bucket surface.

count and groupBy are the two real workhorses — the rest is sugar or escape-hatch. All five honour the same where operator vocabulary findMany uses (see QUERIES.md → where) and the aggregate body runs after the where filter narrows the input — see where vs having.

count vs _count vs countDistinct ​

Three counting shapes, three different SQL forms.

Top-level count({ where }) — COUNT(*) ​

ts
const n = await db.user.count({ where: { active: true } });

Emits SELECT COUNT(*) FROM "user" WHERE "active" = $1 on PG / SQLite / DuckDB (with the dialect's quoting and placeholders), COUNT_BIG(*) on MSSQL, and coll.countDocuments({ active: true }) on Mongo. COUNT(*) includes rows where every column is NULL. The PG driver returns the count as a string (bigint) and forge coerces through Number() at hydration — safe up to 2^53. For tables that approach that magnitude, switch to a $queryRaw SELECT COUNT(*)::text and parse with BigInt yourself.

count({ distinct: [col] }) — COUNT(DISTINCT col) ​

ts
await db.user.count({ distinct: ['email_domain'] });
// → SELECT COUNT(DISTINCT "email_domain") FROM "user"
// Mongo: [{ $group: { _id: '$email_domain' } }, { $count: 'count' }]

COUNT(DISTINCT col) skips NULL values on every SQL dialect — that's the SQL standard. So this over 1,000 rows where 50 have NULL email_domain returns the number of distinct non-null domains. To count NULL as its own bucket, use coalesce("email_domain", '') through $queryRaw.

Multi-column distinct ​

ts
await db.member.count({ distinct: ['org_id', 'role'] });
// → SELECT COUNT(*) FROM (SELECT 1 FROM "member" GROUP BY "org_id","role") s
// Mongo: [{ $group: { _id: { org_id: '$org_id', role: '$role' } } }, { $count: 'count' }]

PG also supports SELECT COUNT(DISTINCT ("org_id","role")) natively but forge does not emit the row-value form because MySQL and SQLite don't. The subquery-then-count form is portable everywhere.

_count on a relation — per-row cardinality ​

_count is the only synthetic-field surface forge exposes on a read. It drops a cardinality alongside each row.

ts
await db.user.findMany({
  include: { _count: { select: { posts: true, comments: true } } },
});
// → user[0]._count = { posts: 5, comments: 12 }

Per relation, that emits a correlated subquery on SQL ((SELECT COUNT(*) FROM "post" WHERE "post"."author_id" = "user"."id") AS "_count_posts") and a $lookup + $size on Mongo. The subquery form is sargable iff the relation's foreign-key column is indexed — f.string().index() on author_id, or part of a compound. Without that, every parent row triggers a sequential scan on the child table and the query collapses past a few thousand parents. See N+1.

A filtered _count picks up the inner filter inside the subquery:

ts
await db.user.findMany({
  include: { _count: { select: { posts: { where: { status: 'PUBLISHED' } } } } },
});

_count: { posts: true } counts every post; the filtered form counts only PUBLISHED. The two forms read similarly and the wrong one looks correct in testing until a draft shows up — be deliberate.

count accepts take / skip / cursor ​

count honours the same pagination args as findMany. The common use is "is there a 21st row?" without materialising the page:

ts
const more = await db.post.count({ where: { author_id: meId }, take: 21, skip: 20 });
// more === 1 means there's at least one row past page 1 of 20

Emits SELECT COUNT(*) FROM (SELECT 1 FROM "post" WHERE … LIMIT 21 OFFSET 20). Don't reach for it when the answer is "the total" — drop the pagination args for that.

groupBy keys — single and composite ​

by accepts an array of one or more own-scalar column names. Each distinct combination becomes one result row.

Single key ​

ts
await db.order.groupBy({
  by:     ['status'],
  _count: { _all: true },
  _sum:   { total: true },
});
// → [{ status: 'PAID', _count: { _all: 17 }, _sum: { total: 12_300 } }, … ]

PG / SQLite / DuckDB emit:

sql
SELECT "status", COUNT(*) AS "__agg_count__all", SUM("total") AS "__agg_sum_total"
FROM "order" GROUP BY "status";

MySQL emits the same with backtick quoting; MSSQL uses COUNT_BIG(*) and square-bracket quoting; Mongo emits [{ $group: { _id: { status: '$status' }, __agg_count__all: { $sum: 1 }, __agg_sum_total: { $sum: '$total' } } }].

The __agg_<bucket>_<field> aliases are wire-stable — the executor re-nests them into the { _count: { _all }, _sum: { total } } shape the caller sees. The aliases are an implementation detail; you never see them unless you $queryRaw the same table.

Composite keys ​

Pass more than one column. Each unique tuple is one bucket.

ts
await db.order.groupBy({
  by:     ['status', 'currency'],
  _sum:   { total: true },
  _count: { _all: true },
});
// → [
//   { status: 'PAID', currency: 'NGN', _sum: { total: 12_300 }, _count: { _all: 17 } },
//   { status: 'PAID', currency: 'USD', _sum: { total:  4_200 }, _count: { _all:  9 } },
//   …
// ]

GROUP BY "status", "currency" on SQL. On Mongo the _id becomes a nested document { status, currency } and the executor unwraps it back into top-level keys at hydration.

The number of groups is bounded by the cardinality of the columns. (status, currency) over a few hundred enum values is fine; (user_id, day) over a year of activity is a few hundred thousand groups and is still fine on a properly-indexed table; (session_id, …) over a billion-row event table is where the planner spills to disk. See Performance.

Grouping by a derived value ​

Forge does not emit GROUP BY <expression> — the by list takes column names only. Date-bucket grouping (per day, per week) is the common case; see Date/time aggregation for the two portable workarounds (a generated column at DDL time, or $queryRaw).

having — filtering aggregates ​

having filters groups after the aggregate. Two equivalent shapes both compile to the same IR — forge's normalizeHaving flips field-first into bucket-first before the dialect compilers see it.

ts
// field-first (Prisma's surface)
having: { total: { _sum: { gte: 120 } } }

// bucket-first (forge's IR shape)
having: { _sum: { total: { gte: 120 } } }

The vocabulary is the same six comparison operators that where exposes: equals, not, lt, lte, gt, gte. Anything richer (in, contains, between) is not honoured — the IR builder silently drops unrecognised ops. Use $queryRaw for richer HAVING predicates (HAVING SUM("total") BETWEEN $1 AND $2).

Per-dialect emit ​

ts
having: { _sum: { total: { gt: 1_000 } }, _count: { _all: { gte: 5 } } }

Emits HAVING SUM("total") > $1 AND COUNT(*) >= $2 on PG / SQLite / DuckDB (with dialect quoting and placeholders); COUNT_BIG(*) on MSSQL; { $match: { __agg_sum_total: { $gt: 1000 }, __agg_count__all: { $gte: 5 } } } on Mongo after the $group stage.

Multiple having clauses are joined with AND on SQL and a single $match document on Mongo. There is no having: { OR: [...] } today — nest the predicate logic in a $queryRaw if you need disjunction across aggregate buckets.

_count._all in having ​

ts
having: { _count: { _all: { gt: 0 } } }

_all is the magic key that means "all rows in the group", i.e. COUNT(*). Per-column counts in having target the column directly: _count: { email: { gt: 0 } } → HAVING COUNT("email") > $1. Recall that COUNT(col) skips NULLs, so _count: { email: { gt: 0 } } is "at least one non-null email in the group", not "at least one row".

_count / _sum / _avg / _min / _max — the bucket shape ​

Each bucket key maps directly to its SQL function — _count → COUNT(col) or COUNT(*) for _all, _sum → SUM, _avg → AVG, _min → MIN, _max → MAX. Mongo uses $sum / $avg / $min / $max on the field, with { $sum: 1 } for _all and a $cond wrapper that skips NULLs for per-column counts (matching SQL's NULL-skip behaviour). _sum returns NULL on all-null input on SQL but 0 on Mongo — see Nullable handling. _avg is floating-point — see Decimal precision.

The call-site shape is the same across every bucket: a record from column name to true. Pass true to opt the column in.

ts
await db.order.groupBy({
  by:    ['status'],
  _count: { _all: true, customer_id: true },   // COUNT(*) AND COUNT("customer_id")
  _sum:   { total: true, tax: true },          // SUM("total"), SUM("tax")
  _avg:   { total: true },
  _min:   { total: true },
  _max:   { total: true },
});

Each bucket key surfaces on the return row only if you asked for it. The shape is not flattened — _sum.total stays under _sum, not under the bucket name's prefix. Destructure on the caller side:

ts
for (const row of result) {
  const { status, _sum, _count, _avg } = row;
  console.log(status, _sum.total, _count._all, _avg.total);
}

The unbucketed aggregate form ​

For "what's the overall sum across this filter", pass no by:

ts
await db.order.aggregate({
  where: { status: 'PAID', created_at: { gte: monthStart } },
  _sum:  { total: true, items_count: true },
  _avg:  { total: true },
  _min:  { total: true },
  _max:  { total: true },
  _count: true,                  // top-level row count, no nesting
});
// → { _sum: { total: 19_872, items_count: 187 },
//     _avg: { total: 432.18 },
//     _min: { total: 12 }, _max: { total: 4500 },
//     _count: 46 }

Per-dialect emit (PG):

sql
SELECT
  SUM("total")        AS "__agg_sum_total",
  SUM("items_count")  AS "__agg_sum_items_count",
  AVG("total")        AS "__agg_avg_total",
  MIN("total")        AS "__agg_min_total",
  MAX("total")        AS "__agg_max_total",
  COUNT(*)            AS "__agg_count"
FROM "order"
WHERE "status" = $1 AND "created_at" >= $2;

No GROUP BY — there is exactly one row in the result. On Mongo the $group stage uses _id: null and the executor unwraps the single document back into the { _sum, _avg, … } payload.

Per-dialect emit table ​

The query is groupBy({ by: ['status'], _sum: { total: true }, _count: { _all: true }, having: { _sum: { total: { gt: 1000 } } }, orderBy: { _sum: { total: 'desc' } }, take: 10 }).

PG / SQLite / DuckDB:

sql
SELECT "status", SUM("total") AS "__agg_sum_total", COUNT(*) AS "__agg_count__all"
FROM "order"
GROUP BY "status"
HAVING SUM("total") > $1
ORDER BY SUM("total") DESC
LIMIT 10;

MySQL emits the same shape with backtick quoting and ? placeholders. MSSQL puts the row cap into TOP 10 at the head and uses COUNT_BIG(*) — COUNT(*) on MSSQL returns int and overflows at 2.1 billion rows. MySQL is the one to watch on legacy installs: ONLY_FULL_GROUP_BY became the default in 5.7.5 and older installs allow SELECT a, b, SUM(c) GROUP BY a with b undefined. Forge always lists every by column in both clauses, so the legacy footgun is a non-issue at the API layer.

Mongo:

js
[
  { $group: { _id: { status: '$status' }, __agg_sum_total: { $sum: '$total' }, __agg_count__all: { $sum: 1 } } },
  { $match: { __agg_sum_total: { $gt: 1000 } } },
  { $sort:  { __agg_sum_total: -1 } },
  { $limit: 10 },
]

$group → $match → $sort → $limit mirrors WHERE → GROUP BY → HAVING → ORDER BY → LIMIT exactly. The Mongo planner cannot push the post-$group $match back into the pre-$group filter the way SQL planners push HAVING into WHERE — so the index choice for the $group stage is on the grouping key, not the filter columns. See Performance.

Nullable handling ​

The single most-asked-about gotcha. SQL aggregate functions and Mongo aggregate operators handle NULL slightly differently, and the answers differ from findMany's.

COUNT(*) vs COUNT(col) ​

COUNT(*) counts every row (even all-NULL rows). COUNT(col) counts rows where col is NOT NULL. COUNT(DISTINCT col) counts distinct non-null values. The forge mapping:

ts
db.user.count({})                                            // COUNT(*)
db.user.groupBy({ by:['org_id'], _count: { email: true } })  // COUNT("email") — non-null
db.user.count({ distinct: ['email'] })                       // COUNT(DISTINCT "email") — distinct non-null

The per-column form is the trap. _count: { email: true } returns the count of rows in the group with a non-null email — if your model has 100 users in an org and 30 have no email, that bucket reads 70, which looks wrong when the caller expected the group's row count. For "every row in the group", use _count: { _all: true }.

SUM over all-NULL input ​

SQL standard: SUM(col) returns NULL on all-NULL input, not 0. PG, MySQL, SQLite, DuckDB, MSSQL all match this. In the forge result, that surfaces as _sum: { revenue: null }. Mongo's $sum is the outlier: empty / all-null input yields 0, not null.

ts
const row = await db.order.aggregate({ where: { status: 'CANCELLED' }, _sum: { revenue: true } });
const total = row._sum.revenue ?? 0;       // explicit zero-coerce — portable everywhere

This is one of the few places the same forge call returns a different scalar across dialects. Pin it with the ?? 0 coerce and you get the SQL-style "I asked for a sum, I got a number" surface everywhere.

AVG, MIN, MAX over all-NULL input ​

All three return null (SQL) or null (Mongo) on empty / all-null input. There is no 0 divergence here — AVG of an empty set is genuinely undefined and SQL is right to say so. Coerce at the call site if your dashboard wants a "no data" placeholder.

GROUP BY on a nullable column ​

Every dialect groups NULL into its own bucket — one row with _id: null on Mongo, one row with the column being NULL on SQL.

ts
await db.order.groupBy({ by: ['referrer_id'], _count: { _all: true } });
// → [{ referrer_id: null, _count: { _all: 42 } }, { referrer_id: 'u1', ...}, … ]

That's standard SQL behaviour and almost always what the caller wants. If you want to exclude the NULL bucket, add where: { referrer_id: { not: null } }.

Decimal precision in _sum and _avg ​

Floats are not associative — (a + b) + c and a + (b + c) can land on different bits when a and b are large and c is small. SUM(col) accumulates left-to-right and the planner is free to reorder under a hash aggregate. The visible drift on a sum of a million floating-point dollar amounts is on the order of cents to single dollars; on an _avg of the same, much smaller.

For money, declare the column as f.decimal({ precision, scale }):

ts
const Order = model('order', {
  id:    f.id(),
  total: f.decimal({ precision: 18, scale: 4 }),
});

Per-dialect storage: PG / MySQL / DuckDB store NUMERIC(18, 4) / DECIMAL(18, 4) as arbitrary-precision and SUM stays exact; MSSQL widens to DECIMAL(38, 4) to avoid overflow; SQLite stores as TEXT and aggregates promote to float unless you ship the SQLite decimal extension; Mongo's Decimal128 is IEEE 754-2008 decimal and $sum widens up to 34 significant digits.

The PG driver returns NUMERIC as a string; forge's hydrator coerces to JavaScript number (a double, so precision is lost on the way out). For exact decimal arithmetic, pull the raw string through $queryRaw and parse with decimal.js or big.js in the caller. On Mongo, Decimal128 survives the round trip if your driver preserves the BSON type — see Mongo.

AVG widening ​

AVG of an integer column returns a float on PG (numeric) and a float on Mongo. SQLite's AVG widens to REAL — the IEEE 754 double. For an integer-only running average, do the math yourself:

ts
const { _sum, _count } = await db.order.aggregate({ _sum: { items_count: true }, _count: true });
const avgItems = _count > 0 ? _sum.items_count / _count : 0;

That's exact integer division on the way in and one float divide at the end — no accumulation error.

Date/time aggregation ​

The classic dashboard query: revenue per day for the last 30 days. Forge does not expose a portable date-bucket operator in the by list — GROUP BY date_trunc('day', "created_at") is per-dialect SQL.

Three portable workarounds, in order of preference.

1. Generated column at DDL time ​

Declare a stored-generated column for the bucket and group on that. Works on PG, MySQL 5.7+, SQLite 3.31+, MSSQL, DuckDB. Mongo doesn't need it — see option 3.

sql
ALTER TABLE "order"
  ADD COLUMN "created_day" date GENERATED ALWAYS AS (("created_at")::date) STORED;
CREATE INDEX "order_created_day_idx" ON "order"("created_day");

Then forge groups portably:

ts
await db.order.groupBy({
  by:    ['created_day'],
  where: { created_at: { gte: thirtyDaysAgo } },
  _sum:  { total: true },
});

The generated column is computed on insert and indexable. Query plans match a plain integer-keyed dashboard.

2. $queryRaw with the dialect's truncation function ​

ts
// Postgres
const rows = await db.$queryRaw<Array<{ day: Date; total: number }>>`
  SELECT date_trunc('day', "created_at") AS day, SUM("total") AS total
  FROM "order"
  WHERE "created_at" >= ${thirtyDaysAgo}
  GROUP BY day ORDER BY day ASC;
`;

Per-dialect day/week/month equivalents: PG and DuckDB use date_trunc('day' | 'week' | 'month', col); MySQL uses DATE(col), YEARWEEK(col, 1), DATE_FORMAT(col, '%Y-%m-01'); SQLite uses date(col), strftime('%Y-W%W', col), strftime('%Y-%m-01', col); MSSQL uses CAST(col AS date), DATEPART(week, col) + year, DATEFROMPARTS(YEAR(col), MONTH(col), 1). Wrap the per-dialect SQL in a helper if your app targets more than one — the abstraction is yours to own, not the ORM's.

3. Mongo $dateTrunc ​

Mongo 5.0+ has $dateTrunc, which fits inside an aggregate pipeline cleanly. Reach for the raw db.<model>.aggregate({ pipeline }) surface:

ts
await db.order.aggregate({
  pipeline: [
    { $match: { created_at: { $gte: thirtyDaysAgo } } },
    { $group: {
        _id:   { $dateTrunc: { date: '$created_at', unit: 'day' } },
        total: { $sum: '$total' },
      } },
    { $sort: { _id: 1 } },
  ],
});

On Mongo 4.x, swap for $dateToString: { format: '%Y-%m-%d', date: '$created_at' } in the _id — string buckets sort lexicographically the same as the underlying dates as long as the format is ISO.

Aggregating embedded / JSON fields ​

groupBy's by list takes own-scalar column names — not JSON paths. For GROUP BY data->'tier', drop to $queryRaw (SQL) or the raw aggregate pipeline (Mongo). The per-dialect read syntax — "data"->>'tier' on PG, JSON_UNQUOTE(JSON_EXTRACT(...)) on MySQL, json_extract on SQLite/DuckDB, JSON_VALUE on MSSQL — is the same syntax forge uses for the path operator inside where; see JSON-PATH.md for the full table.

ts
// Postgres
await db.$queryRaw<Array<{ tier: string; n: number }>>`
  SELECT "data"->>'tier' AS tier, COUNT(*) AS n
  FROM "user" GROUP BY tier;
`;

// Mongo — dotted path inside $group._id
await db.user.aggregate({
  pipeline: [{ $group: { _id: '$data.tier', n: { $sum: 1 } } }],
});

When the JSON path is read often, lift it to a top-level scalar column — a generated column on PG / MySQL, or a write-side hook on every dialect. The dashboard gets indexed-column performance and the JSON-path surface stays the escape hatch for ad-hoc queries.

where vs having — input filter vs output filter ​

The mental model: where is "which rows go into the bucket", having is "which buckets come out the other side".

ts
await db.order.groupBy({
  by:    ['status'],
  where: { created_at: { gte: monthStart } },       // rows from this month only
  _sum:  { total: true },
  having: { _sum: { total: { gt: 10_000 } } },      // only groups whose monthly sum > 10k
});

On SQL that's WHERE "created_at" >= $1 … GROUP BY "status" HAVING SUM("total") > $2; on Mongo, two $match stages bracket the $group. If the predicate references a non-aggregate column, put it in where — the input narrows before the group, fewer rows scan, any index on the predicate column kicks in. If it references an aggregate, it has to be in having because the value doesn't exist until after the group.

A common mistake: having: { status: { equals: 'PAID' } } when you meant where: { status: 'PAID' }. The having form is silently dropped (forge's IR ignores unrecognised buckets) and the filter never applies — every status comes through and the total is wrong. The keys in having must be aggregate buckets (_count, _sum, _avg, _min, _max), not column names. Field-first (having: { total: { _sum: { gt: 1 } } }) is accepted because _sum is the inner key; the IR normaliser flips it. Bucket-less (having: { total: { gt: 1 } }) is silently dropped — spell out the bucket.

ORDER BY an aggregate result ​

Sort by a bucket key with the same orderBy shape findMany uses, with the aggregate bucket as the field path.

ts
orderBy: { _sum: { total: 'desc' } }
// or: orderBy: [{ _sum: { total: 'desc' } }, { status: 'asc' }]

Emits ORDER BY SUM("total") DESC on SQL (every dialect re-emits the aggregate function rather than aliasing through the SELECT-list alias, which is portable enough); { $sort: { __agg_sum_total: -1 } } on the Mongo alias.

Order on a by column directly with orderBy: { status: 'asc' }. On Mongo the by columns live under _id.<name>, which the executor resolves automatically. Sorting by a bucket you didn't ask for is silently dropped on SQL (alias doesn't exist) and an error on Mongo — always ask for every bucket you intend to sort on.

Pagination of aggregated results ​

groupBy honours take (alias limit) and skip (alias offset) — both compile to LIMIT/OFFSET on SQL and $limit/$skip on Mongo.

ts
await db.order.groupBy({
  by:      ['customer_id'],
  _sum:    { total: true },
  orderBy: [{ _sum: { total: 'desc' } }, { customer_id: 'asc' }],
  take:    20,
  skip:    40,
});

The same offset-based caveat from QUERIES.md → pagination applies: if a new order arrives between page loads, the group sums shift and the page boundary moves. For a live dashboard where the aggregate is changing under you, the right answer is usually to materialise the aggregate into a snapshot table (or a materialised view) and paginate over that — see BACKEND.md for the snapshot pattern.

Cursor pagination on a grouped result is harder than on a plain findMany because the visible sort key (the aggregate) is not unique. Two patterns work: cursor on (sum, group_key) with the group key as a tie-breaker, and implement the cursor advance yourself via where: { OR: [ { sum: { lt: lastSum } }, { sum: lastSum, customer_id: { gt: lastKey } } ] } — groupBy has no built-in cursor surface today. Or cap the result: if the dashboard only shows the top 100, skip pagination entirely and let the UI virtualise.

Window functions vs aggregations ​

groupBy collapses to one row per group; window functions keep every row and attach an aggregate computed across a window. Running totals, ranks, moving averages, and top-N-per-group are all window-function shapes. Forge does not emit window functions today — every aggregate goes through GROUP BY — so reach for $queryRaw:

ts
// top 3 most-expensive orders per customer
const rows = await db.$queryRaw<Array<{ id: string; customer_id: string; total: number; rk: number }>>`
  SELECT id, customer_id, total,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rk
  FROM "order" WHERE "status" = 'PAID';
`;
const top3 = rows.filter((r) => r.rk <= 3);

Per-dialect support: PG, MySQL 8+, SQLite 3.25+, MSSQL, and DuckDB all support window functions; MySQL 5.7 does not; Mongo's $setWindowFields (5.0+) is the closest analogue through the raw aggregate({ pipeline }) surface. See RAW-SQL.md.

Performance — indexing for GROUP BY ​

The single biggest lever on aggregate latency is whether the by column (or the leading edge of a compound index on it) is indexed.

SQL plans ​

With a B-tree on the by column, the planner uses an index-only scan (PG) or range scan (MySQL/SQLite) and groups in order — linear in the number of distinct buckets. Without one, it falls back to a sequential scan plus a hash aggregate (PG / DuckDB) or sort-then-group (MySQL / SQLite) — linear in the row count.

For groupBy({ by: ['status'], _sum: { total: true } }) on PG over 1M rows: ~120 ms with Seq Scan → HashAggregate, ~12 ms with Index Only Scan → GroupAggregate. The 10× gap holds across PG / MySQL / MSSQL / DuckDB. SQLite uses sort-then-group by default; the index turns the sort into a no-op.

Composite indexes and filter-before-group ​

For composite by, the index has to match the leading edge: a (status, currency) index serves by: ['status'] and by: ['status', 'currency'] equally well, but not by: ['currency'] alone — same rule as where. The combined (where_col, by_col) index is the optimal shape when both clauses are present:

ts
const Order = model('order', {
  id: f.id(), org_id: f.string().index(), status: f.string(), total: f.int(),
}, {
  indexes: [{ on: ['org_id', 'status'] }],                 // covers per-org GROUP BY status
});

groupBy({ by: ['status'], where: { org_id }, _sum: { total: true } }) uses the compound index for both the WHERE and the GROUP BY, no sort.

Mongo ​

Mongo's $group cannot use an index for the grouping itself — the $group stage always scans every document the $match lets through. The index only helps the $match. The optimisation lever is:

  1. Put the most-selective $match first.
  2. Index the $match filter columns.
  3. Cap the result with $limit when possible (after $sort).

For aggregations that run on every page load, materialise the result with $merge or $out into a rollup collection and read that directly. See MONGO.md.

Mongo aggregation pipeline ​

db.<model>.aggregate({ pipeline }) is the escape hatch into Mongo's native pipeline — every stage the driver supports, with forge's extended-JSON coercion ({ $oid: 'abc' } → native ObjectId) and a post-processor that stringifies ObjectId and remaps top-level _id to id (see src/__tests__/aggregate-output.spec.ts).

How groupBy translates ​

forge callPipeline stage
where$match before $group
by$group._id
_count: { _all: true }__agg_count__all: { $sum: 1 }
_count: { col: true }per-column non-null count
_sum/_avg/_min/_max$sum / $avg / $min / $max on $col
having$match after $group on the __agg_* aliases
orderBy / take / skip$sort / $limit / $skip

When to drop to the raw pipeline ​

Reach for aggregate({ pipeline }) directly when you need a $lookup (Mongo's JOIN — groupBy cannot pull related-collection columns), a $facet for a multi-panel dashboard (see worked example (d) below), a $bucket / $bucketAuto histogram, $dateTrunc in the group key, or a $setWindowFields window-function stage.

ts
// Customers with order count, lifetime value, average order — single round-trip
const rows = await db.user.aggregate({
  pipeline: [
    { $match: { active: true } },
    { $lookup: {
        from: 'order', localField: '_id', foreignField: 'customer_id', as: 'orders',
        pipeline: [{ $match: { status: 'PAID' } }],
      } },
    { $project: {
        name: 1,
        n:              { $size: '$orders' },
        lifetime_value: { $sum:  '$orders.total' },
        avg_order:      { $avg:  '$orders.total' },
      } },
    { $sort: { lifetime_value: -1 } },
    { $limit: 50 },
  ],
});

Four worked dashboards ​

(a) Revenue per day for the last 30 days ​

The bread-and-butter dashboard widget. Date-bucketed sum with a stable sort.

PG / MySQL / SQLite / DuckDB / MSSQL — with a generated created_day column on the table:

ts
const rows = await db.order.groupBy({
  by:      ['created_day'],
  where:   { created_at: { gte: thirtyDaysAgo }, status: 'PAID' },
  _sum:    { total: true },
  _count:  { _all: true },
  orderBy: { created_day: 'asc' },
});
// → [{ created_day: Date('2026-05-26'), _sum: { total: 12_300 }, _count: { _all: 17 } }, …]

Without the generated column, use $queryRaw with the dialect's date_trunc:

ts
const rows = await db.$queryRaw<Array<{ day: Date; total: number; n: number }>>`
  SELECT date_trunc('day', "created_at") AS day,
         SUM("total")                    AS total,
         COUNT(*)                        AS n
  FROM "order"
  WHERE "created_at" >= ${thirtyDaysAgo} AND "status" = 'PAID'
  GROUP BY day
  ORDER BY day ASC;
`;

Mongo:

ts
const rows = await db.order.aggregate({
  pipeline: [
    { $match: { created_at: { $gte: thirtyDaysAgo }, status: 'PAID' } },
    { $group: {
        _id:    { $dateTrunc: { date: '$created_at', unit: 'day' } },
        total:  { $sum: '$total' },
        n:      { $sum: 1 },
      } },
    { $sort: { _id: 1 } },
  ],
});

For a zero-fill (days with no orders show up as total: 0), synthesise the missing days in the caller — neither SQL nor Mongo will emit rows for buckets with zero matching input.

(b) Top 10 customers by lifetime value ​

ts
const top = await db.order.groupBy({
  by:      ['customer_id'],
  where:   { status: 'PAID' },
  _sum:    { total: true },
  _count:  { _all: true },
  orderBy: { _sum: { total: 'desc' } },
  take:    10,
});
// → [{ customer_id: 'u_42', _sum: { total: 89_400 }, _count: { _all: 124 } }, …]

To attach the customer name, follow up with a second findMany against id: { in: top.map(r => r.customer_id) } and zip into a Map. The alternative is a $queryRaw JOIN on SQL or a $lookup on Mongo — both pay one extra scan to save the second round-trip, which is rarely worth it for a 10-row top-N dashboard.

(c) Histogram of order amounts ​

PG / SQLite / DuckDB ship width_bucket(value, min, max, count) — 1-indexed bucket the value falls into, with one extra bucket above max. MySQL doesn't have it; emulate with FLOOR(total / 500).

ts
const hist = await db.$queryRaw<Array<{ bucket: number; n: number }>>`
  SELECT width_bucket("total", 0, 10000, 20) AS bucket, COUNT(*) AS n
  FROM "order" WHERE "status" = 'PAID'
  GROUP BY bucket ORDER BY bucket;
`;

Mongo's $bucket (with explicit boundaries) and $bucketAuto (forge picks them) are the native shape:

ts
const hist = await db.order.aggregate({
  pipeline: [
    { $match: { status: 'PAID' } },
    { $bucket: {
        groupBy: '$total',
        boundaries: [0, 500, 1000, 2500, 5000, 10000, Infinity],
        default:    'over',
        output:     { n: { $sum: 1 } },
      } },
  ],
});

(d) Mongo $facet — multi-dimensional admin dashboard ​

One query, four panels, one shared $match scan.

ts
const dashboard = await db.order.aggregate({
  pipeline: [
    { $match: { created_at: { $gte: monthStart } } },
    { $facet: {
        byStatus: [
          { $group: { _id: '$status', total: { $sum: '$total' }, n: { $sum: 1 } } },
        ],
        topCustomers: [
          { $group: { _id: '$customer_id', total: { $sum: '$total' } } },
          { $sort:  { total: -1 } },
          { $limit: 10 },
        ],
        daily: [
          { $group: { _id: { $dateTrunc: { date: '$created_at', unit: 'day' } }, total: { $sum: '$total' } } },
          { $sort: { _id: 1 } },
        ],
        totals: [
          { $group: { _id: null, n: { $sum: 1 }, gross: { $sum: '$total' }, avg: { $avg: '$total' } } },
        ],
      } },
  ],
});
// → [{ byStatus: [...], topCustomers: [...], daily: [...], totals: [...] }]

On SQL the equivalent is four separate groupBy calls — or one $queryRaw with four CTEs. For a dashboard that loads on every navigation the single-round-trip pattern pays off; for one-shot exports the cost is roughly the same.

  • QUERIES.md — findMany, where, select, orderBy, pagination, count, the _count synthetic field
  • JSON-PATH.md — JSON path reads, used inside aggregations for grouping on embedded scalars
  • MONGO.md — Mongo adapter specifics, including Decimal128, materialised aggregates via $merge/$out, and the rest of the pipeline surface
  • RAW-SQL.md — $queryRaw and $executeRaw for window functions, recursive CTEs, dialect-specific aggregate functions
  • N-PLUS-ONE.md — when _count on a relation degrades to a per-parent subquery
  • DUCKDB.md — DuckDB's columnar planner for analytical aggregates
  • INDEXES.md — composite indexes that cover both where and by
  • BACKEND.md — materialised aggregates and rollup tables for dashboards that read every request

MIT licensed. Built from the repository's own markdown — README.md, CHANGELOG.md and docs/.

© johnsonfash