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

by Luke P. Issac on January 29, 2014

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

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 8 comments… read them below or add one }

1 Bhagyaraj January 30, 2014 at 8:29 am

Nice article, some aberrations.
Basically, Thegeekstuff explains all basic things before starting.
You would have put some basics of node.js (java script – i guess),
format of js,
some note on syntax of js.
But definately nice article.

2 vonskippy January 30, 2014 at 1:27 pm

Pretty skimpy – you skip the MOST important parts – which is security.

Putting your DB password in cleartext without talking about how you secure that file is pretty careless.

The world doesn’t need yet another system to join the botnet army of compromised systems.

3 Kristian Reese January 30, 2014 at 11:44 pm

Give the guy some credit. The purpose is to illustrate how the use of Node.js could be more suitable than php in certain use cases. In addition, he’s also showing alternatives to using php. Security goes without saying and is obviously beyond the scope of the purposes of the article and intentions of the author.

4 Viktor Vad January 31, 2014 at 5:19 am

Basically he is not here to teach you the basics in this article as a short article is not enough and it is not his intent. But here he is showing you how to use connection pools. Everything else is really adhoc and not for starter programmers anyway.
Nice article, thanks for your efforts!

5 Tarak April 3, 2014 at 5:14 am

Hi ,
I am getting this error “TypeError: Object # has no method ‘release’”
while executing connection pool example using the statement connection.release() but not using the statement connection.end() why? please help me.

6 Prabhu April 5, 2014 at 1:20 pm

hi,

i need mysql connection with node.js express.. could you help me in this topic.
in this one i want to execute database table rows in browser but not in console(console.log(rows)).
thanks in advance.

7 Ibrahim Apachi May 21, 2014 at 1:40 am

Pretty nice tutorial ! I really like how you just put emphasize on the basic things :)

8 Michael Grant September 17, 2014 at 8:56 am

In the connection pool example, you call connection.release() right after connection.query(). Shouldn’t the call to connection.release() be inside connection.query() (at the end when you are finished with messing with the query results, but inside this function)?

Unless I misunderstand something, if you don’t do this, you may end up throwing away your connection before the anonymous query function is called.

Or, said another way, if you leave the connection.release() where it is, above it, you need to provide some guard condition to know whether you’ve finished doing all the queries. Am I correct? Or is connection.release() smart enough not to do this?

By the way, the documentation seems to agree with my thinking https://github.com/felixge/node-mysql

Leave a Comment

Previous post:

Next post: