Deleting records in MySQL using Nodejs

After insertion, we need to delete records as well. The records should can be deleted based upon an identifier from the database table. You can delete records from table using "DELETE FROM" statement.

We can delete the records from MySql DB in two ways −

• Static Deletion - In this type of deletion, we give a prefixed filter value to delete

• Dynamic Deletion – In this type of deletion, we ask for an input before deletion and then delete upon its basis.

Before proceeding, please check the following steps are already executed −

• mkdir mysql-test

• cd mysql-test

• npm init -y

• npm install mysql

The above steps are for installing the Node - mysql dependecy in the project folder.

Following are the examples on how to delete records from MySql using Nodejs.

Delete a Record from The Students Table

• For deleting records from the MySQL table, create an app.js file.

• Now copy-paste the below snippet in the file

• Run the code using the following command

   >> node app.js

Example 

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "yourusername",
   password: "yourpassword",
   database: "mydb"
});

con.connect(function(err) {
   if (err) throw err;

   //Delete the records with address="Delhi"
   var sql = "DELETE FROM student WHERE address = 'Delhi'; "
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log("Record deleted = ", results.affectedRows);
      console.log(result);
   });
});

Output

Record deleted = 1
OkPacket {
   fieldCount: 0,
   affectedRows: 1, // No of Records Deleted
   insertId: 0,
   serverStatus: 34,
   warningCount: 0,
   message: '',
   protocol41: true,
   changedRows: 0
}

Example

The following example will take address field as the input and only delete the records which match the filter.

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "yourusername",
   password: "yourpassword",
   database: "mydb"
});

con.connect(function(err) {
   if (err) throw err;

// Delete the desired record from table
let sql = `DELETE FROM student WHERE address = ?`;
// delete a row with address=Delhi
   con.query(sql, 'Dehi', (err, result, fields) => {
      if (err) throw err;
      console.log("Record deleted = ", results.affectedRows);
      console.log(result);
   });
});

Output

OkPacket {
   fieldCount: 0,
   affectedRows: 3, // 3 Rows deleted for address=Delhi
   insertId: 0,
   serverStatus: 34,
   warningCount: 0,
   message: '',
   protocol41: true,
   changedRows: 0
}

Mayank Agarwal

Published on 27-Apr-2021 09:20:52

• Related Questions & Answers
• Dropping a MySQL Table using NodeJS
• Updating a record in MySQL using NodeJS
• Creating a MySQL Table in NodeJS using Sequelize
• Deleting all records of a collection in MongoDB Shell?
• Deleting the nth row in MySQL?
• Sync Copy in fs-extra using NodeJS
• Get records in a certain order using MySQL?
• Fetch records from comma separated values using MySQL IN()?
• Delete selective multiple records using MySQL DELETE query
• Delete records from a MySQL table by excluding non-deleted records using NOT IN
• Deleting partial data from a field in MySQL?
• Inserting records into a MySQL table using PreparedStatement in Java?
• Fetch records on the basis of LastName using MySQL IN()
• Deleting all rows older than 5 days in MySQL
• Introduction to ensureFileSync() in NodeJS