sqlite

Lightweight SQLite database access module [sqlite]

This module provides a simple, JDBC-based interface for working with SQLite databases
in Rhino/Java environments. It supports basic SQL execution, parameter binding, transactions,
migrations, and optional connection pooling for concurrent read/write access.

Designed for use in scripting environments where the SQLite JDBC driver (org.xerial:sqlite-jdbc)
is available on the classpath.

Key Features

• Connection Management:
  • Open/close connections using file paths or JDBC/URI formats.
  • Configurable read-only mode, busy timeout, and PRAGMA foreign_keys settings.
• SQL Execution:
  • run for INSERT/UPDATE/DELETE with affected row count and last insert ID.
  • get for fetching a single row, all for multiple rows.
  • exec for executing one or more SQL statements without parameters.
  • Automatic JDBC-to-JavaScript type coercion (e.g., timestamps to Date).
• Parameter Binding:
  • Supports positional (?) and named (:name, @name, $name) parameters.
  • Handles Date, boolean, and other JavaScript values safely.
• Batch Execution:
  • runMany for executing the same SQL statement with multiple parameter sets.
  • Optional transactional wrapping, batch size control, and error handling callbacks.
• Transactions:
  • transaction helper with deferred, immediate, or exclusive modes.
• Migrations:
  • migrate applies schema changes only once, tracked in an internal _migrations table.
• Advanced Configuration:
  • SQLite.uri() helper for building JDBC URIs with query parameters.
  • setJournalMode / enableWAL for journal mode control.
• Connection Pooling:
  • SQLitePool for managing one writer and multiple reader connections.
  • Thread-safe withWriter / withReader execution helpers.

Example Usage

var { SQLite } = require("sigma/sqlite");
var db = new SQLite("file:./test.db", { timeoutMs: 5000 });

db.run("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT, age INT)");
db.run("INSERT INTO users(name, age) VALUES(?, ?)", ["Tom", 30]);

var row = db.get("SELECT * FROM users WHERE id = ?", [1]);
var rows = db.all("SELECT * FROM users WHERE age >= :minAge", { minAge: 18 });

db.transaction(function () {
  db.run("UPDATE users SET age = age + 1");
});

db.close();

Functions