Nov 1, 2014

Ketan Patel

How to Use Node.js with MySQL



It's very easy to use MySQL with Node.js.The following post is a quick look at the MySQL module for Node. There are currently two modules in node.js for connecting with MySQL : db-mysql and node-mysql. In this post we will be using node-mysql. This post guides you through the process.

I’m assuming you have Node.js installed on your system.If not, then this post explains you How to Install node.js on windows system.


Installation of node-mysql



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

       npm install mysql



In order to use the mysql client in your application javascript file (app.js), add the following code to import the module to your script.

       var mysql = require('mysql');




Creating MySQL Connection



 
Now the next step is to create a MySQL connection in which we'll define the host,username,password and database. To know more about the connection options to read this.

var connection = mysql.createConnection(
    {
      host     : 'localhost',
      user     : 'root',
      password : '123456',
      database : 'nodedemo'
    }
);


the below code will open a new connection.

 connection.connect();



MySQL Query




Below is an example of how to perform query in mysql with node.js. To protect the query string from the sql injection use connection.escape().



var queryString = 'SELECT * FROM users';
connection.query(queryString, function(err, rows, fields) {
    if(err){
       throw err;
    }
   else{
      console.log( rows );
    }
});



you can print all the fields also via loop.


connection.query(queryString, function(err, rows, fields) {
    if (err) throw err;
    for (var i in rows) {
     console.log('Username: ', rows[i].username);
    }
});




Closing connection



In order to close the connection use the below method.

 connection.end();


You can use a callback function also.

connection.end(function(err){
// Actions need to be performed after the connection is terminated.
});


Connection Pooling in MySQL with Node.js




In case of websites with high traffic running on Node.js,it is recommended that instead of creating a new connection for each nodejs instance, you use persistent connections to the database.

By using connection pooling, efficient management of multiple connections can be achieved by limiting them and reusing them with different schedules.

Below is an example of how to create a connection pool.


var mysql = require('mysql');
var pool  = mysql.createPool({
   host     : 'localhost',
   user     : 'root',
   password : '123456'
 });

Now you can get the connection from the created pool as below,

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



In order to query the database, just use the parameter connection in the callback function of getConnection.


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


At last use connection.release() to release the connection and the connection will return to the pool, ready to be used again.


Complete Example:



var mysql = require('mysql');
var connection = mysql.createConnection(
    {
      host     : 'localhost',
      user     : 'root',
      password : '123456',
      database : 'nodedemo'
    }
);



connection.connect();
var queryString = 'SELECT * FROM users';
connection.query(queryString, function(err, rows, fields) {
    if(err){
        throw err;
    }
    else{
       console.log( rows );
    }
});

When running the above code, following output will be displayed.


[{
id:1,
username:'kevin',
email:'kevin@example.com',
password:'123456'
},
{
id:2,
username:'john',
email:'john@example.com',
password:'456789'
}]



That's it.!! This is the basic example of how to use MySQL with Node.js.


You might also like this post : File upload in Node.js Using Formidable
 

Ketan Patel

About Ketan Patel -

I have developed a wide range of websites using CorePHP, Opencart, CakePHP and CodeIgniter including sites for startup companies and small businesses. Apart from my blogging life, I like to read Novels, Listening music and Net surfing.

Subscribe to this Blog via Email :