How to install mysql package in nodejs

You can use the npm tool to install the mysql package in nodejs. The specific installation method: 1. Open the terminal and enter the specified project directory; 2. Execute the "npm install mysql --save" command and wait for the download and installation to complete. Can.

The operating environment of this tutorial: windows7 system, nodejs version 14.15.4, DELL G3 computer.

Nodejs method of installing mysql package and connecting to MySQL

First, install nodejs and npm.

I wrote a simple example. The entire example uses the express framework. The express and ejs templates have been downloaded in node_modules. Let’s take a look at the directory first:

server.js =》 node.js server startup file

db.js =》Database operation file

views =》Store template files, that is, where all pages are stored

public =》Store all static resources, such as css js images and the like

How to connect to MySQL:

The first step is to install an npm package called MySQL. This package is officially provided and its stability can be guaranteed. Of course, there are other npm packages. , here we only use the mysql package. The installation method is very simple. Enter the command npm install mysql --save and wait for the download to complete.

If you see this, the installation is successful. .

Then we open the db.js file and write some codes necessary for database operations. See the picture below:

The code is very small, meaning It is also obvious that the first step requires the introduction of the mysql package. Then we create an empty object and assign it a method called query. This method accepts two parameters. The first parameter is the SQL statement when you query the data. The second parameter is the callback function to obtain the query results.

Looking inside the function, the first block of code is used to set the configuration for operating mysql:

host represents the address of mysql installation, because I am Local database, so use localhost directly

• user represents the user name of mysql

• password represents the password of mysql

• database represents the name of the specific library you want to choose to operate

• port represents the port, which can be left blank. The default is 3306

The return value of mysql.createConnection, connection, is a specific object for our next operation of mysql. All operation methods are based on it.

Call the connect method of connection to determine whether the connection is successful. If it fails, print out the error message and stop running.

Call the query method of connection to directly send sql statements to the database, and use the callback function to return the result. There are three parameters in the callback function, The first parameter is the error object. If the operation fails, it will stop and print the error message. The second parameter is the specific returned result. Under normal circumstances, it is an array containing a lot of json. The third parameter is also an array. Contains the explanation of each data, such as which database the current data belongs to, which table, etc. The second parameter we use most is naturally

Close the connection after the database operation is completed

The whole process is very simple, but there are two problems. The first one is the problem of lost database connection. I don’t know if anyone has encountered it. When I visit the homepage for the first time, the connection The database is normal. When I visit the homepage for the second time, the database cannot be connected, and an error is reported saying that the connection is lost. The reason for this is that we close the database connection after each database operation. When we access the database again, we cannot find the connection, but the connection has to be closed. Some people may find it strange that the db.js file is accessed every time we visit the homepage. Isn't there a new connection generated every time? Yes, it’s just that the code that generated the connection at the beginning was not placed in the db.query function, but was placed outside, as shown below:

This will cause the connection to be generated only once. After it is closed, the second interview will not be connected. After placing it in the function, use exports to expose the interface to the outside world. Every time you visit the homepage, you will go through the process of creating a connection again, and you will get a new connection every time, so there will be no problem in accessing. In fact, the connection pool can be used directly in the project. Saved a lot of trouble.

The second problem is that we introduced db.js in server.js

You may have seen at this time that mysql.query has two Parameters, the first one is sql, the second one is the callback function. The callback function has a result parameter, which is actually the result of the database query. Some people may ask why not use return directly in db.js to return the query results, what kind of callback is there?

In fact, here are some problems caused by the asynchrony of node.js. If we change the code in server.js to the following picture

Because we have seen that the query method of the mysql package is an asynchronous operation, this results in the following res.render() method not waiting for the result to be queried before executing it. Often the result has not come out yet. The page has already been rendered, but the data has not been obtained, so an error will be reported. Therefore, we had to pass a callback function in. After the mysql query method ended, the results were passed into the callback function we wrote ourselves through parameters, so that we can get the results in the callback function. Then perform rendering. Of course, to deal with this problem, you can also introduce the third-party package async to solve the asynchronous problem. It depends on the individual.

[Related recommendations: "How to install nodejs in a windows environment? 》】

The above is the detailed content of How to install mysql package in nodejs. For more information, please follow other related articles on the PHP Chinese website!