How Node.js operates SQLite database records

SQLite is a single-file offline relational SQL database. Its files are stored in a DB data file. Compared with database servers such as MySQL, SQLite is more convenient for migration. For servers with small memory, Using SQLite is also a good choice. SQLite can also be integrated into desktop applications and used as a local database.

Many programming languages ​​can operate SQLite databases, and Node.js is no exception. Recently I am writing an Electron desktop program, which needs to use SQLite as a local database. Here I will briefly write about Node.js operating SQLite.

Install sqlite3 module

Before operating SQLite, you need to install the sqlite3 module first, install it with npm:

npm install sqlite3 --save-dev

If the following error occurs during installation:

gyp ERR! find Python Python is not set from command line or npm configuration
gyp ERR! find Python Python is not set from environment variable PYTHON
gyp ERR! find Python checking if "python" can be used
gyp ERR! find Python - "python" is not in PATH or produced an error
gyp ERR! find Python checking if "python2" can be used
gyp ERR! find Python - "python2" is not in PATH or produced an error
gyp ERR! find Python checking if "python3" can be used
gyp ERR! find Python - "python3" is not in PATH or produced an error

The compilation of the SQLite module requires Python, which can be downloaded and installed.

If you still get an error after installing the latest version of Python, you can install Python 2 through npm. If you are using Windows, you can run Powershell with administrator privileges and enter:

npm install --global windows-build-tools

After installing Python, you should be able to install the sqlite3 module.

Open database

Open a data.db database below:

const sqlite3 = require('sqlite3'); //Introduce the sqlite3 module
const path = require('path'); //Introduce path processing module
const dbName = path.join(__dirname, 'data.db'); // Get the data.db file in the current running directory
//Open database
const db = new sqlite3.Database(dbName, err => {
if (err !== null) console.log(err); // Output error message
});

The sqlite3.Database method can open a database. If the DB database file passed in does not exist, it will create one and return a database object.

If you want to close an open database, you can use the close method, as follows:

db.close(err => {
if (err) console.log(err);
});

The callback function can receive an err, which is an error message.

Create data table

Create a user data table below:

//SQL statement
const sql = `
CREATE TABLE user (
id INTEGER PRIMARY KEY,
user_name VARCHAR (30) NOT NULL,
age TINYINT (3) NOT NULL DEFAULT 0
)
`;
//Create table
db.run(sql, function(err) {
if (err) console.log(err); // If an error occurs, output the error message
});

The run method is used to create the table here. The run method can execute SQL statements and the number of affected rows can be obtained through the callback function. However, the number of affected rows cannot be obtained when creating a table. The number of affected rows can only be obtained when adding, deleting, or modifying.

Add data

The following adds a piece of data to the user table:

db.run('INSERT INTO user (user_name, age) VALUES (?, ?)', ['Mark', 28], function(err) {
if (err) console.log(err); // If there is an error, output the error message
console.log(this.changes); // Output the number of affected lines
console.log(this.lastID); // Output lastID
});

The run method can be used to insert, update, and delete data. The SQL statement of the first parameter contains some ?. This ? is a placeholder. The content in the second array will replace the placeholder. The above SQL statement is executed as follows:

INSERT INTO user (user_name, age) VALUES ('Mark', 28)

The third parameter is a callback function. The callback function will be executed after the statement is executed. The err parameter of the function can obtain the error information. This.changes of the function can obtain the number of affected rows. This.lastID can obtain the lastID.

Modify data

To modify data, still use the run method, as follows:

db.run('UPDATE user SET user_name = $newName WHERE user_name = $userName', {
$userName: 'Mark',
$newName: 'Jack'
}, function(err) {
if (err) console.log(err); // If there is an error, output the error message
console.log(this.changes); // Output the number of affected lines
console.log(this.lastID); // Output lastID
});

The second parameter here is an object. The value of the object will replace the placeholder with the same attribute name in the SQL statement. When the above statement is executed, it is as follows:

UPDATE user SET user_name = 'Jack' WHERE user_name = 'Mark'

Query data

To delete data, still use the run method, which is similar to the above, except that the SQL statement is different, so I won’t write it here.

The contents of the table used for query are as follows:

The following queries all the contents in the above table:

db.all('SELECT id, user_name, age FROM user', (err, rows) => {
if (err) console.log(err); // If an error occurs, output the error message
console.log(rows); // Output query results
});

The results of the query are as follows:

[
{ id: 1, user_name: 'Alice', age: 30 },
{ id: 2, user_name: 'Steve', age: 26 }
]

The following only queries data where user_name is Steve:

db.all('SELECT id, user_name, age FROM user WHERE user_name = ?', ['Steve'], (err, rows) => {
if (err) console.log(err); // If an error occurs, output the error message
console.log(rows); // Output query results
});

The placeholders here are still the same as above.

The all method can query multiple pieces of data. The first parameter is the SQL statement, and the subsequent parameters can be the content used to replace the placeholder, or the callback function. The callback function needs to be placed at the end. The callback function can receive two parameters, err and rows. err is the error message, and rows is the array of query results. If no content is queried, rows is an empty array.

If you only need to query one piece of data, that is, if the query data will not be more than one piece, you can consider using the get method. The get method will only query one piece of data, as follows:

db.get('SELECT id, user_name, age FROM user', (err, row) => {
if (err) console.log(err);
console.log(row);
});

The query results are as follows:

{ id: 1, user_name: 'Alice', age: 30 }

Although there are two pieces of data in the above table, the get method only queries one piece.

The parameters of the get method are similar to all. The first parameter is the SQL statement, and the subsequent parameters can be the content used to replace the placeholder in the SQL, or they can be a callback function. The callback function needs to be at the end. The function can receive err and row. err is the error message and row is the query result. If no data is queried, row is undefined.

Recommended site searches: free trial of virtual space, expired registered domain name, domain name registration number query, how much does server defense cost, free virtual host application for registered domain name purchase, how to register domain name, anti-attack IP, free virtual host, telecommunications host Rent,