sqlite3

What is sqlite3?

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.

What are sqlite3's main functionalities?

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

Other packages similar to sqlite3

better-sqlite3

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

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

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.

README

NAME

node-sqlite3 - Asynchronous, non-blocking SQLite3 bindings for node.js 0.2-0.4 (versions 2.0.x) and 0.6.x (versions 2.1.x).

USAGE

Install with npm install sqlite3.

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
• Big test suite
• Written in modern C++ and tested for memory leaks

API

See the API documentation in the wiki.

BUILDING

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. Make sure that you have at least libsqlite3 >= 3.6.

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 sqlite3

TESTS

expresso is required to run unit tests.

npm install expresso
make test

CONTRIBUTORS

• Konstantin Käfer
• Dane Springmeyer
• Will White
• Orlando Vazquez
• Artem Kustikov
• Eric Fredricksen
• John Wright
• Ryan Dahl
• Tom MacWright
• Carter Thaxton
• Audrius Kažukauskas
• Johannes Schauer

ACKNOWLEDGEMENTS

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 Development Seed.

LICENSE

node-sqlite3 is BSD licensed.