User Avatar

Updating Data in MySQL Database from Node.js

Created by swightJohn - 10-05-2019 at 09:11 PM

Junior Member

Posts:
8
Joined:
Sep 2019
Likes:
0
Credits:
15
Reputation:
0
Vouches:
#1
Posted: 10-05-2019, 09:11 PM
To update data from a node.js application, you use the following steps:
  • Connect to the MySQL database server
  • Execute an UPDATE statement by calling the query() method on a Connection object.
  • Close the database connection.
To connect to the MySQL database, we will use the following config.js module that contains the necessary information of the MySQL database server including host, user, password, and database.
Code:
let config = {
 host    : 'localhost',
 user    : 'root',
 password: '',
 database: 'todoapp'
};

module.exports = config;


Updating data example
The following update.js program updates the status of a todo based on a specific id.
Code:
let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

// update statment
let sql = `UPDATE todos
          SET completed = ?
          WHERE id = ?`;

let data = [false, 1];

// execute the UPDATE statement
connection.query(sql, data, (error, results, fields) => {
 if (error){
   return console.error(error.message);
 }
 console.log('Rows affected:', results.affectedRows);
});

connection.end();

In this example, we used placeholders (?) in the UPDATE statement.

When we executed the UPDATE statement by calling the query() method on the connection object, we passed the data to the UPDATE statement in the form of an array. The placeholders will be substituted by the values in the data array in the order. In this example, completed will be set to false, and id will be set to 1.

The results argument of the callback function has the affectedRows property that returns the number of rows updated by the UPDATE statement.
Before executing the program, let check the row with id 1 in the todos table:
Code:
mysql> SELECT * FROM todos WHERE id = 1;
+----+-------------------------------+-----------+
| id | title                         | completed |
+----+-------------------------------+-----------+
|  1 | Learn how to insert a new row |         1 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)


Let’s run the update.js program.
Code:
>node update.js
Rows affected: 1


The program returned a message indicating that the number of affected rows is 1. We can check it again in the database as follows:
Code:
mysql> SELECT * FROM todos WHERE id = 1;
+----+-------------------------------+-----------+
| id | title                         | completed |
+----+-------------------------------+-----------+
|  1 | Learn how to insert a new row |         0 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)


As you can see, the value in the completed column has been updated to 0, which is false in node.js
In this tutorial, we have shown you how to update data in MySQL from a node.js application.

AdvBot
Advertising Bot

Joined:
Long Time

Advertisment


Make sure to read Forum Advertisment before posting and get the content.
Optional: You can also become a Premium Member and the advertisment not show.
Register an account or login to reply
Create an account
Create a free account today and start posting right away. It only takes a few seconds.
Login
Log into an existing account.
1 Guest(s)