new DatabaseSync(path[, options])#

• path<string> |<Buffer> |<URL> The path of the database. A SQLite database can bestored in a file or completelyin memory. To use a file-backed database,the path should be a file path. To use an in-memory database, the pathshould be the special name':memory:'.
• options<Object> Configuration options for the database connection. Thefollowing options are supported:
  • open<boolean> Iftrue, the database is opened by the constructor. Whenthis value isfalse, the database must be opened via theopen() method.Default:true.
  • readOnly<boolean> Iftrue, the database is opened in read-only mode.If the database does not exist, opening it will fail.Default:false.
  • enableForeignKeyConstraints<boolean> Iftrue, foreign key constraintsare enabled. This is recommended but can be disabled for compatibility withlegacy database schemas. The enforcement of foreign key constraints can beenabled and disabled after opening the database usingPRAGMA foreign_keys.Default:true.
  • enableDoubleQuotedStringLiterals<boolean> Iftrue, SQLite will acceptdouble-quoted string literals. This is not recommended but can beenabled for compatibility with legacy database schemas.Default:false.
  • allowExtension<boolean> Iftrue, theloadExtension SQL functionand theloadExtension() method are enabled.You can callenableLoadExtension(false) later to disable this feature.Default:false.
  • timeout<number> Thebusy timeout in milliseconds. This is the maximum amount oftime that SQLite will wait for a database lock to be released beforereturning an error.Default:0.
  • readBigInts<boolean> Iftrue, integer fields are read as JavaScriptBigInt values. Iffalse,integer fields are read as JavaScript numbers.Default:false.
  • returnArrays<boolean> Iftrue, query results are returned as arrays instead of objects.Default:false.
  • allowBareNamedParameters<boolean> Iftrue, allows binding named parameters without the prefixcharacter (e.g.,foo instead of:foo).Default:true.
  • allowUnknownNamedParameters<boolean> Iftrue, unknown named parameters are ignored when binding.Iffalse, an exception is thrown for unknown named parameters.Default:false.
  • defensive<boolean> Iftrue, enables the defensive flag. When the defensive flag is enabled,language features that allow ordinary SQL to deliberately corrupt the database file are disabled.The defensive flag can also be set usingenableDefensive().Default:false.

Constructs a newDatabaseSync instance.

database.aggregate(name, options)#

Registers a new aggregate function with the SQLite database. This method is a wrapper aroundsqlite3_create_window_function().

• name<string> The name of the SQLite function to create.
• options<Object> Function configuration settings.
  • deterministic<boolean> Iftrue, theSQLITE_DETERMINISTIC flag isset on the created function.Default:false.
  • directOnly<boolean> Iftrue, theSQLITE_DIRECTONLY flag is set onthe created function.Default:false.
  • useBigIntArguments<boolean> Iftrue, integer arguments tooptions.step andoptions.inverseare converted toBigInts. Iffalse, integer arguments are passed asJavaScript numbers.Default:false.
  • varargs<boolean> Iftrue,options.step andoptions.inverse may be invoked with any number ofarguments (between zero andSQLITE_MAX_FUNCTION_ARG). Iffalse,inverse andstep must be invoked with exactlylength arguments.Default:false.
  • start<number> |<string> |<null> |<Array> |<Object> |<Function> The identityvalue for the aggregation function. This value is used when the aggregationfunction is initialized. When a<Function> is passed the identity will be its return value.
  • step<Function> The function to call for each row in the aggregation. Thefunction receives the current state and the row value. The return value ofthis function should be the new state.
  • result<Function> The function to call to get the result of theaggregation. The function receives the final state and should return theresult of the aggregation.
  • inverse<Function> When this function is provided, theaggregate method will work as a window function.The function receives the current state and the dropped row value. The return value of this function should be thenew state.

When used as a window function, theresult function will be called multiple times.

const {DatabaseSync } =require('node:sqlite');const db =newDatabaseSync(':memory:');db.exec(`  CREATE TABLE t3(x, y);  INSERT INTO t3 VALUES ('a', 4),                        ('b', 5),                        ('c', 3),                        ('d', 8),                        ('e', 1);`);db.aggregate('sumint', {start:0,step:(acc, value) => acc + value,});db.prepare('SELECT sumint(y) as total FROM t3').get();// { total: 21 }import {DatabaseSync }from'node:sqlite';const db =newDatabaseSync(':memory:');db.exec(`  CREATE TABLE t3(x, y);  INSERT INTO t3 VALUES ('a', 4),                        ('b', 5),                        ('c', 3),                        ('d', 8),                        ('e', 1);`);db.aggregate('sumint', {start:0,step:(acc, value) => acc + value,});db.prepare('SELECT sumint(y) as total FROM t3').get();// { total: 21 }copy

database.close()#

Closes the database connection. An exception is thrown if the database is notopen. This method is a wrapper aroundsqlite3_close_v2().

database.loadExtension(path)#

• path<string> The path to the shared library to load.

Loads a shared library into the database connection. This method is a wrapperaroundsqlite3_load_extension(). It is required to enable theallowExtension option when constructing theDatabaseSync instance.

database.enableLoadExtension(allow)#

• allow<boolean> Whether to allow loading extensions.

Enables or disables theloadExtension SQL function, and theloadExtension()method. WhenallowExtension isfalse when constructing, you cannot enableloading extensions for security reasons.

database.enableDefensive(active)#

• active<boolean> Whether to set the defensive flag.

Enables or disables the defensive flag. When the defensive flag is active,language features that allow ordinary SQL to deliberately corrupt the database file are disabled.SeeSQLITE_DBCONFIG_DEFENSIVE in the SQLite documentation for details.

database.location([dbName])#

• dbName<string> Name of the database. This can be'main' (the default primary database) or any otherdatabase that has been added withATTACH DATABASEDefault:'main'.
• Returns:<string> |<null> The location of the database file. When using an in-memory database,this method returns null.

This method is a wrapper aroundsqlite3_db_filename()

database.exec(sql)#

• sql<string> A SQL string to execute.

This method allows one or more SQL statements to be executed without returningany results. This method is useful when executing SQL statements read from afile. This method is a wrapper aroundsqlite3_exec().

database.function(name[, options], function)#

• name<string> The name of the SQLite function to create.
• options<Object> Optional configuration settings for the function. Thefollowing properties are supported:
  • deterministic<boolean> Iftrue, theSQLITE_DETERMINISTIC flag isset on the created function.Default:false.
  • directOnly<boolean> Iftrue, theSQLITE_DIRECTONLY flag is set onthe created function.Default:false.
  • useBigIntArguments<boolean> Iftrue, integer arguments tofunctionare converted toBigInts. Iffalse, integer arguments are passed asJavaScript numbers.Default:false.
  • varargs<boolean> Iftrue,function may be invoked with any number ofarguments (between zero andSQLITE_MAX_FUNCTION_ARG). Iffalse,function must be invoked with exactlyfunction.length arguments.Default:false.
• function<Function> The JavaScript function to call when the SQLitefunction is invoked. The return value of this function should be a validSQLite data type: seeType conversion between JavaScript and SQLite.The result defaults toNULL if the return value isundefined.

This method is used to create SQLite user-defined functions. This method is awrapper aroundsqlite3_create_function_v2().

database.setAuthorizer(callback)#

• callback<Function> |<null> The authorizer function to set, ornull toclear the current authorizer.

Sets an authorizer callback that SQLite will invoke whenever it attempts toaccess data or modify the database schema through prepared statements.This can be used to implement security policies, audit access, or restrict certain operations.This method is a wrapper aroundsqlite3_set_authorizer().

When invoked, the callback receives five arguments:

• actionCode<number> The type of operation being performed (e.g.,SQLITE_INSERT,SQLITE_UPDATE,SQLITE_SELECT).
• arg1<string> |<null> The first argument (context-dependent, often a table name).
• arg2<string> |<null> The second argument (context-dependent, often a column name).
• dbName<string> |<null> The name of the database.
• triggerOrView<string> |<null> The name of the trigger or view causing the access.

The callback must return one of the following constants:

• SQLITE_OK - Allow the operation.
• SQLITE_DENY - Deny the operation (causes an error).
• SQLITE_IGNORE - Ignore the operation (silently skip).

const {DatabaseSync, constants } =require('node:sqlite');const db =newDatabaseSync(':memory:');// Set up an authorizer that denies all table creationdb.setAuthorizer((actionCode) => {if (actionCode === constants.SQLITE_CREATE_TABLE) {return constants.SQLITE_DENY;  }return constants.SQLITE_OK;});// This will workdb.prepare('SELECT 1').get();// This will throw an error due to authorization denialtry {  db.exec('CREATE TABLE blocked (id INTEGER)');}catch (err) {console.log('Operation blocked:', err.message);}import {DatabaseSync, constants }from'node:sqlite';const db =newDatabaseSync(':memory:');// Set up an authorizer that denies all table creationdb.setAuthorizer((actionCode) => {if (actionCode === constants.SQLITE_CREATE_TABLE) {return constants.SQLITE_DENY;  }return constants.SQLITE_OK;});// This will workdb.prepare('SELECT 1').get();// This will throw an error due to authorization denialtry {  db.exec('CREATE TABLE blocked (id INTEGER)');}catch (err) {console.log('Operation blocked:', err.message);}copy

database.isOpen#

• Type:<boolean> Whether the database is currently open or not.

database.isTransaction#

• Type:<boolean> Whether the database is currently within a transaction. This methodis a wrapper aroundsqlite3_get_autocommit().

database.open()#

Opens the database specified in thepath argument of theDatabaseSyncconstructor. This method should only be used when the database is not opened viathe constructor. An exception is thrown if the database is already open.

database.prepare(sql)#

• sql<string> A SQL string to compile to a prepared statement.
• Returns:<StatementSync> The prepared statement.

Compiles a SQL statement into aprepared statement. This method is a wrapperaroundsqlite3_prepare_v2().

database.createTagStore([maxSize])#

• maxSize<integer> The maximum number of prepared statements to cache.Default:1000.
• Returns:<SQLTagStore> A new SQL tag store for caching prepared statements.

Creates a newSQLTagStore, which is an LRU (Least Recently Used) cache forstoring prepared statements. This allows for the efficient reuse of preparedstatements by tagging them with a unique identifier.

When a tagged SQL literal is executed, theSQLTagStore checks if a preparedstatement for that specific SQL string already exists in the cache. If it does,the cached statement is used. If not, a new prepared statement is created,executed, and then stored in the cache for future use. This mechanism helps toavoid the overhead of repeatedly parsing and preparing the same SQL statements.

import {DatabaseSync }from'node:sqlite';const db =newDatabaseSync(':memory:');const sql = db.createTagStore();db.exec('CREATE TABLE users (id INT, name TEXT)');// Using the 'run' method to insert data.// The tagged literal is used to identify the prepared statement.sql.run`INSERT INTO users VALUES (1, 'Alice')`;sql.run`INSERT INTO users VALUES (2, 'Bob')`;// Using the 'get' method to retrieve a single row.const id =1;const user = sql.get`SELECT * FROM users WHERE id =${id}`;console.log(user);// { id: 1, name: 'Alice' }// Using the 'all' method to retrieve all rows.const allUsers = sql.all`SELECT * FROM users ORDER BY id`;console.log(allUsers);// [//   { id: 1, name: 'Alice' },//   { id: 2, name: 'Bob' }// ]const {DatabaseSync } =require('node:sqlite');const db =newDatabaseSync(':memory:');const sql = db.createTagStore();db.exec('CREATE TABLE users (id INT, name TEXT)');// Using the 'run' method to insert data.// The tagged literal is used to identify the prepared statement.sql.run`INSERT INTO users VALUES (1, 'Alice')`;sql.run`INSERT INTO users VALUES (2, 'Bob')`;// Using the 'get' method to retrieve a single row.const id =1;const user = sql.get`SELECT * FROM users WHERE id =${id}`;console.log(user);// { id: 1, name: 'Alice' }// Using the 'all' method to retrieve all rows.const allUsers = sql.all`SELECT * FROM users ORDER BY id`;console.log(allUsers);// [//   { id: 1, name: 'Alice' },//   { id: 2, name: 'Bob' }// ]copy

database.createSession([options])#

• options<Object> The configuration options for the session.
  • table<string> A specific table to track changes for. By default, changes to all tables are tracked.
  • db<string> Name of the database to track. This is useful when multiple databases have been added usingATTACH DATABASE.Default:'main'.
• Returns:<Session> A session handle.

Creates and attaches a session to the database. This method is a wrapper aroundsqlite3session_create() andsqlite3session_attach().

database.applyChangeset(changeset[, options])#

• changeset<Uint8Array> A binary changeset or patchset.
• options<Object> The configuration options for how the changes will be applied.

  • filter<Function> Skip changes that, when targeted table name is supplied to this function, return a truthy value.By default, all changes are attempted.

  • onConflict<Function> A function that determines how to handle conflicts. The function receives one argument,which can be one of the following values:

    • SQLITE_CHANGESET_DATA: ADELETE orUPDATE change does not contain the expected "before" values.
    • SQLITE_CHANGESET_NOTFOUND: A row matching the primary key of theDELETE orUPDATE change does not exist.
    • SQLITE_CHANGESET_CONFLICT: AnINSERT change results in a duplicate primary key.
    • SQLITE_CHANGESET_FOREIGN_KEY: Applying a change would result in a foreign key violation.
    • SQLITE_CHANGESET_CONSTRAINT: Applying a change results in aUNIQUE,CHECK, orNOT NULL constraintviolation.

    The function should return one of the following values:

    • SQLITE_CHANGESET_OMIT: Omit conflicting changes.
    • SQLITE_CHANGESET_REPLACE: Replace existing values with conflicting changes (only valid withSQLITE_CHANGESET_DATA orSQLITE_CHANGESET_CONFLICT conflicts).
    • SQLITE_CHANGESET_ABORT: Abort on conflict and roll back the database.

    When an error is thrown in the conflict handler or when any other value is returned from the handler,applying the changeset is aborted and the database is rolled back.

    Default: A function that returnsSQLITE_CHANGESET_ABORT.

• Returns:<boolean> Whether the changeset was applied successfully without being aborted.

An exception is thrown if the database is notopen. This method is a wrapper aroundsqlite3changeset_apply().

import {DatabaseSync }from'node:sqlite';const sourceDb =newDatabaseSync(':memory:');const targetDb =newDatabaseSync(':memory:');sourceDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');targetDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');const session = sourceDb.createSession();const insert = sourceDb.prepare('INSERT INTO data (key, value) VALUES (?, ?)');insert.run(1,'hello');insert.run(2,'world');const changeset = session.changeset();targetDb.applyChangeset(changeset);// Now that the changeset has been applied, targetDb contains the same data as sourceDb.const {DatabaseSync } =require('node:sqlite');const sourceDb =newDatabaseSync(':memory:');const targetDb =newDatabaseSync(':memory:');sourceDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');targetDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');const session = sourceDb.createSession();const insert = sourceDb.prepare('INSERT INTO data (key, value) VALUES (?, ?)');insert.run(1,'hello');insert.run(2,'world');const changeset = session.changeset();targetDb.applyChangeset(changeset);// Now that the changeset has been applied, targetDb contains the same data as sourceDb.copy

database[Symbol.dispose]()#

Closes the database connection. If the database connection is already closedthen this is a no-op.

session.changeset()#

• Returns:<Uint8Array> Binary changeset that can be applied to other databases.

Retrieves a changeset containing all changes since the changeset was created. Can be called multiple times.An exception is thrown if the database or the session is not open. This method is a wrapper aroundsqlite3session_changeset().

session.patchset()#

• Returns:<Uint8Array> Binary patchset that can be applied to other databases.

Similar to the method above, but generates a more compact patchset. SeeChangesets and Patchsetsin the documentation of SQLite. An exception is thrown if the database or the session is not open. This method is awrapper aroundsqlite3session_patchset().

session.close().#

Closes the session. An exception is thrown if the database or the session is not open. This method is awrapper aroundsqlite3session_delete().

statement.all([namedParameters][, ...anonymousParameters])#

• namedParameters<Object> An optional object used to bind named parameters.The keys of this object are used to configure the mapping.
• ...anonymousParameters<null> |<number> |<bigint> |<string> |<Buffer> |<TypedArray> |<DataView> Zero ormore values to bind to anonymous parameters.
• Returns:<Array> An array of objects. Each object corresponds to a rowreturned by executing the prepared statement. The keys and values of eachobject correspond to the column names and values of the row.

This method executes a prepared statement and returns all results as an array ofobjects. If the prepared statement does not return any results, this methodreturns an empty array. The prepared statementparameters are bound usingthe values innamedParameters andanonymousParameters.

statement.columns()#

• Returns:<Array> An array of objects. Each object corresponds to a columnin the prepared statement, and contains the following properties:

  • column<string> |<null> The unaliased name of the column in the origintable, ornull if the column is the result of an expression or subquery.This property is the result ofsqlite3_column_origin_name().
  • database<string> |<null> The unaliased name of the origin database, ornull if the column is the result of an expression or subquery. Thisproperty is the result ofsqlite3_column_database_name().
  • name<string> The name assigned to the column in the result set of aSELECT statement. This property is the result ofsqlite3_column_name().
  • table<string> |<null> The unaliased name of the origin table, ornull ifthe column is the result of an expression or subquery. This property is theresult ofsqlite3_column_table_name().
  • type<string> |<null> The declared data type of the column, ornull if thecolumn is the result of an expression or subquery. This property is theresult ofsqlite3_column_decltype().

This method is used to retrieve information about the columns returned by theprepared statement.

statement.expandedSQL#

• Type:<string> The source SQL expanded to include parameter values.

The source SQL text of the prepared statement with parameterplaceholders replaced by the values that were used during the most recentexecution of this prepared statement. This property is a wrapper aroundsqlite3_expanded_sql().

statement.get([namedParameters][, ...anonymousParameters])#

• namedParameters<Object> An optional object used to bind named parameters.The keys of this object are used to configure the mapping.
• ...anonymousParameters<null> |<number> |<bigint> |<string> |<Buffer> |<TypedArray> |<DataView> Zero ormore values to bind to anonymous parameters.
• Returns:<Object> |<undefined> An object corresponding to the first row returnedby executing the prepared statement. The keys and values of the objectcorrespond to the column names and values of the row. If no rows were returnedfrom the database then this method returnsundefined.

This method executes a prepared statement and returns the first result as anobject. If the prepared statement does not return any results, this methodreturnsundefined. The prepared statementparameters are bound using thevalues innamedParameters andanonymousParameters.

statement.iterate([namedParameters][, ...anonymousParameters])#

• namedParameters<Object> An optional object used to bind named parameters.The keys of this object are used to configure the mapping.
• ...anonymousParameters<null> |<number> |<bigint> |<string> |<Buffer> |<TypedArray> |<DataView> Zero ormore values to bind to anonymous parameters.
• Returns:<Iterator> An iterable iterator of objects. Each object corresponds to a rowreturned by executing the prepared statement. The keys and values of eachobject correspond to the column names and values of the row.

This method executes a prepared statement and returns an iterator ofobjects. If the prepared statement does not return any results, this methodreturns an empty iterator. The prepared statementparameters are bound usingthe values innamedParameters andanonymousParameters.

statement.run([namedParameters][, ...anonymousParameters])#

• namedParameters<Object> An optional object used to bind named parameters.The keys of this object are used to configure the mapping.
• ...anonymousParameters<null> |<number> |<bigint> |<string> |<Buffer> |<TypedArray> |<DataView> Zero ormore values to bind to anonymous parameters.
• Returns:<Object>
  • changes<number> |<bigint> The number of rows modified, inserted, or deletedby the most recently completedINSERT,UPDATE, orDELETE statement.This field is either a number or aBigInt depending on the preparedstatement's configuration. This property is the result ofsqlite3_changes64().
  • lastInsertRowid<number> |<bigint> The most recently inserted rowid. Thisfield is either a number or aBigInt depending on the prepared statement'sconfiguration. This property is the result ofsqlite3_last_insert_rowid().

This method executes a prepared statement and returns an object summarizing theresulting changes. The prepared statementparameters are bound using thevalues innamedParameters andanonymousParameters.

statement.setAllowBareNamedParameters(enabled)#

• enabled<boolean> Enables or disables support for binding named parameterswithout the prefix character.

The names of SQLite parameters begin with a prefix character. By default,node:sqlite requires that this prefix character is present when bindingparameters. However, with the exception of dollar sign character, theseprefix characters also require extra quoting when used in object keys.

To improve ergonomics, this method can be used to also allow bare namedparameters, which do not require the prefix character in JavaScript code. Thereare several caveats to be aware of when enabling bare named parameters:

• The prefix character is still required in SQL.
• The prefix character is still allowed in JavaScript. In fact, prefixed nameswill have slightly better binding performance.
• Using ambiguous named parameters, such as$k and@k, in the same preparedstatement will result in an exception as it cannot be determined how to binda bare name.

statement.setAllowUnknownNamedParameters(enabled)#

• enabled<boolean> Enables or disables support for unknown named parameters.

By default, if an unknown name is encountered while binding parameters, anexception is thrown. This method allows unknown named parameters to be ignored.

statement.setReturnArrays(enabled)#

• enabled<boolean> Enables or disables the return of query results as arrays.

When enabled, query results returned by theall(),get(), anditerate() methods will be returned as arrays insteadof objects.

statement.setReadBigInts(enabled)#

• enabled<boolean> Enables or disables the use ofBigInts when readingINTEGER fields from the database.

When reading from the database, SQLiteINTEGERs are mapped to JavaScriptnumbers by default. However, SQLiteINTEGERs can store values larger thanJavaScript numbers are capable of representing. In such cases, this method canbe used to readINTEGER data using JavaScriptBigInts. This method has noimpact on database write operations where numbers andBigInts are bothsupported at all times.

statement.sourceSQL#

• Type:<string> The source SQL used to create this prepared statement.

The source SQL text of the prepared statement. This property is awrapper aroundsqlite3_sql().

sqlTagStore.all(sqlTemplate[, ...values])#

• sqlTemplate<Template Literal> A template literal containing the SQL query.
• ...values<any> Values to be interpolated into the template literal.
• Returns:<Array> An array of objects representing the rows returned by the query.

Executes the given SQL query and returns all resulting rows as an array of objects.

sqlTagStore.get(sqlTemplate[, ...values])#

• sqlTemplate<Template Literal> A template literal containing the SQL query.
• ...values<any> Values to be interpolated into the template literal.
• Returns:<Object> |<undefined> An object representing the first row returned bythe query, orundefined if no rows are returned.

Executes the given SQL query and returns the first resulting row as an object.

sqlTagStore.iterate(sqlTemplate[, ...values])#

• sqlTemplate<Template Literal> A template literal containing the SQL query.
• ...values<any> Values to be interpolated into the template literal.
• Returns:<Iterator> An iterator that yields objects representing the rows returned by the query.

Executes the given SQL query and returns an iterator over the resulting rows.

sqlTagStore.run(sqlTemplate[, ...values])#

• sqlTemplate<Template Literal> A template literal containing the SQL query.
• ...values<any> Values to be interpolated into the template literal.
• Returns:<Object> An object containing information about the execution, includingchanges andlastInsertRowid.

Executes the given SQL query, which is expected to not return any rows (e.g., INSERT, UPDATE, DELETE).

sqlTagStore.size()#

• Returns:<integer> The number of prepared statements currently in the cache.

A read-only property that returns the number of prepared statements currently in the cache.

sqlTagStore.capacity#

• Returns:<integer> The maximum number of prepared statements the cache can hold.

A read-only property that returns the maximum number of prepared statements the cache can hold.

sqlTagStore.db#

• <DatabaseSync> TheDatabaseSync instance that created thisSQLTagStore.

A read-only property that returns theDatabaseSync object associated with thisSQLTagStore.

sqlTagStore.reset()#

Resets the LRU cache, clearing all stored prepared statements.

sqlTagStore.clear()#

An alias forsqlTagStore.reset().

Type conversion between JavaScript and SQLite#

When Node.js writes to or reads from SQLite it is necessary to convert betweenJavaScript data types and SQLite'sdata types. Because JavaScript supportsmore data types than SQLite, only a subset of JavaScript types are supported.Attempting to write an unsupported data type to SQLite will result in anexception.

SQLite constants#

The following constants are exported by thesqlite.constants object.