How does Node. js use MySQL connection pool instances?

How does Nodejs use MySQL?

To connect Nodejs to MySQL, you can use the MysQL driver of Nodejs. For example, here we use "node-mysql" to connect to the database. We use the following method to connect to the database:

First, we need to use nodejs's package management tool (npm) to install the mysql driver. The command line is as follows:


Npm install musql


Now, to use mysql in the js file, add the following code to your file:

Var mysql = require ('mysql ');


Next, we can use this module to connect to the MySQL database. Of course, to connect to the database, you must specify the host name, user name, and password of the MySQL Server. There are many other options that can be set, such as the database Time Zone, socketPath, and local address.


Var connection = mysql. createConnection ({
Host: "hostName ",
User: "username ",
Password: "password"
});


The following code creates a new connection for you.


Connection. connect ();


With this connection object, we can query the database as below. We can use the connection. escape () method to prevent SQL injection.

Connection. query ("use database1 ");
Var strQuery = "select * from table1 ";
 
Connection. query (strQuery, function (err, rows ){
If (err ){
Throw err;
} Else {
Console. log (rows );
      }
});


Finally, we can close the connection in two ways. Use connection. end or connection. destroy.

 
The following expression ensures that all queries in the queue are executed before the database connection is closed. Note that there is a callback function.


Connection. end (function (err ){
// Do something after the connection is gracefully terminated.

});


The following expression immediately closes the database connection. There is no callback function or any event is triggered.


Connection. destroy ();

Nodejs uses the MysQL connection pool


Using the connection pool can help us better manage database connections. The database connection pool can limit the maximum number of connections and reuse existing connections.

First, we need to create a connection pool:


Var mysql = require ('mysql ');
Var pool = mysql. createPool ({
Host: "hostName ",
User: "username ",
Password: "password"
});


Secondly, we can obtain a connection from the created connection pool:

Pool. getConnection (function (err, connection ){
 

});


Use the connection parameter of the callback function to query the database. Finally, use the connection. Reallocate () method to release the database connection.


Pool. getConnection (function (err, connection ){
Connection. query ("select * from table1", function (err, rows ){
If (err ){
Throw err;
} Else {
Console. log (rows );
      }
});
 
Connection. release ();
});


Execute multiple query statements

To ensure security, multiple query statements are not allowed by default. To use the function of multiple query statements, you must enable this function when creating a database connection:


Var connection = mysql. createConnection ({multipleStatements: true });

After enabling this function, you can use multiple query statements at the same time as in the following example:


Connection. query ('select column1; select column2; select column3; ', function (err, result ){
If (err ){
Throw err;
} Else {
Console. log (result [0]); // Column1 as a result
Console. log (result [1]); // Column2 as a result
Console. log (result [2]); // Column3 as a result
  }
});


Use of mysql connection pool in node. js

If you do not want the program to get stuck or wait for a long time when querying data, it is generally not recommended to enable a connection in node and use this link for all queries without shutting down, because, after you try, you will know why.

Node. js mysql connection pool module

1. install the node mysql module npm-install-g node-mysql
2. Create a class library named mysql. js. The content is as follows:
Var mysql = require ("mysql ");
Var pool = mysql. createPool ({
Host: 'localhost ',
User: 'user ',
Password: 'Password ',
Database: 'database ',
Port: port
});

Var query = function (SQL, callback ){
Pool. getConnection (function (err, conn ){
If (err ){
Callback (err, null, null );
} Else {
Conn. query (SQL, function (qerr, vals, fields ){
// Release the connection
Conn. release ();
// Event-driven callback
Callback (qerr, vals, fields );
});
        }
});
};

Module. exports = query;

3. Use the following in the js class:
Var query = require ("./lib/mysql. js ");

Query ("select 1 from 1", function (err, vals, fields ){
// Do something
});