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.

ApiTypeDescription
/usersPOSTTakes email and password as input data and add new user.
/usersGETReturns every users from database
/users/:userIdGETReturns users from database with match of userId.
/users/:email/PUTUpdate password of user by passing email ID.
/users/:emailDELETEDelete 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