Node.js and MySQL interactive operation and its considerations _node.js

Node.js as a service side of the JS operating environment has appeared for several years, recently I have a friend in the development of this area, but also just contact, encountered a lot of pits. A few days ago they in the operation of the database when there are some problems, and then we looked together, in fact, are the node itself some of the problems of the mechanism, here summed up for beginners to learn.

My friend's database is based on MySQL. (As for why not MongoDB, this is the result of the top selection of the company, because a lot of novice friends always seem to feel node.js is should and MongoDB linked together, so here is simple to say). I later wrote a simple little example, the entire small example uses the Express framework, Node_modules has already downloaded the Express and Ejs templates. First look at the table of contents:

Server.js = "Node.js Server startup file

db.js = "Database operation file

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

public = the place where all static resources are stored, such as CSS JS images, etc.

First of all, introduce the Node.js connection MySQL step, the first step, you need to install a named MySQL package, this package is officially provided, stability is guaranteed, of course, there are other NPM package, here we only use MySQL package. The installation method is very simple, the input instructions npm install MySQL--save, waiting for the download to complete.

See this appearance, that is the installation is successful.

Then we open the Db.js file and write some code in the database that we need to manipulate, and look at the following figure:

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

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

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

User indicates MySQL username

Password represents the MySQL password

Database indicates the name of the specific library you want to select the operation

Port represents the ports, can not fill, the default is 3306

The return value of the Mysql.createconnection connection is a specific object of our next operation for MySQL, all of which are based on his.

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

Call the connection query method to send the SQL statement directly to the database and return the result with the callback function, where there are three parameters in the callback function, the first parameter is the wrong object, and if the operation fails, the error message is stopped and printed, and the second parameter is the result of the concrete return. Normally an array containing a lot of JSON, and a third argument is an array, where the bread contains the interpretation of the most data, such as which library the current data belongs to, the table, and so on. We use the most natural is the second parameter.

Close the connection after the database operation is complete

The whole process is very simple, but there are two problems, the first is the loss of the database connection, do not know if anyone has encountered, the first time to visit the home page, the connection to the database normal, the second visit to the home page, the database connection is not on the error, the connection is missing. This reason is that we closed the database every time the database connection, again visit 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 Db.js file, not every time there will be a new connection generated? Yes, it's just me at the beginning. The code that generated the connection was not placed in the Db.query function, but outside, as in the following figure:

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

The second problem is that we introduced db.js in 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 results of the database query. Some people will say why not directly in the Db.js use return, the query results returned, what a callback Ah!

In fact, this is the node.js of the asynchronous caused by some of the problems, if we change the code in the Server.js to the following figure

Because we see the MySQL package Query method is asynchronous operation, which causes the following Res.render () method will not wait for him to query out the results of the implementation, 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 to go in, after the MySQL query method ends, pass the result through the parameter to our own write callback function, so we can get the result in the callback function. And then perform the rendering. Of course, to deal with this problem can also introduce a Third-party package async to solve the asynchronous problem, specific look at the individual.