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:

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:

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

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:

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:

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:

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:

 

Step #5. Index.js

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

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:

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:

Then open your browser and type the following URL:

http://localhost:8000/

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

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

Then Input Product Name and Price then click Save button.

Then you can see the results like the picture below:

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

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

Then you can see the results like the picture below:

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

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