Creates a new SQLTagStore, which is a Least Recently Used (LRU) 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 the corresponding SQL query 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.
Tagged statements bind the placeholder values from the template literal as parameters to the underlying prepared statement. For example:
sqlTagStore.get`SELECT ${value}`;
is equivalent to:
db.prepare('SELECT ?').get(value);
However, in the first example, the tag store will cache the underlying prepared statement for future use.
Note: The ${value} syntax in tagged statements binds a parameter to the prepared statement. This differs from its behavior in untagged template literals, where it performs string interpolation.
// This a safe example of binding a parameter to a tagged statement.
sqlTagStore.run`INSERT INTO t1 (id) VALUES (${id})`;
// This is an *unsafe* example of an untagged template string.
// `id` is interpolated into the query text as a string.
// This can lead to SQL injection and data corruption.
db.run(`INSERT INTO t1 (id) VALUES (${id})`);
The tag store will match a statement from the cache if the query strings (including the positions of any bound placeholders) are identical.
// The following statements will match in the cache:
sqlTagStore.get`SELECT * FROM t1 WHERE id = ${id} AND active = 1`;
sqlTagStore.get`SELECT * FROM t1 WHERE id = ${12345} AND active = 1`;
// The following statements will not match, as the query strings
// and bound placeholders differ:
sqlTagStore.get`SELECT * FROM t1 WHERE id = ${id} AND active = 1`;
sqlTagStore.get`SELECT * FROM t1 WHERE id = 12345 AND active = 1`;
// The following statements will not match, as matches are case-sensitive:
sqlTagStore.get`SELECT * FROM t1 WHERE id = ${id} AND active = 1`;
sqlTagStore.get`select * from t1 where id = ${id} and active = 1`;
The only way of binding parameters in tagged statements is with the ${value} syntax. Do not add parameter binding placeholders (? etc.) to the SQL query string itself.
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 name = 'Alice';
const user = sql.get`SELECT * FROM users WHERE name = ${name}`;
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' }
// ]