API

class Database
class Statement
class SqliteError
Binding Parameters

class Database

new Database()
Database#prepare() (see Statement)
Database#transaction()
Database#pragma()
Database#backup()
Database#serialize()
Database#function()
Database#aggregate()
Database#table()
Database#loadExtension()
Database#exec()
Database#close()
Properties

new Database(path, [options])

Creates a new database connection. If the database file does not exist, it is created. This happens synchronously, which means you can start executing queries right away. You can create an in-memory database by passing ":memory:" as the first argument. You can create a temporary database by passing an empty string (or by omitting all arguments).

In-memory databases can also be created by passing a buffer returned by .serialize(), instead of passing a string as the first argument.

Various options are accepted:

options.readonly: open the database connection in readonly mode (default: false).
options.fileMustExist: if the database does not exist, an Error will be thrown instead of creating a new file. This option is ignored for in-memory, temporary, or readonly database connections (default: false).
options.timeout: the number of milliseconds to wait when executing queries on a locked database, before throwing a SQLITE_BUSY error (default: 5000).
options.verbose: provide a function that gets called with every SQL string executed by the database connection (default: null).
options.nativeBinding: if you're using a complicated build system that moves, transforms, or concatenates your JS files, better-sqlite3 might have trouble locating its native C++ addon (better_sqlite3.node). If you get an error that looks like this, you can solve it by using this option to provide the file path of better_sqlite3.node (relative to the current working directory).

const Database = require('better-sqlite3');
const db = new Database('foobar.db', { verbose: console.log });

.prepare(string) -> Statement

Creates a new prepared Statement from the given SQL string.

const stmt = db.prepare('SELECT name, age FROM cats');

.transaction(function) -> function

Creates a function that always runs inside a transaction. When the function is invoked, it will begin a new transaction. When the function returns, the transaction will be committed. If an exception is thrown, the transaction will be rolled back (and the exception will propagate as usual).

const insert = db.prepare('INSERT INTO cats (name, age) VALUES (@name, @age)');

const insertMany = db.transaction((cats) => {
  for (const cat of cats) insert.run(cat);
});

insertMany([
  { name: 'Joey', age: 2 },
  { name: 'Sally', age: 4 },
  { name: 'Junior', age: 1 },
]);

Transaction functions can be called from inside other transaction functions. When doing so, the inner transaction becomes a savepoint.

If an error is thrown inside of a nested transaction function, the nested transaction function will roll back to the state just before the savepoint and rethrow the error. If the error is not caught in the outer transaction function, this will cause the outer transaction function to roll back as well.

const newExpense = db.prepare('INSERT INTO expenses (note, dollars) VALUES (?, ?)');

const adopt = db.transaction((cats) => {
  newExpense.run('adoption fees', 20);
  insertMany(cats); // nested transaction
});

Transactions also come with deferred, immediate, and exclusive versions.

insertMany(cats); // uses "BEGIN"
insertMany.deferred(cats); // uses "BEGIN DEFERRED"
insertMany.immediate(cats); // uses "BEGIN IMMEDIATE"
insertMany.exclusive(cats); // uses "BEGIN EXCLUSIVE"

Any arguments passed to the transaction function will be forwarded to the wrapped function, and any values returned from the wrapped function will be returned from the transaction function. The wrapped function will also have access to the same this binding as the transaction function.

Caveats

If you'd like to manage transactions manually, you're free to do so with regular prepared statements (using BEGIN, COMMIT, etc.). However, manually managed transactions should not be mixed with transactions managed by this .transaction() method. In other words, using raw COMMIT or ROLLBACK statements inside a transaction function is not supported.

Transaction functions do not work with async functions. Technically speaking, async functions always return after the first await, which means the transaction will already be committed before any async code executes. Also, because SQLite3 serializes all transactions, it's generally a very bad idea to keep a transaction open across event loop ticks anyways.

It's important to know that SQLite3 may sometimes rollback a transaction without us asking it to. This can happen either because of an ON CONFLICT clause, the RAISE() trigger function, or certain errors such as SQLITE_FULL or SQLITE_BUSY. In other words, if you catch an SQLite3 error within a transaction, you must be aware that any further SQL that you execute might not be within the same transaction. Usually, the best course of action for such cases is to simply re-throw the error, exiting the transaction function.

try {
  ...
} catch (err) {
  if (!db.inTransaction) throw err; // (transaction was forcefully rolled back)
  ...
}

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

Executes the given PRAGMA and returns 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 simple option is provided to make things easier. When simple is true, only the first column of the first row will be returned.

db.pragma('cache_size = 32000');
console.log(db.pragma('cache_size', { simple: true })); // => 32000

If execution of the PRAGMA fails, an Error is thrown.

It's better to use this method instead of normal prepared statements when executing PRAGMA, because this method normalizes some odd behavior that may otherwise be experienced. The documentation on SQLite3 PRAGMA can be found here.

.backup(destination, [options]) -> promise

Initiates a backup of the database, returning a promise for when the backup is complete. If the backup fails, the promise will be rejected with an Error. You can optionally backup an attached database instead by setting the attached option to the name of the desired attached database. A backup file is just a regular SQLite3 database file. It can be opened by new Database() just like any SQLite3 database.

db.backup(`backup-${Date.now()}.db`)
  .then(() => {
    console.log('backup complete!');
  })
  .catch((err) => {
    console.log('backup failed:', err);
  });

You can continue to use the database normally while a backup is in progress. If the same database connection mutates the database while performing a backup, those mutations will be reflected in the backup automatically. However, if a different connection mutates the database during a backup, the backup will be forcefully restarted. Therefore, it's recommended that only a single connection is responsible for mutating the database if online backups are being performed.

You can monitor the progress of the backup by setting the progress option to a callback function. That function will be invoked every time the backup makes progress, providing an object with two properties:

.totalPages: the total number of pages in the source database (and thus, the number of pages that the backup will have when completed) at the time of this progress report.
.remainingPages: the number of pages that still must be transferred before the backup is complete.

By default, 100 pages will be transferred after each cycle of the event loop. However, you can change this setting as often as you like by returning a number from the progress callback. You can even return 0 to effectively pause the backup altogether. In general, the goal is to maximize throughput while reducing pause times. If the transfer size is very low, pause times will be low, but it may take a while to complete the backup. On the flip side, if the setting is too high, pause times will be greater, but the backup might complete sooner. In most cases, 100 has proven to be a strong compromise, but the best setting is dependent on your computer's specifications and the nature of your program. Do not change this setting without measuring the effectiveness of your change. You should not assume that your change will even have the intended effect, unless you measure it for your unique situation.

If the backup is successful, the returned promise will contain an object that has the same properties as the one provided to the progress callback, but .remainingPages will be 0. If the progress callback throws an exception, the backup will be aborted. Usually this happens due to an unexpected error, but you can also use this behavior to voluntarily cancel the backup operation. If the parent database connection is closed, all pending backups will be automatically aborted.

let paused = false;
db.backup(`backup-${Date.now()}.db`, {
  progress({ totalPages: t, remainingPages: r }) {
    console.log(`progress: ${((t - r) / t * 100).toFixed(1)}%`);
    return paused ? 0 : 200;
  }
});

.serialize([options]) -> Buffer

Returns a buffer containing the serialized contents of the database. You can optionally serialize an attached database instead by setting the attached option to the name of the desired attached database.

The returned buffer can be written to disk to create a regular SQLite3 database file, or it can be opened directly as an in-memory database by passing it to new Database().

const buffer = db.serialize();
db.close();
db = new Database(buffer);

.function(name, [options], function) -> this

Registers a user-defined function so that it can be used by SQL statements.

db.function('add2', (a, b) => a + b);

db.prepare('SELECT add2(?, ?)').pluck().get(12, 4); // => 16
db.prepare('SELECT add2(?, ?)').pluck().get('foo', 'bar'); // => "foobar"
db.prepare('SELECT add2(?, ?, ?)').pluck().get(12, 4, 18); // => Error: wrong number of arguments

By default, user-defined functions have a strict number of arguments (determined by function.length). You can register multiple functions of the same name, each with a different number of arguments, causing SQLite3 to execute a different function depending on how many arguments were passed to it. If you register two functions with same name and the same number of arguments, the second registration will erase the first one.

If options.varargs is true, the registered function can accept any number of arguments.

If options.directOnly is true, the registered function can only be invoked from top-level SQL, and cannot be used in VIEWs, TRIGGERs, or schema structures such as CHECK constraints, DEFAULT clauses, etc.

If your function is deterministic, you can set options.deterministic to true, which may improve performance under some circumstances.

db.function('void', { deterministic: true, varargs: true }, () => {});

db.prepare("SELECT void()").pluck().get(); // => null
db.prepare("SELECT void(?, ?)").pluck().get(55, 19); // => null

.aggregate(name, options) -> this

Registers a user-defined aggregate function.

db.aggregate('addAll', {
  start: 0,
  step: (total, nextValue) => total + nextValue,
});

db.prepare('SELECT addAll(dollars) FROM expenses').pluck().get(); // => 92

The step() function will be invoked once for each row passed to the aggregate, using its return value as the new aggregate value. This works similarly to Array#reduce().

If options.start is a function, it will be invoked at the beginning of each aggregate, using its return value as the initial aggregate value. If options.start is not a function, it will be used as the initial aggregate value as-is (shown in the example above). If not provided, the initial aggregate value will be null.

You can also provide a result() function to transform the final aggregate value:

db.aggregate('getAverage', {
  start: () => [],
  step: (array, nextValue) => {
    array.push(nextValue);
  },
  result: array => array.reduce(sum) / array.length,
});

db.prepare('SELECT getAverage(dollars) FROM expenses').pluck().get(); // => 20.2

As shown above, you can use arbitrary JavaScript objects as your aggregation context, as long as a valid SQLite3 value is returned by result() in the end. If step() doesn't return anything (undefined), the aggregate value will not be replaced (be careful of this when using functions that return undefined when null is desired).

Just like regular user-defined functions, user-defined aggregates can accept multiple arguments. Furthermore, options.varargs, options.directOnly, and options.deterministic are also accepted.

If you provide an inverse() function, the aggregate can be used as a window function. Where step() is used to add a row to the current window, inverse() is used to remove a row from the current window. When using window functions, result() may be invoked multiple times.

db.aggregate('addAll', {
  start: 0,
  step: (total, nextValue) => total + nextValue,
  inverse: (total, droppedValue) => total - droppedValue,
  result: total => Math.round(total),
});

db.prepare(`
  SELECT timestamp, dollars, addAll(dollars) OVER day as dayTotal
  FROM expenses
  WINDOW day AS (PARTITION BY date(timestamp))
  ORDER BY timestamp
`).all();

.table(name, definition) -> this

Registers a virtual table. Virtual tables can be queried just like real tables, except their results do not exist in the database file; instead, they are calculated on-the-fly by a generator function in JavaScript.

const fs = require('fs');

db.table('filesystem_directory', {
  columns: ['filename', 'data'],
  rows: function* () {
    for (const filename of fs.readdirSync(process.cwd())) {
      const data = fs.readFileSync(filename);
      yield { filename, data };
    }
  },
});

const files = db.prepare('SELECT * FROM filesystem_directory').all();
// => [{ filename, data }, { filename, data }]

To generate a row in a virtual table, you can either yield an object whose keys correspond to column names, or yield an array whose elements represent columns in the order that they were declared. Every virtual table must declare its columns via the columns option.

Virtual tables can be used like table-valued functions; you can pass parameters to them, unlike regular tables.

db.table('regex_matches', {
  columns: ['match', 'capture'],
  rows: function* (pattern, text) {
    const regex = new RegExp(pattern, 'g');
    let match;

    while (match = regex.exec(text)) {
      yield [match[0], match[1]];
    }
  },
});

const stmt = db.prepare("SELECT * FROM regex('\\$(\\d+)', ?)");

stmt.all('Desks cost $500 and chairs cost $27');
// => [{ match: '$500', capture: '500' }, { match: '$27', capture: '27' }]

By default, the number of parameters accepted by a virtual table is inferred by function.length, and the parameters are automatically named $1, $2, etc. However, you can optionally provide an explicit list of parameters via the parameters option.

db.table('regex_matches', {
  columns: ['match', 'capture'],
  parameters: ['pattern', 'text'],
  rows: function* (pattern, text) {
    ...
  },
});

In virtual tables, parameters are actually hidden columns, and they can be selected in the result set of a query, just like any other column. That's why it may sometimes be desirable to give them explicit names.

When querying a virtual table, any omitted parameters will be undefined. You can use this behavior to implement required parameters and default parameter values.

db.table('sequence', {
  columns: ['value'],
  parameters: ['length', 'start'],
  rows: function* (length, start = 0) {
    if (length === undefined) {
      throw new TypeError('missing required parameter "length"');
    }

    const end = start + length;
    for (let n = start; n < end; ++n) {
      yield { value: n };
    }
  },
});

db.prepare('SELECT * FROM sequence(10)').pluck().all();
// => [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

Note that when using syntax like start = 0 for default parameter values (shown above), the function's .length property does not include the optional parameter, so you need to explicitly declare parameters in this case.

Normally, when you register a virtual table, the virtual table automatically exists without needing to run a CREATE VIRTUAL TABLE statement. However, if you provide a factory function as the second argument (a function that returns virtual table definitions), then no virtual table will be created automatically. Instead, you can create multiple similar virtual tables by running CREATE VIRTUAL TABLE statements, each with their own module arguments. Think of it like defining a virtual table "class" that can be instantiated by running CREATE VIRTUAL TABLE statements.

const fs = require('fs');

db.table('csv', (filename) => {
  const firstLine = getFirstLineOfFile(filename);
  return {
    columns: firstLine.split(','),
    rows: function* () {
      // This is just an example. Real CSV files are more complicated to parse.
      const contents = fs.readFileSync(filename, 'utf8');
      for (const line of contents.split('\n')) {
        yield line.split(',');
      }
    },
  };
});

db.exec('CREATE VIRTUAL TABLE my_data USING csv(my_data.csv)');
const allData = db.prepare('SELECT * FROM my_data').all();

The factory function will be invoked each time a corresponding CREATE VIRTUAL TABLE statement runs. The arguments to the factory function correspond to the module arguments passed in the CREATE VIRTUAL TABLE statement; always a list of arbitrary strings separated by commas. It's your responsibility to parse and interpret those module arguments. Note that SQLite3 does not allow bound parameters inside module arguments.

Just like user-defined functions and user-defined aggregates, virtual tables support options.directOnly, which prevents the table from being used inside VIEWs, TRIGGERs, or schema structures such as CHECK constraints, DEFAULT clauses, etc.

Some extensions can provide virtual tables that have write capabilities, but db.table() is only capable of creating read-only virtual tables, primarily for the purpose of supporting table-valued functions.

.loadExtension(path, [entryPoint]) -> this

Loads a compiled SQLite3 extension and applies it to the current database connection.

It's your responsibility to make sure the extensions you load are compiled/linked against a version of SQLite3 that is compatible with better-sqlite3. Keep in mind that new versions of better-sqlite3 will periodically use newer versions of SQLite3. You can see which version is being used here.

db.loadExtension('./my-extensions/compress.so');

.exec(string) -> this

Executes the given SQL string. Unlike prepared statements, this can execute strings that contain multiple SQL statements. This function performs worse and is less safe than using prepared statements. You should only use this method when you need to execute SQL from an external source (usually a file). If an error occurs, execution stops and further statements are not executed. You must rollback changes manually.

const migration = fs.readFileSync('migrate-schema.sql', 'utf8');
db.exec(migration);

.close() -> this

Closes the database connection. After invoking this method, no statements can be created or executed.

process.on('exit', () => db.close());
process.on('SIGHUP', () => process.exit(128 + 1));
process.on('SIGINT', () => process.exit(128 + 2));
process.on('SIGTERM', () => process.exit(128 + 15));

Properties

.open -> boolean - Whether the database connection is currently open.

.inTransaction -> boolean - Whether the database connection is currently in an open transaction.

.name -> string - The string that was used to open the database connection.

.memory -> boolean - Whether the database is an in-memory or temporary database.

.readonly -> boolean - Whether the database connection was created in readonly mode.

class Statement

An object representing a single SQL statement.

Statement#run()
Statement#get()
Statement#all()
Statement#iterate()
Statement#pluck()
Statement#expand()
Statement#raw()
Statement#columns()
Statement#bind()
Properties

.run([...bindParameters]) -> object

Executes the prepared statement. When execution completes it returns 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 (ignoring those caused by trigger programs). If the current statement did not insert any rows into the database, this number should be completely ignored.

If execution of the statement fails, an Error is thrown.

You can specify bind parameters, which are only bound for the given execution.

const stmt = db.prepare('INSERT INTO cats (name, age) VALUES (?, ?)');
const info = stmt.run('Joey', 2);

console.log(info.changes); // => 1