restful api using node express mysql

RESTful API Using Node Express and Mysql

In this tutorial, we’ll learn how to create basic REST API’s using Node.js and Express which retrieve data from MySQL database and give a response in JSON format.

This tutorial is for the beginners and intermediate users of Node.js.

Introduction :

What is REST ? As per WikiPedia it is

Representational State Transfer (REST) is a software architecture style consisting of guidelines and best practices for creating scalable web services.REST is a coordinated set of constraints applied to the design of components in a distributed hypermedia system that can lead to a more performant and maintainable architecture.

Practically speaking if web services supports Uniform behavior, are stateless, allows caching, and provides high level of abstraction of data then it is more likely called as REST api’s.

Database design :

REST,Node.js,database
user_login will store login information for particular user and each user gets an unique ID. user_info will store more information about user and will have foreign key constraint to user_login table and similarly for user_status table.

DOWNLOAD CODE

Create database name as “restful_api_demo” in MySQL via phpmyadmin and go to SQL tab and copy / paste following code to create above tables.

SQL code
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `restful_api_demo` DEFAULT CHARACTER SET latin1 ;
USE `restful_api_demo` ;

-- -----------------------------------------------------
-- Table `restful_api_demo`.`user_login`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `restful_api_demo`.`user_login` ;

CREATE TABLE IF NOT EXISTS `restful_api_demo`.`user_login` (
  `user_id` INT(70) NOT NULL AUTO_INCREMENT,
  `user_email` VARCHAR(45) NOT NULL,
  `user_password` VARCHAR(45) NULL,
  `user_join_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `user_email_UNIQUE` (`user_email` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `restful_api_demo`.`user_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `restful_api_demo`.`user_info` ;

CREATE TABLE IF NOT EXISTS `restful_api_demo`.`user_info` (
  `user_info_id` INT(70) NOT NULL AUTO_INCREMENT,
  `user_id_fk` INT(70) NOT NULL,
  `user_name` VARCHAR(45) NULL,
  `user_location` VARCHAR(45) NULL,
  PRIMARY KEY (`user_info_id`),
  UNIQUE INDEX `user_id_fk_UNIQUE` (`user_id_fk` ASC),
  CONSTRAINT `user_info_foreign_key`
    FOREIGN KEY (`user_id_fk`)
    REFERENCES `restful_api_demo`.`user_login` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `restful_api_demo`.`user_status`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `restful_api_demo`.`user_status` ;

CREATE TABLE IF NOT EXISTS `restful_api_demo`.`user_status` (
  `user_status_id` INT(70) NOT NULL AUTO_INCREMENT,
  `user_id_fk` INT(70) NOT NULL,
  `status_text` TEXT NULL DEFAULT NULL,
  `status_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_status_id`),
  UNIQUE INDEX `user_id_fk_UNIQUE` (`user_id_fk` ASC),
  CONSTRAINT `user_status_foreign_key`
    FOREIGN KEY (`user_id_fk`)
    REFERENCES `restful_api_demo`.`user_login` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

You can use this code and run it in PHPMyadmin to create databases and tables.

Our project :

Before moving to code , let me explain you directory structure.

Nodejs rest api project structure

First of all, let’s install the dependencies we need. Execute the following command in the terminal.

npm install --S express mysql bcrypt body-parser

Once installed, we can go ahead with the code.

Let’s say “Hello World !”

Let’s build one simple API which will return “Hello World” every time we do hit it. Here is the app.js file.

app.js
const express = require("express");
const bodyParser  = require("body-parser");
const rest = require("./routes.js/index.js");
const config = require('./config');
const app  = express();

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
// include the routes
app.use('/api', rest);

app.listen(process.env.PORT || config.port,() => {
    console.log("All right ! I am alive at Port 3000.");
});

Here is routes.js.

REST.js
const express = require('express');
const router = express.Router();
const mysql = require("mysql");
const bcrypt = require('bcrypt');
const config = require('./config');

// connect to mysql
var pool = mysql.createPool(config.mysql);

// all the routes
router.get("/",function(req,res){
    res.json({"Message" : "Hello World !"});
});

module.exports = router;

Here is our config.js file.

config.js
module.exports = {
    "mysql": {
        connectionLimit : 100,
        host     : 'localhost',
        user     : 'root',
        password : '',
        database : 'restful_api_demo',
        debug    :  false
    },
    "port": 3000
}

Save both of the file. Make sure MySQL is running and your system have npm and node installed. Go to project directory and type

npm start

to execute the project. You should see something like this on console.
REST API
Now as we have our server running on port 3000, we can test our api. You can use any REST simulator but recommended for you is POSTMAN. Download it fromhere.

Here is how to hit the API using Postman.

Postman

Now we have our “Hello World” api ready, let’s develop some real stuff !

Building API’s :

Here is list of api’s we are going to build. Changes will be done in REST.js file only.

Api Type Description
/users POST Takes email and password as input data and add new user.
/users GET Returns every users from database
/users/:userId GET Returns users from database with match of userId.
/users/:email/ PUT Update password of user by passing email ID.
/users/:email DELETE Delete user from database.

#1: POST /users

This api will take email and password and inserts in the database. Here is the code

routes.js
const express = require('express');
const router = express.Router();
const mysql = require("mysql");
const bcrypt = require('bcrypt');
const config = require('./config');

// connect to mysql
var pool = mysql.createPool(config.mysql);

// all the routes
router.get("/",function(req,res){
    res.json({"Message" : "Hello World !"});
});

router.post("/users",function(req,res){
    var query = "INSERT INTO ??(??,??) VALUES (?,?)";
    var table = ["user_login","user_email","user_password",req.body.email,bcrypt.hashSync(req.body.password, 10)];
    query = mysql.format(query,table);
    pool.query(query, (err,rows) => {
        if(err) {
            return res.json({"Error" : true, "Message" : "Error executing MySQL query"});
        }
        res.json({"Error" : false, "Message" : "User Added !"});
    });
});

// rest of the routes in next section

module.exports = router;

Here is the output of above code.
post - user
Have a look at database where new user is added.
post user database

#2 : GET /users

This api returns all users from database. Here is the code.

routes.js
const express = require('express');
const router = express.Router();
const mysql = require("mysql");
const bcrypt = require('bcrypt');
const config = require('./config');

// connect to mysql
var pool = mysql.createPool(config.mysql);

// all the routes
router.get("/",function(req,res){
    res.json({"Message" : "Hello World !"});
});

router.get("/users",function(req,res){
    var query = "SELECT * FROM ??";
    let table = ["user_login"];
    query = mysql.format(query,table);
    pool.query(query, (err,rows) => {
        if(err) {
            return res.json({"Error" : true, "Message" : "Error executing MySQL query"});
        }
        res.json({"Error" : false, "Message" : "Success", "Users" : rows});
    });
});

router.get("/users/:user_id",function(req,res){
    var query = "SELECT * FROM ?? WHERE ??=?";
    var table = ["user_login","user_id",req.params.user_id];
    query = mysql.format(query,table);
    pool.query(query, (err,rows) => {
        if(err) {
            return res.json({"Error" : true, "Message" : "Error executing MySQL query"});
        }
        res.json({"Error" : false, "Message" : "Success", "Users" : rows});
    });
});

router.post("/users",function(req,res){
  // code shown above, saving space here
});

router.put("/users",function(req,res){
  // saving some space, scroll down for more
});

router.delete("/users/:email",function(req,res){
 // saving some space, scroll down for more
});

module.exports = router;

Here is the output of above api’s.
Case 1: Get all users.
get user
Case 2: Get user by ID.
get user 6

#3 : PUT /users

This API uses PUT HTTP verb and it will update the password of user by passing its email ID. Here is the code.

routes.js
const express = require('express');
const router = express.Router();
const mysql = require("mysql");
const bcrypt = require('bcrypt');
const config = require('./config');

// connect to mysql
var pool = mysql.createPool(config.mysql);

// all the routes
router.get("/",function(req,res){
    res.json({"Message" : "Hello World !"});
});

router.get("/users",function(req,res){
  // code shown above, saving space here
});

router.get("/users/:user_id",function(req,res){
  // code shown above, saving space here
});

router.post("/users",function(req,res){
  // code shown above, saving space here
});

router.put("/users",function(req,res){
    var query = "UPDATE ?? SET ?? = ? WHERE ?? = ?";
    var table = ["user_login","user_password",bcrypt.hashSync(req.body.password, 10),"user_email",req.body.email];
    query = mysql.format(query,table);
    pool.query(query, (err,result) => {
        if(err) {
            return res.json({"Error" : true, "Message" : "Error executing MySQL query"});
        }
        res.json({"Error" : false, "Message" : "Updated the password for email "+req.body.email});
    });
});

router.delete("/users/:email",function(req,res){
 // saving some space, scroll down for more
});

module.exports = router;

Here is the output of above API.
users put

#4 : DELETE /users/:email

This API will delete user from database by taking email ID as input. Here is the code.

REST.js
const express = require('express');
const router = express.Router();
const mysql = require("mysql");
const bcrypt = require('bcrypt');
const config = require('./config');

// connect to mysql
var pool = mysql.createPool(config.mysql);

// all the routes
router.get("/",function(req,res){
    res.json({"Message" : "Hello World !"});
});

router.get("/users",function(req,res){
  // code shown above, saving space here
});

router.get("/users/:user_id",function(req,res){
  // code shown above, saving space here
});

router.post("/users",function(req,res){
  // code shown above, saving space here
});

router.put("/users",function(req,res){
  // saving some space, scroll down for more
});

router.delete("/users/:email",function(req,res){
    var query = "DELETE from ?? WHERE ??=?";
    var table = ["user_login","user_email",req.params.email];
    query = mysql.format(query,table);
    pool.query(query, (err,rows) => {
        if(err) {
            return res.json({"Error" : true, "Message" : "Error executing MySQL query"});
        }
        res.json({"Error" : false, "Message" : "Deleted the user with email "+req.params.email});
    });
});

module.exports = router;

Here is the output of above code.
users delete
You can develop more api’s depending upon your requirement and data model.

Conclusion:

REST api’s are very useful for any web app, mobile app, system software etc. You can develop REST api and use it anywhere because of its resource sharing feature.

With the above explanation, I hope you understand the basics of REST API’s and also how to develop one for your system.

Further Study:

Node.js MySQL Tutorial
Build a RESTful API using Node and MongoDB
HTML5 Push Notification System Using Nodejs MySQL Socket.io
Voice Controlled Drone Built Using Node and ARDrone

Shahid

Founder of Codeforgeek. Technologist. Published Author. Engineer. Content Creator. Teaching Everything I learn!

Related Posts

135 Comments

    1. Hi Piyas,
      We call it RESTful because it adheres the REST ( Representational state transfer ) principles which i have mentioned in second section. If web service do not fulfill it then its RESTless, so basically any web service which do not follows REST principles it is RESTless. Hope it clarify.

      Thanks,
      Shahid.

      Reply

  2. You should put a disclaimer on this article telling readers that they should never, under normal circumstances, store passwords in clear text. Please update the article with more modern practices ensuring that passwords are hashed. Thanks!

    Reply

  3. Those examples are not working, I’m geting error:
    TypeError: Cannot read property ‘handle’ of undefined.

    Seems that express.Router() return is not valid.

    Reply

    1. Please download the code and run the npm install and make sure all packages correctly installed. Still dint work just shoot me an email. Code is perfectly working.
      Thanks.

      Reply

      1. There are a lot of tutorials of the MEAN stack online. This was very useful to learn how to substitute MongoDB for MySQL even if it has vulnerabilities. Thanks a bunch! 🙂

        Reply

  5. Great tutorial ！I have a suggestion that why not have some explanations for your code. Not all of reader can fully understand your code. : )

    Reply

  7. Great stuff Shahid,

    Maybe include and show use of MySql views or routines instead of inline mysql queries?

    Thank you very much!!

    Reply

      1. i got some error at #1: POST /users
        after i post some values

        ReferenceError: mysql is not defined
        at Object.handle (C:Usersrazinah radziDesktopooREST.js:13:17)
        at next_layer (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterroute.js:103:13)
        at Route.dispatch (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterroute.js:107:5)
        at C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterindex.js:195:24
        at Function.proto.process_params (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterindex.js:251:12)
        at next (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterindex.js:189:19)
        at next (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterindex.js:166:38)
        at Function.proto.handle (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterindex.js:234:5)
        at Layer.router (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterindex.js:23:12)
        at trim_prefix (C:Usersrazinah radziDesktopoonode_modulesexpresslibrouterindex.js:226:17)

        Reply

  11. Thanks brother for this tutorial,

    I am new in javascript, do we have to use prototype ? or there is another easy way ?

    is there any angularjs implementation as a front end code for this tutorial ?
    Thanks, your help is appreciated.

    Reply

  12. Pretty good tutorial, would have been a great tutorial if you explained
    each step so beginners could understand exactly what you are doing in the code, but it’s pretty simple to figure out anyways.

    Thanks

    Reply

    1. Hey Tony,

      I thought to do this but then length becomes issue, no one likes and reads 3000 word post. I wanna keep it short and simple and damn useful.

      But i will consider your suggestion.

      Thanks.

      Reply

  14. after about a min the api breaks down and i get this error code: “PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR”
    I restart it and it works for another min, then it breaks down again. Any help would be appreciated.

    Reply

    1. Hi Khan,

      Please find code attached.

      connection.on(‘error’, function(err) {
      if(err.code === “PROTOCOL_CONNECTION_LOST”) {
      connection.destroy();
      }
      });

      Reply

      1. Sorry, but I have the same problem and I cant fix it

        ‘{“code”:”PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR”,”fatal”:false}’

        Reply

  15. Nice tut, but I would consider it like a first step, for beginers.
    Next step should be switching to mongoDB or any JS DB and entering in the MEAN world, so JS is used during the entire process chain 😉

    Reply

  16. router.post(“/users”,function(req,res){
    var query = “INSERT INTO ??(??,??) VALUES (?,?)”;
    var table = [“user_login”,”user_email”,”user_password”,req.body.email,md5(req.body.password)];
    console.log(“mail: “+req.body.email);
    console.log(“pass: “+req.body.password);
    query = mysql.format(query,table);
    console.log(query);
    connection.query(query,function(err,rows){
    if(err) {
    res.json({“Error” : true, “Message” : “Error executing MySQL query”});
    } else {
    res.json({“Error” : false, “Message” : “User Added !”});
    }
    });
    });
    ************
    the response of this is :
    mail : undefined
    password : undefined
    INSERT INTO ‘user_login'(‘user_email’,’user_password’) VALUES (NULL , NULL)

    what is the problem with req.body.email and req.body.password? they don’t extract input data? what to do ??

    Reply

  17. router.post(“/users”,function(req,res){
    var query = “INSERT INTO ??(??,??) VALUES (?,?)”;
    var table = [“user_login”,”user_email”,”user_password”,req.body.email,md5(req.body.password)];
    console.log(“mail: “+req.body);
    console.log(“pass: “+req.body.password);
    query = mysql.format(query,table);
    console.log(query);
    connection.query(query,function(err,rows){
    if(err) {
    res.json({“Error” : true, “Message” : “Error executing MySQL query”});
    } else {
    res.json({“Error” : false, “Message” : “User Added !”});
    }
    });
    });
    ***
    the result of POST request is :
    mail: undefined
    password : undefined
    INSERT INTO ‘user_login'(‘user_email’,’user_password’) VALUES (NULL,NULL)
    what’s wrong with req.body.email and req.body.password ? they don’t extract input data,what to do ?

    Reply

  18. Hi,

    When I am inserting data through localhost:3000/api/users API. I am getting error.

    {
    “Error”: true,
    “Message”: “Error executing MySQL query”
    }

    All setting and table are correct.
    Please let me know the exact problem and also let me know could be debugged node.js program line by line ?.

    Reply

  19. Hi,

    I am getting Error in localhost:3000/api/users API.

    {
    “Error”: true,
    “Message”: “Error executing MySQL query”
    }

    What is Exact problem ??
    Please guide me ??
    Also how would be able to debug code line by line??

    Reply

  20. Hi, great article, but, how do you work with long URL?, ex: /api/users/:usersId/contacts/:contactId, I recommend you use Promises to queries on database, thanks!!

    Reply

  21. Your tutorials are the best node.js tutorials I have come across on the web! Keep up the good work!

    Reply

  22. Thanks for your tutorial.
    but can you help me, how to create and send result of json like this

    [
    [
    {
    “name” : “project_A”
    },
    {
    “data” : [
    {
    “price” : 50,
    “day” : 14,
    “month” : 0
    },
    {
    “price” : 150,
    “day” : 90,
    “month” : 3
    }
    ]
    }
    ],
    [
    {
    “name” : “project_B”
    },
    {
    “data” : [
    {
    “price” : 170,
    “day” : 33,
    “month” : 1
    },
    {
    “price” : 290,
    “day” : 60,
    “month” : 2
    }
    ]
    }
    ]
    ]

    Reply

  23. Hi Shahid,

    Awesome tutorial!!

    I came across this one thing that may be worth changing — currently REST gets created with one ‘connection’ from the pool. This one connection gets used and the pool actually never gets asked for another connection.

    I changed Server.js a little, to limit reduce of connections and explicitly ask pool to wait for free connection (mysql by default, limits number of connections to 100 on its end)
    REST.prototype.connectMysql = function() {
    var self = this;
    var pool = mysql.createPool({
    connectionLimit : 50,
    waitForConnection: true,
    host : ”,
    user : ”,
    password : ”,
    database : ”,
    debug : false
    });
    self.configureExpress(pool);
    }

    in REST.prototype.configureExpress we pass in ‘pool’ instead of passing ‘connection’.

    Over in REST.js we wrap each connection.query into pool.getConnection (pretty much as per your guide on mysql and pools)

    Cheers,
    Sasha

    Reply

  24. I tried to install this and get the following error when I run the app:

    ReferenceError: mysql is not defined
    at C:xampphtdocsnodejsnode_modulesREST.js:27:17
    at Layer.handle [as handle_request] (C:xampphtdocsnodejsnode_modulesexpresslibrouterlayer.js:95:5)
    at next (C:xampphtdocsnodejsnode_modulesexpresslibrouterroute.js:131:13)
    at Route.dispatch (C:xampphtdocsnodejsnode_modulesexpresslibrouterroute.js:112:3)
    at Layer.handle [as handle_request] (C:xampphtdocsnodejsnode_modulesexpresslibrouterlayer.js:95:5)
    at C:xampphtdocsnodejsnode_modulesexpresslibrouterindex.js:277:22
    at Function.process_params (C:xampphtdocsnodejsnode_modulesexpresslibrouterindex.js:330:12)
    at next (C:xampphtdocsnodejsnode_modulesexpresslibrouterindex.js:271:10)
    at Function.handle (C:xampphtdocsnodejsnode_modulesexpresslibrouterindex.js:176:3)
    at router (C:xampphtdocsnodejsnode_modulesexpresslibrouterindex.js:46:12)

    DB is installed and when i type npm start it starts up as defined.

    not sure what I am doing wrong.

    any help is appreciated

    Reply

  26. It would be nice to see how to change this so that each route is handled by a seperate controller in its own file.

    Reply

  29. Love the tutorial but I have a question revolving around the use of the pool connection instead of the normal connect function of the MySQL library. When I was reading the GitHub documentation about the library, I noticed they said you should use the release() function at the end of every function to allow the connection to return back to the pool. I did not notice this in your examples. Can you explain why?

    Reply

      1. Hello Shahid,
        I have tried adding release() at the end of all APIs, but on second hit of API it throws error “Error: Connection already released”. Can you please explain why this error is occurring on second time, how we fix this.
        Thanks

        Reply

        1. I got the answer from Sasha Petrovich’s comment, this was exactly my problem. And Yes, solution was also from your article “Nodejs and MYSQL” only. Thanks

          Reply

  30. Hey Mate,

    Sweet tutorial. It helped me take a makeshift api using php into a RESTful API and it only took an hour and a half. Next steps for my own API is that it requires authentication. It would be cool to have a tutorial on this.

    Reply

  31. I already viewed your AngularJS Single page application blog. How can get Node’s Restful api response from that AngularJS Application. I have already tried $http call in Angular that is not working. Please provide how to handle this Restful Api response in AngularJS Single page application or how can I render Restful api response in express framework…..!

    Reply

      1. It works fine…… But i have an another problem. Firefox tell (CROSS DOMAIN ISSUE).

        Because my AngularJS Single page App runs on port 3000 (localhost:3000) and my API runs on 8000 (localhost:8000). that’s y the issue came.

        I also used some .htaccess file, but nothing happened. One good thing is Chrome has a (plugin for disable CROSS DOMAIN ISSUE), In that it works.. I was totally confused.

        Finally, my objective is creating Single page AngularJS web app using node restAPI with same project folder. ie( project running path like “localhost:3000/” means my API calls running at “localhost:3000/api/”)……..That all…! Help me folk….! I believe i’m in expert hand…!

        Reply

        1. Since you are in same port try calling /api/ from angular service and if its still give cross domain origin add the following code in server file.

          app.use(function(req, res, next) {
            res.header("Access-Control-Allow-Origin", "*");
            res.header("Access-Control-Allow-Headers", "X-Requested-With");
            next();
           });
          Reply

          1. Nothing…! Happened….! Can i send my files to your mail…! I think I’m going to maintain a wrong folder structure. Once you found that, give a guidance to maintain perfect folder structure……! Thank you Shahid……!

  32. Nice tutorial. Alternatively you can send a status with the response:

    res.status(200/404/500….).json(responseData);

    Reply

  33. nice tutorial and worked fine mr. Shahid Shaikh, please teach we about your this tutorial to auth some API.

    Reply

  37. AM!

    Running the insert code I noticed:

    When the query string is like:

    var query = “INSERT INTO ??(??,??) VALUES (?,?)”;

    I got an error and in MySQL debug mode it logged:

    message: ‘Column count doesn\’t match value count at row 1’ }

    changing it to this:

    var query = “INSERT INTO ??(??,??) VALUES (?)”;

    it worked, but the password is not in MD5. When I console.log md5(req.body.password) – I receive undefined as the response.

    Thanks

    Any ideas?

    Reply

  39. Got it working now, my mistake was, that I had built a HTML form and 2 elements had the same name, therefore passing 2 elements into the field email, which in turn got pulled into the sql param query.

    Thanks

    Reply

  40. Great tutorial, I learned a lot from this. I would love to see an addition to this by incorporating swagger api doc. Do you have any plans to do this?

    Reply

  41. Great tutorial, I learned a lot. Any chance you will extent the tutorial to include Swagger support for documenting the API?

    Thanks!

    PS: I tried posting this once before but it never showed up 😉

    Reply

  42. Hi Shahid,

    It is a very good tutorial. I got all of the things very easily. I have a doubt for put statement. When we are updating any user details then it is also updating the password coressponding to invalid email.
    So how can we fix it. We need to write the code to restrict it or is there any predefined function for the same.

    Please help me.

    Thanks & Regards,
    Shravan Jain

    Reply

  43. Very good tutorial for Nodejs beginner like me to follow and study.

    I’ve something that confuse me when I follow down to get user by userId.
    router.get(“/users/:userId”,function(req,res){
    ……………..
    });

    What if i want to have another function that can get user by user’s email addres?
    It definitely cannot use router.get(“/users/:email”,function(req,res) to do it.

    How can I have get user function with user_id and email options?

    Reply

    1. If you want that in URL then you can either go with /:id/:email or having /:email in separate function would work with same URL.

      Reply

  44. Fabulous tutorial for NodeJS beginners,

    But I have one simple doubt.

    How can I call function in rest function.

    eg. function demo(connection)
    {
    connection.query(“select * from user_login where user_id = 1”, function(err, rows){
    return rows;
    });
    }

    router.get(“/users”,function(req,res){
    var query = “SELECT * FROM ??”;
    var table = [“user_login”];
    query = mysql.format(query,table);

    console.log(demo(connection));

    connection.query(query,function(err,rows){
    if(err) {
    res.json({“Error” : true, “Message” : “Error executing MySQL query”});
    } else {
    res.json({“Error” : false, “Message” : “Success”, “Users” : rows});
    }
    });
    });

    But demo doesn’t returns the rows.

    Reply

    1. It’s because demo is synchronous and connection.query() is asynchronous function.

      Learn more about Async handling here.

      Reply

      1. I read that blog thoroughly. But even I doesn’t know about how can I achieve my solution.

        My requirement is whenever each and every api call that will check that should be called from valid users.

        So that I need to call a function (eg.function validate()) that may be return 0 or 1.

        Help me Shahid. I believe I’m in expert hand.

        Reply

  45. Thanks for yuor question.

    I will make some shopping mall site by using Node JS.

    One question in this turtorial is
    – you use a tool to send verb to your node js to handle CRUD right?
    – what about implemeneting CRUD in industry? I meant I tried to delete a selected row with route.delete() in database sent from a HTML view (method=’delete’). Seemingly It did not work. From my perspective, it should be worked. If view sends a method with delete to controller with route.delete() like you made here.

    Reply

  47. Hey Shadhid,

    Awesome API, I’m new to node and express can you please tell me how can i query the database with promise, I’m using angular datatables and i need promise in my backend api.

    Thanks
    Wcan

    Reply

  48. yeah I’ve read this tutorial its good and very help full, what i don’t can’t understand is how can i use var Promise = require(‘bluebird’);
    OR
    var async = require(‘async’);

    with the following code

    router.get(‘/HouseList/:TableName’, function(req, res)
    {
    var table_name = [req.params.TableName];

    var query = “SELECT * FROM house”;

    query = mysql.format(query, table_name);
    connection.query(query, function(err, rows)
    {
    if(err)
    {
    res.json({“Error” : true, “Message” : err.toString()});
    }
    else
    {
    res.json({“Error” : false, “Message” : “Success”, [req.params.TableName] : rows});
    }
    });
    });

    anything i try it give me an error. Thanks in advance

    Reply

  49. POST method only works for x-www-form-urlencoded header. how can i achieve POST using plain header (body – json).?

    Reply

      1. Awesome Man. I already written it. After comment

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

        it works fine.

        Reply

  52. Hey ,

    i have a question….here it is ..

    i am using logger.js and logging.js in my project i need to replace the logging.js with logger.js

    with out any confliction’s ..this is my concern

    Note:
    i am using my internal framework to build the project. reply me asap.

    Reply

  53. Thanks for sharing your knowledge. How to implement multi tenant architecture with seperate database for each tenant?

    Reply

  54. Hello Shahid. Nice tutorial, thx for it.
    I have a little issue, perhaps you can help me. I’m trying to print the result in an HTML table, but all I get is [object Object] printed in by table.

    I’m using this script in the HTML file:

    $.getJSON(“/api/clients” , function(req) {
    var tbl_body = document.createElement(“tbody”);
    $.each(req, function(row) {
    var tbl_row = tbl_body.insertRow();
    $.each(this, function(k , v) {
    var cell = tbl_row.insertCell();
    cell.append(document.createTextNode(v.toString()));
    })
    })
    $(“#cl-db”).append(tbl_body);
    console.log(tbl_body);
    });

    Any clues on how to proceed? I’m new to all of this.

    Ps: I changed ‘users’ for ‘ clients’ in my routes.

    Reply

  55. Hi Shahid,

    This Tutorial is very useful. Thanks for it.
    Actually now I am working on Authentication based web and mobile apps using MongoDB, express.js and node.js., In that I am stuck in how to get the api call after verify the username and password. Actually we are using some JWT token generating authentication method. The user information table which contains user details are separate collections in MongoDB and the data which is used to create the api is another collections. The process is, while login the application it verifies the username and password combination and after that it will call the api which contains the corresponding user data. Here the collection is different so we are unable to get the api response after successful login user. Can any one please help me.

    Thanks
    Vicky

    Reply

    1. Hi,

      Thanks for explaining the issue.

      To solve it, you need a middleware hooked before your API calls which in turns checks whether a token is correct or not.

      I actually wrote the complete tutorial on JWT using RethinkDB. You can replace RethinkDB model code with MongoDB and I am sure it will work like charm.

      Here it is.

      Reply

  56. Hi Shahid,
    thanks a lot for this tutorial.
    I am very new to node and express and it is very well explained so we got it up and runnung.
    We are running the webclient(NGINX), the API(Node & Express) and the MySQL in different Docker containers which works well together.
    Just one question, how can i do a proper call to the API – lets say get all users with this function of your REST.js:

    router.get(“/users”,function(req,res){
    var query = “SELECT * FROM ??”;
    var table = [“user_login”];
    query = mysql.format(query,table);
    connection.query(query,function(err,rows){
    if(err) {
    res.json({“Error” : true, “Message” : “Error executing MySQL query”});
    } else {
    res.json({“Error” : false, “Message” : “Success”, “Users” : rows});
    }
    });
    });

    from a js.-file included in the index.html at the webfrontend?
    And, of course get the right result error/success back.

    Many thanks for your time,
    Joe

    Reply

      1. Hi Shahid,
        thanks for your anser. Found out that out already 😉

        Can you maybe give a small example of how to call a stored prodessure with 2 input parameters and the restult returned.
        That wouls made my day.

        Many thanks for your sepended time in advance,
        Joe.

        Reply

  57. Hello i have a problem i’ve replace the credential by mine with remote MySQL DB (host, database, name, password) and i have this error “ISSUE WITH MYSQL nError: getaddrinfo ENOTFOUND MY_DATABASE_ADDR MY_DATABASE_ADDR:3306” Dont know how to fix it ?

    Reply

      1. But the database is already created i can access it via phpmyadmin and i tried with another db i used for another project still have the same issue

        Reply

  58. Hi! My app only works with x-www-form-urlencoded header.
    I have tried commenting out app.use(bodyParser.urlencoded({ extended: true }));
    But still not working with form-data

    Reply

    1. Keep getting this error;

      Error: Illegal argument undefined
      at module.exports (C:\Program Files\nodejs\login_register_app\node_modules\md5\md5.js:152:13)
      at C:\Program Files\nodejs\login_register_app\REST.js:15:79
      at Layer.handle [as handle_request] (C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\layer.js:95:5)
      at next (C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\route.js:131:13)
      at Route.dispatch (C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\route.js:112:3)
      at Layer.handle [as handle_request] (C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\layer.js:95:5)
      at C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\index.js:277:22
      at Function.process_params (C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\index.js:330:12)
      at next (C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\index.js:271:10)
      at Function.handle (C:\Program Files\nodejs\login_register_app\node_modules\express\lib\router\index.js:176:3)

      Reply

  59. Hi, I would like to see a version with express-validator. Does prototype approach change the way this validation is implemented?
    Thanks!!!

    Reply

  62. Hi
    I am new to creating a rest api through express js and try to creating one through your this blog. but i am not able to post data through postman. Its always showing Cannot POST /api/users/ Any suggestion?

    Reply

  63. its working. the node_module folder in downloaded code was might be buggy. freshly installing npm gets working.

    Reply

  64. The code works fine till i get it to print hello world or something. But as soon as i start hitting the database to insert or fetch records it starts giving me errors in the console.The “npm start” command doesn’t works once i start hitting database. Why am i getting the error.

    Reply

  65. Hi Nice Tutorial

    But my problem is MySQL user authentication, with this example, can you please explain me.

    Reply

  66. I want to execute two insert queries in Post method. But by default only the first query gets executed ignoring the second. Below is the code I tried:
    router.post(“/promoinsert/:PriceAfter/:PromoID/:description/:StartDate/:EndDate”,function(req,res){
    var query = “INSERT INTO ??(??,??,??,??,??,??) select ??,??,??-??,??-??,case when ??-??<? then 0 else ABS(??-??-?) end portin,case when ??-??<? then 0 else ABS(??-??-?) end aal from ??";"INSERT INTO ??(??,??,??,??) values (?,?,str_to_date(?,'%m-%d-%Y'),str_to_date(?,'%m-%d-%Y'))";"INSERT INTO ??(??,??,??,??,??) select ??,??,??-??,??-??,case when ??-??<? then 0 else ABS(??-??-?) end portin,case when ??-??<? then 0 else ABS(??-??-?) end aal from ??";
    var table = ["tb_promo_item","model_name","sku","new_acc_amt","upg_amt","port_in_amt","AAL_amt",
    "model_name","sku","SOR_price","standard_IR","SOR_price","standard_IR","SOR_price","standard_IR",req.params.PriceAfter,"SOR_price","standard_IR",req.params.PriceAfter,"SOR_price","standard_IR",req.params.PriceAfter,"SOR_price","standard_IR",req.params.PriceAfter,"tb_item"];["tb_promotion","promotion_id","description"
    ,"start_date","end_date",req.params.PromoID,req.params.description,req.params.StartDate,req.params.EndDate];
    query = mysql.format(query,table);
    connection.query(query,function(err,rows){
    if(err) {
    res.json({"Message" : "Error executing MySQL query"});
    } else {
    res.json({"Message" : "Records inserted"});
    }
    });
    });

    Reply

  68. Hello,
    Its great tutorial and i want to use socket.io in REST.js file how can i access this in REST.js. Its difficult to use socket variable in external file. Awaiting for your reply. Thanks in advance.

    Reply

  69. Hi, thank for this tutorial. It is greatfull!
    however i have got an error when i try to POST and get th param with “req.body.name…”

    for this issue i have add bodyParser in server.js after have do a npm install of that:

    var bodyParser = require(‘body-parser’);
    app.use(bodyParser.json()); // support json encoded bodies
    app.use(bodyParser.urlencoded({ extended: true })); // support encoded bodies

    And i haven’t got an error after. it work fine!
    Thanks!

    Reply

  70. sir… great article and my api working funtastic.
    but my code run on json(applicaton/json)
    not show on above x-www-form-urluncoded.
    plz explain it.

    Reply

  71. Hi that tutorial was great , but how do you authenticate since using sessions in APIs is quite impossible for devices like Android APPS? In the way I see Google API Keys work

    Reply

  73. Hi,

    getting this error,

    can someone help me here ?

    —
    ISSUE WITH MYSQL
    Error: connect ECONNREFUSED 127.0.0.1:3306
    npm ERR! code ELIFECYCLE
    npm ERR! errno 1
    —-

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.