Looking for a lightweight embedded database on node. As a representative of embedded databases, sqlite is undoubtedly an ideal choice. There are two main libraries that integrate sqlite on npm - sqlite3 and realm.
realm is an ideal option, originally designed for mobile apps and can run in nodes, but does not support Windows systems. sqlite3 is a specially designed for nodejs. The ecosystem is more robust on nodejs, so I finally chose sqlite3.
sqlite3 supports almost all versions of nodejs, and can also be integrated with nwjs.
Install
Install based on npm
npm install sqlite3
In this way, in addition to installing the npm package of sqlite3, the most important thing is to install the sqlite database, because sqlite is an embedded database and is embedded in the client. sqlite3 uses node-pre-gyp to download specified precompiled binary files for each platform. If the precompiled binary cannot be downloaded, sqlite3 will use node-gyp and source code to build the extension.
This process has two libraries - node-pre-gyp and node-gyp. What exactly are they?
node-gyp is a cross-platform command line tool used to compile nodejs extensions written in C++. First of all, gyp is a project generation tool created for Chromium projects. It can generate platform-related Visual Studio, Xcode, and Makefile project files from platform-independent configurations. node-gyp is to integrate it into nodejs. Because Linux's binary distribution fast platform is not good, all npm can be distributed directly in source code for convenience, and then compile it on site when the user installs it. However, for some projects, binary distribution is much simpler than source code distribution, so there is also a node-pre-gyp for direct binary extension distribution.
The difference between the two is that node-gyp is the source code for publishing the extension and then compiled during installation; node-pre-gyp is the extension directly released in the compiled secondary form.
Like sqlite3, there are also many npm modules that need to be installed based on node-gyp, such as node-sass, etc., which release the source code and then compile and install.
Basic API
The APIs of sqlite3 are all based on function callbacks, because there is no official database client interface like JDBC in nodejs, so the APIs of each database are different. Here are a few important APIs for sqlite3.
Create and open the database
new (filename, [mode], [callback])
This method returns an automatically opened database object with parameters:
filename: The valid value is a file name, such as: "", after the database is opened, a file of "" will be created to save the data. If the file name is ":memory:", it means it is a memory database (similar to h2), the data will not be persisted and the content will be lost when the database is closed.
mode (optional): The database schema, 3 values in total: sqlite3.OPEN_READONLY (read-only), sqlite3.OPEN_READWRITE (read-write) and sqlite3.OPEN_CREATE (can be created). The default value is OPEN_READWRITE |OPEN_CREATE.
callback (optional): This function will be called when the database is successfully opened or an error occurs. The first parameter is an error object. When it is empty, it means that the opening is successful.
Open a database, such as:
//The name of the database is ""var database;
database = new ("", function(e){
if (err) throw err;
});
//It can also use memory type, data will not be saved permanentlydatabase = new (":memory:", function(e){
if (err) throw err;
});
After execution, a "" file will be generated in the root directory of the project, which is the file where SQLite saves the data.
Close the database
Database#close([callback])
This method can close a database connection object with parameters:
callback (optional): Close successful callback. The first parameter is an error object, and when it is "null", it means that the closing is successful.
Execute DDL and DML statements
Database#run(sql, [param, ...], [callback])
This method can execute DDL and DML statements, such as creating tables, deleting tables, deleting row data, inserting row data, etc. Parameters:
sql: SQL string to run. The types of sql are DDL and DML, and DQL cannot use this command. The return value after execution does not contain any results, and the execution result must be obtained through the callback callback function.
param,... (optional): When the SQL statement contains a placeholder (?), the corresponding parameters can be passed here. Here are three methods of passing values, such as:
// Pass the value directly through the parameters.("UPDATE tbl SET name = ? WHERE id = ?", "bar", 2);
// Encapsulate the value into an array to pass the value.("UPDATE tbl SET name = ? WHERE id = ?", [ "bar", 2 ]);
// Use a json to pass the value. The prefixes of the parameters can be ":name", "@name" and "$name". Recommended to use "$name" form("UPDATE tbl SET name = $name WHERE id = $id", {
$id: 2,
$name: "bar"
});
Regarding the naming of placeholders, sqlite3 also supports more complex forms, and it will no longer be expanded here. If you are interested in learning, please check the official documentation.
callback (optional): If the execution is successful, the first parameter is null, otherwise an error occurs.
If the execution is successful, the context this contains two properties: lastID and changes. The lastID represents the id of the last data when executing the INSERT command statement; changes represents the number of data rows affected by the UPADTE command and the DELETE command.
("UPDATE foo SET id = 1 WHERE id <= 500", function(err) {
if (err) throw err;
//Use to get the number of changed rows (500, );
done();
});
Execute multiple statements
Database#exec(sql, [callback])
Database#exec is like Database#run function, both DDL and DML statements, but Database#exec can execute multiple statements and does not support placeholder parameters.
("CREATE TABLE foo (id INT)", function(e){
if(e !== null){
throw e;
}
//Change to generate sql statements and insert multiple pieces of data in batches var sql = "";
for(var i = 0 ; i < 500; i ++){
sql += 'INSERT INTO foo VALUES(' + i + ');'
}
(sql, done)
});
Query a data
Database#get(sql, [param, ...], [callback])
sql: SQL string to run. The type of sql is DQL. Only the first query data is returned here.
param,... (optional): Param parameters with Database#run
callback (optional): Returning null means execution is successful. 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 found, which is a json object, and the property 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: SQL string to run. The type of sql is DQL. Unlike Database#get, Database#all will return all the query statements.
param,... (optional): Param parameters with Database#run
callback (optional): Returning null means execution is successful. 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 result rows and stores them in memory. For query commands that may have a large amount of data, please use Database#each function or Database#prepare instead.
Traversal of data
Database#each(sql, [param, ...], [callback], [complete])
Same as Database#run function, both query multiple pieces of data, but have the following differences:
The signature of the callback is function(err, row). If the result set is successful but empty, the callback will not be called. For each row retrieved, the method calls a callback once. The execution order corresponds exactly to the row order in the result set.
After calling all row callbacks, if a complete callback function exists, this callback will be called. The first parameter is an error object, and the second parameter is to retrieve the number of rows.
Statement execution order
The sqlite3 APIs are all asynchronous, which will lead to the situation where several commands may be performed simultaneously. Therefore, sqlite3 provides two functions to help control the execution process of the statement. The default is parallel mode.
Serialization execution
Database#serialize([callback])
If a callback is provided, it will be called immediately, i.e. the callback of this method is not an asynchronous callback. All database statements scheduled in this callback will be serialized, i.e. executed one by one. Once the function returns, the database will be set to its original mode again.
// The commands executed here are parallel(function() {
// The commands executed here are serial (function() {
// The commands executed here are serial});
// The commands executed here are serial});
// The commands executed here are parallel
Parallel execution mode
Database#parallelize([callback])
If a callback is provided, it will be called immediately, i.e. the callback of this method is not an asynchronous callback. All database statements scheduled in this callback will run in parallel. Once the function returns, the database will be set to its original mode again.
(function() {
// The commands executed here are serial (function() {
// The commands executed here are parallel});
// The commands executed here are serial});
Precompiled SQL related API
In Java's jdbc, there is a PreparedStatement-related API that can precompile SQL statements and link specific parameters when executing. This advantage is that it can reduce the number of times the SQL statement is compiled. In sqlite3, there are also APIs that implement such functions.
Database#prepare(sql, [param, ...], [callback])
After Database#prepare is executed, a command object will be returned, which can be executed repeatedly. Let's take a look at the API of 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 called the same method as the same name in Database. The difference is that the Statement object here can be reused, avoiding repeated compilation of SQL statements, so the above method is more recommended in the project.
! Note that the param parameters of these methods will bind parameters to the Statement object. If the parameters are not rebinded during the next execution, the last parameter will be used.
Bind parameters
Statement#bind([param, ...], [callback])
When Database#prepare is executed, parameters can be bound. However, using this method, you can reset the statement object and line cursor completely, and delete all previously bound parameters to achieve the rebinding function.
Reset the line cursor of the statement
Statement#reset([callback])
Reset the line cursor of the statement and preserve parameter binding. Use this feature to re-execute the same query using the same binding.
Database transactions
Transactions are an important part of a relational database. SQLite naturally supports transactions, but SQLite3 does not provide transaction-related operations implemented by special APIs, and can only rely on SQL statements to control transactions. Here is a transaction-related example.
var db = new (db_path);
("CREATE TABLE foo (id INT, txt TEXT)");
("BEGIN TRANSACTION");
var stmt = ("INSERT INTO foo VALUES(?, ?)");
for (var i = 0; i < count; i++) {
(i, randomString());
}
("COMMIT TRANSACTION");
Support for SQLCipher
SQLCipher is an open source database that extends on the basis of SQLite. It is different from SQLite that it provides encryption of data and can provide transparent 256-bit AES encryption of database files.
The official website of sqlite3 specifically mentioned its integration of SQLCipher. If you want to integrate SQLcipher, you need to tell sqlite3 through the build options during compilation to integrate SQLCipher:
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=/usr/
node -e 'require("sqlite3")'
However, the author has not tried to integrate SQLCipher. Please check the detailed introduction of this part on the official website for the specific integration method.
Encapsulation of SQLite3API based on promise
The API of sqlite3 is an early API style of node. It is not friendly to asynchronous writing styles and is prone to "pyramid callback"-style code. To make calls to the API more elegant, we often encapsulate callbacks into promises. In fact, this work does not require us to do it ourselves. There are other libraries in the sqlite3 ecosystem that can implement such functions. sqlite is such a library. Based on sqlite3, he reencapsulated the sqlite3 API with Promise, making its code style more elegant and easier to use.
The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.