RESTful API Using Node Express and Mysql
- remove_red_eye76511 Views
- event10 May 2015
- access_time17 min read
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 :
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.
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;
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.
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.");
});
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;
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
}
"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.
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.
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;
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.
Have a look at database where new user is added.
#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;
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.
Case 2: Get user by ID.
#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;
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.
#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;
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.
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
