Shulou(Shulou.com)06/01 Report--

Looking for lightweight embedded databases above node, sqlite is undoubtedly an ideal choice as a representative of embedded databases. There are two main sqlite libraries on npm-sqlite3 and realm.

Realm is an ideal option, originally designed for mobile apps, but it also runs on node, but does not support Windows. Squite3 is designed specifically for nodejs and is ecologically more robust on top of nodejs, so sqlite3 was chosen.

SQLITE3 supports almost all versions of NODEJS and can also be integrated with NWJS.

installation

Installation 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, embedded in the client. sqlite3 uses node-pre-gyp to download specified precompiled binaries for each platform. If the precompiled binaries cannot be downloaded, sqlite3 will use node-gyp and source code to build extensions.

Two libraries emerge from this process--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++. First, gyp is a project generation tool created for Chromium projects. It can generate platform-dependent Visual Studio, Xcode, Makefile project files from platform-independent configurations. Node-gyp is to integrate them into nodejs. Because linux binary distribution fast platform does not do well, all npm for convenience simply direct source distribution, users installed when the site compilation. However, for some projects binary distribution is much simpler than source distribution, so there is a node-pre-gyp to distribute binary extensions directly.

The difference is that node-gyp releases the source code of the extension and compiles it at installation time;node-pre-gyp releases the compiled two-level version of the extension directly.

There are also many npm modules that need to be installed based on node-gyp like sqlite3, such as node-sass, etc., all of which are released source code and then compiled and installed.

Foundation API

SQL 3 APIs are based on function callbacks, because nodejs does not have the official database client interface like java jdbc, so each database API is different, here is a brief introduction to several important SQL 3 APIs.

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

This method returns an automatically opened database object with parameters:

filename: Valid values are a filename, such as "mydatebase.db", which will create a file "mydatebase.db" to save data after the database is opened. If the file name is ":memory: ," it means an in-memory database (like h3), the data is not persisted, and the contents are lost when the database is closed.

mode (optional): The mode of the database, with 3 values: sqlite3.OPEN_READONLY (read-only), sqlite3.OPEN_READWRITE (read-write), and sqlite3.OPEN_CREATE (creatable). Default is OPEN_READWRITE| OPEN_CREATE。

callback (optional): This function is called when the database opens successfully or an error occurs. The first parameter is an error object, which, when empty, indicates successful opening.

Open a database such as:

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

After execution, a "mydatebase.db" file will be generated in the root directory of the project, which is the file where sqlite saves data.

Database#close([callback])

This method can close a database connection object. Parameters:

callback (optional): Closes successful callbacks. The first argument is an error object, which when null indicates successful shutdown.

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. SQL types are DDL and DML, and DQL cannot use this command. After execution, the return value does not contain any results, and the execution result must be obtained through the callback function.

param，... (Optional): When SQL statements contain placeholders (?) In this case, the corresponding parameters can be used. There are three ways to pass values, such as:

//pass the value directly through the parameter.db.run("UPDATE tbl SET name = ? WHERE id = ? ", "bar", 2);//Wrap the value as an array to pass the value.db.run("UPDATE tbl SET name = ? WHERE id = ? ", [ "bar", 2 ]);//use a json pass. Parameters can be prefixed with ":name,""@name," and "$name." Recommended form db.run("UPDATE tbl SET name = $name WHERE id = $id", { $id: 2, $name: "bar"});

For placeholder naming, sqlite3 also supports more complex forms, which are no longer extended here. Please check the official documentation for more information.

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

If executed successfully, the context this contains two attributes: lastID and changes. lastID indicates the id of the last piece of data when the INSERT command statement is executed;changes indicates the number of rows of data affected when the UPADTE command and Delete command are executed.

db.run("UPDATE foo SET id = 1 WHERE id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.