Node.js MySQL CRUD Operation with Example

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

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.

Comments

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

- 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
  - gopal says
    
    September 29, 2020 at 2:42 pm
    
    Getting the below:
    Not Found
    404
    NotFoundError: Not Found
    
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?

- 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
  - RS says
    
    September 29, 2020 at 4:33 pm
    
    The above URLs give me 404. Anything that should be verified prior to running it?
    
    - Noor Khan says
      
      September 29, 2020 at 4:43 pm
      
      read the carefully complete article. After that, execute gain. It will definitely work