Build Restful API's With Node.js - Express - MySQL

Introduction

 
In this article, we are going to cover how to build APIs with Node.js - Express - MySQL. As we all know, Javascript is the most popular language because we can easily build both front end and back end services with it.
 
What we are going to cover in this?
  •  Installation of required packages
  •  Database Configuration setup
  •  CRUD Methods
  •  Execution of Stored Procedure
Step 1
 
Create a folder with the project name and open the folder in Visual Studio Code and open the command prompt (Win + R -> cmd) or terminal in Vs Code and switch to the project directory.
 
In order to create the package.json file please find the below command and it will ask for some project configuration -  just click on enter.
 
Command: npm init
 
 
 
Required  Packages
 
Express - For Server
 
MySql - Interacting with Database Server 
 
body-parser - It allows us to send the JSON data to Node API 
 
Let's install all the packages in one go by the below command and after installing all the packages you will see the packages with the version in the package.json file inside the project.
 
 
 
 package.json 
  1. {  
  2.   "name""nodeapicrud",  
  3.   "version""1.0.0",  
  4.   "description""",  
  5.   "main""index.js",  
  6.   "scripts": {  
  7.     "test""echo \"Error: no test specified\" && exit 1"  
  8.   },  
  9.   "author""",  
  10.   "license""ISC",  
  11.   "dependencies": {  
  12.     "body-parser""^1.19.0",  
  13.     "express""^4.17.1",  
  14.     "mysql""^2.18.1"  
  15.   }  
  16. }  
 Let's create a database in MySQL so that we can perform CRUD operations via Database. I have used MySql Workbench to connect to the database server 
 
 
Create a Database and under that create a table. 
  1. create table Employee(  
  2.    Id INT NOT NULL,  
  3.    Name VARCHAR(100) NOT NULL,  
  4.    Designation VARCHAR(40) NOT NULL,  
  5.    City VARCHAR(40) NOT NULL,
  6.    ContactNo INT NOT NULL,  
  7.    PRIMARY KEY (Id)  
  8. );  
Create a Stored Procedure in Database for Insertion and Updation through Node API. I have used a single procedure for both operations.
  1. CREATE PROCEDURE `AddorUpdateEmployee`(  
  2. IN _EmpID INT,  
  3. IN _Name varchar(45),  
  4. IN _designation varchar(45),  
  5. IN _City varchar(45),  
  6. IN _ContactNo INT  
  7. )  
  8. Begin  
  9.    Declare EmployeeId INTDeclare Status varchar(20);  
  10.     set EmployeeId = (select Id from employee where Id = _EmpID);  
  11.     IF EmployeeId != _EmpID  THEN  
  12.     Insert into employee(Id,Name,Designation,City,ContactNo)  
  13.     values (_EmpID,_Name,_designation,_City,_ContactNo);  
  14.     Set Status = "Insertion Completed";  
  15.     else  
  16.     update employee  
  17.     set   
  18.     Name = _Name,  
  19.     Designation = _designation,  
  20.     City = _City,  
  21.     ContactNo = _ContactNo;  
  22.     set Status = "Updation Done";  
  23.     END IF;  
  24.     END  

Database Configuration in Node.js 

 
I have used a single index.js file for all the configuration setup and for CRUD Methods and I have posted comments under each section so that we can have a clear picture of why we are using this and where it impacts.
 
Steps that I have followed in index.js
  1. Importing the packages
  2. Database configuration and connection string setup for MySql.
  3. To check whether the connection is successful or Failed while running the project
  4. To Run the server with Port Number.
  5. API Methods Get, Put, Post, Delete 
index.js
  1. // Importing the packages required for the project.  
  2.   
  3. const mysql = require('mysql');  
  4. const express = require('express');  
  5. var app = express();  
  6. const bodyparser = require('body-parser');  
  7.   
  8. // Used for sending the Json Data to Node API  
  9. app.use(bodyparser.json());  
  10.   
  11. // Connection String to Database  
  12. var mysqlConnection = mysql.createConnection({  
  13.     host: 'localhost',  
  14.     user : 'root',  
  15.     password : '******',   
  16.     database : 'employeedb',  
  17.     multipleStatements : true  
  18. });  
  19.   
  20. // To check whether the connection is succeed for Failed while running the project in console.  
  21. mysqlConnection.connect((err) => {  
  22.     if(!err) {  
  23.         console.log("Db Connection Succeed");  
  24.     }  
  25.     else{  
  26.         console.log("Db connect Failed \n Error :" + JSON.stringify(err,undefined,2));  
  27.     }  
  28. });  
  29.   
  30. // To Run the server with Port Number  
  31. app.listen(3000,()=> console.log("Express server is running at port no : 3000"));  
  32.   
  33. // CRUD Methods  
  34. //Get all Employees  
  35. app.get('/employees',(req,res)=>{  
  36.     mysqlConnection.query('SELECT * FROM Employee',(err,rows,fields)=>{  
  37.     if(!err)   
  38.     res.send(rows);  
  39.     else  
  40.         console.log(err);  
  41.       
  42. })  
  43. });  
  44.   
  45. //Get the Employee Data based on Id  
  46. app.get('/employees/:id',(req,res)=>{  
  47.     mysqlConnection.query('SELECT * FROM Employee WHERE id = ?',[req.params.id],(err,rows,fields)=>{  
  48.     if(!err)   
  49.     res.send(rows);  
  50.     else  
  51.         console.log(err);  
  52.       
  53. })  
  54. });  
  55.   
  56. //Delete the Employee Data based on Id  
  57. app.delete('/employees/:id',(req,res)=>{  
  58.     mysqlConnection.query('DELETE FROM Employee WHERE id = ?',[req.params.id],(err,rows,fields)=>{  
  59.     if(!err)   
  60.     res.send("Data Deletion Successful");  
  61.     else  
  62.         console.log(err);  
  63.       
  64. })  
  65. });  
  66.   
  67.   
  68. //Insert an Employee through the Stored Procedure  
  69. app.post('/employees',(req,res)=>{  
  70.     let emp = req.body;  
  71.     var sql = "SET @EmpID = ?;SET @Name = ?;SET @Designation = ?;SET @City = ?;SET @ContactNo = ?; \  
  72.               CALL AddorUpdateEmployee(@EmpID,@Name,@Designation,@City,@ContactNo);"  
  73.     mysqlConnection.query(sql,[emp.EmpID,emp.Name,emp.Designation,emp.City,emp.ContactNo],(err,rows,fields)=>{  
  74.     if(!err)   
  75.     res.send("Insertion Completed");  
  76.     else  
  77.         console.log(err);  
  78. })  
  79. });  
  80.   
  81. //Update an Employee through the Stored Procedure  
  82. app.put('/employees',(req,res)=>{  
  83.     let emp = req.body;  
  84.     var sql = "SET @EmpID = ?;SET @Name = ?;SET @Designation = ?;SET @City = ?;SET @ContactNo = ?; \  
  85.               CALL AddorUpdateEmployee(@EmpID,@Name,@Designation,@City,@ContactNo);"  
  86.     mysqlConnection.query(sql,[emp.EmpID,emp.Name,emp.Designation,emp.City,emp.ContactNo],(err,rows,fields)=>{  
  87.     if(!err)   
  88.     res.send("Updation Done");  
  89.     else  
  90.         console.log(err);  
  91. })  
  92. });  
Run the Project,
 
 
 
Run the above command in the terminal -> node index.js
 
 We can see whether our express server and database connection is made successfully or not in the console itself.
 
Now we can test our API's in Postman
 
Get Request 
 
It's a simple Get call to fetch all the employee details in Json format from the database. In the same way for a Delete request just pass the employee id in the query parameter to perform the delete operation.
 
Example: http://localhost:3000/employees/2 (Delete) 
 
 
 
Post Request
 
Same as above but the only change is you need to pass the JSON object to the post-call to insert the record to Database. 
 
 
Finally, you can see the inserted records  in a database table
 
 
 
If you found this article helps you, please give it a thumbs up  
 
Keep learning .......!


Similar Articles