better-sqlite3
Advanced tools
better-sqlite3 is a fast and simple SQLite3 library for Node.js applications. It provides a synchronous API for interacting with SQLite databases, making it easier to write and maintain code. The library is designed to be efficient and easy to use, with a focus on performance and simplicity.
Database Connection
This feature allows you to establish a connection to an SQLite database. The `Database` constructor takes the path to the database file as an argument.
const Database = require('better-sqlite3');
const db = new Database('my-database.db');Executing SQL Statements
This feature allows you to prepare and execute SQL statements. The `prepare` method creates a prepared statement, and the `all` method executes the statement and returns all matching rows.
const stmt = db.prepare('SELECT * FROM users WHERE age > ?');
const users = stmt.all(18);Inserting Data
This feature allows you to insert data into the database. The `run` method executes the prepared statement with the provided parameters.
const insert = db.prepare('INSERT INTO users (name, age) VALUES (?, ?)');
const info = insert.run('John Doe', 30);Transaction Management
This feature allows you to manage transactions. The `transaction` method creates a transaction that can execute multiple statements atomically.
const insert = db.prepare('INSERT INTO users (name, age) VALUES (?, ?)');
const insertMany = db.transaction((users) => {
for (const user of users) insert.run(user.name, user.age);
});
insertMany([{ name: 'Alice', age: 25 }, { name: 'Bob', age: 35 }]);Custom Functions
This feature allows you to define custom SQL functions. The `function` method registers a new function that can be used in SQL statements.
db.function('add', (a, b) => a + b);
const result = db.prepare('SELECT add(2, 3)').get();The `sqlite3` package is another popular SQLite library for Node.js. Unlike better-sqlite3, it provides an asynchronous API, which can be beneficial for non-blocking operations. However, it can be more complex to use due to the asynchronous nature of its API.
The `node-sqlite3` package is similar to `sqlite3` and provides an asynchronous API for SQLite. It is widely used and well-documented, but like `sqlite3`, it can be more challenging to work with compared to the synchronous API of better-sqlite3.
The `sql.js` package is a JavaScript library that runs SQLite in the browser using Emscripten. It is useful for web applications that need to use SQLite in a client-side environment. However, it is not designed for Node.js server-side applications like better-sqlite3.
The fastest and simplest library for SQLite3 in Node.js.
npm install --save better-sqlite3
var Database = require('better-sqlite3');
var db = new Database('foobar.db', options);
db.on('open', function () {
var row = db.statement('SELECT * FROM users WHERE id=?').get(userId);
console.log(row.firstName, row.lastName, row.email);
});
node-sqlite3 uses asynchronous APIs for tasks that don't involve I/O. That's not only bad besign, but it wastes tons of resources.node-sqlite3 exposes low-level (C language) memory management functions. better-sqlite3 does it the JavaScript way, allowing the garbage collector to worry about memory management.better-sqlite3 is much faster than node-sqlite3 in most cases, and just as fast in all other cases.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 an error occured while trying to open or close the database, 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.
If options.memory is true, an in-memory database will be created, rather than a disk-bound one. 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, transactions serve the sole purpose of batch-write operations. For read-only operations, use regular prepared statements. This restriction may change in the future.
Executes the given PRAGMA 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. 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.
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, it returns 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 unless there was no WAL file to begin with.
If execution of the checkpoint fails, an Error is thrown.
Closes the database connection. After invoking this method, no statements/transactions can be created or executed. When all resources have been released, the close event will be fired.
Returns whether the database is currently open.
Returns the string that was used to open the databse connection.
An object representing a single SQL statement.
*(only on write statements)
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. 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 read-only statements)
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 read-only statements)
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 read-only statements)
Similar to .all(), but instead of returning every row together, the callback is invoked for each row as they are retrieved, one by one.
After all rows have been consumed, undefined is returned. If execution of the statement fails, an Error is thrown and iteration stops.
You can specify bind parameters, which are only bound for the given execution.
*(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.
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. However, if the prepared statement will only be executed once, it's faster to bind the parameters directly with the execution (run(), get(), all(), or each()).
Returns the source string that was used to create the prepared statement.
Returns whether the prepared statement is read-only.
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.
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 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.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().
Returns a concatenation of every 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.statement('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 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
});
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'});
Concurrently reading and writing from an SQLite3 database can be very slow in some cases. Since concurrency is usually very important in web applications, it's recommended to turn on WAL mode to greatly increase overall performance and concurrency.
db.pragma('journal_mode = WAL');
WAL mode has a few disadvantages to consider:
However, you trade those disadvantages for greatly improved performance in most web applications.
If you want to further 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. The combination of these two PRAGMAs provides extremely fast performance.
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 use the db.checkpoint() method to force checkpointing whenever you deem appropriate.
The following compilation options are used:
FAQs
The fastest and simplest library for SQLite3 in Node.js.
The npm package better-sqlite3 receives a total of 367,665 weekly downloads. As such, better-sqlite3 popularity was classified as popular.
We found that better-sqlite3 demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
GitHub removed 27 malicious pull requests attempting to inject harmful code across multiple open source repositories, in another round of low-effort attacks.
Security News
RubyGems.org has added a new "maintainer" role that allows for publishing new versions of gems. This new permission type is aimed at improving security for gem owners and the service overall.
Security News
Node.js will be enforcing stricter semver-major PR policies a month before major releases to enhance stability and ensure reliable release candidates.