Node.js MySQL DELETE Records FROM Table

Node.js 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.

• MySQL DELETE Some Records based on filter
• MySQL DELETE All Records from table

Example 1 – Delete Rows from MySQL Table using Node.js

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 Node.js

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.