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.
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
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.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
DatabaseSyncis invisible to other connections, and to thesqlite3command 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. NULLhandling is your job. SQL built-ins usually returnNULLfor aNULLinput, 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 Yis only available after you register a function namedregexp. SQLite passes the pattern as the first argument and the text as the second.