node. JS connects to MySQL and integrates the connection into the Express middleware

Introduction

In the process of connecting to MySQL in node. js, we usually have two connection methods, normal connection and connection pool. These two methods are more common, and when we use the Express framework we also choose to use the intermediate express-myconnection, which can be configured separately for MySQL, or integrate connection into express middleware. Finally, send the previous node. JS connection to the various main database sample code.

Pre-conditions

1, install MySQL corresponding driver, npm install MySQL

2. Install the third-party plugin express-connection, NPM install Express-connection

Normal connection

var mysql      = require (' mysql '); var connection = mysql.createconnection ({  host     ' localhost ',  user     ' root ',  ' secret ',  ' my_db '}); Connection.connect (); Connection.query (' Select  function(err, rows, fields) {  ifthrow  err;    Console.log (' The solution is: ', rows);});

Connection. end ();

Connection pool

Once you've introduced a connection pool, the easiest thing to do is to manually turn off connection after each run. option for connection pooling has many options that can be configured to suit your needs.

var mysql = require (' mysql '); var pool  = Mysql.createpool ({  ten,  host            ' example.org ',  user             ' Bob ',  password    ' secret '});p ool.query (' Select  function  (Err, rows, fields) {  ifthrow  err;  Console.log (' The solution is: ', rows);});

Of course, if your application is not so many, and you do not trust the connection pool recycling mechanism, you can also manually close the connection implementation to put the connection back into the resource pool, call connection.release ()

Pool.getconnection (function(err, connection) {  // Use the connection  function (err, rows) {    //And do with the connection.     connection.release ();     // Don ' t use the connection here, it had been returned to the pool.   });});

To turn off connections for the entire connection pool

Pool.end (  err) {  // all connections in the pool has ended});

Express-myconnection

Express-myconnection is a middleware that connect/express automatically provides MySQL connectivity. Provides a total of three policy management db connections.

• Single To create a single-database application instance, the connection will never be closed, and it will reconnect if the connection is broken due to a failure.
• Pool Create a connection pool based on the application instance, and provide a connection to each request from the connection pool, and the connection is automatically released back to the connection pool each time response.
• Request A new connection is created for each request and is automatically closed at the end of the response.

This is also the method I used in the project, because the business logic is not complex, does not encapsulate the DB layer, directly in the App.js configuration, and then called directly in the routing layer.

App.js

var mysql = require (' mysql '),     = require (' express-myconnection '),    = {      ' localhost '      , ' Dbuser ',      ' password ',      3306,      ' mydb '    };  ' Single '); As a middleware to use

/router/order.js application in Routing files

Stored procedures can also be called here: Conn.query (' Call usp_test ', [parameter],function (Err,result))

Router.get ('/cost ',function(req, res, next) {Req.getconnection (function(ERR, conn) {if(err) {returnNext (ERR); } Else{conn.query (' SELECT * from test ', [],function(err,result) {if(err) {returnNext (ERR); } Else{Res. Json (result); //The result set conversion JSON can be returned to the client directly                }            }); }    });});

Resources

Https://tonicdev.com/npm/express-myconnection

Http://expressjs.com/en/guide/database-integration.html

Https://www.terlici.com/2015/08/13/mysql-node-express.html

node. JS connects to MySQL and integrates the connection into the Express middleware