node-sqlite3 Documentation

To use sqlite3 with node-webkit, you must build it specifically for that runtime due to ABI differences.

1. Install nw-gyp globally: npm install nw-gyp -g.
2. Build using the --runtime=node-webkit flag, specifying the target architecture (ia32 or x64) and the specific node-webkit version.

NODE_WEBKIT_VERSION="0.8.6"
npm install sqlite3 --build-from-source --runtime=node-webkit --target_arch=ia32 --target=$(NODE_WEBKIT_VERSION)

If you need to install directly from the repository's master branch, use the following command:

npm install https://github.com/tryghost/node-sqlite3/tarball/master

To install the latest published version of sqlite3, use the standard package manager commands. This is the recommended method and will attempt to download prebuilt binaries for your platform.

npm install sqlite3
# or
yarn add sqlite3

To use SQLite with the SQLCipher extension, you must compile from source and pass the --sqlite_libname=sqlcipher flag. You may also need to set LDFLAGS and CPPFLAGS to point to your SQLCipher installation.

Linux (including Raspberry Pi)

export LDFLAGS="-L/usr/local/lib"
export CPPFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
export CXXFLAGS="$CPPFLAGS"
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=/usr/local --verbose

macOS with Homebrew

export LDFLAGS="-L`brew --prefix`/opt/sqlcipher/lib"
export CPPFLAGS="-I`brew --prefix`/opt/sqlcipher/include/sqlcipher"
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=`brew --prefix`

You can specify a custom file header (magic) during a source build. Note that the magic string must be exactly 15 characters long (16 bytes including the null terminator). Using a custom magic will make the resulting files incompatible with standard SQLite tools.

npm install --build-from-source --sqlite_magic="MyCustomMagic15"

To use sqlite3, first install the module. You can then require it using .verbose() to enable extended stack traces. The following example demonstrates creating an in-memory database, creating a table, inserting data using a prepared statement, and querying the results using db.each() within a db.serialize() block to ensure sequential execution.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
    db.run("CREATE TABLE lorem (info TEXT)");

    const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
    for (let i = 0; i < 10; i++) {
        stmt.run("Ipsum " + i);
    }
    stmt.finalize();

    db.each("SELECT rowid AS id, info FROM lorem", (err, row) => {
        console.log(row.id + ": " + row.info);
    });
});

db.close();

The backup.retryErrors property is a writable array of SQLite error codes that are treated as non-fatal. If these errors occur, backup.failed will not be set to true, and the backup will attempt to continue.

• Default value: [sqlite3.BUSY, sqlite3.LOCKED]
• To disable automatic finishing: Set backup.retryErrors = []. In this mode, you must manually call backup.finish() to complete the process.

Use the cached.Database constructor to create a new connection to a SQLite database file. You can specify a filename and an optional access mode.

Common access modes include:

• OPEN_READONLY
• OPEN_READWRITE
• OPEN_CREATE

For repeated query execution, use prepare(sql) to create a Statement object. This is more efficient and allows you to bind parameters multiple times using .bind().

Methods on Statement:

• bind(...params): Binds values to the statement.
• run(callback): Executes the statement.
• get(callback): Retrieves one row.
• all(callback): Retrieves all rows.
• reset(callback): Resets the statement to its initial state.
• finalize(callback): Deletes the prepared statement from memory.

Use the configure method on a Database instance to adjust runtime settings.

Supported options:

• busyTimeout: Sets the number of milliseconds to wait for a lock to be released before returning SQLITE_BUSY.
• limit: Sets a specific SQLite limit (requires a limit ID constant).