Using MySQL connection pool with Node.js

This article mainly introduces the method of Node.js using MySQL connection pool, and analyzes the related module installation, connection, query and other usage skills of nodejs operating mysql connection pool based on specific examples. Friends who need it can refer to it

The example in this article describes how Node.js uses the MySQL connection pool. Share it with everyone for your reference, the details are as follows:

How to use MySQL in Nodejs

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

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

npm install musql

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

var mysql = require('mysql');

Next, we can use this module to connect to MySQL database. Of course, to connect to the database, you need to specify the host name, user name and password of 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"
});

Then, the following code will establish a new connection for you.

connection.connect();

Using this connection object, we can query the database as follows. 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 will ensure that all queries in the queue will be executed before the database connection is closed. Note that there is a callback function here.

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

The following expression will immediately close the database connection. And there are no callback functions or any events triggered.

connection.destroy( );

Nodejs uses 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, reuse existing connections, etc.

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 get a connection we need from the created connection pool:

pool.getConnection(function(err, connection){
});

Use the callback function Parameter connection to query the database. Finally, use the

connection.realease() 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

For security reasons, multiple query statements are not allowed to be executed by default. To use the function of multiple query statements, you need to turn on this function when creating a database connection:

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

After this function is turned on, you can use multiple query statements at the same time like 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 don’t want the program to get stuck or wait too long when querying data, generally It is not recommended to use this link for all queries after opening a connection in node and not close it, because you will know why after you try it

Node.js mysql connection pool module

1. Installation node's mysql module

npm -install -g node-mysql

2. Create a class library, let's call it mysql.js, and then 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){
    //释放连接
    conn.release();
    //事件驱动回调
    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
});

The above is what I compiled for everyone. I hope it will be helpful to everyone in the future.

Related articles:

Configuration method without dev-server.js in vue2.0

Use Vue in Vue. set method to dynamically add object attributes

How to dynamically bind attributes of form elements in vue

The above is the detailed content of Using MySQL connection pool with Node.js. For more information, please follow other related articles on the PHP Chinese website!