How to Update Data Using Node.js and MySQL

This tutorial will help you to update the data of the MYSQL table row. If you want to use MySQL with Node.js, it will be very useful for your project. Even you will get the best & easy Node.js MySQL Update Query with a Simple example. So, Read all the next steps and use the given concept in your project.

Node.js MySQL Update Data

Before getting started, You must insert data into the MySQL database and display data on the page. If you have done both, you need not create them again.

1. Install Express Application

You have to Install Basic Express Application like the following project folder structure

myapp/
  |__routes/
  |    |__users.js
  |__views/
  |    |__users-form.ejs
  |__app.js
  |__database.js
  |

Note – If you have already installed the express application then you need not do it again

2. Connect Node.js App to MySQL

You must connect the Node.js app to the MySQL database

File Name – database.js

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

Make sure that the database  & the table are created with the name of nodeapp and users respectively

Note – If you have already done this step then you need not do it again

3. Create Routes to Edit & Update Data

Configure the following points to create routes for inserting data –

• Include database connection file database.js
• Create a route /edit/:id with the GET method to display data in the HTML form
• Create another route /edit/:id with the POST method to update data

File Name – users.js

var express = require('express');
var router = express.Router();
var db=require('../database');
// write here create & display data script
 
router.get('/edit/:id', function(req, res, next) {
      var UserId= req.params.id;
      var sql=`SELECT * FROM users WHERE id=${UserId}`;
      db.query(sql, function (err, data) {
        if (err) throw err;
       
        res.render('users-form', { title: 'User List', editData: data[0]});
      });
});
router.post('/edit/:id', function(req, res, next) {
  var id= req.params.id;
    var updateData=req.body;
    var sql = `UPDATE users SET ? WHERE id= ?`;
    db.query(sql, [updateData, id], function (err, data) {
    if (err) throw err;
    console.log(data.affectedRows + " record(s) updated");
  });
  res.redirect('/users/user-list');
});
module.exports = router;

4. Load Route Into the Root File

You have to include user.js route file in  app.js the root file as

File Name – app.js

var usersRouter = require('./routes/users');
;
app.use('/users', usersRouter);

Note – If you have already loaded the above route then you can skip this step.

5. Create Edit Button

You have to create an edit button for each record in the HTML table. So, to do it, you have to display all the records in an HTML table and just add the following line in the user-list.ejs –

<td><a href="/users/edit/<%=data.id%>">Edit</a></td>

6. Display Data in HTML Form

Use the following code in the views/users.ejs file. This code will help you edit and update data from the form.

Path – users-form.ejs

<!--====form section start====-->
<div class="user-detail">
   
    <form action="<%=(typeof editData!='undefined')?'/users/edit/'+editData.id:'/users/create'%>" method="POST">
          <label>Full Name</label>
          <input type="text" placeholder="Enter Full Name" name="fullName" required value="<%=(typeof editData!='undefined')? editData.fullName:''%>">
          <label>Email Address</label>
          <input type="email" placeholder="Enter Email Address" name="emailAddress" required value="<%=(typeof editData!='undefined')? editData.emailAddress:''%>">
          <label>City</label>
          <input type="city" placeholder="Enter Full City" name="city" required value="<%=(typeof editData!='undefined')? editData.city:''%>">
          <label>Country</label>
          <input type="text" placeholder="Enter Full Country" name="country" required value="<%=(typeof editData!='undefined')? editData.country:''%>">
          <button type="submit">Submit</button>
        </form>
</div>
<!--====form section start====-->

6. Run Node.js Code to update data 

To Run Node.js code for updating data from the form, you will have to do the following things –

• First of all, Start your Node.js server using npm start command
• Enter the following URL into your web browser to display records in an HTML table

http://localhost:3000/users/user-list

After displaying data in the HTML form, you will have to click the edit button to update the record based on its id.

When you click the edit button, you will be redirected to the HTML form. The data will be displayed in that form based on the passing id from the edit button.

After that, When you write new values and submit the form then the record will be updated successfully

My Suggestion

Dear developers, I hope you have understood the above script, Now you are able to update data using Node.js & MySQL. Even you can update another table data by using the above steps.

If you have any questions or suggestions regarding Node.js. You can directly ask me through the below comment box.