DOCSEARCHbyAlgolia
Submit Docs

better-sqlite3

repository·master·Indexed 27 days ago

https://github.com/wiselibs/better-sqlite3

A high-performance, synchronous SQLite library for Node.js focusing on simplicity and efficiency. It provides full support for SQLite features including transactions, user-defined functions, 64-bit integers, and virtual tables. Key functionality includes the Database class for connection management, prepared statements for safe query execution, and methods for database backups, serialization, and PRAGMA configuration.

Tokens
11.1K
Snippets
39
Records
66
Agent score
92%

What's inside better-sqlite3

  1. Understand the scope and nature of better-sqlite3

    master

    Project Nature

    better-sqlite3 is a low-level Node.js package providing bindings to SQLite. It is not an ORM and is not designed for specific application frameworks.

    Scope

    • In-scope: Features directly provided by SQLite that can be implemented safely, are commonly used, and cannot be reasonably implemented in pure JavaScript.
    • Out-of-scope: Anything SQLite does not directly provide.

    Platform Support & Compatibility

    • Native Addons: Uses C++ and node-gyp. Most systems compile during npm install.
    • Electron: Not officially supported as a first-class platform, but widely used and supported by community contributors.
    • TypeScript: The package is written in JavaScript. For TypeScript support, use the community-provided types: @types/better-sqlite3.

  2. Enable or disable Unsafe mode

    master

    By default, better-sqlite3 prevents operations that could corrupt the database or cause undefined behavior, such as mutating the database while iterating through a query's result set or actions blocked by SQLITE_DBCONFIG_DEFENSIVE.

    Advanced users can enable "unsafe mode" to allow these operations at their own risk. Unsafe mode can be toggled at any time and is applied independently to each database connection.

    db.unsafeMode(); // Unsafe mode ON
db.unsafeMode(true); // Unsafe mode ON
db.unsafeMode(false); // Unsafe mode OFF

  3. Initialize a database connection

    master

    You can initialize a database connection using CommonJS require or ES6 import syntax. You can provide an optional options object during initialization.

    CommonJS:

    const db = require('better-sqlite3')('foobar.db', options);

    ES6 Modules:

    import Database from 'better-sqlite3';
const db = new Database('foobar.db', options);

  4. Run the better-sqlite3 benchmark

    master

    To run the performance benchmarks locally, clone the repository, install dependencies, and execute the benchmark script using Node.js. If running as a root user, you must use the --unsafe-perm flag during installation.

    git clone https://github.com/WiseLibs/better-sqlite3.git
cd better-sqlite3
npm install # if you're doing this as the root user, --unsafe-perm is required
node benchmark

  5. Understand how DEFAULT values behave with NULL

    master
    A column's DEFAULT value is only applied when an INSERT statement omits that column entirely. If the INSERT statement explicitly specifies NULL for that column, the DEFAULT value will NOT be used; the column will be set to NULL instead.

  7. Install necessary native tools on Windows

    master

    On Windows, better-sqlite3 requires native build tools. If you did not select "Automatically install the necessary tools" during the Node.js installation, you can install Chocolatey, Visual Studio, and Python by running the following script as an administrator:

    C:\Program Files\nodejs\install_tools.bat

  9. Use worker threads for slow queries

    master
    While better-sqlite3 is typically fast enough for the main thread, you can use Node.js worker threads to perform very slow queries in the background without blocking the main event loop. This is typically implemented by creating a worker script that manages its own database connection and a master script that manages a pool of workers and a job queue.

  10. Configure Foreign Key constraints and actions

    master

    To ensure relational integrity, use NOT NULL on child columns to prevent foreign key constraints from being bypassed by NULL values.

    Referential Actions

    You can append ON DELETE and ON UPDATE clauses to foreign key definitions using these values:

    • SET NULL: Sets the child column to NULL when the parent is deleted/updated. (Note: This fails if the child column is NOT NULL).
    • SET DEFAULT: Sets the child column to its DEFAULT value. (Note: This fails if the default value doesn't exist in the parent table).
    • CASCADE: Deletes the child row if the parent is deleted, or updates the child column if the parent column is updated.
    -- Example of a mandatory foreign key relationship
CREATE TABLE comments (
  value TEXT,
  user_id INTEGER NOT NULL REFERENCES users
);

-- Example with CASCADE
CREATE TABLE comments (
  value TEXT,
  user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE
);

  11. 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. This leads to high disk usage and poor performance.

    If you are accessing the database from multiple processes or threads simultaneously, you can prevent this by periodically running the wal_checkpoint(RESTART) pragma when the WAL file size exceeds an acceptable 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();

  12. Design efficient and secure tables with INTEGER PRIMARY KEY

    master

    For optimal performance and security, use INTEGER PRIMARY KEY AUTOINCREMENT for table primary keys.

    • INTEGER PRIMARY KEY: Reuses SQLite's built-in rowid for improved performance.
    • AUTOINCREMENT: Guarantees that new rows will never reuse IDs from deleted rows, preventing potential bugs and security issues.

    Important Constraints:

    • If you do not use INTEGER PRIMARY KEY, you must apply the NOT NULL constraint to all primary key columns to avoid an SQLite bug that allows primary keys to be NULL.
    • Any column defined as INTEGER PRIMARY KEY will automatically increment if you attempt to set its value to NULL during an insert.