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.
Asynchronous, non-blocking SQLite3 bindings for Node.js.
The sqlite3 module works with Node.js v0.10.x, v0.12.x, v4.x, v5.x, v6.x and v7.x.
Binaries for most Node versions and platforms are provided by default via node-pre-gyp.
The sqlite3 module also works with node-webkit if node-webkit contains a supported version of Node.js engine. (See below.)
SQLite's SQLCipher extension is also supported. (See below.)
Note: the module must be installed before use.
var sqlite3 = require('sqlite3').verbose();
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);
}
stmt.finalize();
db.each("SELECT rowid AS id, info FROM lorem", function(err, row) {
console.log(row.id + ": " + row.info);
});
});
db.close();
See the API documentation in the wiki.
You can use npm to download and install:
The latest sqlite3 package: npm install sqlite3
GitHub's master branch: npm install https://github.com/mapbox/node-sqlite3/tarball/master
The module uses node-pre-gyp to download a pre-compiled binary for your platform, if it exists. Otherwise, it uses node-gyp to build the extension.
It is also possible to make your own build of sqlite3 from its source instead of its npm package (see below).
It is possible to use the installed package in node-webkit instead of the vanilla Node.js. See Building for node-webkit for details.
To skip searching for pre-compiled binaries, and force a build from source, use
npm install --build-from-source
The sqlite3 module depends only on libsqlite3. However, by default, an internal/bundled copy of sqlite will be built and statically linked, so an externally installed sqlite3 is not required.
If you wish to install against an external sqlite then you need to pass the --sqlite argument to npm wrapper:
npm install --build-from-source --sqlite=/usr/local
If building against an external sqlite3 make sure to have the development headers available. 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. Make sure that you have at least libsqlite3 >= 3.6.
Note, if building against homebrew-installed sqlite on OS X you can do:
npm install --build-from-source --sqlite=/usr/local/opt/sqlite/
Because of ABI differences, sqlite3 must be built in a custom to be used with node-webkit.
To build node-sqlite3 for node-webkit:
Install nw-gyp globally: npm install nw-gyp -g (unless already installed)
Build the module with the custom flags of --runtime, --target_arch, and --target:
NODE_WEBKIT_VERSION="0.8.6" # see latest version at https://github.com/rogerwang/node-webkit#downloads
npm install sqlite3 --build-from-source --runtime=node-webkit --target_arch=ia32 --target=$(NODE_WEBKIT_VERSION)
This command internally calls out to node-pre-gyp which itself calls out to nw-gyp when the --runtime=node-webkit option is passed.
You can also run this command from within a node-sqlite3 checkout:
npm install --build-from-source --runtime=node-webkit --target_arch=ia32 --target=$(NODE_WEBKIT_VERSION)
Remember the following:
You must provide the right --target_arch flag. ia32 is needed to target 32bit node-webkit builds, while x64 will target 64bit node-webkit builds (if available for your platform).
After the sqlite3 package is built for node-webkit it cannot run in the vanilla Node.js (and vice versa).
npm test of the node-webkit's package would fail.Visit the “Using Node modules” article in the node-webkit's wiki for more details.
For instructions for building sqlcipher see Building SQLCipher for node.js
To run node-sqlite3 against sqlcipher you need to compile from source by passing build options like:
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=/usr/
node -e 'require("sqlite3")'
If your sqlcipher is installed in a custom location (if you compiled and installed it yourself), you'll also need to to set some environment variables:
Set the location where brew installed it:
export LDFLAGS="-L`brew --prefix`/opt/sqlcipher/lib"
export CPPFLAGS="-I`brew --prefix`/opt/sqlcipher/include"
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=`brew --prefix`
node -e 'require("sqlite3")'
Set the location where make installed it:
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
node -e 'require("sqlite3")'
Running sqlite3 through electron-rebuild does not preserve the sqlcipher extension, so some additional flags are needed to make this build Electron compatible. Your npm install sqlite3 --build-from-source command needs these additional flags (be sure to replace the target version with the current Electron version you are working with):
--runtime=electron --target=1.7.6 --dist-url=https://atom.io/download/electron
In the case of MacOS with Homebrew, the command should look like the following:
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=`brew --prefix` --runtime=electron --target=1.7.6 --dist-url=https://atom.io/download/electron
mocha is required to run unit tests.
In sqlite3's directory (where its package.json resides) run the following:
npm install mocha
npm 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.
Development of this module is sponsored by MapBox.
node-sqlite3 is BSD licensed.
FAQs
Asynchronous, non-blocking SQLite3 bindings
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
Research
An advanced npm supply chain attack is leveraging Ethereum smart contracts for decentralized, persistent malware control, evading traditional defenses.
Security News
Research
Attackers are impersonating Sindre Sorhus on npm with a fake 'chalk-node' package containing a malicious backdoor to compromise developers' projects.
Security News
The npm package for the LottieFiles Player web component was hit with a supply chain attack after a software engineer's npmjs credentials were compromised.