WebAPI Using NodeJS, Express, And MySQL

In this article, you will learn to create a Web API using NodeJS, Express, and MySQL.

Introduction

 
In this article, we are going to create a Web API with the help of node.js, Express, and MySQL. Web API is an application which serves HTTP based requests by different applications on different platforms, such as web, Windows, and mobile.
 
Requirements
  1. MySQL
  2. NodeJS
  3. Active Internet
  4. Postman Application
  5. Visual Studio Code
Step 1
 
Create a database in MySQL, named sampledb and use it.
  1. mysql> create database sampledb;  
  2. mysql> use sampledb;  
Step 2
 
Create a new table named employee.
  1. mysql> create table employee  
  2.          (  
  3.         id int primary key,  
  4.         name varchar(20),  
  5.         gender varchar(20),  
  6.         city varchar(20),  
  7.         salary int  
  8. );  
Step 3
 
Add some rows to the table.
  1. mysql> insert into employee values(1,'pankaj','male','delhi',15000);  
  2. mysql> insert into employee values(2,'pooja','female','pune',25000);  
Step 4
 
Open command prompt/cmd and navigate to the application folder, i.e., webapi.
 
C:\Users\Pankaj Singh\Desktop\webapi>
 
Step 5
 
Initialize the app with npm init,
 
C:\Users\Pankaj Singh\Desktop\webapi>npm init
 
Web API Using NodeJS+Express+MySql
 
Step 6
 
Now, add some Node modules, as mentioned below.
 
C:\Users\Pankaj Singh\Desktop\webapi>npm install express
C:\Users\Pankaj Singh\Desktop\webapi>npm install mysql
C:\Users\Pankaj Singh\Desktop\webapi>npm install body-parser
 
Step 7
 
Add a server.js file to the application.
 
C:\Users\Pankaj Singh\Desktop\webapi>null>server.js
 
Step 8
 
Open Visual Studio Code.
 
C:\Users\Pankaj Singh\Desktop\webapi>code .
 
Web API Using NodeJS+Express+MySql
 
Step 9
 
Our package.json file looks like this.
 
Web API Using NodeJS+Express+MySql
 
Step 10
 
Now, configure the server.js file. Basic Express app is as follows
  1. var express = require('express');  
  2. var mysql = require('mysql');  
  3. var bodyParser=require('body-parser');  
  4.   
  5. var urlencoderParser = bodyParser.urlencoded({extended:false});  
  6.   
  7. var app=express();  
  8. var port = process.env.port||3000;  
  9.   
  10. //Api code here  
  11.   
  12. app.listen(port);  
  13. console.log('Server is started on http://localhost:'+port);  
Step 11
 
Add a connection to MySQL.
  1. //Mysql Connection  
  2. var con = mysql.createConnection({  
  3.     host:'localhost',  
  4.     user:'root',  
  5.     password:'password0',  
  6.     database:'sampledb'  
  7. });  
Step 12
 
Now, add code for GET request on root “/api” . This route returns all the rows of employee table.
  1. //GET  
  2. app.get('/api',function(req,res){  
  3.     var qry = "select * from employee";  
  4.     con.query(qry,function(err,rows){  
  5.         if(err)  
  6.             throw err;  
  7.         console.log(rows);  
  8.         res.json(rows);  
  9.     });  
  10. });  
Step 13
 
Add code for GET request with id on root “/api/<id>”. This route returns the row of employee table with the given id
  1. //GET with id  
  2. app.get('/api/:id',function(req,res){  
  3.     var qry = "select * from employee where id="+req.params.id;  
  4.     con.query(qry,function(err,rows){  
  5.         if(err)  
  6.             throw err;  
  7.         console.log(rows[0]);  
  8.         res.json(rows[0]);  
  9.     });  
  10. });  
Step 14
 
Now, add code for POST request. In this request, we use urlencoderParser to read the body content of the request.
  1. //POST  
  2. app.post('/api',urlencoderParser,function(req,res){  
  3.     var qry = "insert into employee values("+parseInt(req.body.id)+",'"+req.body.name+"','"+req.body.gender+"','"+req.body.city+"',"+parseInt(req.body.salary)+")";  
  4.     con.query(qry,function(err,rows){  
  5.         if(err)  
  6.             throw err;  
  7.         console.log("1 Row Added.");  
  8.         res.send("1 Row Added.")  
  9.     });  
  10. });  
Step 15
 
Now, add code for POST request. In this request, we use urlencoderParser to read the body content of the request.
  1. //PUT  
  2. app.put('/api/:id',urlencoderParser,function(req,res){  
  3.     var qry = "update employee set name='"+req.body.name+"',gender='"+req.body.gender+"',city='"+req.body.city+"',salary="+parseInt(req.body.salary)+" where id="+parseInt(req.params.id);  
  4.     con.query(qry,function(err,rows){  
  5.         if(err)  
  6.             throw err;  
  7.         console.log("1 Row Updated.");  
  8.         res.send("1 Row Updated.")  
  9.     });  
  10. });  
Step 16
 
Add the code for DELETE Request.
  1. //DELETE  
  2. app.delete('/api/:id',function(req,res){  
  3.     var qry = "delete from employee where id="+parseInt(req.params.id);  
  4.     con.query(qry,function(err,rows){  
  5.         if(err)  
  6.             throw err;  
  7.         console.log("1 Row Removed.");  
  8.         res.send("1 Row Removed.")  
  9.     });  
  10. });  
Step 17
 
The complete code is as follows. 
  1. var express = require('express');  
  2. var mysql = require('mysql');  
  3. var bodyParser=require('body-parser');  
  4.   
  5. var urlencoderParser = bodyParser.urlencoded({extended:false});  
  6.   
  7. var app=express();  
  8. var port = process.env.port||3000;  
  9.   
  10. //Api code here  
  11.   
  12. //Mysql Connection  
  13. var con = mysql.createConnection({  
  14.     host:'localhost',  
  15.     user:'root',  
  16.     password:'password0',  
  17.     database:'sampledb'  
  18. });  
  19.   
  20. //GET  
  21. app.get('/api',function(req,res){  
  22.     var qry = "select * from employee";  
  23.     con.query(qry,function(err,rows){  
  24.         if(err)  
  25.             throw err;  
  26.         console.log(rows);  
  27.         res.json(rows);  
  28.     });  
  29. });  
  30.   
  31. //GET with id  
  32. app.get('/api/:id',function(req,res){  
  33.     var qry = "select * from employee where id="+req.params.id;  
  34.     con.query(qry,function(err,rows){  
  35.         if(err)  
  36.             throw err;  
  37.         console.log(rows[0]);  
  38.         res.json(rows[0]);  
  39.     });  
  40. });  
  41.   
  42. //POST  
  43. app.post('/api',urlencoderParser,function(req,res){  
  44.     var qry = "insert into employee values("+parseInt(req.body.id)+",'"+req.body.name+"','"+req.body.gender+"','"+req.body.city+"',"+parseInt(req.body.salary)+")";  
  45.     con.query(qry,function(err,rows){  
  46.         if(err)  
  47.             throw err;  
  48.         console.log("1 Row Added.");  
  49.         res.send("1 Row Added.")  
  50.     });  
  51. });  
  52.   
  53. //PUT  
  54. app.put('/api/:id',urlencoderParser,function(req,res){  
  55.     var qry = "update employee set name='"+req.body.name+"',gender='"+req.body.gender+"',city='"+req.body.city+"',salary="+parseInt(req.body.salary)+" where id="+parseInt(req.params.id);  
  56.     con.query(qry,function(err,rows){  
  57.         if(err)  
  58.             throw err;  
  59.         console.log("1 Row Updated.");  
  60.         res.send("1 Row Updated.")  
  61.     });  
  62. });  
  63.   
  64. //DELETE  
  65. app.delete('/api/:id',function(req,res){  
  66.     var qry = "delete from employee where id="+parseInt(req.params.id);  
  67.     con.query(qry,function(err,rows){  
  68.         if(err)  
  69.             throw err;  
  70.         console.log("1 Row Removed.");  
  71.         res.send("1 Row Removed.")  
  72.     });  
  73. });  
  74.   
  75. app.listen(port);  
  76. console.log('Server is started on http://localhost:'+port);  
Step 18
 
Now, Run the API.
 
C:\Users\Pankaj Singh\Desktop\webapi>nodemon server.js
[nodemon] 1.19.1
[nodemon] to restart at any time, enter `rs`
[nodemon] watching: *.*
[nodemon] starting `node server.js`
 
Server is started on http://localhost:3000
 
Step 19
 
Open Postman to test the API.
 
Web API Using NodeJS+Express+MySql
 
Step 20
 
Sending GET Request to API. In response, we get all the rows of the employee table as JSON data.
 
Web API Using NodeJS+Express+MySql
 
Step 21
 
Sending GET Request with Id to API. In response, we get all the row of the employee table with given id as JSON data.
 
Web API Using NodeJS+Express+MySql
 
Step 22
 
Sending POST Request with the body to API.
 
Web API Using NodeJS+Express+MySql
 
Step 23
 
Sending put request with the body to API.
 
Web API Using NodeJS+Express+MySql
 
Step 24
 
Sending put request with the body to API.
 
Web API Using NodeJS+Express+MySql