Nodejs MySQL Delete Records
In this tutorial, we will learn how to delete records from a MySQL table, using Node.js.
Node.js MySQL Delete Query helps you to delete one or more records from a table based on a filtering condition.
Example 1 Delete Rows from MySQL Table using Nodejs
In this example, we will take a MySQL table, and delete the rows from it which satisfy given condition.
To delete rows that obey a given condition, execute DELETE FROM query on specified table with filter applied on one or many properties of records in the table.
MySQL Table – students
mysql> select * from students; +---------------+--------+-------+ | name | rollno | marks | +---------------+--------+-------+ | John | 1 | 74 | | Arjun | 2 | 74 | | Prasanth | 3 | 77 | | Adarsh | 4 | 78 | | Raja | 5 | 94 | | Sai | 6 | 84 | | Ross | 7 | 54 | | Monica Gellar | 8 | 86 | | Lee | 9 | 98 | | Bruce Wane | 10 | 92 | | Sukumar | 11 | 99 | | Anisha | 12 | 95 | | Miley | 13 | 85 | | Jobin | 14 | 87 | | Jack | 16 | 82 | | Priya | 17 | 88 | +---------------+--------+-------+ 16 rows in set (0.00 sec)
deleteRecordsFiltered.js
// include mysql module var mysql = require('mysql'); // create a connection variable with the required details var con = mysql.createConnection({ host: "localhost", // ip address of server running mysql user: "arjun", // user name to your mysql database password: "password", // corresponding password database: "studentsDB" // use the specified database }); // make connection to the database. con.connect(function(err) { if (err) throw err; // if connection is successful con.query("DELETE FROM students WHERE rollno>10", function (err, result, fields) { // if any error while executing above query, throw error if (err) throw err; // if there is no error, you have the result console.log(result); }); });
Open a terminal or command prompt and run this script using node command as shown in the following.
Output
arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node deleteRecordsFiltered.js OkPacket { fieldCount: 0, affectedRows: 6, insertId: 0, serverStatus: 34, warningCount: 0, message: '', protocol41: true, changedRows: 0 }
Observe that in the result, affectedRows is 6, which means six records have been deleted.
Following are the records left after executing MySQL DELETE FROM query on students table.
mysql> select * from students; +---------------+--------+-------+ | name | rollno | marks | +---------------+--------+-------+ | John | 1 | 74 | | Arjun | 2 | 74 | | Prasanth | 3 | 77 | | Adarsh | 4 | 78 | | Raja | 5 | 94 | | Sai | 6 | 84 | | Ross | 7 | 54 | | Monica Gellar | 8 | 86 | | Lee | 9 | 98 | | Bruce Wane | 10 | 92 | +---------------+--------+-------+ 10 rows in set (0.00 sec)
Example 2 Delete All Records from MySQL Table via Nodejs
In this example, we will take a MySQL table, and delete all of its rows.
To delete all the rows, execute DELETE FROM query on specified table.
deleteRecordsAll.js
// include mysql module var mysql = require('mysql'); // create a connection variable with the required details var con = mysql.createConnection({ host: "localhost", // ip address of server running mysql user: "arjun", // user name to your mysql database password: "password", // corresponding password database: "studentsDB" // use the specified database }); // make connection to the database. con.connect(function(err) { if (err) throw err; // if connection is successful con.query("DELETE FROM students", function (err, result, fields) { // if any error while executing above query, throw error if (err) throw err; // if there is no error, you have the result console.log(result); }); });
Open a terminal or command prompt and run this script using node command as shown in the following.
Output
arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node deleteRecordsAll.js OkPacket { fieldCount: 0, affectedRows: 10, insertId: 0, serverStatus: 34, warningCount: 0, message: '', protocol41: true, changedRows: 0 }
Following are the contents of students table after execution
mysql> select * from students; Empty set (0.00 sec)
Conclusion
In this Node.js Tutorial – Node.js MySQL – DELETE FROM, we have learnt to delete records based on a condition or delete all the records from the table.