How to create simple api in node js with mysql

In this post you will learn how to create simple api in node js with mysql.

In todays world where almost every system is connected to some other because of resource sharing or some data sharing. Every company wants its users to use its multiple apps where data can be managed from centre point.

Ever imagined how it all works and comes into existence. For programmers who create apps in different technologies and they connect multiple apps very easily. How is it possible. The answer is API

According to Wikipedia, An application programming interface is a computing interface that defines interactions between multiple software intermediaries. It defines the kinds of calls or requests that can be made, how to make them, the data formats that should be used, the conventions to follow, etc.

So enough of theory about API. Now let's see how to create simple api in node js with mysql:

First create a directory named sample_api in any directory or even in any local disk.
Then open up a terminal/command prompt and change directory to sample_api and then initialize by typing npm init -y
Now its time to install some packages to create API. We will install express, mysql,body-parser packages. Enter command npm i express body-parser mysql --save

Now create a main file named main.js and type in the following script:

var express = require('express');
var app = express();
var route = require('./routes/routes');
var bodyParser = require('body-parser');


app.use(bodyParser.urlencoded({extended:true}));
app.use(bodyParser.json());

app.set('view engine','ejs');

route(app);

app.listen(3000,(err)=>{
    if(err){
        console.log("error");
    }
    else{
        console.log("app running at 3000");
    }
});

Now create a directory named routes and in that directory create a file named routes.js and type in the following script:

var usercontroller = require('../controllers/UserController');

var routes = function(app){
    app.post('/user_sign_in',(req,res)=>{
        var email = req.body.email;
        var password = req.body.password;
        if(email == ''||email == null){
            res.end(JSON.stringify({status:false,msg:"enter email"}));
        }
        else if(password == ''||password == null){
            res.end(JSON.stringify({status:false,msg:"enter password"}));
        }
        else{
            var qry = "select * from users where email='"+email+"' and password='"+password+"'";
            
            usercontroller.user_sign_in(qry,function(cb){
                res.send(cb).end();
            });
        }
    });

    app.post('/user_sign_up',(req,res)=>{
        var name = req.body.name;
        var username = req.body.username;
        var email = req.body.email;
        var password = req.body.password;

        if(name == ''||name == null){
            res.end(JSON.stringify({status:false,msg:"enter name"}));
        }
        else if(username == ''||username == null){
            res.end(JSON.stringify({status:false,msg:"enter username"}));
        }
        else if(email == ''||email == null){
            res.end(JSON.stringify({status:false,msg:"enter email"}));
        }
        else if(password == ''||password == null){
            res.end(JSON.stringify({status:false,msg:"enter password"}));
        }
        else{
            var qry = "insert into users values(null,'"+name+"','"+username+"','"+email+"','"+password+"',1)";
            usercontroller.insert_user(qry,function(cb){
                res.send(cb).end();
            });
        }
    });

    app.get('/get_all_users',(req,res)=>{
        usercontroller.get_all_users(function(cb){
            res.send(cb).end();
        });
    });
}
module.exports = routes;

Now create a directory named controllers and create two files namely DatabaseController.js and UserController.js and open DatabaseController.js file and type in the following script:

var mysql = require('mysql');

var con = mysql.createConnection({
    host:"localhost",
    user:"user",
    password:"user",
    database:"node_sample_api_base"
});
con.connect(function(err){
    if(err){
        console.log(err);
    }
    else{
        console.log("connected");
    }
});
module.exports = con;

Open UserController.js file and type in the following script:

var con = require('./DatabaseController');


var get_all_users = function(callback){
    con.query("select * from users",function(err,res,fields){
        if(err){
            callback(JSON.stringify(
                {
                    status:false,
                    msg:err
                }
            ));
        }
        else{
            callback(JSON.stringify(
                {
                    status:true,
                    msg:res
                }
            ));
        }
    });
}

var insert_user = function(qry,callback){
        con.query(qry,function(err,res,fields){
            if(err){
                callback(JSON.stringify(err));
            }
            else{
                callback(JSON.stringify(
                    {
                        status:true,
                        msg:"successfully user signed up!"
                    }
                ));
            }
        });
}

var user_sign_in = function(qry,callback){
    con.query(qry,function(err,res,fields){
        if(err){
            callback(JSON.stringify(err));
        }
        else{
            if(res.length > 0){
                callback(JSON.stringify(
                    {
                        status:true,
                        msg:"successfully user signed in!",
                        extra_data:res
                    }
                ));
            }
            else{
                callback(JSON.stringify(
                    {
                        status:false,
                        msg:"profile not found!"
                    }
                ));
            }
        }
    });
}


module.exports = {
    get_all_users,
    insert_user,
    user_sign_in
};

All the scripting is done.
Now lets discuss what is happening, a request comes in to main.js file it transfers the request to routes.js and routes.js matches the route. For eg in this app a route is user_sign_up, so if this route is encountered the request is transferred to UserController.js where all the tasks are done according to function called.
Lets explore the output now.
User sign up
User sign in
Get all users

That is it for this article. We will continue bringing interesting articles related to programming. If you face any problem while implementing this, please comment you error, we will help you in solving your error.