How to DELETE data from a MySQL database using Node.js

A guide on how to use Prepared Statements in Node.js to delete data from a table in a MySQL database 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 a DELETE statement, allowing you to remove records from a table in the database using multiple parameters.

const mysql = require('mysql');

// Create a 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) throw err;
    console.log('Connected to the database!');
});

// DELETE statement with Prepared Statement
const deleteQuery = 'DELETE FROM students WHERE id = ? AND name = ?';
const params = [1, 'John Doe'];

// Execute the DELETE statement
connection.query(deleteQuery, params, (error, results) => {
    if (error) throw error;
    console.log(`${results.affectedRows} record(s) deleted.`);
});

// Close the connection
connection.end();

Detailed explanation:

1. const mysql = require('mysql');: Imports the mysql library for connecting and interacting with MySQL.
2. const connection = mysql.createConnection(...): Creates a connection to the MySQL database with details such as host, user, password, and database.
3. connection.connect(...): Connects to the database and checks for connection errors.
4. const deleteQuery = 'DELETE FROM students WHERE id = ? AND name = ?';: Defines the DELETE statement with specified parameters.
5. const params = [1, 'John Doe'];: Declares the parameter values to be passed into the DELETE statement.
6. connection.query(deleteQuery, params, ...): Executes the DELETE statement with the specified parameters.
7. console.log(${results.affectedRows} record(s) deleted.);: Prints the number of records that were deleted.
8. connection.end();: Closes the connection to the database.

System Requirements:

• Node.js version 14.x or higher
• Library: mysql

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

Use npm to install the library:

npm install mysql

Tips:

• Make sure your MySQL server is running before attempting to connect.
• Double-check your connection details like host, user, password, and database to avoid connection errors.
• Use Prepared Statements to protect against SQL injection attacks.