Linux: NodeJS MySql Connectivity : With Sample Application

Monday, September 22, 2014

NodeJS MySql Connectivity : With Sample Application

Hi,

Here, i will share with you on NodeJS sample application with MySql Database connectivity.

I am assuming that you already installed the NodeJS, NPM and Socket.io on your machine.
If not, you can follow below link to install the above requirements
http://tarunlinux.blogspot.in/2014/09/nodejs-basics.html

Here, I will show you in step-wise :

Step 1: Install the mysql module... here also i am assuming that mysql is already installed in your machine.
Now install mysql module for the NodeJs dependency.
# sudo npm install mysql

That will install the MySql dependency in your machine.

Step 2: Now Create the server.js file for the nodejs:

var mysql = require('mysql'); // load mysql module
var client = require('socket.io').listen(8080).sockets;  //Load socket.io module

//Database connectivity
var db = mysql.createConnection({
    host: 'localhost'
    , user: 'root'
    , password: 'root'
    , database: 'zfskel'
    , port: '3306'    
});

db.connect(function(err){
    console.log(err);
});

//Create connection with the client and emit data on socket
client.on('connection', function(socket){
    db.query('SELECT * FROM employee')
      .on('result', function(data){

        socket.emit('all-data', [data]);
    });
});

Step 3: Create Index.html file for the front-end:

<html>
    <head>
        <title> NodeJs Mysql APP </title>
        <link rel="stylesheet" href="">
    </head>
    <body>
        <div class="data-set"></div>
    <script src="http://localhost:8080/socket.io/socket.io.js" type="text/javascript"></script>
    <script src="http://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
    <script>
    (function(){
        try{
            var socket = io.connect('http://localhost:8080');
        } catch(e){
            console.log(e);
        }
        
        if(socket !== undefined) {
            socket.on('all-data', function(data){
                for(var i=0; i < data.length; i++) {
                    $(".data-set").append('<li>'+ data[i].name +'</li>');
                }
            });
        }
    })();
    </script>
    </body>
</html>

Now your have 2 files i.e. server.js and index.html file.

Step 4: Now you need the sql file for the database connectivity:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` VALUES (1,'tarun',26),(2,'varun',34);

Step 5: Now you have to run only server.js file form command line:
# nodejs server.js

Then open your browser and run the below url:
http://localhost:8080/

Here, you will see the MySql data on your browser...

I hope it will help you to understand NodeJs and its connectivity with the MySql connectivity.

Bye.. :)

2 comments :

IT said...: Useful & Great Article for NodeJS Development

Node.js Training; December 27, 2015 at 11:27 PM
Unknown said...: Im no expert, but I believe you just made an excellent You certainly understand what youre speaking about, and I can truly get behind that.
Regards,
Node JS training|Node JS training in chennai; March 4, 2016 at 4:32 AM