SQLite#

Added in: v22.5.0

Stability: 1.1 - Active development.

Source Code: lib/sqlite.js

The node:sqlite module facilitates working with SQLite databases. To access it:

import sqlite from 'node:sqlite';const sqlite = require('node:sqlite');

This module is only available under the node: scheme.

The following example shows the basic usage of the node:sqlite module to open an in-memory database, write data to the database, and then read the data back.

import { DatabaseSync } from 'node:sqlite';
const database = new DatabaseSync(':memory:');

// Execute SQL statements from strings.
database.exec(`
  CREATE TABLE data(
    key INTEGER PRIMARY KEY,
    value TEXT
  ) STRICT
`);
// Create a prepared statement to insert data into the database.
const insert = database.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
// Execute the prepared statement with bound values.
insert.run(1, 'hello');
insert.run(2, 'world');
// Create a prepared statement to read data from the database.
const query = database.prepare('SELECT * FROM data ORDER BY key');
// Execute the prepared statement and log the result set.
console.log(query.all());
// Prints: [ { key: 1, value: 'hello' }, { key: 2, value: 'world' } ]'use strict';
const { DatabaseSync } = require('node:sqlite');
const database = new DatabaseSync(':memory:');

// Execute SQL statements from strings.
database.exec(`
  CREATE TABLE data(
    key INTEGER PRIMARY KEY,
    value TEXT
  ) STRICT
`);
// Create a prepared statement to insert data into the database.
const insert = database.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
// Execute the prepared statement with bound values.
insert.run(1, 'hello');
insert.run(2, 'world');
// Create a prepared statement to read data from the database.
const query = database.prepare('SELECT * FROM data ORDER BY key');
// Execute the prepared statement and log the result set.
console.log(query.all());
// Prints: [ { key: 1, value: 'hello' }, { key: 2, value: 'world' } ]

Class: DatabaseSync#

History
VersionChanges
v24.0.0

Add timeout option.

v23.10.0, v22.15.0

The path argument now supports Buffer and URL objects.

v22.5.0

Added in: v22.5.0

This class represents a single connection to a SQLite database. All APIs exposed by this class execute synchronously.

new DatabaseSync(path[, options])#

History
VersionChanges
v24.4.0

Add new SQLite database options.

v22.5.0

Added in: v22.5.0

  • path <string> | <Buffer> | <URL> The path of the database. A SQLite database can be stored in a file or completely in memory. To use a file-backed database, the path should be a file path. To use an in-memory database, the path should be the special name ':memory:'.
  • options <Object> Configuration options for the database connection. The following options are supported:
    • open <boolean> If true, the database is opened by the constructor. When this value is false, the database must be opened via the open() method. Default: true.
    • readOnly <boolean> If true, the database is opened in read-only mode. If the database does not exist, opening it will fail. Default: false.
    • enableForeignKeyConstraints <boolean> If true, foreign key constraints are enabled. This is recommended but can be disabled for compatibility with legacy database schemas. The enforcement of foreign key constraints can be enabled and disabled after opening the database using PRAGMA foreign_keys. Default: true.
    • enableDoubleQuotedStringLiterals <boolean> If true, SQLite will accept double-quoted string literals. This is not recommended but can be enabled for compatibility with legacy database schemas. Default: false.
    • allowExtension <boolean> If true, the loadExtension SQL function and the loadExtension() method are enabled. You can call enableLoadExtension(false) later to disable this feature. Default: false.
    • timeout <number> The busy timeout in milliseconds. This is the maximum amount of time that SQLite will wait for a database lock to be released before returning an error. Default: 0.
    • readBigInts <boolean> If true, integer fields are read as JavaScript BigInt values. If false, integer fields are read as JavaScript numbers. Default: false.
    • returnArrays <boolean> If true, query results are returned as arrays instead of objects. Default: false.
    • allowBareNamedParameters <boolean> If true, allows binding named parameters without the prefix character (e.g., foo instead of :foo). Default: true.
    • allowUnknownNamedParameters <boolean> If true, unknown named parameters are ignored when binding. If false, an exception is thrown for unknown named parameters. Default: false.

Constructs a new DatabaseSync instance.

database.aggregate(name, options)#

Added in: v24.0.0

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