The SQLite Module in Node.js

1. Introduction

The node:sqlite module is loaded via require('node:sqlite'). It allows developers to open SQLite databases either in memory or file-backed, execute SQL statements, and manage prepared statements securely.

2. DatabaseSync Class

• new DatabaseSync(path[, options]): Opens a database connection.
• Options include readOnly, timeout, enableForeignKeyConstraints, allowExtension, readBigInts, and more.
• database.exec(sql): Executes SQL statements without returning results.
• database.close(): Closes the database connection.

3. Prepared Statements (StatementSync)

• database.prepare(sql): Compiles SQL into a prepared statement.
• statement.run(): Executes INSERT/UPDATE/DELETE with bound parameters.
• statement.get(): Retrieves a single row.
• statement.all(): Retrieves all rows.
• statement.iterate(): Iterates over rows efficiently.
• statement.columns(): Provides metadata about columns.

4. SQLTagStore

An LRU cache for prepared statements, created with database.createTagStore(). It supports tagged template literals for queries:

sql.run`INSERT INTO users VALUES (1, 'Alice')`;
sql.get`SELECT * FROM users WHERE id = ${id}`;

5. Custom Functions and Aggregates

• database.function(name, options, fn): Defines user-defined SQLite functions.
• database.aggregate(name, options): Defines aggregate or window functions.

6. Sessions and Changesets

• database.createSession(): Tracks changes to tables.
• session.changeset(): Produces a binary changeset.
• database.applyChangeset(): Applies changesets to another database.

7. Security Features

• database.setAuthorizer(callback): Restricts or audits SQL operations.
• database.enableDefensive(true): Enables defensive mode to prevent corruption.

8. Type Conversion

SQLite types are mapped to JavaScript types:

• NULL → null
• INTEGER → number or BigInt
• REAL → number
• TEXT → string
• BLOB → TypedArray or DataView

9. Backup Support

Databases can be backed up using sqlite.backup(sourceDb, path[, options]), allowing safe duplication of data.

Conclusion

The node:sqlite module integrates SQLite tightly into Node.js, offering synchronous APIs, prepared statement caching, sessions, and advanced security features. It is ideal for lightweight, embedded databases and applications requiring efficient local storage.