node. JS connection MySQL operation and precautions

node. js as a service-side JS running environment has been there for several years, recently I have a friend is also doing this development, but also just contact, encountered a lot of pits. A few days ago they in the operation of the database, there are some problems, and then we looked together, in fact, are the node itself some of the problems of the mechanism, here to summarize for beginners to learn from.

My friend's database is based on MySQL. (As for why not MongoDB, this is the result of the company's upper-level selection, because many novice friends seem to always feel that node. JS is supposed to be associated with MongoDB, so here's a simple word). I later wrote a simple small example, the entire small example using the Express framework, Node_modules has downloaded the Express and Ejs templates. First look at the directory:

Server.js = "node. js Server Startup file

db.js = "Database operation file

views = "Store template files, which is where all pages are stored

public = "places where all static resources are stored, such as CSS JS images

First of all, we introduce the steps of node. js to connect MySQL, the first step is to install a npm called MySQL package, the package is officially provided, stability is guaranteed, of course, there are other NPM packages, here we only use MySQL package. The installation method is very simple, the input command npm install MySQL--save, waiting for the download to complete.

See this look, that is the installation succeeded.

Then we open the Db.js file, in the inside write the database operation must have some code, see:

The code is very small, the meaning is obvious, the first step needs to be introduced to the MySQL package, and then we create an empty object, and assign him a method called query, this method takes two parameters, the first parameter is the SQL statement when you query the data, the second parameter is the callback function to get the result of the query.

Looking again inside the function, the first block of code is used to set the operation of MySQL configuration:

Host represents the address of the MySQL installation because I am the local database, so use localhost directly

User name for MySQL

Password represents the MySQL password

Database indicates the name of the specific library you want to choose to manipulate

Port is not required, but 3306 is the default.

The return value of mysql.createconnection connection is a specific object that we will then operate on MySQL, all of which are based on his.

Call connection's Connect method to determine if the connection is successful, and if it fails, print out the error message and stop running.

Call connection's Query method to send SQL statements directly to the database, and return the result with a callback function, where there are three parameters in the callback function, the first parameter is the wrong object, if the operation fails, it will stop and print the error message, the second parameter is the specific return result, Normally it is an array containing a lot of JSON, and the third parameter is an array, with the explanation of the most data in the bread, such as which library the current data belongs to, the table, and so on. The most natural we use is the second parameter.

Close the connection after the database operation has ended

The whole process is very simple, but there are two problems, the first is the database connection loss problem, do not know if anyone has encountered, the first time to visit the home page, the connection database is normal, the second access to the home page, the database connection is not on, will error said connection lost. This is the reason we closed the database after each operation of the database connection, once again access to the time can not find the connection, but the connection can not be closed, some people may feel strange, every visit to the home page will be access to the Db.js file, not every time there will be a new connection to produce it? Yes, it was just the first time that the code that generated the connection was not placed in the Db.query function, but on the outside, such as:

This causes the connection to be generated only once, closed after the second interview is not connected. After placing it inside the function, use exports to expose the interface externally. Every time you visit the home page, you will go through the process of creating the connection, each time you can get a new connection, so that access is no problem. In fact, the connection pool can be used directly in the project. Save a lot of trouble.

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

At this point you may see, Mysql.query has two parameters, the first is SQL, the second is a callback function, the callback function has a result parameter, in fact, he is the database query results. Some people will say why not directly in the Db.js use return, the query results returned, what callback Ah!

In fact, this is the async problem of node. js, if we change the code in Server.js to

Because we see the MySQL package Query method is asynchronous operation, which leads to the following Res.render () method will not wait for him to query the results after the execution, often the result has not come out, has begun to render the page, but the data did not get, so it will be an error. So we have to send a callback function, and after the end of the MySQL query method, the result is passed to our own write callback function, so that we can get the result in the callback function. And then perform the rendering. Of course, dealing with this problem can also introduce a third-party package async to solve the asynchronous problem, specifically to see the individual.

node. JS connection MySQL operation and precautions