≡ Menu

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
  }
});
Add your comment

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

Comments on this entry are closed.

  • Bhagyaraj January 30, 2014, 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.

  • vonskippy January 30, 2014, 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.

  • Kristian Reese January 30, 2014, 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.

  • Viktor Vad January 31, 2014, 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!

  • Tarak April 3, 2014, 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.

  • Prabhu April 5, 2014, 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.

  • Ibrahim Apachi May 21, 2014, 1:40 am

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

  • Michael Grant September 17, 2014, 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

  • Ashutosh June 8, 2015, 1:15 am

    Preety good content. liked it , thanks

  • genoki June 28, 2015, 6:11 pm

    Where does mysql db user and password values/config file get saved so they are not viewable by site visitors?