The Tech Platform
Mar 2, 20214 min
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
Importing the packages
Database configuration and connection string setup for MySql.
To check whether the connection is successful or Failed while running the project
To Run the server with Port Number.
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