How to Use Node.js with MySQL using Example for Connection Pool

In certain situations it’s a good option to use Nodejs with MySQL instead of PHP or any other server-side language.

By using Nodejs you can get the advantage of its asynchronous behaviour, which in certain case may increase the performance, and you may not need to migrate an existing MySQL database to some other NoSQL database to gain additional performance.

How to Use MySQL with Node.js

For using MySQL with Nodejs, we can use the nodejs driver for MySQL. For our examples, we’ll use the “node-mysql” driver to connect to database as follows:

First, we need to install mysql driver with the help of node package manager (npsm). Use the following command on node shell or terminal.

npm install mysql

Now, for using mysql client in your javascript application file, add the following code which basically imports the module to your script.

  var mysql =  require('mysql');

Next, we can use this module to create MySQL connection, where we have to specify our mysql server host name, user name and password. There are many other options which we can set while creating the connection like database, timezone, socketPath, localAddress and stringifyObjects.

  var connection =  mysql.createConnection({
  	host : “hostName”,
  	user : “username”,
  	password: “password”
  });

Next, the following line of code will open a new connection for you.

  connection.connect();

Using this connection object we can query the database as follows. We can use connection.escape( ) to protect the query strings from 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 now end connection in two ways. Either use connection.end, or connection.destroy.

The following statement will close the connection ensuring that all the queries in the queue are processed. Please note that this is having a callback function.

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

});

The following statement will terminate the assigned socket and close the connection immediately. Also there is no more callbacks or events triggered for the connection.

  connection.destroy( );

Implementing Connection Pool in MySQL Node.js

By connection pooling we can do efficient management of multiple connections by limiting them and reusing them with different schedules.

First, we need to create a connection pool. It can be done as scripted below. Pools accept all options as of connection.

  var mysql =  require('mysql');                  
  var pool =  mysql.createPool({
	host : “hostName”,
	user : “username”,
	password: “password”
  });	

Next, we can get a connection from the created pool when needed as follows:

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

Use the connection parameter in the callback function of getConnection to query the database as follows. At the end, to release the connection use “connection.release();”, as mentioned in the last line of the following code snippet.

pool.getConnection(function(err, connection){
  connection.query( “select * from table1”,  function(err, rows){
  	if(err)	{
  		throw err;
  	}else{
  		console.log( rows );
  	}
  });
  
  connection.release();
});

Executing Multiple Statement Queries in MySQL Node.js

For security purpose, by default, executing multiple statement queries is disabled. To use multiple statement queries, you should first enable it while creating a connection as shown below.

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

Once it is enabled, you can execute multiple statement queries as shown below in your connection.query.

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
  }
});