Documentation

Opening Database

To open a new Database connection, construct the Database class with the path to the database file. If the file does not exist, it will be created unless you pass create: false in the options.

Options

create: boolean - Whether to create the database file if it does not exist. Defaults to true.
readonly: boolean - Whether to open the database in read-only mode. Defaults to false.
memory: boolean - Whether to open the database in memory. Defaults to false.
int64: boolean - Whether to support BigInt columns. False by default, which means integers larger than 32 bit will be inaccurate. Causes mixed values to be returned like number | bigint.
flags: number - Raw flags to pass to the C API. Normally you don't need this. Passing this ignores all other options.
unsafeConcurrency: boolean - Enable optimizations that will affect syncronization with other clients. This can largerly improve performance for cases where you only have one client.
enableLoadExtension: boolean - Enables the loading of SQLite extensions from a dynamic library, this needs to be set to true for the method loadExtension to work. Defaults to false.
parseJson: boolean - Enables parsing JSON. True by default.

Usage

// Open using default options
const db = new Database("test.db");

// Open using URL path (relative to current file/module, not CWD)
const db = new Database(new URL("./test.db", import.meta.url));

// Open in memory
const db = new Database(":memory:");

// Open in read-only mode
const db = new Database("test.db", { readonly: true });

// Open existing database, error if it doesn't exist
const db = new Database("test.db", { create: false });

Properties of `Database`

inTransaction: boolean - Whether the database is currently in a transaction.
open: boolean - Whether the database connection is open.
path: string - The path to the database file (not full path, just the once passed to the constructor).
totalChanges: number - The total number of changes made to the database since it was opened.
changes: number - The number of changes made to the database by last executed statement.
lastInsertRowId: number - The rowid of the last inserted row.
autocommit: boolean - Whether the database is in autocommit mode. This is true when not in a transaction, and false when in a transaction.
enableLoadExtension: boolean - Enables the loading of SQLite extensions from a dynamic library, this needs to be set to true for the method loadExtension to work. Defaults to false.

Loading extensions

Loading SQLite3 extensions is enabled through the enableLoadExtension property and config option. For security reasons it is disabled by default. If enabled it is used with the loadExtension method on the database, it will attempt to load the specified file as specified by the SQLite documentation. Optionally a second argument can be passed to the method specifying the entrypoint name.

const db = new Database("test.db", { enableLoadExtension: true });

db.loadExtension("mod_spatialite");

It is also possible to load an extension directly from SQL using the load_extension functions as specified by the SQLite documentation.

db.exec("SELECT load_extension('mod_spatialite')");

Closing Database

To close the database connection, call the close() method. This will close the database connection and free all resources associated with it. Calling it more than once will be a no-op.

db.close();

Executing SQL

To execute SQL statements, use the exec() method. This method will execute all statements in the SQL string, and return the number of changes made by the last statement. This method is useful for executing DDL statements, such as CREATE, DROP, ALTER, and even pragma statements that do not return any data.

const changes = db.exec(
  "CREATE TABLE foo (bar TEXT); INSERT INTO foo VALUES ('baz');",
);

console.log(changes); // 1

// Executing pragma statements
db.exec("pragma journal_mode = WAL");
db.exec("pragma synchronous = normal");
db.exec("pragma temp_store = memory");

Any parameters past the first argument will be bound to the statement. When you pass parameters, the function under the hood instead uses the prepared statement API.

Note that when the prepared statement API is used, this method only supports one statement at a time. You cannot execute multiple statements AND pass parameters at the same time.

See Binding Parameters for more details.

Alternatively, use the .sql tagged template to safely execute SQL with given parameters. It will execute the given SQL with parameters bounded and returns all rows with .all().

const minimum = 20;
const results = db.sql`
  SELECT
    id,
    name,
    age
  FROM students
  WHERE age > ${minimum}`;

console.log(results); // [ [ 2, "Brian", 30 ] ]

Creating Prepared Statements

To prepare a statement, use the prepare() method. This method will return a Statement object, which can be used to execute it, bind the parameters, retrieve the results, and more.

const stmt = db.prepare("SELECT * FROM foo WHERE bar = ? AND baz = ?");

// or with a using statement

{
  using stmt = db.prepare("SELECT * FROM foo WHERE bar = ? AND baz = ?");
  // use stmt
}

// automatically disposed

For more details on binding parameters, see Binding Parameters.

Properties of `Statement`

db: Database - The database the statement belongs to.
expandedSql: string - The SQL string with all bound parameters expanded.
sql: string - The SQL string used to prepare the statement.
readonly: boolean - Whether the statement is read-only.
bindParameterCount: number - The number of parameters the statement expects.

You can use enableInt64, enableParseJson, disableInt64, disableParseJson, defaultInt64, defaultParseJson at statement level to override the behavior just for this statement and not whole database. Default falls back to database, not the default value for these options.

Executing Statement

To execute a statement, use the run() method. This method will execute the statement, and return the number of changes made by the statement.

const changes = stmt.run(...params);

Retrieving Rows

To retrieve rows from a statement, use the all() method. This method will execute the statement, and return an array of rows as objects.

const rows = stmt.all(...params);

To get rows in array form, use values() method.

const rows = stmt.values(...params);

To get only the first row as object, use the get() method.

const row = stmt.get(...params);

To get only the first row as array, use the value() method.

const row = stmt.value(...params);

all/values/get/value methods also support a generic type parameter to specify the type of the returned object.

interface Foo {
  bar: string;
  baz: number;
}

const rows = stmt.all<Foo>(...params);
// rows is Foo[]

const row = stmt.get<Foo>(...params);
// row is Foo | undefined

const values = stmt.values<[string, number]>(...params);
// values is [string, number][]

const row = stmt.value<[string, number]>(...params);
// row is [string, number] | undefined

SQLite functions that return JSON

When using SQLite's builtin JSON functions, sqlite3 will detect when a value has a "subtype" of JSON. If so, it will attempt to JSON.parse() the text value and return the parsed JavaScript object or array.

const [list] = db
  .prepare("SELECT json_array(1, 2, 3) as list")
  .value<[number[]]>()!;
// list = [ 1, 2, 3 ]

const [object] = db
  .prepare("SELECT json_object('name', 'Peter') as object")
  .value<[{ name: string }]>()!;

// object = { name: "Peter" }

Use the builtin json() SQL function to convert your text values into JSON.

Freeing Prepared Statements

Though the Statement object is automatically freed once it is no longer used, that is it's caught by the garbage collector, you can also free it manually by calling the finalize() method. Do not use the Statement object after calling this method.

stmt.finalize();

You can also use using statement to automatically free the statement once the scope ends.

{
  using stmt = db.prepare("SELECT * FROM foo WHERE bar = ? AND baz = ?");
  stmt.run("bar", "baz");
}

// stmt is automatically finalized here

Setting fixed parameters

To set fixed parameters for a statement, use the bind() method. This method will set the parameters for the statement, and return the statement itself.

It can only be called once and once it is called, changing the parameters is not possible. It's merely an optimization to avoid having to bind the parameters every time the statement is executed.

const stmt = db.prepare("SELECT * FROM foo WHERE bar = ? AND baz = ?");
stmt.bind("bar", "baz");

Iterating over Statement

If you iterate over the statement object itself, it will iterate over the rows step by step. This is useful when you don't want to load all the rows into memory at once. Since it does not accept any parameters, you must bind the parameters before iterating using bind method.

for (const row of stmt) {
  console.log(row);
}

Transactions

To start a transaction, use the transaction() method. This method takes a JavaScript function that will be called when the transaction is run. This method itself returns a function that can be called to run the transaction.

When the transaction function is called, BEGIN is automatically called. When the transaction function returns, COMMIT is automatically called. If the transaction function throws an error, ROLLBACK is called.

If the transaction is called within another transaction, it will use SAVEPOINT/RELEASE/ROLLBACK TO instead of BEGIN/COMMIT/ROLLBACK to create a nested transaction.

The returned function also contains deferred/immediate/exclusive properties (functions) which can be used to change BEGIN to BEGIN DEFERRED/BEGIN IMMEDIATE/BEGIN EXCLUSIVE.

const stmt = db.prepare("INSERT INTO foo VALUES (?)");
const runTransaction = db.transaction((data: SomeData[]) => {
  for (const item of data) {
    stmt.run(item.value);
  }
});

runTransaction([
  { value: "bar" },
  { value: "baz" },
]);

// Run with BEGIN DEFERRED

runTransaction.deferred([
  { value: "bar" },
  { value: "baz" },
]);

Binding Parameters

Parameters can be bound both by name and positiion. To bind by name, just pass an object mapping the parameter name to the value. To bind by position, pass the values as rest parameters.

SQLite supports :, @ and $ as prefix for named bind parameters. If you don't have any in the Object's keys, the : prefix will be used by default.

Bind parameters can be passed to Database#exec after SQL parameter, or to Statement's bind/all/values/run function.

// Bind by name
db.exec("INSERT INTO foo VALUES (:bar)", { bar: "baz" });

// In prepared statements
const stmt = db.prepare("INSERT INTO foo VALUES (:bar)");
stmt.run({ bar: "baz" });

// Bind by position
db.exec("INSERT INTO foo VALUES (?)", "baz");

// In prepared statements
const stmt = db.prepare("INSERT INTO foo VALUES (?, ?)");
stmt.run("baz", "foo");

JavaScript to SQLite type mapping:

JavaScript type	SQLite type
`null`	`NULL`
`undefined`	`NULL`
`number`	`INTEGER`/`FLOAT`
`bigint`	`INTEGER`
`string`	`TEXT`
`boolean`	`INTEGER`
`Date`	`TEXT` (ISO)
`Uint8Array`	`BLOB`
JSON-serializable value	`TEXT` (`JSON.stringify()`)

When retrieving rows, the types are mapped back to JavaScript types:

SQLite type	JavaScript type
`NULL`	`null`
`INTEGER`	`number`/`bigint`
`FLOAT`	`number`
`TEXT`	`string`
`TEXT` with JSON subtype	`object` (`JSON.parse()`)
`BLOB`	`Uint8Array`

Note 1: The int64 option allows you to return BigInt for integers bigger than max safe integer in JavaScript. It is disabled by default, and precision may not be accurate for bigger numbers. When enabled, the library can return both number | bigint, but when disabled (default), it will only return number. In the former case, bigint is returned ONLY if its too big to fit in a JavaScript Number.

Note 2: We only support Uint8Array for the BLOB type as V8 Fast API will optimize for it instead of other arrays like Uint16Array. And it is also to stay consistent: we only support passing Uint8Array and we consistently return Uint8Array when we return a BLOB to JS. It is easy to support passing all typed arrays with good performance, but then at the time we have to retreive again we don't know what the original typed array type was, as the only type into in the column is that it is a BLOB.

You can easily convert between other typed arrays and Uint8Array like this:

const f32 = new Float32Array(1);
const u8 = new Uint8Array(f32.buffer); // no copy, can pass this

const u8FromSqlite = new Uint8Array(4);
const f32FromSqlite = new Float32Array(u8FromSqlite.buffer); // safely convert back when retrieved from sqlite, no copy

Note 3: The parseJson option allows you to disable JSON parsing which is enabled by default.

Statement Status Counters

Prepared statements expose SQLite sqlite3_stmt_status() values through helper methods:

statusFullscanStep(reset?: boolean): number
statusSort(reset?: boolean): number
statusAutoindex(reset?: boolean): number
statusVmStep(reset?: boolean): number
statusReprepare(reset?: boolean): number
statusRun(reset?: boolean): number
statusFilterMiss(reset?: boolean): number
statusFilterHit(reset?: boolean): number
statusMemused(): number

Passing true to reset returns the current counter value and resets it to zero for future reads.

const stmt = db.prepare("SELECT text FROM test ORDER BY text");
stmt.values();

const runs = stmt.statusRun(); // > 0
const vmSteps = stmt.statusVmStep(true); // read and reset
const vmStepsAfterReset = stmt.statusVmStep(); // 0
const mem = stmt.statusMemused(); // approximate bytes used by statement

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Documentation

Opening Database

Options

Usage

Properties of `Database`

Loading extensions

Closing Database

Executing SQL

Creating Prepared Statements

Properties of `Statement`

Executing Statement

Retrieving Rows

SQLite functions that return JSON

Freeing Prepared Statements

Setting fixed parameters

Iterating over Statement

Transactions

Binding Parameters

Statement Status Counters

Uh oh!

FilesExpand file tree

doc.md

Latest commit

History

doc.md

File metadata and controls

Documentation

Opening Database

Options

Usage

Properties of Database

Loading extensions

Closing Database

Executing SQL

Creating Prepared Statements

Properties of Statement

Executing Statement

Retrieving Rows

SQLite functions that return JSON

Freeing Prepared Statements

Setting fixed parameters

Iterating over Statement

Transactions

Binding Parameters

Statement Status Counters

Properties of `Database`

Properties of `Statement`