Node.js and MySQL tutorial RealMoney

Posted on

Node.js and MySQL is one of the necessary binding needed for any web application. MySQL is one of the most popular open source database in world and efficient as well. Almost every popular programming language like Java or PHP provides driver to access and perform operations with MySQL.
In this tutorial i am trying to cover code for learning and code for production. So if you know this already and looking for ready made code for production.
Introduction:
Node.js is rich with number of popular packages registered at package registry called NPM. Most of them are not so reliable to use for production but there are some on which we can rely upon. For MySQL there is one popular driver callednode-mysql.
In this tutorial i am going to cover following points related to Node.js and MySQL.

Sample code to get started.
Code for Production.
Testing concurrent users.

Sample code to get started.
Project directory:
—node_modules
—–+ mysql
—–+ express
—index.js
—package.json
package.json
{
“name”: “node-mysql”,
“version”: “0.0.1”,
“dependencies”: {
“express”: “^4.10.6”,
“mysql”: “^2.5.4″
}
}
Install dependencies using

npm install
Here is sample code which connects to Database and perform SQL query.
var mysql = require(‘mysql’);
var connection = mysql.createConnection({
host : ‘localhost’,
user : ‘< MySQL username >’,
password : ‘< MySQL password >’,
database : ”
});

connection.connect();

connection.query(‘SELECT * from < table name >’, function(err, rows, fields) {
if (!err)
console.log(‘The solution is: ‘, rows);
else
console.log(‘Error while performing Query.’);
});

connection.end();
Make sure you have started MySQL on default port and changed the parameter in above code then run this code using
node file_name.js
Code for production :
Above code is just for learning purpose and not for production payload. In production scenario is different, there may be thousands of concurrent users which turns into tons of MySQL queries. Above code won’t run for concurrent users and here is a proof. Let’s modify our code little bit and add Express routes in that, here it is.
test.js ( Change database settings in code )
var express = require(“express”);
var mysql = require(‘mysql’);
var connection = mysql.createConnection({
host : ‘localhost’,
user : ‘root’,
password : ”,
database : ‘address_book’
});
var app = express();

connection.connect(function(err){
if(!err) {
console.log(“Database is connected … \n\n”);
} else {
console.log(“Error connecting database … \n\n”);
}
});

app.get(“/”,function(req,res){
connection.query(‘SELECT * from user LIMIT 2’, function(err, rows, fields) {
connection.end();
if (!err)
console.log(‘The solution is: ‘, rows);
else
console.log(‘Error while performing Query.’);
});
});

app.listen(3000);
Install siege in your system. I use this command to install it in Ubuntu.
apt-get install siege
then run our node and server and following command.
node test.js
siege -c10 -t1M http://localhost:3000
Assuming you are running Node server on Port 3000.
Here is the output.
Node and mysql
In above code, we are allowing it to run for standalone connection i.e one connection at a time but reality is bit different. You may get 100 or 1000 connection at one particular time and if your server is not powerful enough to serve those request then at least it should put them in queue.
Pool connection in MySQL :
Connection Pooling is mechanism to maintain cache of database connection so that connection can be reused after releasing it. In Node mysql, we can use pooling directly to handle multiple connection and reuse the connection. Let’s write same code with pooling and check whether it can handle multiple connection or not.
test.js
var express = require(“express”);
var mysql = require(‘mysql’);
var app = express();

var pool = mysql.createPool({
connectionLimit : 100, //important
host : ‘localhost’,
user : ‘root’,
password : ”,
database : ‘address_book’,
debug : false
});

function handle_database(req,res) {

pool.getConnection(function(err,connection){
if (err) {
connection.release();
res.json({“code” : 100, “status” : “Error in connection database”});
return;
}

console.log(‘connected as id ‘ + connection.threadId);

connection.query(“select * from user”,function(err,rows){
connection.release();
if(!err) {
res.json(rows);
}
});

connection.on(‘error’, function(err) {
res.json({“code” : 100, “status” : “Error in connection database”});
return;
});
});
}

app.get(“/”,function(req,res){-
handle_database(req,res);
});

app.listen(3000);
Run the app using
node test.js
and fire 10 concurrent users for 1 minute using siege by using this command.
siege -c10 -t1M http://localhost:3000
Here is output.
Code is stable !
Final comments :
Siege is really powerful tool for testing server under pressure. We have created 100 connection limit in code, so you might be wondering that after 100 concurrent connection code will break. Well let me answer it via code. Fire 1000 concurrent user for 1 minute and let’s see how our code reacts.
siege -c1000 -t1M http://localhost:3000
If your MySQL server is configured to handle such traffic at one socket then it will run and our code will manage the scheduling of concurrent connection. It will serve 100 connection a time but rest 900 will be in queue. So code will not break.
Conculusion :
MySQL is one of widely used database engine in world and with Node it really works very well. Node-mysql pooling and event based debugging is really powerful and easy to code.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s