Connecting nodejs app to Mysql

This article explains how to connect Node.js to Mysql, Mysql is  most popular open source Database, and Node.js is a non-blocking and Asynchronous type of Scripting language, That enables us to create very fast server side applications. So let's have a look how we can Connect NodeJs to Mysql.

To achieve connection between Node.js and Mysql am taking the example of Ajax Auto Search Box using Node.js. Here am using Express for Node.js and  AngularJs for the client side to manage data and to communicate with a server.

Let's start with  PACKAGE.JSON file, You can directly install this application using these steps:

  1. Open CMD and change the directory using cd command.
  2. After that run npm install command to install all  the dependencies.

Or you can install all the dependencies manually.

PACKAGE.JSON:

{
  "name": "Connecting nodejs app to Mysql",
  "description": "connecting nodejs app to Mysql",
  "main": "server.js",
  "scripts": {
    "start": "node server.js"
  },
  "dependencies": {
    "express": "4.13.3",
    "body-parser": "1.14.1",
    "mysql": "2.9.0"
  }
}

We can connect Nodejs to Mysql using three methods listed below:

  1. Using  var con = mysql.createConnection({ ... });
  2. Using  var con = mysql.createClient({ ... });
  3. OR, by creating Pool Connection var con = mysql.createPool({ ... });

You can use any one, Now let’s look into our server.js file.

SERVER.JS:

var bodyParser = require('body-parser');
var mysql = require("mysql");
var http = require('http').Server(app);

app.use(require("express").static('data'));
app.use(bodyParser.urlencoded({
  extended: true
}));
app.use(bodyParser.json());

/* Creating MySQL connection.*/
var con = mysql.createPool({
  host : 'localhost',
  user : 'root',
  password : '',
  database : 'test'
});


/*
2nd way to make connection

//setting up connection object
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"test"
});

//making the connection 
con.connect();

*/

/*
3nd way to make connection

//setting up connection object
var con = mysql.createClient({
  host: "localhost",
  user: "root",
  password: "",
  database:"test"
});

*/

app.get("/",function(req,res){
    res.sendFile(__dirname + '/index.html');
});

app.post('/get_data', function (req, res) {
  var key=req.body.name;
  con.getConnection(function(err,connection){
    if(err){
      connection.release();
    }else{
      var query="select * from user where name like '%"+key+"%'";
      con.query(String(query),function(err,rows){
        connection.release();
        if(!err) {
          if(rows.length > 0){
              res.write(JSON.stringify(rows));
              res.end();
          }else{
              rows=[];
              res.write(JSON.stringify(rows));
              res.end();
          } 
        } else {
          console.log("Query failed");  
        }        
      });
    }
  });  
});

http.listen(8080,function(){
    console.log("Listening on http://127.0.0.1:8080/");
});

 

In our,app.post('/get_data', function (req, res) { ... });.
we have very simple like query which return the result set which is sent to client side Ajax in the form JSON string, where AngularJs will manage server-side result set. Here is our code for HTML code consisting AngularJs.

Now, in our Html we have input text where we can type anything accordingly server will return  result, Now we don't want to send request every time when user types something, We should send request when user finishes typing for that, we can write code something like this,

var TypeTimer;                
var TypingInterval = 1000;

$scope.keyup = function() {
    $timeout.cancel(TypeTimer);
    TypeTimer=$timeout( function(){ get_data(); }, TypingInterval);
};
$scope.keydown = function(){
    $timeout.cancel(TypeTimer);
};

// When user stop the typing get_data() is called
function get_data(){
    var name=$scope.name;
    $http.post('/get_data',{name:name}).success(function(data, status, headers, config) {
       $scope.data_server=data;
    }).error(function(data, status) {
        alert("Connection Error");
    });
}

Complete Index.html code

INDEX.HTML

<html ng-app="sample">
  <head>
    <title>Connecting Node.js app to MySql</title>
    <link rel="stylesheet" href="css/bootstrap.min.css">
    <link rel="stylesheet" href="css/font-awesome.min.css">
    <script src = "js/angular.min.js"></script>
    <script type="text/javascript">
        var app = angular.module('sample',[]);
        app.controller('sample', function ($scope,$http,$timeout) { 
            
            var TypeTimer;                
            var TypingInterval = 1000;

            $scope.keyup = function() {
                $timeout.cancel(TypeTimer);
                TypeTimer=$timeout( function(){ get_data(); }, TypingInterval);
            };

            $scope.keydown = function(){
                $timeout.cancel(TypeTimer);
            };

            function get_data(){
                var name=$scope.name;
                $http.post('/get_data',{name:name}).success(function(data, status, headers, config) {
                   $scope.data_server=data;
                }).error(function(data, status) {
                    alert("Connection Error");
                });
            }            
        });
    </script>
  </head>
  <body ng-controller="sample">
    <div class="container">
      <div id="name-group" class="form-group">
        <label>Type something</label>
        <br/>
        <input type="text" class="form-control"  ng-model="name" ng-keyup="keyup()" ng-keydown="keydown()" ng-blur="blur()"  ng-change="change()" placeholder="Type here and check the Title in Tab"/>
        <br/>
      </div>

      <div class="rows">
        <div class="col-md-12">
          <div class="data" ng-repeat="data in data_server">
            {{ data.name }}
            <hr/>
          </div>
          <div ng-show="data_server.length == 0"  >
            <div class="alert alert-info" >
              <p class="text-center" >
                Sorry, Right now we don't have any results for you.  
              </p >
            </div >
          </div >
        </div>
      </div>
    </div>
  </body>
</html>
  • ARVIND A

    I am fresher and interested in Angularjs and nodejs.This article is very nice
    I tried to run this application in visual studio but i could not run this.
    please give me the steps to run this application.

    • Hello ARVIND, Thanks for stopping by. To run nodejs app first, make sure u have installed NODEJS & Mysql (Xampp, Wamp or Lamp will be fine).

      1. open the directory where u have your nodejs application, copy the path now go to the CMD.

      2.Enter into that directory by writing cd command.

      3. Now write node server.js and your application should run properly.

      hope this helps.

  • Loy houngnakhone

    why I can not download this file? any body help me please.
    I can download about 50% and after that can not continues to download

    • Hello Loy, Sorry for the trouble but that’s very rare, However am sending you the source code.

      • shahid ahmad

        Hi Shashank,
        Can you please send me the source code because its not downloadable.It’ll be apriciated.Thanks. 786sag@gmail.com