7 Steps to Create Simple CRUD application using Node.js, Express, and MySQL

7 Steps to Create Simple CRUD application using Node.js, Express, and MySQL

In this tutorial, I will share with you how to create a CRUD (Create Read Update Delete) application using node.js and mysql.

Not only that, I will also share how to use bootstrap modals for input and update form.

So that the CRUD application will be responsive and user friendly.

Let’s get started.

 

Step #1. Introduction

This is important!

In this tutorial, you will learn how to create CRUD operations with node.js and mysql.

If you haven't known node.js before, I recommend you to read the previous post about: Node.js tutorial for beginners.

In this tutorial I will use Bootstrap and Jquery.

If you don't have bootstrap and jquery, please download it on the official website getbootstrap.com and jquery.com.

 

Step #2. Creating Database Structure

Create a new database, here I create a database named crud_db.

If you create it with the same name it's better.

To create a database in MySQL, it can be done by executing the following query:

CREATE DATABASE crud_db;

The SQL command above will create a database with the name crud_db.

Next, create a table in the crud_db database.

Here I create a table named "product".

If you create it with the same name it's better. 

To create a "product" table, it can be done by executing the SQL command below:

CREATE TABLE product(
product_id INT(11) PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(200),
product_price INT(11) 
)ENGINE=INNODB;

The SQL command above will create a "product" table with fields: product_id, product_name, and product_price.

 

Step #3. Install Dependencies

Before installing dependencies, please create a folder, here I create a folder and I named crud.

like the picture below:

Project Folder

The crud folder is our project folder in this tutorial.

Ok, let’s continue, 

In this tutorial, we need 4 dependencies below:

1. Express (node.js framework)

2. MySQL (mysql driver for node.js)

3. Body-parser (middleware for handling post body request)

4. Handlebars (template engine)

To install dependencies on node.js can be done easily using "NPM" (Node Package Manager).

You can run NPM in "Terminal" or "Command Prompt".

But, in this tutorial I don't use Command Prompt, instead I use Git Bash Terminal.

I highly recommend you also use Git Bash Terminal.

You can download Git Bash in the url below:

https://git-scm.com/downloads

Download it according to your platform, then install it on your computer.

If you have installed "Git Bash", please open the crud folder that was created before.

Then right-click and select Git Bash Here, like picture below:

Git Bash Here

Then will appear the git bash terminal like the picture below:

Git Bash Terminal

In the terminal above, you can install all the dependencies that you need for your project.

Before installing dependencies, we need to create package.json file to our project.

To create package.json file, you can run the following command on the terminal.

npm init

Like the picture below:

NPM Init

The above command will automatically create a package.json file on your project.

After that,

Install all the dependencies that you need by typing the following command in the terminal:

npm install --save express mysql body-parser hbs

Like the picture below:

NPM Dependencies

The above command will install all the dependencies that you need: express, mysql, body-parser, and handlebars.

If you open the package.json file, it will look like this:

{
  "name": "crud",
  "version": "1.0.0",
  "description": "Crud Node.js",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "author": "M Fikri",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.18.3",
    "express": "^4.16.3",
    "hbs": "^4.0.1",
    "mysql": "^2.16.0"
  }
}

 

Step #4. Project Structure

Open the crud folder using text editor, here I use ATOM as my text editor.

You can use Sublime Text, Intellij IDEA, or any other editors.

Then create a new folder in the crud folder. Here I create two folders, public and views folder.

Like the picture below:

Project Structure

After that create css and js folder inside the public folder.

Then copy the bootstrap.css file that was previously downloaded into the public/css/ folder.

And copy the bootstrap.js and jquery files in the public/js/ folder.

So that our project structure looks like the picture below:

Structure Project

 

Step #5. Index.js

Create a file named index.js. like the picture below:

Index Js

And then open index.js and type the code below:

//use path module
const path = require('path');
//use express module
const express = require('express');
//use hbs view engine
const hbs = require('hbs');
//use bodyParser middleware
const bodyParser = require('body-parser');
//use mysql database
const mysql = require('mysql');
const app = express();

//Create connection
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'crud_db'
});

//connect to database
conn.connect((err) =>{
  if(err) throw err;
  console.log('Mysql Connected...');
});

//set views file
app.set('views',path.join(__dirname,'views'));
//set view engine
app.set('view engine', 'hbs');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
//set public folder as static folder for static file
app.use('/assets',express.static(__dirname + '/public'));

//route for homepage
app.get('/',(req, res) => {
  let sql = "SELECT * FROM product";
  let query = conn.query(sql, (err, results) => {
    if(err) throw err;
    res.render('product_view',{
      results: results
    });
  });
});

//route for insert data
app.post('/save',(req, res) => {
  let data = {product_name: req.body.product_name, product_price: req.body.product_price};
  let sql = "INSERT INTO product SET ?";
  let query = conn.query(sql, data,(err, results) => {
    if(err) throw err;
    res.redirect('/');
  });
});

//route for update data
app.post('/update',(req, res) => {
  let sql = "UPDATE product SET product_name='"+req.body.product_name+"', product_price='"+req.body.product_price+"' WHERE product_id="+req.body.id;
  let query = conn.query(sql, (err, results) => {
    if(err) throw err;
    res.redirect('/');
  });
});

//route for delete data
app.post('/delete',(req, res) => {
  let sql = "DELETE FROM product WHERE product_id="+req.body.product_id+"";
  let query = conn.query(sql, (err, results) => {
    if(err) throw err;
      res.redirect('/');
  });
});

//server listening
app.listen(8000, () => {
  console.log('Server is running at port 8000');
});

 

Step #6. View

Create a file named product_view.hbs in the views folder. Like the following picture:

Product View

And then open product_view.hbs and type the following code:

<html lang="en">
<head>
	<meta charset="utf-8">
	<title>CRUD Node.js and Mysql</title>
  <link href="/assets/css/bootstrap.css" rel="stylesheet" type="text/css"/>
</head>
<body>
  <div class="container">
    <h2>Product List</h2>
		<button class="btn btn-success" data-toggle="modal" data-target="#myModalAdd">Add New</button>
    <table class="table table-striped" id="mytable">
      <thead>
        <tr>
          <th>Product ID</th>
          <th>Product Name</th>
          <th>Price</th>
          <th>Action</th>
        </tr>
      </thead>
      <tbody>
         {{#each results}}
        <tr>
          <td>{{ product_id }}</td>
          <td>{{ product_name }}</td>
          <td>{{ product_price }}</td>
          <td>
            <a href="javascript:void(0);" class="btn btn-sm btn-info edit" data-id="{{ product_id }}" data-product_name="{{ product_name }}" data-product_price="{{ product_price }}">Edit</a>
            <a href="javascript:void(0);" class="btn btn-sm btn-danger delete" data-id="{{ product_id }}">Delete</a>
          </td>
        </tr>
        {{/each}}
      </tbody>
    </table>
  </div>

	<!-- Modal Add Product-->
	  <form action="/save" method="post">
        <div class="modal fade" id="myModalAdd" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
         <div class="modal-dialog" role="document">
           <div class="modal-content">
             <div class="modal-header">
               <h5 class="modal-title" id="exampleModalLabel">Add New Product</h5>
               <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                 <span aria-hidden="true">&times;</span>
               </button>
             </div>
             <div class="modal-body">
               <div class="form-group">
                   <input type="text" name="product_name" class="form-control" placeholder="Product Name" required>
               </div>

               <div class="form-group">
                   <input type="text" name="product_price" class="form-control" placeholder="Price" required>
               </div>
             </div>
             <div class="modal-footer">
               <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
               <button type="submit" class="btn btn-primary">Save</button>
             </div>
           </div>
         </div>
        </div>
	 </form>

	 <!-- Modal Update Product-->
   <form action="/update" method="post">
       <div class="modal fade" id="EditModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
        <div class="modal-dialog" role="document">
          <div class="modal-content">
            <div class="modal-header">
              <h5 class="modal-title" id="exampleModalLabel">Edit Product</h5>
              <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                <span aria-hidden="true">&times;</span>
              </button>
            </div>
            <div class="modal-body">
              <div class="form-group">
                  <input type="text" name="product_name" class="form-control product_name" placeholder="Product Name" required>
              </div>

              <div class="form-group">
                  <input type="text" name="product_price" class="form-control price" placeholder="Price" required>
              </div>
            </div>
            <div class="modal-footer">
              <input type="hidden" name="id" class="product_id">
              <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
              <button type="submit" class="btn btn-primary">Update</button>
            </div>
          </div>
        </div>
       </div>
  </form>

	 <!-- Modal Delete Product-->
 	  <form id="add-row-form" action="/delete" method="post">
 	     <div class="modal fade" id="DeleteModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
 	        <div class="modal-dialog">
 	           <div class="modal-content">
 	               <div class="modal-header">
									 	<h5 class="modal-title" id="myModalLabel">Delete Product</h5>
 	                  <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
 	               </div>
 	               <div class="modal-body">
												 <strong>Are you sure to delete this data?</strong>
 	               </div>
 	               <div class="modal-footer">
									 		<input type="hidden" name="product_id" class="form-control product_id2" required>
 	                   	<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
 	                  	<button type="submit" class="btn btn-success">Delete</button>
 	               </div>
 	      			</div>
 	        </div>
 	     </div>
 	 </form>

<script src="/assets/js/jquery-3.3.1.js"></script>
<script src="/assets/js/bootstrap.js"></script>
<script>
    $(document).ready(function(){
			//showing data to edit modal      $('#mytable').on('click','.edit',function(){
        var product_id = $(this).data('id');
        var product_name = $(this).data('product_name');
        var product_price = $(this).data('product_price');
        $('#EditModal').modal('show');
        $('.product_name').val(product_name);
        $('.price').val(product_price);
        $('.product_id').val(product_id);
      });
			//showing delete record modal
			$('#mytable').on('click','.delete',function(){
        var product_id = $(this).data('id');
        $('#DeleteModal').modal('show');
        $('.product_id2').val(product_id);
      });

    });
</script>
</body>
</html>

 

Step #7. Testing

Test the application to make sure the crud application that we create runs well.

To run the application, type the following command on the terminal.

node index

Then it will appear on the console message "Server is running at port 8000" and "Mysql Connected".

Like the picture below:

Node Index

Then open your browser and type the following URL:

http://localhost:8000/

If running well, then it will look like the following picture:

Product List

Click the "Add New" button, then will appear "Add New Product" modal like the picture below:

Add New Product

Then Input Product Name and Price then click Save button.

Then you can see the results like the picture below:

Product List Saved

Click the Edit button to edit the record, then will appear the "Edit Product" modal like the picture below:

Edit Modals

Edit the data you want to edit, then click the Update button.

Then you can see the results like the picture below:

Product Updated

To delete a record, click the Delete button, then will appear the confirmation modal as follows:

Delete Modals

Then click the Delete button to delete the record.

Done.

 

Conclusion:

Today's tutorial is about how to build a CRUD application (Create Read Update Delete) using node.js and mysql.

In this tutorial, you have learned how to make CRUD applications with node.js and mysql.

Not only that, you have also learned how to use bootstrap modals for input and update forms.

So what are you waiting for. Let's coding!

Download Source Code

Share:



Sponsorship:


Recommended for you


Comments (7)

RobertC, 08 February 2019 13:01 -

Sorry to write you here, but the contact menu is not linked to anything. Just wanted to warn you that in the home page of your website, the latest from the blog are not shown Thank you for your awesome tutorials Robert

M Fikri, 31 March 2019 10:23 -

Thank's Robert for your feedback.
Now, I have done it.

mehul, 13 July 2019 18:55 -

Thanks

Mark Scandura, 15 November 2020 10:40 -

excellent simple tutorial, Thanks!

Francesco, 28 May 2021 15:36 -

Hi MFikri, thank you very much for the beautiful tutorial; I am a beginner and would like to understand how to deploy the app on a production server. Thank you.

Bloom, 05 July 2021 14:13 -

I used cdn and it works the same. Thanks for this awesome tutorial! :)

Leave a Comment