method

sqlite.DatabaseSync.createTagStore

maxSize?: number

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

When a tagged SQL literal is executed, the SQLTagStore checks if a prepared statement 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 to avoid the overhead of repeatedly parsing and preparing the same SQL statements.

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
const sql = db.createSQLTagStore();

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' }
// ]

@returns

A new SQL tag store for caching prepared statements.

Referenced types

interface SQLTagStore

This class represents a single LRU (Least Recently Used) cache for storing prepared statements.

Instances of this class are created via the database.createSQLTagStore() method, not by using a constructor. The store caches prepared statements based on the provided SQL query string. When the same query is seen again, the store retrieves the cached statement and safely applies the new values through parameter binding, thereby preventing attacks like SQL injection.

The cache has a maxSize that defaults to 1000 statements, but a custom size can be provided (e.g., database.createSQLTagStore(100)). All APIs exposed by this class execute synchronously.

readonly capacity: number
A read-only property that returns the maximum number of prepared statements the cache can hold.
readonly db: DatabaseSync
A read-only property that returns the DatabaseSync object associated with this SQLTagStore.
all(
stringElements: TemplateStringsArray,
...boundParameters: SQLInputValue[]
): Record<string, SQLOutputValue>[];
Executes the given SQL query and returns all resulting rows as an array of objects.
clear(): void;
Resets the LRU cache, clearing all stored prepared statements.
get(
stringElements: TemplateStringsArray,
...boundParameters: SQLInputValue[]
): undefined | Record<string, SQLOutputValue>;
Executes the given SQL query and returns the first resulting row as an object.
iterate(
stringElements: TemplateStringsArray,
...boundParameters: SQLInputValue[]
): Iterator<Record<string, SQLOutputValue>>;
Executes the given SQL query and returns an iterator over the resulting rows.
run(
stringElements: TemplateStringsArray,
...boundParameters: SQLInputValue[]
): StatementResultingChanges;
Executes the given SQL query, which is expected to not return any rows (e.g., INSERT, UPDATE, DELETE).
size(): number;
A read-only property that returns the number of prepared statements currently in the cache.
@returns
The maximum number of prepared statements the cache can hold.