method

sqlite.Database.function

name: string,
fn: (...args: any[]) => SQLFunctionResult
): this;

Define a SQL function that calls a JavaScript function.

The number of parameters of fn (fn.length) is the number of arguments the SQL function takes, unless varargs is set. Functions with the same name and different numbers of arguments exist side by side. A function with the same name and number of arguments replaces the earlier one, also a function that is built into SQLite.

An argument has the type that a column of a result row has: INTEGER is a number (a bigint with safeIntegers), BLOB is a Uint8Array. The return value is stored like a bound parameter, and undefined is NULL. Any other return value, such as an object or a promise, makes the query throw a TypeError.

What fn throws ends the query, and the call that ran the query (get(), all(), run(), ...) throws the same value.

While fn runs, every other use of this Database and of its statements throws TypeError: This database connection is busy executing a query.

@param name

Name of the SQL function

@param fn

Called with this set to undefined

@returns

The Database

db.function("add2", (a, b) => a + b);
db.query("SELECT add2(12, 4) AS sum").get();
// => { sum: 16 }

db.function("count_args", { varargs: true }, (...args) => args.length);
db.query("SELECT count_args(1, 2, 3) AS n").get();
// => { n: 3 }
name: string,
options: FunctionOptions,
fn: (...args: any[]) => SQLFunctionResult
): this;

Define a SQL function that calls a JavaScript function.

The number of parameters of fn (fn.length) is the number of arguments the SQL function takes, unless varargs is set. Functions with the same name and different numbers of arguments exist side by side. A function with the same name and number of arguments replaces the earlier one, also a function that is built into SQLite.

An argument has the type that a column of a result row has: INTEGER is a number (a bigint with safeIntegers), BLOB is a Uint8Array. The return value is stored like a bound parameter, and undefined is NULL. Any other return value, such as an object or a promise, makes the query throw a TypeError.

What fn throws ends the query, and the call that ran the query (get(), all(), run(), ...) throws the same value.

While fn runs, every other use of this Database and of its statements throws TypeError: This database connection is busy executing a query.

@param name

Name of the SQL function

@param fn

Called with this set to undefined

@returns

The Database

db.function("add2", (a, b) => a + b);
db.query("SELECT add2(12, 4) AS sum").get();
// => { sum: 16 }

db.function("count_args", { varargs: true }, (...args) => args.length);
db.query("SELECT count_args(1, 2, 3) AS n").get();
// => { n: 3 }

Referenced types

interface FunctionOptions

Options for Database.function and Database.aggregate

  • deterministic?: boolean

    Promise SQLite that the function returns the same result for the same arguments. SQLite then allows it in an index and in a generated column, and can call it fewer times.

    Equivalent to SQLITE_DETERMINISTIC

  • directOnly?: boolean

    Only SQL that is passed to the Database can call the function. A view, a trigger or another part of the schema cannot. Use it for a function with side effects when the database file comes from somebody else.

    Equivalent to SQLITE_DIRECTONLY

  • safeIntegers?: boolean

    An INTEGER argument is a bigint instead of a number, and a bigint result that does not fit in a signed 64-bit integer throws a RangeError.

  • varargs?: boolean

    The SQL function takes any number of arguments, whatever the number of parameters of the JavaScript function.