Hi everybody, in this blog we will cover MySQL connection with Node.js.
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.
In this tutorial i am going to cover following points related to Node.js and MySQL.
Sample code to get started.
Code for Production.
Pool connection in MySQL
Sample code to get started
Create a file name package.json in your project directory:
package.json
{
"name": "node-mysql",
"version": "0.0.1",
"dependencies": {
"express": "^4.10.6",
"mysql": "^2.5.4"
}
}
Install dependencies using
npm install
Create a file connect.js and write 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 : '<your database name>'
});
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 connect.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 on two different terminal.
Run this command on one terminal
node test.js
On another terminal run this command
siege -c10 -t1M http://localhost:3000
Assuming you are running Node server on Port 3000.
Here is the output.
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.
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.
Thanks for read this blog, if you have your own thoughts please share it with me.
