How To Create a CRUD Application Using Express And MySQL

Node Js and Express are technology stacks for developing APIs and backend services. Usually, we need a backend database. There are several relational databases available in the development market. And My SQL is one of them.

In this blog, you will learn to develop a simple Node.js MySQL CRUD application to perform all the operations (Create, Read, Update, and Delete) on a MySQL database table in one place in the Node.js Express app.

Now, we will start the process.

Step-1: Install Npm And Node.Js

Go to the node.js website

https://nodejs.org/en/

and install node.js

Step-2: Install Express Framework

Open your terminal and run the following command to install express:

express CrudeDemo --view ejs

As you can see express creates some folders and files,

Now, select to project directory; i.e CrudeDemo.

cd CrudeDemo

Now, we are in our project directory. So first we have to install the npm in our project.

cd CrudeDemo

Now, the project setup is ready.

Run the following command to start the server.

npm start

Go to http://localhost:3000/

Now, we can make routes and view as per our requirement.

But, one problem is there! We have to restart the server on every single change. So to prevent from restarting the server read the next section.

Ste -3: Install Nodemon

Run the following command in a Terminal to install nodemon.

npm install -g nodemon

Now, open the package.json file and add the below line.

"devstart": "nodemon ./bin/www"

Step- 4: Run Devstart

Now, run the following command in the Terminal to run the project.

npm run devstart

You can make some changes in the index file and see the changes by page refresh(F5).

Step -5: Create One Route

Open app.js file and create one route.

Go to http://localhost:3000/crud

Here, aap is an object of express module. Get is a method. Function taking two arguments req and res for request and response data.

Step -6: Make View

Make a file insert.ejs as follow.

Step-7: Render The File

Here, render() is used for render the file insert.ejs. In browser when we hit the http://localhost:3000/crud the route, the screen display one form of employee as shown below.

Now, our layout file is ready. Now save the data into the database. Before store data, we’ll check the response.

And yes, you can get the response. Now store data into the database.

For that, we have to make connectivity with database. For that, we make a file config.js in our project.

Step-8: Install Mysql In Project

npm install MySQL

Mysql Setup is successfully installed. Now, make an object of MySQL in config.js file. Write the code into config.js as given below.

Include config file in app.js.

var pool=require ("./config");

You can see that our database is connected.

Step-9: Store Submitted Data Into Table.

You can see, our data is inserted successfully in the database.

Step -10: Display The Stored Data

Define a route that render the view of data. Write the below code into app.js

Here, the pool is an object of a config file. It is used for database related operations. Such as Insert, Update and delete. As you can err adn result these two parameters are used. Err for error and result for response of query. If query executed successfully then route render to the view file show.ejs.

Step-11: Make A View

Make another file show.ejs in views folder.

You can see the view, browse http://localhost:3000/show

Now, Data is retrieved successfully.

Step -12: Update The Data

Write the below code into app.js.

View Raw

Here, the edit is variable that indicates the, it is a request for edit. While we click on edit it will send the id of a user. If the user id is found then it will fill fetch that user’s data and display on view. Now, we can edit these data.

Update.Ejs:

View Raw

Before Edit:

Now, Save The edited data into the database.

View Raw

When we submit the data server send the response like these:

Now, confirm that data is updated or not! http://localhost:3000/show

Data is updated successfully as shown in the image.

Step -13: Delete The Data

Write the following code into app.js.

Now you can see the response data deleted successfully.

Note

If you don’t want to display response than redirect your as shown below.

return res.redirect('/show');

View Raw

Conclusion:

This step-by-step guide will walk you through database connectivity, basic CRUD operations, and web application development. And if you get stuck at any point or confused, try using the source code given in this blog. That will be helpful to you.