It is a popular JavaScript runtime, providing many useful modules and libraries to build web applications, and SQLite is an embedded relational database that can run on various operating systems, including Windows, Linux, and Mac OS X. In which SQLite database can be accessed by installing the sqlite3 module.
SQLite is a single-file offline relational SQL database. Its files are stored in a DB data file. Compared with database servers like MySQL, SQLite is more convenient to migrate. 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.
Quick Start
What is it
It is a JavaScript running environment based on Chrome V8 engine: that is, the V8 engine with built-in Chrome can directly run JavaScript programs in the environment. JavaScript can also be run without the browser environment, as long as there is a JavaScript engine.
In layman's terms, it is a backend program specially used to provide data for front-end services.
Installation and download
Official website: (/en)
Chinese website: Chinese website (/)
After the installation is complete, open the console and enter node -v to view the installation version number.
getting Started
Using front-end development tools VSCode:
① Create a new folder NodeJS in the workspace, create a file, and write it in the file
("hello ");② Right-click the NodeJS folder you created and select Open in terminal. At this time, a console will appear. Enter node in the console to run the js file. The result output hello
③Write a simple server-side application and create a file
//Introduce http moduleconst http=require('http');
//Create a server(function(request,response){
/*
Send HTTP header
HTTP status value: 200: OK
Content type: text/plain
*/
(200,{'Content-Type':'text/html'});
//Send response data "Hello"('<h1>Hello </h1>');
}).listen(8888);//Set the listening port number 8888//Terminal printing('Server running at http://127.0.0.1:8888/');Enter node in the console. Use your browser to access http://127.0.0.1:8888. Responsive data will be obtained
SQLite Quick Start
What is SQLite
SQlite is a very lightweight database, reflected in the following three features
Small size
No installation required
The entire database (definitions, tables, indexes, and data themselves) is stored in a single file on the host host
SQLite installation and download
SQLite download address: SQLite Download Page (/)
Download the corresponding windows version
Download and unzip it locally, and place the dll and exe in the same folder
ps: Here it is best to set the sqlite environment variable yourself and add the startup address to the system environment variable path
Then CMD input: sqite3 Check whether the environment variable is successfully configured
Use of SQLite
Here we use SQLiteStudio as a visualization tool
① Create a new database file
Give it a random name, create a new file. Name Test, suffix.db
② Link database file
Find the file you just created and give your database a name
Test the connection first, click OK after success
At this time, you can create new fields for data tables
Operation SQLite
First install SQLite in Node environment
Install sqlite3 module
Before operating SQLite, you also need to install the sqlite3 module first, and 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
Python is required to compile SQLite modules, and you can download and install it.
If you still have an error when 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 the database
The following opens a database:
const sqlite3 = require('sqlite3'); // Introduce sqlite3 moduleconst path = require('path'); // Introduce path processing moduleconst dbName = (__dirname, ''); // Get the file in the currently running directory// Open the databaseconst db = new (dbName, err => {
if (err !== null) (err); // Output error message});Methods can open a database. If the incoming DB database file 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:
(err => {
if (err) (err);
});The callback function can receive an err, that is, an error message.
Create a data table
Create a user data table below:
// SQL statementconst sql = `
CREATE TABLE user (
id INTEGER PRIMARY KEY,
user_name VARCHAR (30) NOT NULL,
age TINYINT (3) NOT NULL DEFAULT 0
)
`;
// Create a table(sql, function(err) {
if (err) (err); // If an error occurs, the error message will be output});The table creation here uses the run method. The run method can execute SQL statements, and the number of affected rows can be obtained through the callback function. However, when creating a table, you cannot get the number of rows affected. Only when adding, deleting, or modifying can you get the number of rows affected.
Add data
Here is a data to the user table:
('INSERT INTO user (user_name, age) VALUES (?, ?)', ['Mark', 28], function(err) {
if (err) (err); // If there is an error, output an error message(); // Output the number of affected rows(); // Output lastID});The run method can be used to insert, update, and delete data. The SQL statement with the first parameter contains some ?, this ? is the placeholder, and the contents 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 error information, the function can obtain the number of affected rows, and the lastID can be obtained.
Modify data
To modify data or use the run method, as follows:
('UPDATE user SET user_name = $newName WHERE user_name = $userName', {
$userName: 'Mark',
$newName: 'Jack'
}, function(err) {
if (err) (err); // If there is an error, output an error message(); // Output the number of affected rows(); // Output lastID});The second parameter here is an object, and the value of the object will replace the placeholder with the same attribute name in the SQL statement. The above statement is executed as follows:
UPDATE user SET user_name = 'Jack' WHERE user_name = 'Mark'
Query data
To delete data, 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 table contents used for query are as follows:
| id | user_name | age |
|---|---|---|
| 1 | Alice | 30 |
| 2 | Steve | 26 |
The following query shows all the contents in the above table:
('SELECT id, user_name, age FROM user', (err, rows) => {
if (err) (err); // If an error occurs, the error message will be output(rows); // Output query results});The query results are as follows:
[
{ id: 1, user_name: 'Alice', age: 30 },
{ id: 2, user_name: 'Steve', age: 26 }
]The following is only querying the data where user_name is Steve:
('SELECT id, user_name, age FROM user WHERE user_name = ?', ['Steve'], (err, rows) => {
if (err) (err); // If an error occurs, the error message will be output(rows); // Output query results});The placeholders here are the same as those above.
The all method can query multiple pieces of data. The first parameter is the SQL statement. The subsequent parameters can be used to replace the placeholder or a 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, rows is an array of query results. If the content is not queried, rows is an empty array.
If you only need to query one data, that is, the query will not exceed one data, you can consider using the get method. The get method will only query one data, as follows:
('SELECT id, user_name, age FROM user', (err, row) => {
if (err) (err);
(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.
The parameters of the get method are similar to all. The first parameter is a SQL statement. The subsequent parameters can be used to replace the placeholder in SQL, or they can be a callback function. The callback function needs to be at the end. Functions can receive err and row, err is error information, row is query result, if data is not queried, row is undefined.
Summarize
This is the end of this article about using SQLite database methods. For more related SQLite operations, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!