SQLite with Nodejs

A module for working with SQLite databases.

Table of Contents

• DatabaseSync Class — Connection to database
• Session Class — Database change tracking
• StatementSync Class — Prepared(Binary) SQL statements
• SQLite Utility Functions — Useful functions
• Example code

DatabaseSync Class

Represents a single, synchronous connection to an SQLite database.

Methods

• database.open()
  Opens the database if it was not opened during construction.
• database.close()
  Closes the database connection.
• database.exec(sql)
  Executes an SQL statement. No return value (useful for executing SQL from files).
• database.prepare(sql)
  Converts an SQL statement into a prepared statement before execution.
• database.function(name[, options], function)
  Defines an SQLite user-defined function.
• database.applyChangeset(changeset, options)
  Applies a binary changeset to the database. Can handle conflicts using predefined values.

Additional Options

• readonly – Open the database in read-only mode.
• enableForeignKeyConstraints – Enable or disable foreign key constraints.
• enableDoubleQuoteStringLiterals – Allow double-quoted string literals.
• allowExtension – Allow SQLite extensions.

Session Class

Handles database change tracking.

Methods

• session.changeset()
  Retrieves all changes made to the database.
• session.patchset()
  Retrieves a compact version of the changes.
• session.close()
  Closes the session.

StatementSync Class

Represents a single prepared SQL statement. Cannot be instantiated directly; use database.prepare(sql).
All APIs in this class are synchronous.

Why Use Prepared Statements?

Prepared statements are efficient binary representations of SQL queries. They also protect against SQL injection, making them safer for handling user input.

Methods

• statement.all([namedParameters][, ...anonymousParameters])
  Executes a prepared statement and returns all results as an array of objects.
• Each object corresponds to a row.
• Keys and values match column names and row values.
• statement.get([namedParameters][, ...anonymousParameters])
  Executes a prepared statement and returns the first result as an object.
• statement.iterate([namedParameters][, ...anonymousParameters])
  Executes a prepared statement and returns an iterator of objects.
• statement.run([namedParameters][, ...anonymousParameters])
  Executes a prepared statement and returns an object summarizing changes.
• statement.setAllowBareNamedParameters(enabled)
  Enables/disables binding named parameters without a prefix character.
• statement.setReadBigInts(enabled)
  Enables/disables the use of BigInts when reading INTEGER fields.
• If SQLite INTEGER values exceed JavaScript number limits, enable this to use JavaScript BigInts.

Properties

• statement.expandedSQL
  Returns the SQL query with parameter values included.
• statement.sourceSQL
  Returns the original SQL query.

SQLite Utility Functions

• sqlite.backup(sourceDb, path[, options])
  Creates a backup of the database.
• sqlite.constants
  Contains commonly used SQLite constants.

Summary with an example

const { DatabaseSync } = require('node:sqlite');
const db = new DatabaseSync('example.db', { readonly: false });

db.exec('PRAGMA foreign_keys = ON;');
// Create table & insert data
db.exec(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    );
`);

db.prepare('INSERT INTO users (name, age) VALUES (?, ?)').run('Alice', 25).finalize();
db.prepare('INSERT INTO users (name, age) VALUES (?, ?)').run('Bob', 30).finalize();

// Retrieve all users
const users = db.prepare('SELECT * FROM users').all();
console.log('All Users:', users);

// Retrieve a single user
const user = db.prepare('SELECT * FROM users WHERE name = ?').get('Alice');
console.log('Single User:', user);

// Define a custom SQLite function
db.function('square', (x) => x * x);

// Use the custom function
const squareResult = db.prepare('SELECT square(4) AS result').get();
console.log('Square of 4:', squareResult.result);

// Backup the database
db.backup('backup.db');
console.log('Database backup completed.');

// Close the database
db.close();
console.log('Database connection closed.');

Thanks for reading.

Get Farhad’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Hope it was useful. 🙂

Some content adapted from Node.js Documentation. © Node.js contributors, used under the MIT License.