Connecting nodejs app to Mysql

This article explains how to connect Node.js to Mysql. Mysql is most popular and widely used open source Database and Node.js is a non-blocking and Asynchronous, That enables us to create very fast server-side applications.Combining these two would make your web development very useful needless to say. So let’s have a look how we can Connect NodeJs to Mysql.

To demonstrate the connection between Node.js and Mysql am taking the example of Ajax Auto Search Box. Basically, we’ll have a textbox and whatever you will write into it, you will get the response from the server based on the text written inside the textbox.Since those results will be stored in the MySQL database table, SO we will be using some kind of Mysql query to fetch it, we’ll go through down the road. Speaking of down the road, I forgot to mention Here I will be using the AngularJs for the front end.You can use any front-end framework of your choice.

 Download

 Demo

Also, Read Realtime chatting application using Node.js Socket.io AngularJS and Mysql.

1. Understanding the project structure

1. As I said earlier, here we will create an application, which will imitate the functionality of Ajax Auto Search, in other words, autocomplete functionality.Here we will give a very little bit of styling to our web application just to make it look presentable.And as I said, we will just AngularJs to make a server side call in order to fetch the results. So below is our project folder structure.

2.Inside the client folder, we will write down the MARKUP, CSS, and all the client-side Javascript scripts, including CSS and Javascript libraries.

3. Let’s start off by creating a new Nodejs project by usingng initcommand.This command will create a new package.json file. Below is my package.json file for this application.

package.json:

{
    "name": "nodejs-mysql-connection",
    "version": "1.0.0",
    "description": "connecting nodejs app to Mysql database",
    "main": "server.js",
    "scripts": {
        "test": "echo \"Error: no test specified\" && exit 1",
        "start": "node server.js"
    },
    "author": "Shashank Tiwari",
    "license": "MIT",
    "dependencies": {
    "body-parser": "^1.18.2",
    "express": "^4.16.2",
    "mysql": "^2.15.0"
    }
}

2. Creating a NodeJs Server

1.Create a server.js in the root of the project, which will be our entry point for the project.Here we will define our route and we will start the server.

=>TheappConfig()method will set the application configuration.

=>InincludeRoutes()method we will execute the application route.

=>TheappExecute()method will call theappConfig()as well asappExecute()and it will run the nodejs server.

server.js:

/* 
* @author Shashank Tiwari
* Connect NodeJs to Mysql
*/'use strict';

const express = require("express");
const http = require('http');
const bodyParser = require('body-parser');
const helper = require('./helper');

class Server {

    constructor() {
        this.port = process.env.PORT || 4000;
        this.host = `localhost`;

        this.app = express();
        this.http = http.Server(this.app);
    }

    appConfig() {
        this.app.use(
            bodyParser.json()
        );
        this.app.use(require("express").static('client'));
    }

    /* Including app Routes starts*/    includeRoutes(app) {
        app.get("/", function (req, res) {
            res.sendFile(__dirname + '/client/index.html');
        });
        
        app.post("/getSuggestion", function (request, response) {
            //Storing the user entered string into variable
            let suggestionString = request.body.suggestion;

            helper.getSuggestion(suggestionString,(result)=>{
                if (result.error) {
                    response.status(100).json({ "error": true,"message": "Error in connection database" });
                }else if(result.rows.length === 0){
                    response.status(404).json({ "error": true,"message": "No result Found" });
                }else{
                    response.status(200).json(result);
                }
            });
        });
    }
    /* Including app Routes ends*/
    appExecute() {

        this.appConfig();
        this.includeRoutes(this.app);

        this.http.listen(this.port, this.host, () => {
            console.log(`Listening on http://${this.host}:${this.port}`);
        });
    }

}

const app = new Server();
app.appExecute();

3. Connect NodeJs to Mysql

1. Now we have written the code to start the Nodejs server, let’s write the code to connect Nodejs to MySql. Create helper.js file inside the root of the project folder.In this file, we will write nodejs Connectivity code and we will write a method to execute a query to fetch results from the database.

=>Theconstructor()method will connect the Nodejs to mysql.

=>IngetSuggestion()method , we have written the MySql LIKE query to fetch the result from Table.

helper.js:

/* 
* @author Shashank Tiwari
* Connect NodeJs to Mysql
*/'use strict';
const mysql = require('mysql');

class Helper {

    constructor() {

        this.pool = mysql.createPool({
            connectionLimit: 100,
            host: 'localhost',
            user: 'root',
            password: '',
            database: 'test',
            debug: false
        });
    }

    getSuggestion(key,callback){
        this.pool.getConnection( (err, connection) => {
            if (err) {
                callback({ "error": true });
            }

            connection.query("SELECT title FROM articles WHERE title LIKE '%" + key +"%'", (err, rows) => {
                if (!err) {
                    callback({"error":false,"rows":rows});
                }
            });

            connection.on('error',  (err) => {
                callback({ "error": true });
            });
        });
    }

}

module.exports = new Helper();

4. Creating the FrontEnd

1. Now that we have completed all the server side work let’s start the development of front-end work. Create an index.html inside the /client folder and write done the below markup.

=> In the below markup we will render the results using ng-repeat.

index.html:

<html ng-app="app"  ng-controller="app">
    <head>
        <title>Connecting nodejs app to Mysql</title>
        <link rel="stylesheet" href="css/bootstrap.min.css">
        <link rel="stylesheet" href="css/style.css">
    </head>
    <body>
        <div class="container">
            <div class="app-heading">
                <h2>
                    Connecting nodejs app to Mysql
                </h2>
            </div>
        </div>
        <div class="container">
            <div class="app">
                <input type="text" class="form-control " ng-model="suggestion" placeholder="Type something here....">
                <div ng-show="suggestionCollection.length > 0" class="container suggestion-container">
                    <div class="suggestion-collection">
                        <div ng-repeat="suggestion in suggestionCollection">
                            {{suggestion.title}}
                            <hr/>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </body> 
    <script src="js/angular.min.js"></script>
    <script src="js/script.js"></script>
</html>

2.Now let’s write the write the AngularJs code in order fetch the result from the server by making an HTTP call. So create a script.js inside the /js folder under the client folder.

=> First, we will create service which will make an HTTP call and returns the HTTP response back to the caller.

=> Here we will use $watch() function, in order to make a call toappService, Once the response received from the server we will render the response using ng-repeat as I mentioned earlier.

script.js:

/* 
* @author Shashank Tiwari
*  Connect NodeJs to Mysql
*/const app = angular.module('app',[]);

app.service('appService', ['$http', function($http){
    return {
        'getSuggestion' : function(suggestion,callback){
            $http.post('/getSuggestion', {
                'suggestion': suggestion
            }).success(function (response) {
                callback(response);
            })
            .error(function (data, status, header, config) {
                callback({
                    'error': true,
                    'message': "Something went wrong."
                });
            });
        }
    }
}]);

app.controller('app', function ($scope,appService) {
    
    $scope.suggestion = null;
    $scope.suggestionCollection = [];

    $scope.$watch('suggestion', function (newValue, oldValue) {
        if (newValue !== null) {
            if (newValue.length > 3) {
                appService.getSuggestion(newValue, function (response) {
                    $scope.suggestionCollection = response.rows;
                });
            }else{
                $scope.suggestionCollection = [];
            }
        }else{
            $scope.suggestionCollection = [];
        }
    });

});

So this was a small demonstration of Nodejs and Mysql database connectivity. Please be sure to comment down below if you have any question or suggestion in order to add something in this tutorial, I’ll happy to know.

Till then Happy coding