Now you can provide your feedback to our articles!

How to create simple api in node js with mysql

Last updated : Dec 12, 2021

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.



Setup react and express on sam...

Read more

How neurons in brain function

Read more

How to create custom router in...

Read more

Run C++ as CGI script in web b...

Read more

Real time chat application usi...

Read more

How to setup lemp stack in ubu...

Read more

How to secure phpmyadmin in Ub...

Read more

How to create real time notifi...

Read more

Task scheduling with cron job ...

Read more

How to use modules in Javascri...

Read more

Create a multi language(lingua...

Read more

Create food ordering app - Foo...

Read more

Linear Regression in machine l...

Read more

Create simple web application ...

Read more

Why algorithms can drive the i...

Read more

Why Javascript frameworks are ...

Read more

Create authenticated API in La...

Read more

Create simple and static websi...

Read more

Setup Pusher app

Read more

Laravel Installation and setti...

Read more

Realtime chat application in L...

Read more

Working of neurons (the easy w...

Read more

Progressive web app

Read more

Sign in for comment. Sign in