Nodejs Integrated SQLite

is looking for a lightweight embedded database above node, as the representative of the embedded database, SQLite is undoubtedly an ideal choice. There are two--sqlite3 and realms in the library that integrates SQLite on NPM.

Realm is an ideal option, originally designed for mobile apps and can be run on node, but not in Windows. Sqlite3 is a nodejs designed for the Nodejs, which is ecologically more robust, and therefore ultimately chooses Sqlite3.

Sqlite3 supports almost all versions of Nodejs, and it can be integrated with NWJS.

Installation

Based on NPM installation

NPM Install Sqlite3

In addition to installing the Sqlite3 NPM package, the main thing is to also complete the SQLite database, because SQLite is an embedded database embedded in the client. Sqlite3 uses NODE-PRE-GYP to download the specified precompiled binaries for each platform. If it is not possible to download to a precompiled binary, Sqlite3 will use NODE-GYP and source code to build the extension.

This process occurs with two libraries--node-pre-gyp and NODE-GYP. What the hell are they?

Node-gyp is a cross-platform command-line tool for compiling Nodejs extensions written in C + +, first Gyp is a project build tool created for chromium projects that can generate platform-dependent visual Studio, Xcode, The Makefile project file, Node-gyp, is to integrate it into Nodejs. Because Linux binary distribution fast platform does not do well, all npm in order to facilitate the direct source of the distribution, the user installed the site to compile. However, for some projects binary distribution is much simpler than the source distribution, so there is also a node-pre-gyp to direct binary extension distribution.

The difference is that Node-gyp is the source of the release extension, and then the installation time to compile; Node-pre-gyp is the direct release of the compiled two-tier form of extension.

As with Sqlite3, there are many NPM modules that need to be installed based on NODE-GYP, such as Node-sass, and so on, all of which are release source code and then compiled and installed.

Base API

Sqlite3 APIs are based on function callbacks, because there is no official database client interface like Java JDBC in Nodejs, so the API for each database is different, here is a brief introduction to several Sqlite3 important APIs.

Create and open a database

New Sqlite3. Database (filename, [mode], [callback])

The method returns an automatically opened database object, parameters:

FileName: Valid value is a file name, such as: "Mydatebase.db", after the database is opened, a "mydatebase.db" is created to hold the data. If the file name is ": Memory:", it means that it is a database (like H2), the data is not persisted, and the content is lost when the database is closed.

Mode (optional): Database schema, a total of 3 values: Sqlite3. Open_readonly (Read only), Sqlite3. Open_readwrite (Readable and writable) and sqlite3.open_create (can be created). 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 parameter is an Error object, and when it is empty, it indicates that the opening was successful.

Open a database, such as:

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 a memory type, and the data will not be permanently saved by database = new Sqlite3. Database (": Memory:", function (e) {if (err) throw err;});

After execution, a "mydatebase.db" file is generated at the root of the project, which is the file for SQLite to save the data.

Close the database

Database#close ([callback])

The method can close a database connection object, parameters:

Callback (optional): Closes the successful callback. The first parameter is an Error object, and when it is "null", it indicates a successful shutdown.

Executing DDL and DML statements

Database#run (SQL, [Param, ...], [callback])

The method can execute DDL and DML statements, such as building tables, deleting tables, deleting row data, inserting row data, and so on, parameters:

sql: The SQL string to run. The type of SQL is DDL and DML,DQL cannot use this command. The return value does not contain any results after execution, and the execution result must be obtained through the callback callback function.

Param, ... (optional): When the SQL statement contains a placeholder (?), the corresponding parameter can be passed here. Here are three methods of passing values, such as:

Pass the value directly through the parameter. Db.run ("UPDATE tbl SET name =?") WHERE id =? "," Bar ", 2);

Encapsulates a value as an array pass-through value. Db.run ("UPDATE tbl SET name =?"). WHERE id =? ", [" Bar ", 2]);

Use a JSON to pass a value. The prefix of the parameter can be ": Name", "@name" and "$name". It is recommended to use the "$name" form Db.run ("UPDATE tbl SET name = $name WHERE id = $id", {$id: 2, $name: "Bar"});

With regard to the naming of placeholders, Sqlite3 also supports more complex forms, which are no longer extended and are interested to see the official documentation.

Callback (optional): If execution succeeds, the first parameter is NULL, otherwise it is an error.

If the execution succeeds, the context this contains two properties: LastID and changes. LastID represents the number of data rows that are affected when the id;changes of the last data represents the Upadte command and the Delete command when an INSERT command statement is executed.

Db.run ("UPDATE foo SET id = 1 WHERE ID <=", function (Err) {if (err) throw err;//use this.changes to get the number of rows changed assert.equal (this.changes); Done ();});

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 do not support placeholder parameters.

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

Querying a single piece of data

Database#get (SQL, [Param, ...], [callback])

sql: The SQL string to run. The type of SQL is DQL. This returns only the data from the first query.

Param, ... (optional): param parameters of the same database#run

Callback (optional): The same return null represents 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, and he is a JSON object, and the property name corresponds to the column name of the result set, so each column of the query should give a list name.

Querying 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 to the statement.

Param, ... (optional): param parameters of the same database#run

Callback (optional): The same return null represents successful execution. The signature of the callback is function (err, rows). Rows is an array if the query result set is an empty array.

Note that Database#all first retrieves all the result rows and stores them in memory. Use the Database#each function or Database#prepare instead of this method for query commands that may have a large amount of data.

Traversing data

Database#each (SQL, [Param, ...], [callback], [complete])

The same as the Database#run function, queries multiple data, but with the following differences:

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

After all row callbacks are called, the callback is called if a complete callback function exists. The first parameter is an Error object, and the second parameter is the number of rows to retrieve.

Statement execution order

The Sqlite3 API is asynchronous, and there may be several commands at the same time, so sqlite3 provides two functions to help control the execution of a statement. The default is parallel mode.

Serialization of execution

Database#serialize ([callback])

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

The command executed here is the parallel db.serialize (function () {//command executed here is Serial db.serialize (function () {//command executed here is serial});//The command executed here is serial});//Here The command executed is in parallel

Parallel execution Mode

Database#parallelize ([callback])

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

Db.serialize (function ()///The command executed here is a serial db.parallelize (function () {//the command executed here is parallel});//The command executed here is serial});

Precompiled SQL-related APIs

In Java JDBC, there is a PreparedStatement-related API that can precompile SQL statements and then link specific parameters when executed. The benefit is that you can reduce the number of times that SQL statements are compiled. In Sqlite3, there is also an API to implement such functionality.

Database#prepare (SQL, [Param, ...], [callback])

Database#prepare executes, it returns a command object that can be executed repeatedly. Here's a look at the API for this Command object (statement):

Statement#run ([param, ...], [callback]) statement#get ([Param, ...], [callback]) Statement#all ([Param, ...], [callback] ) Statement#each ([Param, ...], [callback])

The above API method is the same as the method invocation of database with the same name. The difference is that the statement object here is reusable and avoids compiling SQL statements repeatedly, so the above method is more recommended in the project.

Note that the param parameter of these methods will bind the parameters to the statement object, and the last parameter will be used at the next execution if there are no rebinding parameters.

Binding parameters

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

When the Database#prepare executes, it is possible to bind the parameters. However, using this method, you can completely reset the statement object and row cursors, and remove all previously bound parameters to implement the functionality of Rebinding.

Reset a row cursor for a statement

Statement#reset ([callback])

Resets the row cursor for the statement and preserves the parameter bindings. Use this feature to re-execute the same query with the same bindings.

Database transactions

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

var db = new Sqlite3. Database (Db_path);d b.run ("CREATE TABLE foo (id INT, txt TEXT)");d B.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");

Support for the Sqlcipher

Sqlcipher is an open source database that expands on SQLite, and unlike SQLite, it provides encryption of data, which provides transparent 256-bit AES encryption of database files.

Sqlite3 's official website specifically mentions his integration with Sqlcipher, if the integration Sqlcipher need to be compiled with the build option to tell Sqlite3 to integrate Sqlcipher:

NPM install sqlite3--build-from-source--sqlite_libname=sqlcipher--sqlite=/usr/node-e ' require ("sqlite3") '

However, the author did not try to integrate the Sqlcipher, the specific integration method, please consult the official website of this part of the detailed introduction.

Encapsulation of SQLITE3API based on promise

Sqlite3 's API is the early API style of node, which is not friendly to the asynchronous writing style and is prone to "pyramid callback" code. To make calls to the API more elegant, we tend to encapsulate callbacks as promise. In fact, the job does not require us to do it ourselves, and there are other libraries in the sqlite3 ecosystem that can do this. SQLite is one such library. Based on Sqlite3, he only promise the Sqlite3 API with the hand, making its code style more elegant and easier to use.

Nodejs Integrated SQLite