• Skip to main content
  • Skip to secondary menu
  • Skip to primary sidebar
  • About Us
  • Privacy Policy
  • Disclaimer
  • Terms And Conditions
  • Contact Us

CodingStatus

- Learn to Build Web Applications

  • Home
  • HTML
  • CSS
  • JavaScript
  • jQuery
  • Ajax
  • Node.js
  • PHP
  • Tips

Node.js MySQL CRUD Operation with Example

November 12, 2020 By Noor Khan 7 Comments

I have shared some simple steps to create Node.js MySQL CRUD operation with an example. The CRUD app performs mainly four operations like create, read, update & delete. All these operations are the most important to build any web applications.

For your better understanding, you should see the following points –

  • C: CREATE – To insert new data into the database
  • R: READ  –    To display data on the web page
  • U: UPDATE – To update existing data in the database
  • D: DELETE – To delete existing data from the database

If you want to see the node js crud example with MySQL then you must read all the given points in this tutorial. So that you will be able to implement it in your project.

node.js mysql crud operation

Contents

  • Create CRUD Operations Using Node.js, Express & MySQL
    • Install Express Application
    • Create MySQL Database & Table
    • Connect Node.js App to MySQL Database
  • Create Data Using Node.js and MySQL
    • The Model to create data
    • The Controller to create data
    • The View to create data
    • The Route to create data
  • Read Data Using Node.js and MySQL
    • The Model to read data
    • The Controller to read data
    • The View to read data
    • The Route to read data
  • Update Data Using Node.js and MySQL
    • The Model to update data
    • The Controller to update data
    • The View to update data
    • The Route to update data
  • Delete Data Using Node.js and MySQL
    • The Model to delete data
    • The Controller to delete data
    • The Route to delete data
  • Load all Routes in App.js
  • Run Node.js CRUD Operation
  • My Suggestion

Create CRUD Operations Using Node.js, Express & MySQL

Before, creating Node.js MySQL CRUD App, you must configure all the following steps –

Install Express Application

First of all, Install Basic Express Application and create the following necessary folders & files. Here, default folders & files of the express application are not shown.

nodeapp/
  |__controllers/
  |     |__create.js
  |     |__read.js
  |     |__update.js
  |     |__delete.js
  |__models/
  |     |__create.js
  |     |__read.js
  |     |__update.js
  |     |__delete.js
  |__routes/
  |     |__create.js
  |     |__read.js
  |     |__update.js
  |     |__delete.js
  |__views/
  |     |__crud-form.ejs
  |     |__create-table.ejs
  |__app.js
  |__database.js

Create MySQL Database & Table

Create a Database with the name nodeapp using the following query

CREATE DATABASE nodeapp

 

Create a table with the name  crud into the MySQL database nodeapp as the following query

CREATE TABLE `users` ( 
 `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, 
 `full_name` varchar(255) DEFAULT NULL, 
 `email_address` varchar(255) DEFAULT NULL, 
 `city` varchar(255) DEFAULT NULL, 
 `country` varchar(255) DEFAULT NULL 
)

Connect Node.js App to MySQL Database

  • First, make sure the MySQL module must be installed
  • Create database.js file in the root folder
  • Write the following script to connect Node.js express to MySQL Database.

Here is the script of Node.js MySQL database connection 

File Name – database.js

var mysql = require('mysql');
var conn = mysql.createConnection({
  host: 'localhost', 
  user: 'root',      
  password: '',     
  database: 'nodeapp'
}); 
conn.connect(function(err) {
  if (err) throw err;
  console.log('Database is connected successfully !');
});
module.exports = conn;

Create Data Using Node.js and MySQL

Now, We are going to configure the first part of the CRUD operation. In this part, we have to create data into the database using Node.js & MYSQL. So, we must implement all the following steps –

The Model to create data

  • Include database connection file database.js
  • Create a method createData with two parameters like inputData & callback and write SQL query to insert data into the crud table.
    • inputData – It will take input values from the createData method of controllers/create.js
    • callback – It will return the created value within itself.

Path – models/create.js

var db=require('../database');
module.exports={ 

  createData:function(inputData,callback)
    var sql = 'INSERT INTO crud SET ?'; 
    db.query(sql, inputData,function (err, data) {
    if (err) throw err;
      return callback(data);
    });
  }

}

The Controller to create data

  • Include the model file create.js using require('../models/create') and assign to a variable createModel.
  • Create crudForm method to display crud form in the browser
  • Also, Create a method createData with two parameters like req res and configure the following steps –
    • Create an object inputData with all input values coming from the form
    • Pass inputData to the createData method of the model createModel
    • After creating data successfully, redirect to the form.

Path – controllers/create.js

var createModel=require('../models/create');
module.exports={
crudForm:function(req, res) {
    res.render('crud-form');
},
createData:function(req,res){
  const inputData= {
    full_name:     req.body.full_name,
    eamil_address: req.body.email_address,
    city :         req.body.city,
    country :      req.body.country
  };
createModel.createData(inputData,function(data){
      res.redirect('/crud/form');
      console.log(data.affectedRows + " record created");
    });
}

}

The View to create data

Create a CRUD form with the following input attributes –

 Field Name  Type Attribute  name attribute
 full Name  type=”text”  name=”full_name”
 Email Address  type=”email”  name=”email_address”
 City  type=”text”  name=”city”
 Country  type=”text”  name=”country”

Also, you must declare the following attributes –

  • method =”post”
  • action =”/crud/create”

Now, use the following complete HTML code to create a CRUD form.

Path – views/crud-form.ejs

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<!--====form section start====-->
<div class="crud-form">
  <form action="/crud/create" method="POST">
     <input type="text" placeholder="Full Name" name="full_name" required>
          
     <input type="email" placeholder="Email Address" name="email_address" required>
          
     <input type="city" placeholder=" City" name="city" required>
          
     <input type="text" placeholder="Country" name="country" required>
          <button type="submit">Submit</button>
        </div>
</div>
<!--====form section start====-->

</body>
</html>

 

You can use the following CSS code to design the CRUD form.

<style>
.crud-form {
  height: 100vh;
  border: 2px solid #f1f1f1;
  padding: 16px;
  background-color: white;
  width: 30%;}
input{
  width: 100%;
  padding: 15px;
  margin: 5px 0 22px 0;
  display: inline-block;
  border: none;
  background: #f1f1f1;}
input[type=text]:focus, input[type=password]:focus {
    background-color: #ddd;
    outline: none;}
button[type=submit] {
    background-color: #434140;
    color: #ffffff;
    padding: 10px 20px;
    margin: 8px 0;
    border: none;
    cursor: pointer;
    width: 100%;
    opacity: 0.9;
    font-size: 20px;}
button[type=submit]:hover {
  background-color:#3d3c3c;}
 
</style>

The Route to create data

  • Include express module & access a router from it.
  • Also, include a controller file create.js using require('../controllers/create') assign it to createController
  • Create a route  /form to display the CRUD form.
  • Also, create another route /create to insert data into the database. It will call when you submit the CRUD form
  • At last, export the router

Path – routes/create.js

var express = require('express');
var createController=require('../controllers/create');
var router = express.Router();

// to display form
router.get('/form', createController.crudForm );

// to create data
router.post('/create', createController.createData);

module.exports = router;

Read Data Using Node.js and MySQL

Now, We are going to configure the second part of the CRUD operation. In this part, we have to read/display data from the database using Node.js & MYSQL. So, we must implement all the following steps –

The Model to read data

  • Include database connection file database.js
  • Create a method readData with a parameter like  callback and write SQL query to fetch data from the crud table.
    • callback – It will return the fetched value within itself.

Path – models/read.js

var db=require('../database');
module.exports={
  readData:function(callback){
    var sql='SELECT * FROM crud';
    db.query(sql, function (err, data, fields) {
    if (err) throw err;
    return callback(data);
    });  
  }
 
}

 

The Controller to read data

  • Include the model file read.js using require('../models/read') and assign to a variable readModel.
  • Create a method readData with two parameters like req res and configure the following steps –
    • Create an object inputData with all input values coming from the form
    • Fetch data using readData method of the model createModel
    • Render and send fetched data to the crud table

Path – controllers/read.js

var readModel=require('../models/read');

module.exports={
 readData:function(req,res){
    
    readModel.readData(function(data){
    res.render('crud-table', {fetchData:data}); 
    });
  }

}

The View to read data

Now create a CRUD table in the views folder and write HTML code to create a table. After that, display all the records form the crud database using fetchData that comes from /read route.

Path – views/crud-table.ejs

<!DOCTYPE html>
<html lang="en">
<head>
  <title></title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
    <div class="table-data">
    <table border="1" >
        <tr>
            <th>S.N</th>
            <th>Full Name</th>
            <th>Email Address</th>
            <th>City</th>
            <th>Country</th>
            <th>Edit</th>
            <th>Delete</th>
        </tr>
        
        <%
        if(fetchData.length!=0){
        var i=1;
        fetchData.forEach(function(data){
        %>
        <tr>
            <td><%=i; %></td>
            <td><%=data.full_name %></td>
            <td><%=data.email_address %></td>
            <td><%=data.city %></td>
            <td><%=data.country %></td>
            <td><a href="/crud/edit/<%=data.id%>">Edit</a></td>
            <td><a href="/crud/delete/<%=data.id%>">Delete</a></td>
        </tr>
        <%  i++; }) %>
        <% } else{ %>
            <tr>
                <td colspan="7">No Data Found</td>
            </tr>
        <% } %>
    </table>
    </div>
</body>
</html>

 

You can use the following CSS code to design a CRUD form.

<style>
table, td, th {  
 border: 1px solid #ddd;
 text-align: left;
}
table{
 border-collapse: collapse;
 width: 50%;
}
.table-data{
  overflow-x:auto;
}
th, td {
  padding: 15px;
}
 </style>

The Route to read data

  • Include express module & access a router from it.
  • Also, include a controller file create.js using require('../controllers/read') assign it to readController
  • Create a route  /read to display the CRUD table
  • At last, export the router

Path – routes/read.js

var express = require('express');
var readController=require('../controllers/read');
var router = express.Router();

// to display data 
router.get('/read', readController.readData);

module.exports = router;

Update Data Using Node.js and MySQL

Now, We are going to configure the third part of the CRUD operation. In this part, we have to update data from the database using Node.js & MYSQL. So, we must implement all the following steps –

The Model to update data

Configure the following points to edit data

  • Include database connection file database.js
  • Create a method editData with two parameters like editId & callback and write SQL query to fetch data based on editId from the crud table.
    • editId – It will take input values from the editData method of controllers/update.js
    • callback – It will return the fetched value within itself.

Also, configure the following points to update data

  • Create a method updateData with three parameters like inputData updateId & callback and write SQL query to update data based on updateId from the crud table.
    • inputData – It will take input values from the createData method of controllers/create.js
    • updateId – Also, It will take input values from the updateData method of controllers/update.js
    • callback – It will return the updated value within itself.

Path – models/update.js

var db=require('../database');
module.exports={

  editData:function(editId, callback){
    
    var sql=`SELECT * FROM crud WHERE id=${editId}`;
    db.query(sql, function (err, data) {
      if (err) throw err;
      return callback(data[0]);
    });

  },
  updateData:function(inputData,updateId,callback){
    
    var sql = `UPDATE crud SET ? WHERE id= ?`;
    db.query(sql, [inputData, updateId], function (err, data) {
    if (err) throw err;
     return callback(data);
  });
  }
}

The Controller to update data

Configure the following steps to display the existing record based on editId in the form

  • Include the model file update.js using require('../models/update') and assign to a variable updateModel.
  • Create a method editData with two parameters like req res and configure the following steps –
    • Get an id using req.params.id and assign it to the editId
    • Pass editId to the updateData of model updateModel.
    • Render and send fetched data through editData to the crud form.

Also, configure the following steps to update the existing record based on updateId

  • Create a method updateData with two parameters like req res and configure the following steps –
    • Create an object inputData with all input values coming from the form
    • Get an id using req.params.id and assign it to the updateId
    • Pass inputData & updateId to the updateData of model updateModel.
    • After that, redirect to the crud table page

Path – controllers/update.js

var updateModel=require('../models/update');


module.exports={

editData:function(req,res){

    const editId=req.params.id;
    updateModel.editData(editId,function(data){
      res.render('crud-form', { editData:data});
      console.log(data.affectedRows + " record fetched");
    });
   
},
updateData:function(req,res){

  const inputData= { 
   full_name: req.body.full_name, 
   eamil_address: req.body.email_address, 
   city : req.body.city, 
   country : req.body.country 
  }; 
  const updateId=req.params.id;
  updateModel.updateData(inputData,updateId,function(data){
    res.redirect('/crud/read');
    console.log(data.affectedRows + " record(s) updated");

  }); 
}

}

The View to update data

You can use the CRUD form to update data. So, when you click the edit button then it will display with existing input values based on the specified id.

Finally, you just replace the existing code of CRUD form with the following HTML code. Don’t worry this code will work for both creating & updating data.

Path – views/crud-form.ejs

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<!--====form section start====-->
<div class="crud-form">
  <form action="<%=(typeof editData!='undefined')?'/crud/update/'+editData.id:'/crud/create'%>" method="POST">
     <input type="text" placeholder="Full Name" name="full_name" required value="<%=(typeof editData!='undefined')? editData.full_name:''%>">
          
     <input type="email" placeholder="Email Address" name="email_address" required value="<%=(typeof editData!='undefined')? editData.email_address:''%>">
          
     <input type="city" placeholder=" City" name="city" required value="<%=(typeof editData!='undefined')? editData.city:''%>">
          
     <input type="text" placeholder="Country" name="country" required value="<%=(typeof editData!='undefined')? editData.country:''%>">
          <button type="submit">Submit</button>
        </div>
</div>
<!--====form section start====-->

</body>
</html>

The Route to update data

  • Include express module & access a router from it.
  • Also, include a controller file create.js using require('../controllers/update') assign it to updateController
  • Create a route /edit/:id to fetch data based on id that must be a number. It will call when you click edit button in the CRUD table
  • Also, create another route /update/:id to update data based on id that must be a number. It will call when you submit the CRUD form
  • At last, export the router

Path – routes/update.js

var express = require('express');
var updateController=require('../controllers/update');
var router = express.Router();

// to edit data 
router.get('/edit/:id', updateController.editData);

// to update data 
router.post('/update/:id', updateController.updateData);

module.exports = router;

Delete Data Using Node.js and MySQL

Now, We are going to configure the fourth part of the CRUD operation. In this part, we have to delete data from the database using Node.js & MYSQL. So, we must implement all the following steps –

The Model to delete data

  • Create a method deleteData with two parameters like  deleteId & callback and write SQL query to delete data based on deleteId from the crud table.
    • deleteId – It will take input values from the deleteData method of controllers/update.js
    • callback – It will return the deleted value within itself.

Path – models/delete.js

var db=require('../database');
module.exports={

  deleteData:function(deleteId,callback){

    var sql = 'DELETE FROM crud WHERE id = ?';
    db.query(sql, [deleteId], function (err, data) {
    if (err) throw err;
     return callback(data);
  });

  }
}

The Controller to delete data

  • Include the model file delete.js using require('../models/delete') and assign to a variable deleteModel.
  • Create a method deleteData with two parameters like req res and configure the following steps –
    • Get an id using req.params.id and assign it to the deleteId
    • Pass deleteId to the deleteData of model deleteModel.
    • At last, redirect to the crud table.

Path – controllers/delete.js

var deleteModel=require('../models/delete');

module.exports={

deleteData:function(req,res){
   
  const deleteId=req.params.id;
  deleteModel.deleteData(deleteId,function(data){
    res.redirect('/crud/read');
    console.log(data.affectedRows + " record deleted");
  });
 
}

}

The Route to delete data

  • Include express module & access a router from it.
  • Also, include a controller file create.js using require('../controllers/delete') assign it to deleteController
  • Create a route /delete/:id to delete data based on id that must be a number. It will call when you click the delete button in the CRUD table
  • At last, export the router

Path – routes/delete.js

At last, export the router
var express = require('express');
var deleteController=require('../controllers/delete');
var router = express.Router();

// to delete data 
router.get('/delete/:id', deleteController.deleteData);

module.exports = router;

Load all Routes in App.js

Now, you must load all the created routes in the root file app.js. You will get various default code ( not showing here ) in the app.js file while you install the express application.

Now, configure only the following two steps –

Include the following routes just below the default code require('./routes/users')

var createRouter = require('./routes/create');
var readRouter = require('./routes/read');
var updateRouter = require('./routes/update');
var deleteRouter = require('./routes/delete');

 

Also, use the following routes just below the default code app.use('/users', usersRouter);

app.use('/crud', createRouter);
app.use('/crud', readRouter);
app.use('/crud', updateRouter);
app.use('/crud', deleteRouter);

Run Node.js CRUD Operation

You can use the following two main routes to run Node.js CRUD operation –

http://localhost:3000/crud/form    // call it on GET request to display crud form
http://localhost:3000/crud/read   // call it on GET request to display data

My Suggestion

Dear developers, I hope you have understood the above script, Now you are able to develop CRUD Operation using Node.js & MySQL. So, Continue to visit my site, I will share more tutorials related to Node.js / Express as soon as possible.

If you have any doubts or questions. You can directly ask me through the below comment box.

Filed Under: Node.js

You may like also

  1. MongoDB CRUD Operations Using Node.js Express and Mongoose
  2. CRUD Operations in PHP Using MySQL
  3. Express MVC Structure – How to Create Model, View, and Controller in Node.js Express
  4. Create Registration and Login Form in Node.js & MySQL

Hey there, Welcome to CodingStatus. My Name is Noor Khan from Bihar, India. I'm a Software Engineer. I have been working in the Web Technology field for 3 years. Here, I blog about Web Development & Designing. Even I help developers to build the best Web Applications.

Reader Interactions

Comments

  1. gopal says

    September 29, 2020 at 12:34 pm

    The files mentioned above have been created in different folders specified.
    How do we run this application

    Reply
    • Noor Khan says

      September 29, 2020 at 12:48 pm

      Hi Gopal, You can run this application through following steps

      • Run command – npm start
      • Enter the http://localhost:3000/crud/form in your browser
      Reply
      • gopal says

        September 29, 2020 at 2:42 pm

        Getting the below:
        Not Found
        404
        NotFoundError: Not Found

        Reply
  2. RS says

    September 29, 2020 at 2:30 pm

    After entering the above code snippets into respective files in the folders, how do you run this?
    After starting npm and executing app.js, should we typing a URL to get the page? what URL and at which port would it listen?

    Reply
    • Noor Khan says

      September 29, 2020 at 4:04 pm

      You can enter the following url in your browser

      • http://localhost:3000/crud/form – for the HTML form to insert data
      • http://localhost:3000/crud/create – It will redirect when you sumbit form
      • http://localhost:3000/crud/fetch – for displaying data on the page
      Reply
      • RS says

        September 29, 2020 at 4:33 pm

        The above URLs give me 404. Anything that should be verified prior to running it?

        Reply
        • Noor Khan says

          September 29, 2020 at 4:43 pm

          read the carefully complete article. After that, execute gain. It will definitely work

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Follow us

  • facebook
  • twitter
  • linkedin
  • pinterest

Recent Posts

  • How to Learn Web Development Step by Step
  • How to Learn Web Designing From Basics
  • How to Become a Programmer Step By Step
  • 10 Best Google Chrome Extensions for Web Developers
  • Top 10 Web Designing Languages for Beginners

Latest Node.js Tutorials

  • Fetch data from MongoDB Using Mongoose and Node.js Express
  • Node.js Form Validation with an Example
  • Upload Multiple Files Using Multer in Node.js and Express
  • Node.js File Upload Using Express Multer
  • How to Display Image From MySQL Database in Node.js
  • Home
  • About Us
  • Privacy Policy
  • Disclaimer
  • Terms & Conditions
  • Sitemap
  • Contact Us

Copyright © 2021 CodingStatus - All Rights Reserved