CRUD Operations in MySQL with NodeJS.
In one of my previous blogs, we saw the fundamentals of SQL, some commands, and queries. In this blog, we will see how to connect the MySQL database with a NodeJs and Express application and how to perform CRUD operations.
Application Pre-Requisite:
- Basic understanding of NodeJS and Express. Visit this link if you are a newbie!
- NodeJS and MySQL Database Installed on your device.
The flow of data in our application will be as follows:
First, We have to create a table to store user data and we will do this using the MySQL application called MySql Workbench. MySql Workbench comes along with the installation of MySQL.
Click on the database icon to create a new database and enter the name of the database in the green box as shown above. I have named it userdb , Now click on the apply button. Our database schema is now created!
Now, in the left navigation bar, navigate to userdb > Tables , right-click on Tables, and click on Create Table. After that, an interface similar to the above image will appear, Fill up the table information as given above. The name of the table is user and it has 5 columns that include:-
User id -id
User first name -fName
User last name -lName
User age -age
User occupation -occupation.
Click on the apply button to create the table.
Now that we have a table, we can create our application to store data in this database table. Go ahead and download or fork the starting files of the project from the GitHub repo here. The folder structure looks like below:
Once you have the above files on your computer, run the following commands in the root of the project directory:
npm install
nodemon server.jsA server will now be running on http://localhost:80. You will get the below web page on port 80.
In this web application, currently, there is no functionality to store data or retrieve data from the database. There are 5 pages in the application, they are Add User, Search user, Update user, View User and User not found. We have to go through the following steps to complete the functionality of this application.
- Connecting MySql database to our application.
- A Route to get the update page of the application.
- Routes to create, read, update, and delete user data.
So, Let’s get started with code and implementation.
Connecting MySql database.
Download the mysql2 dependency package using the below command
npm i mysql2Write the below code in server.js to connect the database.
const mysql = require("mysql2");
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'qwerty@12345',
database: 'userdb'
})
connection.connect(function(err) {
if (err) {
console.error('Error connecting: ' + err.stack);
return;
}
console.log('Connected as id ' + connection.threadId);
});All right, Here are the steps we performed till now, we imported the mysql2 dependency and created a connection object using the mysql.createConnection() method. We provided all the information related to a database like host, user, password, and database name. Next, we have established the connection using the connect method. This method takes a callback function, this function prints different statements based on whether the connection is successful or not.
function runDBCommand(sqlQuery){
return new Promise((resolve, reject) => {
connection.query(sqlQuery, (error, results) => {
if (error) {
reject(error);
} else {
resolve(results);
}
});
});
}In the above code, we created a reusable function runDBCommand() that will execute all the queries of the application. This function takes an argument which is an SQL command and then executes that command using the connection.query() method. The runDBCommand() method returns a promise. If the promise is fulfilled, it returns the result set returned from the database, if not it returns the error. We will call this method from different routes to execute our query.
Route for creating a new user.
In the below code, the app.post() method is a type of HTTP request handler used to create a post request. Every request handler requires two arguments, first is the path where the request is made and second is the callback function that executes when a user hits the route. In the below code, the ‘/add’ is the route, and the async function takes two arguments that is, request and response .
// Add user - Create
app.post("/add", async (request, response)=>{
try{
const data = request.body;
const query = `insert into user (fName, lName, age, occupation) values ('${data.fName}', '${data.lName}', ${data.age}, '${data.occupation}')`
const addToDatabase = await runDBCommand(query);
response.status(201).redirect(`/user/${addToDatabase.insertId}`)
}catch(err){
console.log(err)
response.status(500).redirect("/")
}
})Inside the callback function, a try-catch block is created to handle any errors that might occur, we retrieved the data passed from the form in the frontend using the request.body and stored it in a variable data. The variablequery contains an SQL insert command in which we replace the data using string literals. We then called the runDBCommand() method and passed the query to it. Once the records are created the request redirects to the route ‘/user/:id’ and if any error occurs in the runDBCommand() method, then the catch block executes and redirects it to the home page /.
Displaying a single user data page.
On the add user page of the application click on the Get User Information button. You will be redirected to the below page:
This page takes a user ID and redirects to the view user page if the user exists. Otherwise, it renders the user not found page. The code is as follows:
// Finds a user with id and renders the user data - Read
app.get("/user/:userid", async (request,response)=>{
try{
const query = `select * from user where id = ${request.params.userid}`;
const data = await runDBCommand(query);
(data.length !== 0)? response.render("user", {user: data[0]}) : response.render("notfound")
}catch(err){
console.log(err)
response.status(500).redirect("/")
}
})The above code has a get request handler with the path ‘/user/:userid’. :userid is a path parameter that holds the ID of the user to be fetched. Inside the callback method, we have declared the SQL query to retrieve a particular user data using the user ID. Then we passed that query to the runDBCommand() method and the method returns an array of 1 object if the record is found else an empty array is returned. The returned record is passed to the response that renders the template. The render page is shown below.
If the record is not found it renders the user not found page.
Route to get the update page.
Note that on the view user page, there is an update button. On clicking the update button, the user should be redirected to the update.ejs template. The route for this is as follows:
// Get update page for user
app.get("/update-page/:id", async (request, response)=>{
try{
const query = `select * from user where id = ${request.params.id}`;
const data = await runDBCommand(query);
response.render("update", {user: data[0]})
}
catch(err){
console.log(err)
response.status(500).redirect(`/user/${request.params.id}`)
}
})The above code has a get request handler with the path ‘/update-page/:id’. Inside the callback method, we have assigned the SQL query to retrieve a particular user data using user-id. Then we passed that query to the runDBCommand() method and the method returns an array of 1 object. The returned record is passed to the response that renders the update.ejs template. The update page is a form with the values of the user as shown below.
Route for updating a user.
The update page updates the data by clicking on the update button. This button click runs the below function which is inside update.ejs script tag to the below route.
function makeUpdateRequest(){
const fName = document.getElementById("fName").value
const lName = document.getElementById("lName").value
const age = document.getElementById("age").value
const occupation = document.getElementById("occupation").value
makeRequest('/update', 'PUT', '<%= user.id%>',
fName, lName, age, occupation)
}The above method retrieves the value of the input fields with the help of DOM and stores it in the variables fName, lName, age, and occupation . The variables are stored to pass the changed values of the user to the API. Then we have called the makeRequest() method and its definition is as follows:
function makeRequest(path, method, ...data) {
let requestBody = {}
if (method === "PUT") {
requestBody.id = data[0]
requestBody.fName = data[1]
requestBody.lName = data[2]
requestBody.age = data[3]
requestBody.occupation = data[4]
}
fetch(path, {
method: method,
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(requestBody)
})
.then(async (data)=>{
const result = await data.json();
window.location.href = result.redirect
}).catch(async (err)=>{
const result = await err.json();
window.location.href = result.redirect
alert("Internal server error")
console.log(result.message)
})
}The makeRequest() method takes two mandatory arguments that are, path and method, the variable argument data is used to pass any number values in array form. We initialize a requestBody empty object, if the method value is PUT then only we have to add the values that need to be present in the request body, otherwise, it remains empty. Then we make a fetch request to a path that is passed, which in this case is /update and method is equal to themethod argument which is PUT. The requestBody object is parsed in json , it is the body of the request. If the request is successful it executes the then block and inside this block, we have parsed the JSON object to a javascript object, stored it in the result variable, and then redirected the application route to the value result.redirect which is present in the response object. If the request is not successful it will execute the catch block, parse the response, and redirect the application route and then it console logs the received response message.
Now let’s create a put API to update the data, it is as follows:
// Update a user
app.put("/update", async(request,response)=>{
try{
const data = request.body;
const query = `Update user set fName = '${data.fName}', lName = '${data.lName}', age=${data.age}, occupation='${data.occupation}' where id = ${data.id}`
await runDBCommand(query);
response.status(200).json({success: true,
redirect: `/user/${data.id}`})
}
catch(err){
console.log(err)
response.status(500).json({success: false,
redirect: `/update-page/${request.body.id}`,
message: "Internal Server Error"})
}
})In this put request, We have retrieved the data of the record that needs to be updated using request.body and again stored the query to update the document in the query variable. Then we passed the query to the runDBCOmmand() method and then the request returns a JSON object to the makeRequest() method which will request /user/:id route to display updated data
Route for Deleting user data.
On the user data page, there is a button to delete the record. On click of the delete button, the makeRequest() method is called with the following values:
makeRequest('/delete/<%= user.id%>', 'DELETE')Here we have passed the path argument value as the /delete/:id, where we retrieve the user ID using EJS syntax with <%= user.id %>. The value of the method parameter is DELETE to make a delete request. Now let’s create a delete API in the server.js file to delete a user.
// Delete a user
app.delete("/delete/:id", async(request,response)=>{
try{
const query = `delete from user where id = ${request.params.id}`
await runDBCommand(query);
response.status(200).json({success: false, redirect: `/`})
}
catch(err){
console.log(err)
response.status(500).json({success: false,
redirect:`/user/${request.params.id}`})
}
})In the above delete request, we have retrieved the ID of the record that needs to be deleted from the request parameters using request.params.id and again stored the query to delete a document in the query variable. Then we pass the query to the runDBCOmmand() method and once the record is deleted, the request is redirected to the home page.
Conclusion:
In this article, we have seen how to implement CRUD operations using NodeJs and MySQL as backend tech stack. Note that for learning purpose we have used hardcoded SQL queries in the application but in real-world applications we generally use ORM. In the next article, we will see how to implement the same crud operations using MongoDB as a database.
My name is Alfiya Siddique, I am a 3rd-year diploma student studying Computer Science. You can follow me on Linkedin and Twitter.
Thank you for your time, hope you find this article helpful. Have a great day ahead!
