Node.js SQLite3

Introduction

This is a node.js driver (Asynchronous, non-blocking SQLite3 bindings) for sqlite3. It is written in JavaScript, does not require compiling. It provides all most all connection/query from SQLit3. This is probably one of the best modules used for working with SQLite3 database and the module is actively maintained.

We assume that you have already installed SQLite3 and node.js on Windows or Linux environment.

Here is an example to retrieve the names (first name, last name) from 'employees' table belongs to 'hr' database :

Note: the module must be installed before use.

var sqlite3 = require('sqlite3').verbose();
var file = "hr";
var db = new sqlite3.Database(file);
db.all("SELECT first_name,last_name FROM employees", function(err, rows) {
        rows.forEach(function (row) {
            console.log(row.first_name, row.last_name);
        })
	});	
db.close();

Output :

E:\nodejs>node test.js
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg
Daniel Faviet
--------------
--------------

Another example to create a new database, table, insert and retrieve records :

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('abcd');

db.serialize(function() {
  db.run("CREATE TABLE user (id INT, dt TEXT)");

  var stmt = db.prepare("INSERT INTO user VALUES (?,?)");
  for (var i = 0; i < 10; i++) {
  
  var d = new Date();
  var n = d.toLocaleTimeString();
  stmt.run(i, n);
  }
  stmt.finalize();

  db.each("SELECT id, dt FROM user", function(err, row) {
      console.log("User id : "+row.id, row.dt);
  });
});

db.close();

Output :

E:\nodejs>node test.js
User id : 0 17:14:51
User id : 1 17:14:51
User id : 2 17:14:51
User id : 3 17:14:51
User id : 4 17:14:51
User id : 5 17:14:51
User id : 6 17:14:51
User id : 7 17:14:51
User id : 8 17:14:51
User id : 9 17:14:51

Features

• Straightforward query and parameter binding interface
• Full Buffer/Blob support
• Extensive debugging support
• Query serialization API
• Extension support
• Written in modern C++ and tested for memory leaks

Installing

You can use npm to download and instal l:

The latest sqlite3 package : npm install sqlite3

GitHub's master branch: npm install https://github.com/mapbox/node-sqlite3/tarball/master

In both cases the module is automatically built with npm's internal version of node-gyp, and thus your system must meet node-gyp's requirements.

Supported platforms

The sqlite3 module works with Node.js v0.10.x or v0.11.x (though only v0.11.13 and above). Binaries for most Node versions and platforms are provided by default via node-pre-gyp.

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

API

new sqlite3.Database(filename, [mode], [callback])

Returns a new Database object and automatically opens the database. There is no separate method to open the database.

sqlite3.verbose()

Sets the execution mode to verbose to produce long stack traces. There is no way to reset this. See the wiki page on debugging for more information.

Close Database

Database#close([callback])

Closes the database.

callback (optional) : If provided, this function will be called when the database was closed successfully or when an error occurred.

Run SQL Query

Database#run(sql, [param, ...], [callback])

Runs the SQL query with the specified parameters and calls the callback afterward. It does not retrieve any result data. The function returns the Database object for which it was called to allow for function chaining.

Runs SQL query with specified parameters

Database#get(sql, [param, ...], [callback])
Runs the SQL query with the specified parameters and calls the callback with the first result row afterward. The function returns the Database object to allow for function chaining.

Database#all(sql, [param, ...], [callback])
Runs the SQL query with the specified parameters and calls the callback with all result rows afterward. The function returns the Database object to allow for function chaining.

Database#each(sql, [param, ...], [callback], [complete])
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.

Runs SQL queries in the supplied string

Database#exec(sql, [callback])
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.

Preparing SQL statement

Database#prepare(sql, [param, ...], [callback])
Prepares the SQL statement and optionally binds the specified parameters and calls the callback when done. The function returns a Statement object.

Statement#bind([param, ...], [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.

Statement#reset([callback])
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.

Statement#finalize([callback])
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).

Statement#run([param, ...], [callback])
Binds parameters and executes the statement. The function returns the Statement object to allow for function chaining.

Statement#get([param, ...], [callback])
Binds parameters, executes the statement and retrieves the first result row. The function returns the Statement object to allow for function chaining.

Statement#all([param, ...], [callback])
Binds parameters, executes the statement and calls the callback with all result rows. The function returns the Statement object to allow for function chaining.

Statement#each([param, ...], [callback], [complete])
Binds parameters, executes the statement and calls the callback for each result row. The function returns the Statement object to allow for function chaining.

Debugging

Writing asynchronous functions using the thread pool unfortunately also removes all stack trace information, making debugging very hard since you only see the error message, not which statement caused it. To mitigate this problem, node-sqlite3 has a verbose mode which captures stack traces when enqueuing queries. To enable this mode, call the sqlite3.verbose(), or call it directly when requiring: var sqlite3 = require('sqlite3').verbose().

When you throw an error from a callback passed to any of the database functions, node-sqlite3 will append the stack trace information from the original call, like this:

var db = new sqlite3.Database('abcd);
                              ^^^^^^^
SyntaxError: Unexpected token ILLEGAL
    at Module._compile (module.js:439:25)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)
    at Function.Module.runMain (module.js:497:10)
    at startup (node.js:119:16)
    at node.js:902:3

Note that you shouldn't enable the verbose mode in a production setting as the performance penalty for collecting stack traces is quite high. Verbose mode currently does not add stack trace information to error objects emitted on Statement or Database objects.

Database#on('trace', [callback])
The trace event is emitted whenever a query is run. The first and only parameter to the callback is the SQL string that was sent to the database. The event is emitted as soon as the query is executed (e.g. with .run() or .get()). A single statement may be emitted more once. EXPLAIN statements will not trigger an event, so it's safe to pipe all SQL queries you receive from this event back into the database prefixed with an EXPLAIN QUERY PLAN.

If you execute statements from this callback, make sure that you don't enter an infinite loop!

Database#on('profile', [callback])
The profile event is emitted whenever a query is finished. The first parameter is the SQL string that was sent to the database, the second parameter is the time approximate time it took to run in milliseconds. The event is emitted after the query completed.

If you execute statements from this callback, make sure that you don't enter an infinite loop!

Structure of 'hr' database :

Previous: Node-MySQL
Next: Introduction