Aggregates

A dashboard that loads every invoice to add up the amounts moves all those rows to the server first. Aggregates let the database do the math and return only the totals, in the same request as the rows they belong to.

Enable aggregates in PostgRESTCopy Anchor Link

PostgREST (12 and later) supports aggregate functions, but they are off by default. Turn them on for the authenticator role in a migration:

alter role authenticator set pgrst.db_aggregates_enabled = 'true';
notify pgrst, 'reload config';

Without it, PostgREST answers PGRST123. better-supabase returns that as an invalid_request error whose hint names the setting, and better-supabase doctor warns (BS210) when your code uses aggregates while the setting is off. Over better-supabase/postgres the same calls compile to SQL and need no setting.

Aggregates run under RLS like any other read: they see only the rows the caller may read, and plugins such as tenant and softDelete scope them the same way as findMany.

Per-row aggregates of a relationCopy Anchor Link

_sum, _avg, _min and _max work like _count: each takes to-many relations and, per relation, the columns to aggregate:

const customers = await db.customers
  .findMany({
    select: ["id", "name"],
    include: {
      _count: { invoices: true },
      _sum: { invoices: { amount: true } },
      _max: { invoices: { issuedAt: true } },
    },
  })
  .orThrow();
// {
//   id: string; name: string;
//   _count: { invoices: number };
//   _sum: { invoices: { amount: number | null } };
//   _max: { invoices: { issuedAt: string | null } };
// }[]

The values are null when a row has no related rows. Keys follow the configured casing, like every other column.

Totals for a tableCopy Anchor Link

db.x.aggregate() returns one result for the rows that match where:

const totals = await db.invoices
  .aggregate({
    where: { status: "open" },
    _count: true,
    _sum: { amount: true },
    _avg: { amount: true },
  })
  .orThrow();
// { _count: number; _sum: { amount: number | null }; _avg: { amount: number | null } }

Grouped totalsCopy Anchor Link

With groupBy, it returns one result per distinct combination of those columns. orderBy sorts the groups by the groupBy columns; limit and offset page through the groups:

const byStatus = await db.customers
  .aggregate({
    groupBy: ["status"],
    _count: true,
    _min: { createdAt: true },
    orderBy: { status: "asc" },
  })
  .orThrow();
// { status: 'lead' | 'active' | 'archived'; _count: number; _min: { createdAt: string | null } }[]

Sort groups by an aggregateCopy Anchor Link

orderBy also takes _count and the measures, mixed with groupBy columns. A list applies the terms in order, so this returns the five statuses with the most customers, and breaks ties by status:

const top = await db.customers
  .aggregate({
    groupBy: ["status"],
    _count: true,
    orderBy: [{ _count: "desc" }, { status: "asc" }],
    limit: 5,
  })
  .orThrow();

A measure takes the columns it aggregates, each with a direction or { direction, nulls }: { _sum: { amount: "desc" } }, { _avg: { amount: "asc" } }, { _min: { issuedAt: "asc" } } and { _max: { issuedAt: "desc" } }. The sort doesn't need the aggregate in the result, and the result type doesn't change. _sum and _avg sort by number columns only, like the aggregates themselves.

PostgREST has no syntax to sort by an aggregate. better-supabase sends order=count.desc for _count, which Postgres reads as count(customers), the row count of each group. That needs a table without a column named count; on such a table the call returns an invalid_request error. Sorting by _sum, _avg, _min or _max returns an invalid_request error on PostgREST; over better-supabase/postgres and SQLite every sort compiles to order by in SQL.

Value typesCopy Anchor Link

_sum and _avg only accept number columns (and numeric columns read as strings). Passing a text column is a type error, and an invalid_request error at runtime.

Specs and query optionsCopy Anchor Link

aggregate is a read method, so it works everywhere a spec does:

const spec = betterSupabase.spec.customers.aggregate({
  groupBy: ["status"],
  _count: true,
});
const groups = await db.$run(spec).orThrow();

// TanStack Query
useQuery(queries.customers.aggregate({ groupBy: ["status"], _count: true }));

CostCopy Anchor Link

Each call is one request: (count), amount.sum() and friends are rendered into PostgREST's select, and into one select ... group by over SQL. No related rows cross the network.