Home » MySQL, Node.js22 August 2017

Node.js, Express & MySQL: Simple Add, Edit, Delete, View (CRUD)

This article shows how you can create a simple CRUD (Create, Read, Update, Delete) application in Node.js using MySQL as database. We will also be using Express Framework to build this Node.js web application.

We will be using EJS as templating engine.

We will be using HTTP Methods like GET, PUT, POST, DELETE to make our application RESTful. We will be posting and retrieving data to and from MySQL database in JSON format.

Here’s the step-by-step procedure to create the CRUD application:

1) Open terminal/command-prompt and go to your Node.js project directory. Mine is /var/www/html/test/nodejs/crud-mysql

2) Run npm init command in terminal that will create package.json file for your project.

3) Install Express Framework module with the following command:

4) Install express-validator module that will be used for form validation

5) Install body-parser module

body-parser module is used to read HTTP POST data. It’s an express middleware that reads form’s input and store it as javascript object.

6) Install method-override module

This module let us use HTTP verbs such as PUT or DELETE in places where they are not supported. This module provides different ways of overriding like overriding using header, overriding using query value, or through custom logic. In this CRUD application, we will be using the custom logic of overriding.

7) Install express-flash module

Flash messages are shown for some time and cleared after being displayed to the user. They are mainly used when we have to redirect to next page and show certain message like success message or error message. Flash messages are store in session.

8) Install cookie-parser & express-session modules

Flash messages are stored in session. So, we also have to install and use cookie-parser & session modules.

9) Install EJS templating engine

10) Install MySQL module with the following command:

11) Install express-myconnection module

Express middleware to provide consistent API for MySQL connection. It can auto close/release mysql connection. Using this module, the connection can be accessed anywhere in router.

12) We will now create a new MySQL database. Let us name the database as ‘test‘.

Then, we will create a new table in database ‘test’. Let us name the table as ‘users‘.

13) Create a config.js file in your project root folder. This file basically contains your database credentials.

config.js

14) Create app.js in your project root folder. In this file, we load all the modules that we installed in above steps.

app.js

15) As you can see above app.js code, we have loaded two local files/modules: routes/index.js and routes/users.js. These two files contains code to show content of index and users page.

routes/index.js

16) routes/users.js is responsible for handling CRUD operations like adding, editing, deleting and viewing users from database. Database queries, form validation and template rendering is done here.

routes/users.js

17) In routes/index.js file, we have only rendered views/index.ejs template with a title text.

views/index.ejs

18) You can see that in views/index.js, we have only included two layouts files. Here are they:

views/layouts/header.ejs

views/layouts/footer.ejs

19) In routes/users.js file, there are different functions to add, edit, delete and view data. The first one is to show the list of users. This uses views/user/list.ejs template file to show the list of users.

views/user/list.ejs

20) Add New User Form

views/user/add.ejs

21) Edit User Form

views/user/edit.ejs

As you see in app.js code, our server runs on port 3000. Hence, to use the application, we have to run the following URL on browser: http://127.0.0.1:3000

http://127.0.0.1:3000 = Shows index page which just contains a simple text title
http://127.0.0.1:3000/users = Shows list of users
http://127.0.0.1:3000/users/add = Shows form to add new user
http://127.0.0.1:3000/users/edit/ID = Shows form to edit user. ID can be any number.

Here’s the GitHub Repository containing all source code of this CRUD application: https://github.com/chapagain/nodejs-mysql-crud

Hope this helps. Thanks.

Node.js

Get New Post by Email

Find me on

FacebookTwitterGoogle+LinkedInRSS Feed