DOCSEARCHbyAlgolia
Submit Docs

better-sqlite3-multiple-ciphers

repository·master·Indexed 42 days ago

https://github.com/m4heshd/better-sqlite3-multiple-ciphers

A high-performance, synchronous SQLite library for Node.js that extends better-sqlite3 with support for multiple-cipher encryption via the SQLite3MultipleCiphers extension. Version 13.0.3 provides a synchronous API for interacting with SQLite databases, including support for sqleet and legacy SQLCipher encryption through PRAGMA statements and dedicated .key() and .rekey() methods.

Tokens
12.7K
Snippets
41
Records
52
Agent score
51%

What's inside better-sqlite3-multiple-ciphers

  1. Overview of the better-sqlite3-multiple-ciphers API

    master
    The better-sqlite3-multiple-ciphers library provides a high-performance interface for interacting with SQLite databases, featuring support for multiple encryption ciphers. The primary entry points are the Database class for managing connections and the Statement class for executing SQL. Errors are handled via the SqliteError class.

  3. Visualizing and editing encrypted databases

    master

    While better-sqlite3-multiple-ciphers can open databases created by DB Browser for SQLite (using the sqlcipher + legacy=4 configuration), it is not recommended for creating them if you intend to use DB Browser.

    To visually edit databases created with this library, it is recommended to use SQLiteStudio, as it also uses SQLite3MultipleCiphers under the hood and can handle the various cipher configurations.

  4. Configure Statement output modes: pluck(), expand(), and raw()

    master

    You can modify how a Statement returns data using these mutually exclusive modes:

    • Statement#pluck([toggleState]): Returns only the value of the first column instead of a row object.
    • Statement#expand([toggleState]): Returns data namespaced by table name. Useful for JOIN operations with overlapping column names. Result columns from expressions/subqueries are placed in a $ namespace.
    • Statement#raw([toggleState]): Returns rows as arrays instead of objects. This is a performance optimization for high-volume row retrieval. When using raw(), you should use Statement#columns() to recover column names.

    Note: pluck(), expand(), and raw() are mutually exclusive. Enabling one will disable the others.

    // Plucking ON
stmt.pluck(); 

// Expansion ON
stmt.expand(); 

// Raw mode ON
stmt.raw();

  5. Design efficient and safe tables with INTEGER PRIMARY KEY

    master

    For optimal performance and safety, use INTEGER PRIMARY KEY AUTOINCREMENT for your table's primary key column.

    • Performance: Using INTEGER PRIMARY KEY allows SQLite to reuse its built-in rowid column, improving lookup speeds.
    • Safety: Adding AUTOINCREMENT ensures that IDs are never reused from previously deleted rows, preventing potential bugs or security issues related to ID recycling.

    Important Constraints:

    • If you do not use INTEGER PRIMARY KEY, you must explicitly add NOT NULL to all primary key columns to avoid an SQLite bug that allows primary keys to be NULL.
    • Columns defined as INTEGER PRIMARY KEY will automatically increment if you attempt to set their value to NULL during an insert.
    • Note that NULL values are considered unique from each other in SQLite, which affects UNIQUE constraints and equality tests.
    CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL
);

  6. Basic Usage of better-sqlite3-multiple-ciphers

    master

    The library provides a synchronous API for interacting with SQLite databases. You can use CommonJS require or ES6 import syntax.

    For optimal performance, it is generally recommended to set the journal_mode to WAL (Write-Ahead Logging).

    // CommonJS
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', options);
db.pragma('journal_mode = WAL');
const row = db.prepare('SELECT * FROM users WHERE id = ?').get(userId);

// ES6 Modules
import Database from 'better-sqlite3-multiple-ciphers';
const db = new Database('foobar.db', options);
db.pragma('journal_mode = WAL');

  7. Encrypt and decrypt databases using PRAGMA

    master

    Encryption and decryption can be managed using key and rekey PRAGMA statements. By default, the library uses the sqleet cipher.

    To encrypt a database (using the default cipher): Use rekey to set a new key.

    To read an encrypted database (using the default cipher): Use key to provide the existing key before performing queries.

    To read a legacy SQLCipher database (e.g., created by DB Browser for SQLite): You must explicitly set the cipher to sqlcipher and set legacy=4 before providing the key.

    // Encrypting with default cipher (sqleet)
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', options);
db.pragma(`rekey='secret-key'`);
db.close();

// Reading with default cipher
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', options);
db.pragma(`key='secret-key'`);
const row = db.prepare('SELECT * FROM users WHERE id = ?').get(userId);

// Reading legacy SQLCipher databases
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', options);
db.pragma(`cipher='sqlcipher'`);
db.pragma(`legacy=4`);
db.pragma(`key='secret-key'`);
const row = db.prepare('SELECT * FROM users WHERE id = ?').get(userId);

  8. Use worker threads for slow queries

    master

    While better-sqlite3-multiple-ciphers is typically fast enough for the main thread, you can use Node.js worker threads to perform very slow queries in the background. This prevents the main event loop from blocking, ensuring the application remains responsive.

    To implement this, you should create a worker script that handles the database connection and a master script that manages a pool of workers and a job queue.

  9. Use a custom SQLite amalgamation

    master

    If you need to use a specific version of SQLite or a custom build (such as an encryption extension like SEE or sqleet), you can specify the directory of your SQLite amalgamation during installation using the --sqlite3 flag.

    Important: If better-sqlite3-multiple-ciphers is listed in your package.json dependencies, a standard npm install will not apply these custom flags. To ensure your custom configuration is used, you should remove the package from your dependencies and use a preinstall script instead.

    npm install better-sqlite3-multiple-ciphers --build-from-source --sqlite3=/path/to/sqlite-amalgamation

  10. How to set up a custom SQLite build step-by-step

    master

    Follow these steps to integrate a custom SQLite build (e.g., for specific compile-time options or encryption extensions) into your project:

    1. Download the SQLite source code (amalgamation) from the official website.
    2. Unzip the archive.
    3. Move sqlite3.c and sqlite3.h into your project folder.
    4. Define any desired compile-time options by adding #define statements at the very top of sqlite3.c.
    5. Add a preinstall script to your package.json (as shown in the configuration guide) that points the --sqlite3 flag to the location of your sqlite3.c and sqlite3.h files.
    6. Remove better-sqlite3-multiple-ciphers from your dependencies list in package.json.
    7. Run npm install in your project folder.

    If using an encryption extension that is a drop-in replacement for SQLite, simply replace the standard sqlite3.c and sqlite3.h with the extension's source files.

    // These go at the top of sqlite3.c
#define SQLITE_ENABLE_FTS5 1
#define SQLITE_DEFAULT_CACHE_SIZE 16000

// ... the original content of the file remains below

  11. Configure custom SQLite via preinstall script

    master

    To ensure better-sqlite3-multiple-ciphers always builds with your custom SQLite amalgamation, add a preinstall script to your package.json. This method avoids the issue where standard npm install commands ignore the --sqlite3 flag.

    Note: Use the --no-save flag in the script to prevent the package from being added to your package.json dependencies, as you should be managing the installation via the script to maintain the custom build configuration.

    {
  "scripts": {
    "preinstall": "npm install better-sqlite3-multiple-ciphers@'^7.0.0' --no-save --build-from-source --sqlite3=\"(pwd)/sqlite-amalgamation\""
  }
}

  12. Prevent WAL checkpoint starvation

    master

    Checkpoint starvation occurs when continuous concurrent reads prevent SQLite from recycling the WAL file, causing the WAL file to grow indefinitely and degrade performance. This typically only happens when accessing the database from multiple processes or threads simultaneously.

    To mitigate this, you can periodically trigger a checkpoint using the wal_checkpoint(RESTART) pragma when the WAL file size exceeds a certain threshold.

    setInterval(fs.stat.bind(null, 'foobar.db-wal', (err, stat) => {
  if (err) {
    if (err.code !== 'ENOENT') throw err;
  } else if (stat.size > someUnacceptableSize) {
    db.pragma('wal_checkpoint(RESTART)');
  }
}), 5000).unref();