Skip to content
Tutorials Center
My learning

Topics

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().

NODE
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

Node.js Grouped aggregates: median and a joined list

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.

Node.js Window functions with inverse, and the start trap

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

NODE
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 step returns undefined, the next call receives undefined as the state, and SQL ends up with NULL (or a JavaScript error inside step, if it reads the state). The second example shows the lost result.
  • Group by group. SQLite starts each group from start again. With a function start you 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 result runs with the initial state, so SUM-style functions that should return NULL need a check in result.
  • NULL arguments arrive as null. Built-in aggregates skip NULL values. Yours must do that on purpose, because 5 + null is 5 but 5 * null is 0 in JavaScript.
  • inverse must be an exact undo of step. For things that cannot be undone, such as max, leave it out and use the function as a normal aggregate.
  • State is kept in JavaScript between calls, so large states cost memory. A median over millions of rows holds them all.
  • Function and aggregate names live in one namespace per connection, and re-registering a name replaces it.