Security News
ESLint is Now Language-Agnostic: Linting JSON, Markdown, and Beyond
ESLint has added JSON and Markdown linting support with new officially-supported plugins, expanding its versatility beyond JavaScript.
The sqlite3 npm package is a library that provides a straightforward interface for interacting with SQLite databases in Node.js applications. It allows you to create, read, update, and delete records in SQLite databases, execute SQL queries, and manage database connections.
Create a Database
This code demonstrates how to create an in-memory SQLite database, create a table, and insert some records into it.
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.close();
Query a Database
This code shows how to create a table, insert records, and query the database to retrieve and print the records.
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();
Update Records
This code demonstrates how to update records in an SQLite database.
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.run('UPDATE lorem SET info = ? WHERE rowid = ?', ['Updated Ipsum', 1]);
db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
console.log(row.id + ': ' + row.info);
});
});
db.close();
Delete Records
This code shows how to delete records from an SQLite database.
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.run('DELETE FROM lorem WHERE rowid = ?', 1);
db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
console.log(row.id + ': ' + row.info);
});
});
db.close();
better-sqlite3 is a faster and simpler alternative to sqlite3. It provides a more synchronous API, which can be easier to work with in some cases. Unlike sqlite3, better-sqlite3 does not use callbacks and instead returns results directly.
Sequelize is a promise-based Node.js ORM for various SQL databases, including SQLite. It provides a higher-level abstraction over SQL queries and supports features like model definition, associations, and migrations. It is more feature-rich compared to sqlite3 but also more complex.
Knex.js is a SQL query builder for Node.js that supports multiple databases, including SQLite. It provides a flexible and powerful API for building and executing SQL queries. Knex.js can be used with or without an ORM and offers more flexibility compared to sqlite3.
node-sqlite3 - Asynchronous, non-blocking SQLite3 bindings for node.js 0.2.* and 0.4.*.
var db = new sqlite3.Database(':memory:');
db.serialize(function() {
db.run("CREATE TABLE lorem (info TEXT)");
var stmt = db.prepare("INSERT INTO lorem VALUES(?)");
for (var i = 0; i < 10; i++) {
stmt.run("Ipsum " + i);
}
db.each("SELECT rowid AS id, info FROM lorem", function(err, row) {
console.log(row.id + ": " + row.info);
});
});
db.close();
node-sqlite3
has built-in function call serialization and automatically waits before executing a blocking action until no other action is pending. This means that it's safe start calling functions on the database object even if it is not yet fully opened. The Database#close()
function will wait until all pending queries are completed before closing the database. To control serialization and parallelization of queries, see the Control Flow section of this document.
Returns a new Database object and automatically opens the database. There is no separate method to open the database.
filename
: Valid values are filenames, ":memory:"
for an anonymous in-memory database and an empty string for an anonymous disk-based database. Anonymous databases are not persisted and when closing the database handle, their contents are lost.
mode
(optional): One or more of sqlite3.OPEN_READONLY
, sqlite3.OPEN_READWRITE
and sqlite3.OPEN_CREATE
. The default value is OPEN_READWRITE | OPEN_CREATE
.
callback
(optional): If provided, this function will be called when the database was opened successfully or when an error occurred. The first argument is an error object. When it is null
, opening succeeded. If no callback is provided and an error occurred, an error
event with the error object as the only parameter will be emitted on the database object. If opening succeeded, an open
event with no parameters is emitted, regardless of whether a callback was provided or not.
Runs the SQL query with the specified parameters and calls the callback afterwards. It does not retrieve any result data. The function returns the Database object for which it was called to allow for function chaining.
sql
: The SQL query to run. If the SQL query is invalid and a callback was passed to the function, it is called with an error object containing the error message from SQLite. If no callback was passed and preparing fails, an error
event will be emitted on the underlying Statement object.
param, ...
(optional): When the SQL statement contains placeholders, you can pass them in here. They will be bound to the statement before it is executed. There are three ways of passing bind parameters: directly in the function's arguments, as an array, and as an object for named parameters.
// Directly in the function arguments.
db.run("SELECT * FROM tbl WHERE id = ? AND name = ?", 2, "bar");
// As an array.
db.run("SELECT * FROM tbl WHERE id = ? AND name = ?", [ 2, "bar" ]);
// As an object with named parameters.
db.run("SELECT * FROM tbl WHERE id = $id AND name = $name", {
$id: 2,
$name: "bar"
});
Named parameters can be prefixed with :name
, @name
and $name
. We recommend using $name
since JavaScript allows using the dollar sign as a variable name without having to escape it. You can also specify a numeric index after a ?
placeholder. These correspond to the position in the array. Note that placeholder indexes start at 1 in SQLite. node-sqlite3
maps arrays to start with one so that you don't have to specify an empty value as the first array element (with index 0). You can also use numeric object keys to bind values. Note that in this case, the first index is 1:
db.run("SELECT * FROM tbl WHERE id = $id AND name = ?5", {
1: 2,
5: "bar"
});
This binds the first placeholder ($id
) to 2
and the placeholder with index 5
to "bar"
. While this is valid in SQLite and node-sqlite3
, it is not recommended to mix different placeholder types.
If you use an array or an object to bind parameters, it must be the first value in the bind arguments list. If any other object is before it, an error will be thrown. Additional bind parameters after an array or object will be ignored.
callback
(optional): If given, it will be called when an error occurs during any step of the statement preparation or execution, and after the query was run. If an error occurred, the first (and only) parameter will be an error object containing the error message. If execution was successful, the first parameter is null
. The context of the function (the this
object inside the function) is the statement object. Note that it is not possible to run the statement again because it is automatically finalized after running for the first time. Any subsequent attempts to run the statement again will fail.
If execution was successful, it contains two properties named lastID
and changes
which contain the value of the last inserted row ID and the number of rows affected by this query respectively. Note that lastID
only contains valid information when the query was a successfully completed INSERT
statement and changes
only contains valid information when the query was a successfully completed UPDATE
or DELETE
statement. In all other cases, the content of these properties is inaccurate and should not be used. The .run()
function is the only query method that sets these two values; all other query methods such as .all()
or .get()
don't retrieve these values.
Runs the SQL query with the specified parameters and calls the callback with the first result row afterwards. The function returns the Database object to allow for function chaining. The parameters are the same as the Database#run
function, with the following differences:
The signature of the callback is function(err, row) {}
. If the result set is empty, the second parameter is undefined
, otherwise it is an object containing the values for the first row. The property names correspond to the column names of the result set. It is impossible to access them by column index; the only supported way is by column name.
Runs the SQL query with the specified parameters and calls the callback with all result rows afterwards. The function returns the Database object to allow for function chaining. The parameters are the same as the Database#run
function, with the following differences:
The signature of the callback is function(err, rows) {}
. If the result set is empty, the second parameter is an empty array, otherwise it contains an object for each result row which in turn contains the values of that row, like the Database#get
function.
Note that it first retrieves all result rows and stores them in memory. For queries that have potentially large result sets, use the Database#each
function to retrieve all rows or Database#prepare
followed by multiple Statement#get
calls to retrieve a previously unknown amount of rows.
Runs the SQL query with the specified parameters and calls the callback with for each result row. The function returns the Database object to allow for function chaining. The parameters are the same as the Database#run
function, with the following differences:
The signature of the callback is function(err, row) {}
. If the result set succeeds but is empty, the callback is never called (this will change in the future). In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set.
If you know that a query only returns a very limited number of rows, it might be more convenient to use Database#all
to retrieve all rows at once.
There is currently no way to abort execution.
Runs all SQL queries in the supplied string. No result rows are retrieved. The function returns the Database object to allow for function chaining. If a query fails, no subsequent statements will be executed (wrap it in a transaction if you want all or none to be executed). When all statements have been executed successfully, or when an error occurs, the callback function is called, with the first parameter being either null
or an error object. When no callback is provided and an error occurs, an error
event will be emitted on the database object.
Note: This function will only execute statements up to the first NULL byte. Comments are not allowed and will lead to runtime errors.
Prepares the SQL statement and optionally binds the specified parameters and calls the callback when done. The function returns a Statement object.
When preparing was successful, the first and only argument to the callback is null
, otherwise it is the error object. When bind parameters are supplied, they are bound to the prepared statement before calling the callback.
Binds parameters to the prepared statement and calls the callback when done or when an error occurs. The function returns the Statement object to allow for function chaining. The first and only argument to the callback is null
when binding was successful, otherwise it is the error object.
Binding parameters with this function completely resets the statement object and row cursor and removes all previously bound parameters, if any.
Resets the row cursor of the statement and preserves the parameter bindings. Use this function to re-execute the same query with the same bindings. The function returns the Statement object to allow for function chaining. The callback will be called after the reset is complete. This action will never fail and will always return null
as the first and only callback parameter.
Finalizes the statement. This is typically optional, but if you experience long delays before the next query is executed, explicitly finalizing your statement might be necessary. This might be the case when you run an exclusive query (see section Serialization). After the statement is finalized, all further function calls on that statement object will throw errors.
Binds parameters and executes the statement. The function returns the Statement object to allow for function chaining.
If you specify bind parameters, they will be bound to the statement before it is executed. Note that the bindings and the row cursor are reset when you specify even a single bind parameter.
The callback behavior is identical to the Database#run
method with the difference that the statement will not be finalized after it is run. This means you can run it multiple times.
Binds parameters, executes the statement and retrieves the first result row. The function returns the Statement object to allow for function chaining. The parameters are the same as the Statement#run function, with the following differences:
The signature of the callback is function(err, row) {}
. If the result set is empty, the second parameter is undefined, otherwise it is an object containing the values for the first row. Like with Statement#run
, the statement will not be finalized after executing this function.
Binds parameters, executes the statement and calls the callback with all result rows. The function returns the Statement object to allow for function chaining. The parameters are the same as the Statement#run function, with the following differences:
The signature of the callback is function(err, rows) {}
. If the result set is empty, the second parameter is an empty array, otherwise it contains an object for each result row which in turn contains the values of that row. Like with Statement#run
, the statement will not be finalized after executing this function.
Binds parameters, executes the statement and calls the callback for each result row. The function returns the Statement object to allow for function chaining. The parameters are the same as the Statement#run function, with the following differences:
The signature of the callback is function(err, row) {}
. If the result set succeeds but is empty, the callback is never called (this will change in the future). In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set. Like with Statement#run
, the statement will not be finalized after executing this function.
If you know that a query only returns a very limited number of rows, it might be more convenient to use Statement#all
to retrieve all rows at once.
There is currently no way to abort execution.
node-sqlite3
provides two functions to help controlling the execution flow of statements. The default mode is to execute statements in parallel. However, the Database#close
method will always run in exclusive mode, meaning it waits until all previous queries have completed and node-sqlite3
will not run any other queries while a close is pending.
Puts the execution mode into serialized. This means that at most one statement object can execute a query at a time. Other statements wait in a queue until the previous statements executed.
If a callback is provided, it will be called immediately. All database queries scheduled in that callback will be serialized. After the function returns, the database is set back to its original mode again. Calling Database#serialize()
with in nested functions is safe:
// Queries scheduled here will run in parallel.
db.serialize(function() {
// Queries scheduled here will be serialized.
db.serialize(function() {
// Queries scheduled here will still be serialized.
});
// Queries scheduled here will still be serialized.
});
// Queries scheduled here will run in parallel again.
Note that queries scheduled not directly in the callback function are not necessarily serialized:
db.serialize(function() {
// These two queries will run sequentially.
db.run("CREATE TABLE foo (num)");
db.run("INSERT INTO foo VALUES (?)", 1, function() {
// These queries will run in parallel and the second query will probably
// fail because the table might not exist yet.
db.run("CREATE TABLE bar (num)");
db.run("INSERT INTO bar VALUES (?)", 1);
});
});
If you call it without a function parameter, the execution mode setting is sticky and won't change until the next call to Database#parallelize
.
Puts the execution mode into parallelized. This means that queries scheduled will be run in parallel.
If a callback is provided, it will be called immediately. All database queries scheduled in that callback will run parallelized. After the function returns, the database is set back to its original mode again. Calling Database#parallelize()
with in nested functions is safe:
db.serialize(function() {
// Queries scheduled here will be serialized.
db.parallelize(function() {
// Queries scheduled here will run in parallel.
});
// Queries scheduled here will be serialized again.
});
If you call it without a function parameter, the execution mode setting is sticky and won't change until the next call to Database#serialize
.
Make sure you have the sources for sqlite3
installed. Mac OS X ships with these by default. If you don't have them installed, install the -dev
package with your package manager, e.g. apt-get install libsqlite3-dev
for Debian/Ubuntu.
To obtain and build the bindings:
git clone git://github.com/developmentseed/node-sqlite3.git
cd node-sqlite3
./configure
make
You can also use npm
to download and install them:
npm install https://github.com/developmentseed/node-sqlite3/tarball/master
expresso is required to run unit tests.
npm install expresso
make test
Thanks to Orlando Vazquez, Eric Fredricksen and Ryan Dahl for their SQLite bindings for node, and to mraleph on Freenode's #v8 for answering questions.
node-sqlite3
is BSD licensed.
FAQs
Asynchronous, non-blocking SQLite3 bindings
The npm package sqlite3 receives a total of 662,057 weekly downloads. As such, sqlite3 popularity was classified as popular.
We found that sqlite3 demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 9 open source maintainers 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
ESLint has added JSON and Markdown linting support with new officially-supported plugins, expanding its versatility beyond JavaScript.
Security News
Members Hub is conducting large-scale campaigns to artificially boost Discord server metrics, undermining community trust and platform integrity.
Security News
NIST has failed to meet its self-imposed deadline of clearing the NVD's backlog by the end of the fiscal year. Meanwhile, CVE's awaiting analysis have increased by 33% since June.