Node.js MySQL DELETE Records FROM Table

Node.js MySQL DELETE Records from a Table

In this tutorial, we will learn how to delete records from a MySQL table using Node.js. A MySQL DELETE query removes one or more rows from a table, usually based on a WHERE condition.

Node.js MySQL Delete Query helps you delete matching records from a table through a Node.js script. You can delete selected records, delete all records, or use parameterized values when the delete condition comes from user input.

Important: Always use a WHERE clause when you want to delete only specific records. A DELETE FROM table_name query without a condition deletes every row in that table.

• Delete selected records from MySQL table using Node.js
• Use placeholder values in Node.js MySQL DELETE query
• Delete all records from MySQL table using Node.js
• DELETE vs TRUNCATE for removing all MySQL rows
• Node.js MySQL DELETE query FAQs

MySQL DELETE query syntax used in Node.js

The basic SQL syntax to delete records from a MySQL table is shown below.

DELETE FROM table_name WHERE condition;

In Node.js, this SQL statement is passed to con.query(). The callback receives a result object that contains details such as affectedRows, which tells how many rows were deleted.

con.query("DELETE FROM students WHERE rollno > 10", function (err, result) {
  if (err) throw err;
  console.log(result.affectedRows + " record(s) deleted");
});

Example 1 – Delete selected records from MySQL table using Node.js

In this example, we will take a MySQL table and delete only the rows that satisfy a given condition.

To delete rows that obey a given condition, execute a DELETE FROM query on the specified table with a filter applied to one or more columns in the table.

The following students table is used for this example. We will delete the records whose rollno value is greater than 10.

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)

Use placeholder values in Node.js MySQL DELETE query

When a value in the delete condition comes from a variable, form field, route parameter, or API request, do not build the SQL query by joining strings. Use placeholder values instead. Placeholders make the query easier to read and help avoid unsafe SQL construction.

The following example deletes the student whose rollno matches the value stored in the rollNumberToDelete variable.

deleteRecordByRollNo.js

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "arjun",
  password: "password",
  database: "studentsDB"
});

var rollNumberToDelete = 7;

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

  var sql = "DELETE FROM students WHERE rollno = ?";

  con.query(sql, [rollNumberToDelete], function (err, result) {
    if (err) throw err;

    console.log(result.affectedRows + " record(s) deleted");
  });
});

Output

1 record(s) deleted

If no row matches the condition, the query still runs successfully, but affectedRows will be 0.

0 record(s) deleted

Check affectedRows after a Node.js MySQL DELETE operation

After a DELETE query, the result object is important because it confirms how many rows were removed. In most practical code, you should check result.affectedRows before showing a success message to the user.

con.query("DELETE FROM students WHERE rollno = ?", [rollNumberToDelete], function (err, result) {
  if (err) throw err;

  if (result.affectedRows === 0) {
    console.log("No matching student record found.");
  } else {
    console.log(result.affectedRows + " student record(s) deleted.");
  }
});

This check is useful when you are deleting a record by id, username, email, roll number, or any other unique column.

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 the specified table without a WHERE clause.

Use this only when you are sure that the whole table data should be removed. If the table contains important data, take a backup or test the condition with a SELECT query first.

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)

DELETE vs TRUNCATE for removing all MySQL rows

Both DELETE and TRUNCATE can remove rows from a MySQL table, but they are used differently.

Operation	Typical use	Condition allowed?
DELETE FROM students WHERE rollno > 10	Delete selected rows	Yes
DELETE FROM students	Delete all rows while using DELETE syntax	No condition in this form
TRUNCATE TABLE students	Remove all rows from a table quickly and reset table storage details such as auto-increment counter in common MySQL usage	No

For this Node.js tutorial, use DELETE when you need a condition such as WHERE rollno = ? or WHERE marks < 35. Use TRUNCATE TABLE only when the requirement is to clear the whole table and you understand how it affects your table.

Common mistakes in Node.js MySQL DELETE queries

• Running DELETE FROM students when only one student record should be deleted.
• Forgetting to test the WHERE condition with a SELECT query before running DELETE on important data.
• Building SQL by directly joining user input into the query string.
• Ignoring affectedRows and assuming that a record was deleted.
• Not closing or reusing database connections properly in larger applications.

Editorial QA checklist for this Node.js MySQL DELETE tutorial

• The DELETE example includes a clear WHERE condition for selected records.
• The all-records delete example clearly warns that omitting WHERE removes every row.
• The tutorial explains affectedRows, because it is the easiest way to verify the result in Node.js.
• The placeholder example uses ? and a values array instead of string concatenation.
• The FAQ answers cover deleting selected records, deleting all rows, and the DELETE versus TRUNCATE decision.

Node.js MySQL DELETE query FAQs

Conclusion

In this Node.js Tutorial – Node.js MySQL – DELETE FROM, we have learnt how to delete records based on a condition, how to delete all the records from a table, how to use placeholder values, and how to check the number of deleted rows using affectedRows.