How to UPDATE data in a MySQL database using Node.js

A guide on how to use Prepared Statements in Node.js to update data in a MySQL database table safely and effectively.

In this article, you'll learn how to connect to a MySQL database and use Node.js with Prepared Statements to execute an UPDATE statement, allowing you to update records in a table using multiple parameters.

// Import the mysql2 library
const mysql = require('mysql2');

// Set up the connection to the MySQL database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test_db'
});

// Connect to the database
connection.connect((err) => {
  if (err) {
    return console.error('Connection error:', err.message);
  }
  console.log('Successfully connected to the MySQL database.');
});

// UPDATE statement with Prepared Statement
const updateQuery = `UPDATE students SET name = ?, age = ? WHERE id = ?`;
const params = ['Jane Doe', 25, 1];

// Execute the UPDATE statement
connection.execute(updateQuery, params, (err, results) => {
  if (err) {
    return console.error('Execution error:', err.message);
  }
  console.log(`Number of records updated: ${results.affectedRows}`);
});

// Close the connection
connection.end((err) => {
  if (err) {
    return console.error('Error closing the connection:', err.message);
  }
  console.log('Connection to the MySQL database closed.');
});

Detailed explanation:

1. const mysql = require('mysql2'): Imports the mysql2 library to work with MySQL.
2. const connection = mysql.createConnection(...): Sets up the connection to the MySQL database with details like host, user, password, and database.
3. connection.connect((err) => {...}): Connects to the database and handles any connection errors.
4. const updateQuery = "UPDATE students SET name = ?, age = ? WHERE id = ?": Defines the UPDATE statement with placeholders for parameters.
5. const params = ['Jane Doe', 25, 1]: Provides the parameter values to be used in the UPDATE statement.
6. connection.execute(updateQuery, params, (err, results) => {...}): Executes the UPDATE statement with the provided parameters.
7. console.log(results.affectedRows): Logs the number of records updated.
8. connection.end((err) => {...}): Closes the connection to the MySQL database.

System Requirements:

• Node.js version 14.x or later
• mysql2 library

How to install the libraries needed to run the Node.js code above:

Use npm to install the library:

npm install mysql2

Tips:

• Double-check your connection details like host, user, password, and database to avoid connection errors.
• Use Prepared Statements to protect against SQL injection attacks.