Need help with node-sqlite3?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

mapbox
4.6K Stars 614 Forks BSD 3-Clause "New" or "Revised" License 1.3K Commits 345 Opened issues

Description

Asynchronous, non-blocking SQLite3 bindings for Node.js

Services available

!
?

Need anything else?

Contributors list

No Data

Asynchronous, non-blocking SQLite3 bindings for Node.js.

NPM

Build Status Build status Coverage Status Dependencies FOSSA Status N-API v3 Badge

Supported platforms

The

sqlite3
module works with: * Node.js v11.x, v12.x, v13.x and v14.x. * Electron v6.0.x, v6.1.x, v7.0.x, v7.1.x, v8.0.x, v8.1.x and v8.2.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.)

Usage

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();

Features

  • Straightforward query and parameter binding interface
  • Full Buffer/Blob support
  • Extensive debugging support
  • Query serialization API
  • Extension support, including bundled support for the json1 extension.
  • Big test suite
  • Written in modern C++ and tested for memory leaks
  • Bundles SQLite3 3.32.3 as a fallback if the installing system doesn't include SQLite

API

See the API documentation in the wiki.

Installing

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.

Source install

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/

By default the node-gyp install will use

python
as part of the installation. A different python executable can be specified on the command line.
npm install --build-from-source --python=/usr/bin/python2

This uses the npmconfigpython config, so values in .npmrc will be honoured:

python=/usr/bin/python2

Custom file header (magic)

The default sqlite file header is "SQLite format 3".
You can specify a different magic, though this will make standard tools and libraries unable to work with your files.

npm install --build-from-source --sqlite_magic="MyCustomMagic15"

Note that the magic must be exactly 15 characters long (16 bytes including null terminator).

Building for node-webkit

Because of ABI differences,

sqlite3
must be built in a custom to be used with node-webkit.

To build node-sqlite3 for node-webkit:

  1. Install

    nw-gyp
    globally:

    npm install nw-gyp -g
    (unless already installed)
  2. 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).
    • For example,
      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.

Building for sqlcipher

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:

On OS X with Homebrew

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")'

On most Linuxes (including Raspberry Pi)

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")'

Custom builds and Electron

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://electronjs.org/headers

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://electronjs.org/headers

Testing

mocha is required to run unit tests.

In sqlite3's directory (where its

package.json
resides) run the following:
npm install mocha
npm test

Contributors

Acknowledgments

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.

License

node-sqlite3
is BSD licensed.

FOSSA Status

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.