CodingStatus

- Level Up Your Status with Coding Expertise!

Node.js MySQL CRUD Operation with Example

By

I have shared some simple steps to create a 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

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-controller.js
  |     |__read-controller.js
  |     |__update-controller.js
  |     |__delete-controller.js
  |__models/
  |     |__create-model.js
  |     |__read-model.js
  |     |__update-model.js
  |     |__delete-model.js
  |__routes/
  |     |__create-route.js
  |     |__read-route.js
  |     |__update-route.js
  |     |__delete-route.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 a table with the name  crud into the MySQL database nodeapp as the following query

Table Name – users

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.

File Name – create-model.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.

File Name – create-controller.js

var createModel=require('../models/create-model');
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.

File Name – crud-form.ejs

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style type="text/css">

.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>
</head>
<body>
<!--====form section start====-->
<!--====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====-->
<!--====form section start====-->

</body>
</html>

 

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

File Name – create-route.js

var express = require('express');
var createController=require('../controllers/create-controller');
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 queries to fetch data from the crud table.
    • callback – It will return the fetched value within itself.

File Name – read-model.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

File Name – read-controller.js

var readModel=require('../models/read-model');
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.

File Name – 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">
  <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>
</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>

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

File Name – read-route.js

var express = require('express');
var readController=require('../controllers/read-controller');
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.

File Name – update-model.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

File Name – update-controller.js

var updateModel=require('../models/update-model');
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 existing input values based on the specified id.

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

File Name – crud-form.ejs

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 the 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

File Name – update-route.js

var express = require('express');
var updateController=require('../controllers/update-controller');
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 queries 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.

File Name – delete-model.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.

File Name – delete-controller.js

var deleteModel=require('../models/delete-model');
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

File Name – delete-route.js

var express = require('express');
var deleteController=require('../controllers/delete-controller');
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.

File Name – app.js

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


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 a 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 Code Snippets