node-sqlite

To manage the state and resources of a prepared statement, use the following methods:

• bind(...params): Binds parameters to the statement. This completely resets the statement object, the row cursor, and removes all previously bound parameters.
• reset(): Resets the row cursor but preserves the current parameter bindings. Use this to re-execute the same query with the same parameters.
• finalize(): Releases the statement resources. After calling this, all further calls on the statement object will throw errors. Use this to prevent long delays or database locks, especially after using get().

// Example: Reusing a statement with reset
await stmt.bind('param1', 'param2');
await stmt.run();

await stmt.reset(); // Resets cursor, keeps bindings
await stmt.run();

await stmt.finalize(); // Clean up

The SqlType type defines the allowed input for SQL execution methods. It supports two forms:

1. A standard string containing a raw SQL statement.
2. A SQLStatement object (typically from sql-template-strings), which allows for safe, parameterized queries using tagged template strings.

SqlType = SQLStatement | string

To use this library, you must install both the sqlite wrapper and a database driver like sqlite3.

• sqlite: The wrapper library that adds ES6 Promises and a migrations API. v4 targets Node.js 10 and above.
• sqlite3: The recommended database driver.

If you are using an older version of Node.js, install sqlite@3 instead.

# Install the driver
$ npm install sqlite3 --save

# Install the wrapper (v4 for Node.js 10+)
$ npm install sqlite --save

# For legacy Node.js versions
$ npm install sqlite@3 --save

The library supports two ways to provide SQL for execution via the SqlType type:

1. Raw SQL String: A standard string containing the SQL command.
2. SQLStatement Object: An object containing the sql string and an optional values array for parameter binding.

This allows for both simple queries and prepared statements with parameterized values.

// Using a raw string
const query1: ISqlite.SqlType = "SELECT * FROM users";

// Using a SQLStatement object
const query2: ISqlite.SqlType = {
  sql: "SELECT * FROM users WHERE id = ?",
  values: [1]
};

To enable the database object cache, use sqlite3.cached.Database as the driver in the open configuration.

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      driver: sqlite3.cached.Database
    })
})()

The library is compatible with sql-template-strings. This allows you to write cleaner, safer queries using template literals.

import SQL from 'sql-template-strings'

const book = 'harry potter';
const author = 'J. K. Rowling';

const data = await db.all(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`);

The open function is the primary entry point for the library. It initializes a connection to a SQLite database based on the provided configuration and returns a Database instance. This instance is used to execute queries and manage the database lifecycle.

Parameters:

• config: A Config object specifying the database driver and connection details (such as the file path or in-memory settings).

import { open } from 'sqlite';

const db = await open({
  filename: './database.db',
  driver: sqlite3.Database
});

Use exec() to run a string containing one or more SQL statements. No result rows are retrieved.

Constraints:

• It only executes statements up to the first NULL byte.
• Comments are not allowed and will cause runtime errors.
• If a query fails, subsequent statements will not execute. To ensure atomicity, wrap the string in a transaction.

await db.exec('CREATE TABLE test (id INTEGER); INSERT INTO test VALUES (1);');

Use run() for SQL queries that do not return result rows (e.g., INSERT, UPDATE, DELETE). It returns a RunResult (which includes the statement object) and allows for function chaining.

await db.run('INSERT INTO users (name) VALUES (?)', 'Alice');

Use the open function to create a database connection. You must provide a filename and a driver (typically sqlite3.Database).

Configuration Options

• filename: A string representing the file path. Use ":memory:" for an anonymous in-memory database or an empty string for an anonymous disk-based database.
• driver: The database driver class (e.g., sqlite3.Database). Any library conforming to the sqlite3 API can be used.
• mode (optional): One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE, and sqlite3.OPEN_CREATE. The default is OPEN_READWRITE | OPEN_CREATE.

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      driver: sqlite3.Database
    })
})()