Node.js Reference ยท node:sqlite and Runtime Services
Node.js database.aggregate() method
Reference for database.aggregate() in node:sqlite: write custom aggregate and window functions with start, step, result and inverse, plus common pitfalls.
database.aggregate(name, options) registers a custom aggregate function: one that reads many rows and produces a single value, as SUM, AVG and MAX do. You describe it with a starting value, a step function called once per row, and an optional result function that turns the final state into the value SQL sees. With an extra inverse function the same aggregate also works as a window function.
Use it for statistics SQLite lacks (median, standard deviation, a geometric mean), for string building, or for any fold you would otherwise do in JavaScript after fetching every row. Plain row-by-row functions are registered with database.function().
import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync(":memory:"); db.aggregate("product", { start: 1, step: (total, value) => total * value }); console.log(db.prepare("SELECT product(column1) AS p FROM (VALUES (2), (3), (4))").get());
Try it
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.
Syntax
database.aggregate(name, options) // returns undefined
| Option | Description |
|---|---|
start |
The initial state: a primitive value, or a function returning the state. Required. Use a function for arrays and objects. |
step(state, ...args) |
Called once per row with the current state and the SQL arguments. Returns the new state. Required. |
result(state) |
Converts the final state to the SQL result. Optional: without it the state itself is the result. |
inverse(state, ...args) |
Removes the effect of a row that left the window. Makes the function usable with OVER. |
deterministic |
Same arguments always give the same result (default false). |
directOnly |
Forbid use inside triggers and views (default false). |
useBigIntArguments |
Pass SQLite integers as bigint (default false). |
varargs |
Accept any number of arguments (default false). |
Arity. Without varargs, the number of SQL arguments is step.length - 1: the first parameter is the state. A step of (state, value) is a one-argument aggregate.
Return values. The result must be a supported SQLite value: number, bigint, string, Uint8Array or null (and undefined, which becomes NULL).
Errors. A missing or non-function step throws ERR_INVALID_ARG_TYPE. A missing start throws the same error. Calling the aggregate with the wrong number of arguments fails with "wrong number of arguments to function name()". An exception thrown in step, result or inverse aborts the statement and is rethrown to the caller. Using the function with OVER when no inverse was given fails with "name() may not be used as a window function".
Usage notes
- The state must be returned. If
stepreturnsundefined, the next call receivesundefinedas the state, and SQL ends up withNULL(or a JavaScript error insidestep, if it reads the state). The second example shows the lost result. - Group by group. SQLite starts each group from
startagain. With a functionstartyou get a new array or object each time; with a value, mutable state leaks from one query to the next. - Empty input. On zero rows
resultruns with the initial state, soSUM-style functions that should returnNULLneed a check inresult. - NULL arguments arrive as
null. Built-in aggregates skipNULLvalues. Yours must do that on purpose, because5 + nullis5but5 * nullis0in JavaScript. inversemust be an exact undo ofstep. For things that cannot be undone, such asmax, leave it out and use the function as a normal aggregate.- State is kept in JavaScript between calls, so large states cost memory. A
medianover millions of rows holds them all. - Function and aggregate names live in one namespace per connection, and re-registering a name replaces it.