Updating MySQL Records with Node.js: A Practical Guide to Modifying Database Data

Learn how to update records in a MySQL database using Node.js. This tutorial provides a step-by-step guide, demonstrating the `UPDATE` command with a `WHERE` clause, and emphasizes the importance of error handling and parameterized queries for secure and efficient database operations.

Updating Records in MySQL Using Node.js

Introduction

This tutorial demonstrates updating records in a MySQL database using Node.js. We'll use the `UPDATE` SQL command with a `WHERE` clause to target specific rows for modification.

Prerequisites

• Node.js and npm installed.
• MySQL server running.
• MySQL Connector/Node.js installed (`npm install mysql`).
• A database named `tutorialsarena`.
• A table named `employees` with columns `id`, `name`, `age`, and `city` (populated with sample data).

Updating a Record

This example updates the city of the employee with id '1' from 'Allahabad' to 'Delhi':

const mysql = require('mysql');
const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "your_password", // Replace with your MySQL root password
  database: "tutorialsarena"
});

con.connect(function(err) {
  if (err) throw err;
  var sql = "UPDATE employees SET city = 'Delhi' WHERE id = '1'";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " record(s) updated");
  });
});

Save this code as `update.js` and run it using `node update.js` from your terminal.

Verifying the Update

Verifying the Update in the `employees` Table

To verify that an update has been successfully applied to a record in the `employees` table (e.g., changing the city from "Allahabad" to "Delhi"), you can execute a `SELECT` query to check the updated value.

Step 1: Update the Record

Here’s an example of an `UPDATE` query that changes the city for an employee with `id = 1`:

UPDATE employees
SET city = 'Delhi'
WHERE city = 'Allahabad' AND id = 1;

Step 2: Verify the Update

After performing the update, you can run a `SELECT` query to retrieve the updated record and confirm that the city has been successfully changed:

SELECT * FROM employees WHERE id = 1;

Expected Output

If the update was successful, the `SELECT` query should show the city has changed from "Allahabad" to "Delhi". The expected output will look like this:

+----+-----------+---------+------------+
| id | name      | city    | department |
+----+-----------+---------+------------+
|  1 | John Doe  | Delhi   | HR         |
+----+-----------+---------+------------+

The `city` column now reflects "Delhi" instead of "Allahabad", confirming that the update was successful.

Conclusion

This tutorial showed how to update records in MySQL using Node.js and the `UPDATE` command. Remember to always include error handling in your code and use parameterized queries (not shown in this example) to prevent SQL injection vulnerabilities.