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

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.

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

{  
 "name": "nodeapicrud",  
 "version": "1.0.0",  
 "description": "",  
 "main": "index.js",  
 "scripts": {  
 "test": "echo \"Error: no test specified\" && exit 1" 
  },  
 "author": "",  
 "license": "ISC",  
 "dependencies": {  
 "body-parser": "^1.19.0",  
 "express": "^4.17.1",  
 "mysql": "^2.18.1" 
  }  
}  

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 My SQL Workbench Create a Database and under that create a table.

create table Employee(  
   Id INT NOT NULL,  
   Name VARCHAR(100) NOT NULL,  
   Designation VARCHAR(40) NOT NULL,  
   City VARCHAR(40) NOT NULL,
   ContactNo INT NOT NULL, 
 PRIMARY KEY (Id)  
);  

Create a Stored Procedure in Database for Insertion and Updation through Node API. I have used a single procedure for both operations.

CREATE PROCEDURE `AddorUpdateEmployee`(  
IN _EmpID INT,  
IN _Name varchar(45),  
IN _designation varchar(45),  
IN _City varchar(45),  
IN _ContactNo INT 
)  
Begin 
 Declare EmployeeId INT; Declare Status varchar(20);  
 set EmployeeId = (select Id from employee where Id = _EmpID);  
    IF EmployeeId != _EmpID  THEN 
 Insert into employee(Id,Name,Designation,City,ContactNo)  
 values (_EmpID,_Name,_designation,_City,_ContactNo);  
 Set Status = "Insertion Completed";  
 else 
 update employee  
 set 
 Name = _Name,  
    Designation = _designation,  
    City = _City,  
    ContactNo = _ContactNo;  
 set Status = "Updation Done";  
 END IF;  
 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

// Importing the packages required for the project. 
 
const mysql = require('mysql');  
const express = require('express');  
var app = express();  
const bodyparser = require('body-parser');  
 
// Used for sending the Json Data to Node API 
app.use(bodyparser.json());  
 
// Connection String to Database 
var mysqlConnection = mysql.createConnection({  
    host: 'localhost',  
    user : 'root',  
    password : '******',   
    database : 'employeedb',  
    multipleStatements : true 
});  
 
// To check whether the connection is succeed for Failed while running the project in console. 
mysqlConnection.connect((err) => {  
 if(!err) {  
        console.log("Db Connection Succeed");  
    }  
 else{  
        console.log("Db connect Failed \n Error :" + JSON.stringify(err,undefined,2));  
    }  
});  
 
// To Run the server with Port Number 
app.listen(3000,()=> console.log("Express server is running at port no : 3000"));  
 
// CRUD Methods 
//Get all Employees 
app.get('/employees',(req,res)=>{  
    mysqlConnection.query('SELECT * FROM Employee',(err,rows,fields)=>{  
 if(!err)   
    res.send(rows);  
 else 
        console.log(err);  
 
})  
});  
 
//Get the Employee Data based on Id 
app.get('/employees/:id',(req,res)=>{  
    mysqlConnection.query('SELECT * FROM Employee WHERE id = ?',[req.params.id],(err,rows,fields)=>{  
 if(!err)   
    res.send(rows);  
 else 
        console.log(err);  
 
})  
});  
 
//Delete the Employee Data based on Id 
app.delete('/employees/:id',(req,res)=>{  
    mysqlConnection.query('DELETE FROM Employee WHERE id = ?',[req.params.id],(err,rows,fields)=>{  
 if(!err)   
    res.send("Data Deletion Successful");  
 else 
        console.log(err);  
 
})  
});  
 
 
//Insert an Employee through the Stored Procedure 
app.post('/employees',(req,res)=>{  
    let emp = req.body;  
 var sql = "SET @EmpID = ?;SET @Name = ?;SET @Designation = ?;SET @City = ?;SET @ContactNo = ?; \  
              CALL AddorUpdateEmployee(@EmpID,@Name,@Designation,@City,@ContactNo);"  
    mysqlConnection.query(sql,[emp.EmpID,emp.Name,emp.Designation,emp.City,emp.ContactNo],(err,rows,fields)=>{  
 if(!err)   
    res.send("Insertion Completed");  
 else 
        console.log(err);  
})  
});  
 
//Update an Employee through the Stored Procedure 
app.put('/employees',(req,res)=>{  
    let emp = req.body;  
 var sql = "SET @EmpID = ?;SET @Name = ?;SET @Designation = ?;SET @City = ?;SET @ContactNo = ?; \  
              CALL AddorUpdateEmployee(@EmpID,@Name,@Designation,@City,@ContactNo);"  
    mysqlConnection.query(sql,[emp.EmpID,emp.Name,emp.Designation,emp.City,emp.ContactNo],(err,rows,fields)=>{  
 if(!err)   
    res.send("Updation Done");  
 else 
        console.log(err);  
})  
});  

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

Keep learning .......!


Source: C# Corner, Wikipedia


The Tech Platform

www.thetechplatform.com

0 comments

Recent Posts

See All

Setting Up A Local Web Server With NodeJS & ExpressJS

Setting up a local web server with NodeJS is actually much easier than you would imagine. I was pretty shocked at how straight forward it is to do, so I thought I would share how to do it, really quic