Skip to content
Tutorials Center
My learning

Topics

Node.js Reference ยท node:sqlite and Runtime Services

Node.js database.function() method

Reference for database.function() in node:sqlite: register JavaScript functions callable from SQL, with varargs, deterministic, BigInt arguments and error handling.

database.function(name, [options,] fn) registers a JavaScript function that your SQL can call by name. SQLite already has functions such as upper(), length() and round(), but you often need something it lacks: a URL slug, a regular-expression test, a custom hash, a business rule. With function() you write it once in JavaScript and use it in SELECT lists, WHERE clauses, ORDER BY, views, triggers and generated columns.

The function is registered on one connection only. It lasts until you close that database, and registering the same name again replaces the earlier version. Aggregates (functions that fold many rows into one, like SUM) use database.aggregate() instead.

NODE
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.function("double", (x) => x * 2);
console.log(db.prepare("SELECT double(21) AS answer").get());

Try it

Node.js A slug function and a REGEXP operator

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 Arguments, return values and errors

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.function(name[, options], fn)  // returns undefined
Parameter Description
name The SQL name. SQL function names are case-insensitive.
options.deterministic Promise that the same arguments always give the same result (default false). Lets SQLite use the function in indexes and cache calls.
options.directOnly Forbid use inside triggers and views, which is safer for functions with side effects (default false).
options.useBigIntArguments Receive SQLite integers as bigint instead of number (default false).
options.varargs Accept any number of arguments (default false).
fn The JavaScript function. Its length sets the number of SQL arguments unless varargs is set.

Argument conversion. INTEGER and REAL arrive as number (or bigint with useBigIntArguments), TEXT as string, BLOB as Uint8Array and NULL as null.

Return values. number, bigint, string, Uint8Array (and Buffer), null and undefined (stored as NULL) are accepted. Anything else, such as a boolean or an object, makes the SQL statement fail with ERR_SQLITE_ERROR ("Returned JavaScript value cannot be converted to a SQLite value"). A returned number is a REAL to SQLite; return a bigint for an exact INTEGER.

Errors. A wrong number of arguments (without varargs) fails with "wrong number of arguments to function name()". A non-string name or a non-function throws ERR_INVALID_ARG_TYPE. An exception thrown inside your function aborts the statement and is rethrown to the caller with its original message.

Usage notes

  • A function you register on one DatabaseSync is invisible to other connections, and to the sqlite3 command line tool. Views and triggers that use it cannot be read elsewhere.
  • Mark pure functions deterministic: true. SQLite may then evaluate the call once instead of for every row, and you may use it in an expression index or a generated column. Never mark a function that reads the clock or random numbers.
  • Calling JavaScript for every row is slower than a built-in. For a scan of millions of rows, filter with plain SQL first and call your function only on the survivors.
  • The function runs synchronously on the main thread. It cannot be async: a returned promise is not a supported value.
  • NULL handling is your job. SQL built-ins usually return NULL for a NULL input, and users expect your function to do the same.
  • A function that has side effects, such as writing a file, should set directOnly: true, so that a trigger or view cannot call it without the user's knowledge.
  • X REGEXP Y is only available after you register a function named regexp. SQLite passes the pattern as the first argument and the text as the second.