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 

{
  "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

 

 

Git-Hub

 

If you found this article helps you, please give it a thumbs up  

 

Keep learning .......!