Node.js MySQL Delete

In database management, the DELETE statement is a powerful tool used to remove one or more existing records from a table. When working with Node.js and MySQL, executing a delete operation requires precision; unlike a simple SELECT, a DELETE operation is permanent and can significantly impact your data integrity if handled incorrectly.

Key Features of Node.js MySQL Delete

1. Targeted Deletion: By using specific identifiers (like a Primary Key), you can remove exactly one specific record without affecting the rest of the dataset.
2. Bulk Removal: You can clear out large sets of data, such as removing all logs older than 30 days, by using range-based conditions.
3. Data Safety: Node.js allows you to use prepared statements to ensure that user input doesn't lead to accidental data loss or security vulnerabilities.
4. Atomic Transactions: For critical applications (like banking or e-commerce), deletions can be part of a transaction. If one part of the process fails, the deletion can be rolled back.

Step 1 Prerequisites

To follow this tutorial, you need a Node.js environment set up and access to a MySQL database. We will use the mysql2 library, which is a modern, faster, and more secure alternative to the original mysql package.

npm install mysql2

Step 2 Basic Query to Delete Data

The most common way to delete data is by identifying a specific row using a unique ID or a specific column value. In the example below, we look for a user named 'John Doe' and remove them from the table.

Example Code for Deleting Specific Rows

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         
  password: 'your_password',         
  database: 'testdb'     
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Use '?' as a placeholder for security (Prepared Statement)
  const deleteQuery = 'DELETE FROM users WHERE name = ?';
  const userName = 'John Doe';

  connection.query(deleteQuery, [userName], (err, results) => {
    if (err) {
      console.error('Error executing delete:', err.message);
      return;
    }
    // results.affectedRows tells you how many rows were actually removed
    console.log('Deleted Rows:', results.affectedRows);
  });

  // Close the connection
  connection.end();
});

Output:

Deleted Rows: 1

The results object is very useful here. The affectedRows property confirms how many records matched your criteria and were successfully deleted. If the name 'John Doe' didn't exist, affectedRows would be 0.

Step 3 Delete All Rows from a Table

Sometimes you need to wipe a table clean, perhaps to reset a demo environment or clear temporary session data. If you omit the WHERE clause entirely, MySQL will remove every single row in the specified table.

Example Code for Deleting All Rows

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'testdb'
});

connection.connect((err) => {
  if (err) throw err;

  // WARNING: This removes every row in the 'users' table
  const deleteQuery = 'DELETE FROM users';

  connection.query(deleteQuery, (err, results) => {
    if (err) throw err;
    console.log('Table cleared. Total records removed:', results.affectedRows);
  });

  connection.end();
});

Output:

Table cleared. Total records removed: 5

Step 4 Deleting with Multiple Conditions

In real-world applications, names are rarely unique. You often need to combine multiple conditions like an email address and a status code to ensure you are deleting the correct data. We use AND and OR logic for this.

Example Code for Deleting Rows with Multiple Conditions

const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'testdb'
});

connection.connect((err) => {
  if (err) throw err;

  // Deleting a user only if they match both name AND age
  const deleteQuery = 'DELETE FROM users WHERE name = ? AND age = ?';
  const params = ['John Doe', 25];

  connection.query(deleteQuery, params, (err, results) => {
    if (err) throw err;
    console.log('Targeted delete successful. Rows removed:', results.affectedRows);
  });

  connection.end();
});

Output:

Targeted delete successful. Rows removed: 1

Step 5 Deleting Records with Limit

The LIMIT clause is a safety valve. If you have a query that might accidentally match thousands of rows, adding a LIMIT ensures that only a specific number of rows are deleted at once. This is also useful for "queuing" deletions to avoid locking a database table for too long.

Example Code for Deleting Limited Rows

const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'testdb'
});

connection.connect((err) => {
  if (err) throw err;

  // Delete only the 2 oldest inactive users (ordered by created_at)
  const deleteQuery = 'DELETE FROM users WHERE status = "inactive" ORDER BY created_at ASC LIMIT 2';

  connection.query(deleteQuery, (err, results) => {
    if (err) throw err;
    console.log('Cleanup complete. Rows removed:', results.affectedRows);
  });

  connection.end();
});

Output:

Cleanup complete. Rows removed: 2

Step 6 Using Transactions for Deletion

Transactions are vital when a deletion in one table depends on a condition in another, or when you need to ensure a "point of no return" hasn't been crossed. In a transaction, the changes aren't "final" until you call commit().

Example Code with Transaction

const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'testdb'
});

connection.connect((err) => {
  if (err) throw err;

  // Start the transaction
  connection.beginTransaction((err) => {
    if (err) throw err;

    const deleteQuery = 'DELETE FROM users WHERE name = ?';
    
    connection.query(deleteQuery, ['Sam Smith'], (err, results) => {
      if (err) {
        // If something goes wrong, undo everything
        return connection.rollback(() => {
          console.error('Transaction failed. Changes rolled back.');
        });
      }

      // If the query was successful, make the change permanent
      connection.commit((err) => {
        if (err) {
          return connection.rollback(() => {
            console.error('Commit failed. Changes rolled back.');
          });
        }
        console.log('Transaction finished. Record safely deleted.');
      });
    });
  });

  connection.end();
});

Output:

Transaction finished. Record safely deleted.

Summary

The DELETE statement in Node.js provides the flexibility to manage your data lifecycle, from removing single rows to performing bulk cleanups. By leveraging the mysql2 driver's prepared statements (using the ? placeholder), you protect your application from injection attacks. For advanced workflows, combining DELETE with LIMIT or wrapping operations in Transactions ensures that your data remains accurate and your application stays resilient against errors.