better-sqlite3

The fastest and most carefully designed library for SQLite3 in Node.js.

• Full transaction support
• Full atomicity on a single connection
• Geared for performance and efficiency
• Easy-to-use node-style API

Installation

npm install --save better-sqlite3

Usage

var Database = require('better-sqlite3');
var db = new Database('foobar.db', options);

db.on('open', function () {
    db.statement('SELECT * FROM users WHERE id=?').get(userId, function (err, row) {
        console.log(row.firstName, row.lastName, row.email);
    });
})

Why should I use this instead of node-sqlite3?

• node-sqlite3 uses asynchronous APIs for tasks that don't touch the hard disk. That's not only bad besign, but it wastes tons of resources.
• node-sqlite3 forces you to manage the memory of SQLite3 statements yourself. better-sqlite3 does it the JavaScript way, allowing the garbage collector to worry about memory management.
• This module secretly splits your database connectino into two parts; a read-only connection, and a writable connection, which gives you completely atomic transactions and protects you from reading uncommitted data.

API

new Database(path, [options])

Creates a new database connection. If the database file does not exist, it is created.

When the database connection is ready, the open event is fired.

If the database is closed, the close event will be fired. If the database was closed because of an error, the associated Error object will be available as the first parameter of the close event. If there was no error, the first parameter will be null.

Options

If options.memory is true, an in-memory database will be created, rather than a disk-bound one. Default is false.

.statement(string) -> Statement

Creates a new prepared Statement object. This method will throw an exception if the provided string is not a valid SQL statement.

.transaction(arrayOfStrings) -> Transaction

Creates a new prepared Transaction object. Each string in the given array must be a valid SQL statement. Transaction objects cannot contain read-only statements. In better-sqlite3, transactions serve the sole purpose of batch-write operations. For read-only operations, use regular prepared statements.

.pragma(string, [simplify]) -> results

This method will execute the given PRAGMA statement synchronously and return its result. By default, the return value will be an array of result rows. Each row is represented by an object whose keys correspond to column names.

Since most PRAGMA statements return a single value, the simplify option is provided to make things easier. With this option, only the first column of the first row will be returned.

db.pragma('cache_size = 32000');
var cacheSize = db.pragma('cache_size', true); // returns the string "32000"

The data returned by .pragma() is always in string format. The documentation on SQLite3 PRAGMA statements can be found here.

WARNING: You should NOT use prepared statements or transactions to run PRAGMA statements. Doing so could result in database corruption.

.checkpoint([force], callback) -> this

This method is provided because .pragma()'s synchronous nature makes it unsuitable for running WAL mode checkpoints.

By default, this method will execute a checkpoint in "PASSIVE" mode, which means it might not perform a complete checkpoint if there are pending reads or write on the database. If the first argument is true, it will execute the checkpoint in "RESTART" mode, which ensures a complete checkpoint operation.

When the operation is complete, the callback is invoked with an Error or null as its first parameter, depending on if the operation was successful. If successful, the callback's second parameter will be a number between 0 and 1, indicating the fraction of the WAL file that was checkpointed. For forceful checkpoints ("RESTART" mode), this number will always be 1.

.close() -> this

Closes the database connection. After invoking this method, no statements/transactions can be created or executed. The underlying connection will wait for any outstanding queries to complete before gracefully closing the connection. When all outstanding queries have completed, the close event will be fired.

get .open -> boolean

Returns whether the database is currently open.

class Statement

An object representing a single SQL statement.

.run([...bindParameters], callback) -> this

**(only on write statements)*

Executes the prepared statement. When execution completes the callback will be invoked with either an Error or null as its first parameter.

If successful, the callback's second parameter will be an info object describing any changes made. The info object has two properties:

• info.changes: The total number of rows that were inserted, updated, or deleted by this operation. Changes made by foreign key actions or trigger programs do not count.
• info.lastInsertROWID: The rowid of the last row inserted into the database. If the current statement did not insert any rows into the database, this number should be completely ignored.

You can optionally specify bind parameters, which are automatically unbound when execution completes.

.get([...bindParameters], callback) -> this

**(only on read-only statements)*

Executes the prepared statement. When execution completes the callback will be invoked with either an Error or null as its first parameter.

If successful, the callback's second parameter will be an object that represents the first row retrieved by the query. The object's keys represent column names. If the statement was successful but retrieved no data, the second parameter will be undefined instead.

You can optionally specify bind parameters, which are automatically unbound when execution completes.

.all([...bindParameters], callback) -> this

**(only on read-only statements)*

Similar to .get(), but instead of only retrieving one row all matching rows will be retrieved. The callback's second parameter will be an array of row objects. If no rows are retrieved, the array will be empty.

You can optionally specify bind parameters, which are automatically unbound when execution completes.

.each([...bindParameters], rowCallback, finalCallback) -> this

**(only on read-only statements)*

Similar to .all(), but instead of returning every row together, rowCallback will be invoked for each row as they are retrieved, one by one. After all rows have been consumed, finalCallback is invoked to indicate completion.

If execution of the statement fails, finalCallback will be invoked with an Error object as its first argument, and iteration will stop.

You can optionally specify bind parameters, which are automatically unbound when execution completes.

.pluck() -> this

**(only on read-only statements)*

Causes the prepared statement to only return the value of the first column of any rows that it retrieves, rather than the entire row object.

This method can only be invoked before the statement is first executed. After a statement invokes this method, it cannot be undone.

.bind([...bindParameters]) -> this

Binds the given parameters to the statement permanently. Unlike binding parameters upon execution, these parameters will stay bound to the prepared statement for its entire life.

This method can only be invoked before the statement is first executed. After a statement's parameters are bound this way, you may no longer provide it with execution-specific (temporary) bound parameters.

This method is primarily used as a performance optimization when you need to execute the same prepared statement many times with the same bound parameters.

get .busy -> boolean

Returns whether the prepared statement is mid-execution. If a statement is busy, it cannot be executed again until its query completes.

get .source -> string

Returns the source string that was used to create the prepared statement.

get .readonly -> boolean

Returns whether the prepared statement is read-only.

class Transaction

An object representing many SQL statements grouped into a single logical transaction.

.run([...bindParameters], callback) -> this

Similar to Statement#run().

Each statement in the transaction is executed in order. Failed transactions are automatically rolled back.

If successful, the callback's second parameter will be an info object describing any changes made. The info object has two properties:

• info.changes: The total number of rows that were inserted, updated, or deleted by this transaction. Changes made by foreign key actions or trigger programs do not count.
• info.lastInsertROWID: The rowid of the last row inserted into the database. If the current transaction did not insert any rows into the database, this number should be completely ignored.

You can optionally specify bind parameters, which are automatically unbound when execution completes.

.bind([...bindParameters]) -> this

Same as Statement#bind().

get .busy -> boolean

Same as Statement#busy.

get .source -> string

Returns a concatenation of every source string that was used to create the prepared transaction. The source strings are seperated by newline characters (\n).

Binding Parameters

This section applies to anywhere in the documentation that specifies the optional argument [...bindParameters].

There are many ways to bind parameters to a prepared statement or transaction. The simplest way is with anonymous parameters:

var stmt = db.statement('INSERT INTO people VALUES (?, ?, ?)');

// The following are equivalent.
stmt.run('John', 'Smith', 45, callback);
stmt.run(['John', 'Smith', 45], callback);
stmt.run(['John'], ['Smith', 45], callback);

You can also use named parameters. SQLite3 provides 4 different syntaxes for named parameters, three of which are supported by better-sqlite3 (@foo, :foo, and $foo). However, if you use named parameters, make sure to only use one syntax within a given Statement or Transaction object. Mixing syntaxes within the same object is not supported.

// The following are equivalent.
var stmt = db.statement('INSERT INTO people VALUES (@firstName, @lastName, @age)');
var stmt = db.statement('INSERT INTO people VALUES (:firstName, :lastName, :age)');
var stmt = db.statement('INSERT INTO people VALUES ($firstName, $lastName, $age)');

stmt.run({
    firstName: 'John',
    lastName: 'Smith',
    age: 45
}, callback);

Below is an example of mixing anonymous parameters with named parameters.

var stmt = db.statement('INSERT INTO people VALUES (@name, @name, ?)');
stmt.run(45, {name: 'Henry'}, callback);

Performance

By default, SQLite3 databases are not well suited for some write-heavy applications. If your application reads frequently, but writes to the database very infrequently, you'll probably be fine. But if this is not the case, it's recommended to turn on WAL mode:

db.pragma('journal_mode = WAL');

WAL mode has a few disadvantages to consider:

• Transactions that involve ATTACHed databases are atomic for each individual database, but are not atomic across all databases as a set.
• Under rare circumstances, the WAL file may experience "checkpoint starvation" (see below).
• Some hardware/system limitations that may affect some users, listed here.

However, you trade those disadvantages for greatly improved performance in most web applications.

If you want to massively improve write performance and you're willing to sacrifice a tiny bit of durability, you can use this:

db.pragma('journal_mode = WAL');
db.pragma('synchronous = 1');

Normally, setting synchronous = 1 would introduce the risk of database corruption following a power loss or hard reboot. But in WAL mode, you do not introduce this risk.

Defending against "checkpoint starvation"

Checkpoint starvation is when SQLite3 is unable to recycle the WAL file due to everlasting concurrent reads to the database. If this happens, the WAL file will grow without bound, leading to unacceptable amounts of disk usage and deteriorating performance.

To prevent this, you can optionally use the db.checkpoint() method to force checkpointing whenever you deem appropriate.

SQLite3 compilation options

The following compilation options are used:

• SQLITE_THREADSAFE=1
• SQLITE_ENABLE_FTS5
• SQLITE_ENABLE_JSON1
• SQLITE_ENABLE_RTREE
• SQLITE_DEFAULT_CACHE_SIZE=-16000
• SQLITE_DEFAULT_FOREIGN_KEYS=1
• SQLITE_USE_URI=1

License

MIT