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.
If options.memory is true, an in-memory database will be created, rather than a disk-bound one. Default is false.
If options.readonly is true, the database connection will be opened in readonly mode. Default is false.
If options.fileMustExist is true and the database does not exist, an Error will be thrown instead of creating a new file. This option does not affect in-memory or readonly database connections. Default is false.
Creates a new prepared Statement from the given SQL string.
Creates a new prepared Transaction from the given array of SQL strings.
NOTE: Transaction objects cannot contain read-only statements. In better-sqlite3, these objects serve the sole purpose of batch-write operations. For more complex transactions, simply run BEGIN and COMMIT with regular prepared statements (see tutorial). This restriction may change in the future.
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 simplify option is provided to make things easier. When simplify is true, 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 number 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.
Runs a WAL mode checkpoint on all attached databases (including the main database).
Unlike automatic checkpoints, this method executes a checkpoint in “RESTART” mode, which ensures a complete checkpoint operation even if other processes are using the database at the same time. You only need to use this method if you are accessing the database from multiple processes at the same time.
If databaseName is provided, it should be the name of an attached database (or "main"). This causes only that database to be checkpointed.
If the checkpoint fails, an Error is thrown.
Registers the given function so that it can be used by SQL statements.
db.register(function add2(a, b) {return a + b;});
db.prepare('SELECT add2(?, ?)').get(12, 4); // => 16
db.prepare('SELECT add2(?, ?)').get('foo', 'bar'); // => 'foobar'
db.prepare('SELECT add2(?, ?, ?)').get(12, 4, 18); // => Error: wrong number of arguments
By default, registered 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.name is given, the function will be registered under that name (instead of defaulting to function.name).
If options.varargs is true, the registered function can accept any number of arguments.
If your function is deterministic, you can set options.deterministic to true, which may improve performance under some circumstances.
db.register({name: "void", deterministic: true, varargs: true}, function () {});
db.prepare("SELECT void()").get(); // => null
db.prepare("SELECT void(?, ?)").get(55, 19); // => null
You can create custom aggregates by using generator functions. Your generator function must yield a regular function that will be invoked for each row passed to the aggregate.
db.register(function* addAll() {
var total = 0;
yield function (rowValue) {total += rowValue;};
return total;
});
var totalTreasure = db.prepare('SELECT addAll(treasure) FROM dragons').pluck().get();
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 better-sqlite3. Keep in mind that new versions of better-sqlite3 will periodically use newer versions of SQLite3. The version of SQLite3 currently used by better-sqlite3 is listed on the version and compilation options documentation page.
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.
Closes the database connection. After invoking this method, no statements/transactions can be created or executed.
.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 database.
.readonly -> boolean - Whether the database connection was created in readonly mode.
An object representing a single SQL statement.
*(only on statements that do not return data)
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.
*(only on statements that return data)
Executes the prepared statement. When execution completes it returns an object that represents the first row retrieved by the query. The object’s keys represent column names.
If the statement was successful but found no data, undefined is returned. If execution of the statement fails, an Error is thrown.
You can specify bind parameters, which are only bound for the given execution.
*(only on statements that return data)
Similar to .get(), but instead of only retrieving one row all matching rows will be retrieved. The return value is an array of row objects.
If no rows are found, the array will be empty. If execution of the statement fails, an Error is thrown.
You can specify bind parameters, which are only bound for the given execution.
*(only on statements that return data)
Similar to .all(), but instead of returning every row together, an iterator is returned so you can retrieve the rows one by one. If you plan on retrieving every row anyways, .all() will perform slightly better.
If execution of the statement fails, an Error is thrown and the iterator is closed.
You can specify bind parameters, which are only bound for the given execution.
var stmt = db.prepare('SELECT * FROM entries');
for (var row of stmt.iterate()) {
if (row.foo === desiredData) {
console.log('found it!');
break;
}
}
*(only on statements that return data)
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.
You can toggle this on/off as you please:
var stmt = db.prepare(SQL);
stmt.pluck(); // plucking ON
stmt.pluck(true); // plucking ON
stmt.pluck(false); // plucking OFF
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.
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.
.source -> string - The source string that was used to create the prepared statement.
.returnsData -> boolean - Whether the prepared statement returns data.
An object representing many SQL statements grouped into a single logical transaction.
Similar to Statement#run().
Each statement in the transaction is executed in order. Failed transactions are automatically rolled back. If execution of the transaction fails, an Error is thrown.
You can specify bind parameters, which are only bound for the given execution.
Same as Statement#bind().
.source -> string - A concatenation of each source string that was used to create the prepared transaction. The source strings are seperated by newline characters (\n).
This section refers 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.prepare('INSERT INTO people VALUES (?, ?, ?)');
// The following are equivalent.
stmt.run('John', 'Smith', 45);
stmt.run(['John', 'Smith', 45]);
stmt.run(['John'], ['Smith', 45]);
You can also use named parameters. SQLite3 provides 3 different syntaxes for named parameters, all of which are supported by better-sqlite3 (@foo, :foo, and $foo).
// The following are equivalent.
var stmt = db.prepare('INSERT INTO people VALUES (@firstName, @lastName, @age)');
var stmt = db.prepare('INSERT INTO people VALUES (:firstName, :lastName, :age)');
var stmt = db.prepare('INSERT INTO people VALUES ($firstName, $lastName, $age)');
var stmt = db.prepare('INSERT INTO people VALUES (@firstName, :lastName, $age)');
stmt.run({
firstName: 'John',
lastName: 'Smith',
age: 45
});
Below is an example of mixing anonymous parameters with named parameters.
var stmt = db.prepare('INSERT INTO people VALUES (@name, @name, ?)');
stmt.run(45, {name: 'Henry'});