Convert Figma logo to code with AI

TryGhost logonode-sqlite3

SQLite3 bindings for Node.js

6,189
813
6,189
166
View on GitHubView on NPM

Top Related Projects

TryGhost's avatar

node-sqlite3

6,189

SQLite3 bindings for Node.js

WiseLibs's avatar

better-sqlite3

5,385

The fastest and simplest library for SQLite3 in Node.js.

mysqljs's avatar

mysql

18,264

A pure node.js JavaScript Client implementing the MySQL protocol.

Quick Overview

TryGhost/node-sqlite3 is a Node.js binding for SQLite3. It provides an asynchronous, non-blocking SQLite3 driver for Node.js, allowing developers to interact with SQLite databases in their Node.js applications efficiently. This library is widely used and actively maintained.

Pros

  • Asynchronous API for non-blocking database operations
  • Supports both callbacks and promises for flexible usage
  • Prebuilt binaries available for many platforms, simplifying installation
  • Extensive documentation and community support

Cons

  • Limited to SQLite databases, not suitable for other database types
  • May require manual compilation on some platforms if prebuilt binaries are not available
  • Performance may be slower compared to native SQLite for very large datasets
  • Occasional issues with version compatibility between Node.js and the library

Code Examples

  1. Opening a database and running a query:
const sqlite3 = require('sqlite3').verbose();

const db = new sqlite3.Database('mydb.sqlite', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the database.');
});

db.all("SELECT * FROM users", [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row.name);
  });
});
  1. Using promises with node-sqlite3:
const sqlite3 = require('sqlite3');
const { open } = require('sqlite');

async function getUsers() {
  const db = await open({
    filename: 'mydb.sqlite',
    driver: sqlite3.Database
  });

  const users = await db.all('SELECT * FROM users');
  console.log(users);
  await db.close();
}

getUsers();
  1. Performing a transaction:
db.serialize(() => {
  db.run("BEGIN TRANSACTION");
  
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["John", "john@example.com"]);
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Jane", "jane@example.com"]);
  
  db.run("COMMIT", (err) => {
    if (err) {
      console.error(err.message);
      db.run("ROLLBACK");
    } else {
      console.log("Transaction completed successfully");
    }
  });
});

Getting Started

To use node-sqlite3 in your project:

  1. Install the package:

    npm install sqlite3

  2. Require the module in your JavaScript file:

    const sqlite3 = require('sqlite3').verbose();

  3. Create a database connection:

    const db = new sqlite3.Database('mydb.sqlite', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the database.');
});

  4. Use the db object to perform database operations as shown in the code examples above.

Competitor Comparisons

TryGhost logo

node-sqlite3

6,189

SQLite3 bindings for Node.js

Pros of node-sqlite3

  • Actively maintained with regular updates
  • Extensive documentation and community support
  • Wide range of features for SQLite database operations

Cons of node-sqlite3

  • Larger package size due to comprehensive feature set
  • May have a steeper learning curve for beginners
  • Potential performance overhead for simple use cases

Code Comparison

Both repositories appear to be the same project, so there isn't a meaningful code comparison to make. The TryGhost/node-sqlite3 repository is likely a fork or mirror of the original node-sqlite3 project. Here's a basic example of how to use node-sqlite3:

const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./mydb.sqlite', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the SQLite database.');
});

This code snippet demonstrates how to create a connection to an SQLite database using node-sqlite3. The same code would work for both repositories since they represent the same project.

WiseLibs logo

better-sqlite3

5,385

The fastest and simplest library for SQLite3 in Node.js.

Pros of better-sqlite3

  • Significantly faster performance due to synchronous API design
  • Built-in support for prepared statements, improving query efficiency
  • Simpler API with less boilerplate code required

Cons of better-sqlite3

  • Lacks support for asynchronous operations, which may block the event loop
  • More limited platform support compared to node-sqlite3
  • Smaller community and ecosystem

Code Comparison

better-sqlite3:

const db = new Database('foobar.db', { verbose: console.log });
const row = db.prepare('SELECT * FROM users WHERE id = ?').get(userId);
console.log(row.name);

node-sqlite3:

const db = new sqlite3.Database('foobar.db');
db.get('SELECT * FROM users WHERE id = ?', [userId], (err, row) => {
  if (err) {
    console.error(err);
  } else {
    console.log(row.name);
  }
});

better-sqlite3 offers a more straightforward, synchronous API, while node-sqlite3 uses callbacks for asynchronous operations. The former is generally faster but may block the event loop, while the latter is more suitable for handling multiple concurrent database operations without blocking.

mysqljs logo

mysql

18,264

A pure node.js JavaScript Client implementing the MySQL protocol.

Pros of mysql

  • Supports complex queries and joins, ideal for relational data
  • Better suited for large-scale applications with high concurrency
  • Offers robust replication and clustering capabilities

Cons of mysql

  • Requires more setup and configuration compared to SQLite
  • Higher resource consumption and overhead
  • Less portable, as it relies on a separate database server

Code Comparison

mysql:

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'mydb'
});
connection.query('SELECT * FROM users', (error, results) => {
  if (error) throw error;
  console.log(results);
});

node-sqlite3:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./mydb.sqlite');
db.all('SELECT * FROM users', [], (error, rows) => {
  if (error) throw error;
  console.log(rows);
});

Both libraries provide similar query interfaces, but mysql requires more initial setup with connection details. node-sqlite3 is simpler to use for local databases, while mysql is better suited for networked, multi-user environments. The choice between them depends on the specific requirements of your project, such as scalability needs, deployment environment, and data complexity.

Convert Figma logo designs to code with AI

Visual Copilot

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

⚙️ node-sqlite3

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

Latest release Build Status FOSSA Status N-API v3 Badge N-API v6 Badge

Features

Installing

You can use npm or yarn to install sqlite3:

  • (recommended) Latest published package:
npm install sqlite3
# or
yarn add sqlite3
  • GitHub's master branch: npm install https://github.com/tryghost/node-sqlite3/tarball/master

Prebuilt binaries

sqlite3 v5+ was rewritten to use Node-API so prebuilt binaries do not need to be built for specific Node versions. sqlite3 currently builds for both Node-API v3 and v6. Check the Node-API version matrix to ensure your Node version supports one of these. The prebuilt binaries should be supported on Node v10+.

The module uses prebuild-install to download the prebuilt binary for your platform, if it exists. These binaries are hosted on GitHub Releases for sqlite3 versions above 5.0.2, and they are hosted on S3 otherwise. The following targets are currently provided:

  • darwin-arm64
  • darwin-x64
  • linux-arm64
  • linux-x64
  • linuxmusl-arm64
  • linuxmusl-x64
  • win32-ia32
  • win32-x64

Unfortunately, prebuild cannot differentiate between armv6 and armv7, and instead uses arm as the {arch}. Until that is fixed, you will still need to install sqlite3 from source.

Support for other platforms and architectures may be added in the future if CI supports building on them.

If your environment isn't supported, it'll use node-gyp to build SQLite, but you will need to install a C++ compiler and linker.

Other ways to install

It is also possible to make your own build of sqlite3 from its source instead of its npm package (See below.).

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.)

API

See the API documentation in the wiki.

Usage

Note: the module must be installed before use.

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

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/

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

You can also run this command from within a 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 on building SQLCipher, see Building SQLCipher for Node.js. Alternatively, you can install it with your local package manager.

To run against SQLCipher, you need to compile sqlite3 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 need 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/sqlcipher"
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=18.2.1 --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=18.2.1 --dist-url=https://electronjs.org/headers

Testing

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.

This module was originally created by Mapbox & is now maintained by Ghost.

Changelog

We use GitHub releases for notes on the latest versions. See CHANGELOG.md in git history for details on older versions.

License

node-sqlite3 is BSD licensed.

FOSSA Status

NPM DownloadsLast 30 Days