Github NPM API

Asynchronous, non-blocking SQLite3 bindings for Node.js

Sqlite3 is a lightweight embedded database designed for NodeJS. As a representative of embedded databases, SQLite is an ideal solution.

Sqlite3 supports almost all versions of NodeJS and can also be integrated with NWJS.

The installation

Install based on NPM

npm install sqlite3
Copy the code

In addition to installing the NPM package for SQlite3, the most important thing is also installing the SQLite database, because SQLite is an embedded database, embedded in the client. Sqlite3 uses Node-pre-gyp to download the specified pre-compiled binaries for each platform. If the pre-compiled binaries cannot be downloaded, SQlite3 will build the extension using Node-gyp and source code.

This process produces two libraries — Node-pre-gyp and Node-gyp. What exactly are they?

Node-gyp is a cross-platform command line tool for compiling nodejs extensions written in C++. Gyp is a project generation tool created for the Chromium project. You can generate platform-specific Visual Studio, Xcode, and Makefiles from platform-independent configurations, which node-gyp integrates into NodeJS. Because Linux binary distribution fast platform does not do well, all NPM for the convenience of simply direct source distribution, user installation time and then on-site compilation. However, for some projects binary distribution is much easier than source distribution, so there is a Node-pre-gyp to distribute binary extensions directly.

The difference between node-gyp and Node-gyp is that you distribute the source of the extension and compile it at install time. Node-pre-gyp is an extension that directly publishes compiled second-level form.

As with SQlite3, there are many NPM modules that need to be installed based on Node-gyp, such as Node-sass, etc., which are distributed source code, compiled and installed.

Based on the API

Sqlite3 apis are based on function callbacks. Nodejs does not have an official database client interface like Java’s JDBC, so each database API is different. Here are a few important APIS for SQlite3.

Create and open the database

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

This method returns an automatically opened database object with the following arguments:

Filename: indicates a valid filename, for example, mydatebase.db. After the database is opened, a file named mydatebase.db is created to save data. If the file name is “:memory:”, it indicates an in-memory database (similar to h2). The data is not persisted and is lost when the database is shut down.

Mode (Optional) : indicates the database mode. The value can be sqlite3.OPEN_READONLY (read-only), sqlite3.OPEN_READWRITE (read and write), or sqlite3.OPEN_CREATE (create). The default value is OPEN_READWRITE | OPEN_CREATE.

Callback (Optional) : this function is called when the database is successfully opened or an error occurs. The first argument is an error object, which, when empty, indicates successful opening.

Open a database

// The name of the database is "mydatebase.db"
var database;
database = new sqlite3.Database("mydatebase.db".function(e) {
  if (err) throw err;
});
// You can also use the memory type, data is not stored forever
database = new sqlite3.Database(":memory:".function(e) {
  if (err) throw err;
});
Copy the code

This generates a “myDateBase.db” file in the root directory of the project, which is where SQLite stores the data.

Closing the database

Database#close([callback])

This method closes a database connection object with the following parameters:

Callback (Optional) : Closes successful callback. The first argument is an error object, which, when null, indicates successful closure.

Execute DDL and DML statements

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

This method can execute DDL and DML statements, such as create table, delete table, delete row data, insert row data, etc.

SQL: THE SQL string to run. The types of SQL are DDL and DML. DQL cannot use this command. The return value does not contain any result after execution and must be retrieved through the callback function.

param,… (Optional) : When the SQL statement contains placeholders (?) , the corresponding parameters can be passed here. There are three methods of passing values, such as:

// Pass the value directly through the argument.
db.run("UPDATE tbl SET name = ? WHERE id = ?"."bar".2);

// Encapsulate the value as an array pass.
db.run("UPDATE tbl SET name = ? WHERE id = ?"["bar".2]);

// Use a JSON pass. Arguments can be prefixed with ":name", "@name", or "$name". $name is recommended
db.run("UPDATE tbl SET name = $name WHERE id = $id", {
  $id: 2.$name: "bar"
});
Copy the code

Sqlite3 also supports more complex forms of placeholder naming, which are not extended here, but should you be interested in the official documentation.

Callback (Optional) : If the execution succeeds, the first argument is null; otherwise, an error occurs.

If executed successfully, the context this contains two properties: lastID and CHANGES. LastID specifies the ID of the last data item when the INSERT command statement is executed. Changes Indicates the number of rows affected by the UPADTE and DELETE commands.

db.run("UPDATE foo SET id = 1 WHERE id <= 500".function(err) {
  if (err) throw err;
  // Use this.changes to get the number of rows changed
  assert.equal(500.this.changes);
  done();
});
Copy the code

Execute multiple statements

Database#exec(sql, [callback])

Database#exec, like the Database# run function, are both DDL and DML statements, but Database#exec can execute multiple statements and does not support placeholder arguments.

database.run("CREATE TABLE foo (id INT)".function(e) {
  if(e ! = =null) {
    throw e;
  }
  // Generate SQL statement loop, batch insert multiple data
  var sql = "";
  for (var i = 0; i < 500; i++) {
    sql += "INSERT INTO foo VALUES(" + i + ");";
  }
  database.exec(sql, done);
});
Copy the code

Query a piece of data

Database#get(sql, [param, …] , [callback])

SQL: THE SQL string to run. The type of SQL is DQL. Only the data from the first query is returned.

param,… (optional) : same as the param parameter of Database#run

Callback (Optional) : Also returns null indicating successful execution. The signature of the callback is function (err, row). If the query result set is empty, the second parameter is undefined. Otherwise, the second parameter value is the first object queried, which is a JSON object, and the attribute name corresponds to the column name of the result set, so each column in the query should be given a list name.

Query all data

Database#all(sql, [param, …] , [callback])

SQL: THE SQL string to run. The type of SQL is DQL. Unlike Database#get, Database#all returns all queries.

param,… (optional) : same as the param parameter of Database#run

Callback (Optional) : Also returns null indicating successful execution. The signature of the callback is function(err, rows). Rows is an array if the query result set is an empty array.

! Notice that Database#all first retrieves all result rows and stores them in memory. Use Database# each or Database# prepare instead of this method for query commands with potentially large data volumes.

Through the data

Database#each(sql, [param, …] , [callback], [complete])

The Database# run function queries multiple sets of data, but with the following differences:

The signature of the callback is function (err, row). If the result set is successful but empty, the callback is not invoked. The method invokes a callback for each retrieved row. The execution order corresponds exactly to the row order in the result set.

After all the row callbacks are called, the complete callback is called if it exists. The first argument is an error object, and the second argument is the number of rows to retrieve.

Using Prepared Statements

In Java JDBC, there is a PreparedStatement API that allows you to precompile SQL statements and link specific parameters when executing them. This has the advantage of reducing the number of times SQL statements are compiled. In SQlite3, apis exist to do this as well.

Database#prepare(sql, [param, …] , [callback])

Database#prepare returns a command object that can be executed repeatedly. Let’s look at the API for this statement:

Statement#run([param, …] , [callback])

Statement#get([param, …] , [callback])

Statement#all([param, …] , [callback])

Statement#each([param, …] , [callback], [complete])

The above API methods are called in the same way as the Database method of the same name. The difference is that the Statement object can be reused, avoiding the repeated compilation of SQL statements. Therefore, the above method is recommended in this project.

! Note that the param parameters of these methods bind parameters to the Statement object and will be used the next time they are executed if they are not rebound.

Binding parameters

Statement#bind([param, …] , [callback])

When Database#prepare is executed, parameters can be bound. Using this method, however, you can completely reset the statement object and the row cursor, and remove all previously bound parameters for rebinding.

Reset the row cursor of the statement

Statement#reset([callback])

Reset the line cursor of the statement and preserve the parameter binding. Use this feature to re-execute the same query with the same binding.

Database transactions are an important part of a relational database, and SQLite naturally supports transactions, but SQlite3 does not provide a special API to implement transaction-related operations, only to rely on SQL statements to control transactions. Here is a transaction-related example.

var db = new sqlite3.Database(db_path);
db.run("CREATE TABLE foo (id INT, txt TEXT)");
db.run("BEGIN TRANSACTION");
var stmt = db.prepare("INSERT INTO foo VALUES(? ,?) ");
for (var i = 0; i < count; i++) {
  stmt.run(i, randomString());
}
db.run("COMMIT TRANSACTION");
Copy the code

Statement execution order (Control Flow)

Sqlite3’s apis are asynchronous, which makes it possible to have several commands running simultaneously, so SQlite3 provides two functions to help control the flow of statements. The default is parallel mode.

Serialized execution

Database#serialize([callback])

If a callback is provided, it will be invoked immediately, that is, the callback for this method is not an asynchronous callback. All database statements scheduled in this callback are run serialized, that is, executed one after the other. When the function returns, the database is set to its original mode again.

// The commands are executed in parallel
db.serialize(function() {
  // The command is executed sequentially
  db.serialize(function() {
    // The command is executed sequentially
  });
  // The command is executed sequentially
});
// The command is executed in parallel mode
Copy the code

Database#parallelize([callback])

If a callback is provided, it will be invoked immediately, that is, the callback for this method is not an asynchronous callback. All database statements scheduled in this callback are run in parallel. When the function returns, the database is set to its original mode again.

db.serialize(function() {
  // The command is executed sequentially
  db.parallelize(function() {
    // The commands are executed in parallel
  });
  // The command is executed sequentially
});
Copy the code

Support for SQLCipher

SQLCipher is an open source database extended on the basis of SQLite. Different from SQLite, it provides data encryption and transparent 256-bit AES encryption for database files.

Sqlite3’s official website specifically refers to its integration of SQLCipher, if you want to integrate SQLCipher needs to be compiled by building options to tell SQlite3 to integrate SQLCipher:

npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=/usr/
node -e 'require("sqlite3")'
Copy the code

However, the author did not attempt to integrate SQLCipher. For specific integration methods, please refer to the detailed introduction of this part on the official website.

Wrapper around the sqlite3API based on promise

Sqlite3’s API is an early Node API style that is not friendly to asynchronous writing and is prone to “pyramid callback” code.

To make calls to the API more elegant, we tend to wrap callbacks as promises.

We don’t actually have to do this ourselves, there are other libraries in the SQlite3 ecosystem that can do this.

Sqlite is one such library. Based on SQlite3, he single-handedly repackaged the SQlite3 API with Promise, making it more elegant and easier to use.

API

Main

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

Database

  • Database#close([callback])
  • Database#configure(option, value)
  • Database#run(sql, [param, …] , [callback])
  • Database#get(sql, [param, …] , [callback])
  • Database#all(sql, [param, …] , [callback])
  • Database#each(sql, [param, …] , [callback], [complete])
  • Database#exec(sql, [callback])
  • Database#prepare(sql, [param, …] , [callback])

Statement

  • Statement#bind([param, …] , [callback])
  • Statement#reset([callback])
  • Statement#finalize([callback])
  • Statement#run([param, …] , [callback])
  • Statement#get([param, …] , [callback])
  • Statement#all([param, …] , [callback])
  • Statement#each([param, …] , [callback], [complete])