Node.js MySQL Delete

The DELETE statement in MySQL is used to delete existing records from a table. It allows you to remove one or more rows from the database based on a condition.

 

Key Features of Node.js MySQL Delete

1. Delete Specific Rows: You can delete specific rows based on a condition.
2. Delete All Rows: You can delete all rows in a table using the DELETE statement without specifying a condition.
3. Safe Deletion: Always use a WHERE clause to prevent deleting all rows by accident.
4. Transactional Deletion: Deleting data is often wrapped in transactions to ensure data integrity.

 

Step 1 Prerequisites

Ensure that MySQL is installed and the mysql2 package is added to your Node.js project.

npm install mysql2

Step 2 Basic Query to Delete Data

The simplest form of the DELETE statement is without a condition, which removes all rows 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',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

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

  // Delete record from the users table where the name is 'John Doe'
  const deleteQuery = 'DELETE FROM users WHERE name = "John Doe"';

  connection.query(deleteQuery, (err, results) => {
    if (err) {
      console.error('Error deleting record:', err.stack);
      return;
    }
    console.log('Record deleted:', results.affectedRows);
  });

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

Output:

Record deleted: 1

The result indicates that one row was deleted from the users table where the name column matched 'John Doe'.

Step 3 Delete All Rows from a Table

If you want to delete all rows from a table, you can omit the WHERE clause.

Example Code for Deleting All Rows

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

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

  // Delete all records from the users table
  const deleteQuery = 'DELETE FROM users';

  connection.query(deleteQuery, (err, results) => {
    if (err) {
      console.error('Error deleting records:', err.stack);
      return;
    }
    console.log('All records deleted:', results.affectedRows);
  });

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

Output:

All records deleted: 5

This shows that all five rows in the users table have been deleted.

Step 4 Deleting with Multiple Conditions

You can delete rows based on multiple conditions by combining them using AND or OR.

Example Code for Deleting Rows with Multiple Conditions

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

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

  // Delete record from the users table where name is 'John Doe' and age is 25
  const deleteQuery = 'DELETE FROM users WHERE name = "John Doe" AND age = 25';

  connection.query(deleteQuery, (err, results) => {
    if (err) {
      console.error('Error deleting record:', err.stack);
      return;
    }
    console.log('Record deleted with multiple conditions:', results.affectedRows);
  });

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

Output:

Record deleted with multiple conditions: 1

Step 5 Deleting Records with Limit

To delete a limited number of rows, you can use the LIMIT clause along with DELETE.

Example Code for Deleting Limited Rows

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

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

  // Delete only 2 records where age is 30
  const deleteQuery = 'DELETE FROM users WHERE age = 30 LIMIT 2';

  connection.query(deleteQuery, (err, results) => {
    if (err) {
      console.error('Error deleting record:', err.stack);
      return;
    }
    console.log('Limited records deleted:', results.affectedRows);
  });

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

Output:

Limited records deleted: 2

Step 6 Using Transactions for Deletion

To ensure that the deletion process is safe and reliable, it is a good practice to use transactions.

Example Code with Transaction

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

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

  // Start a transaction
  connection.beginTransaction((err) => {
    if (err) {
      console.error('Error starting transaction:', err.stack);
      return;
    }

    // Delete record from users table
    const deleteQuery = 'DELETE FROM users WHERE name = "Sam Smith"';

    connection.query(deleteQuery, (err, results) => {
      if (err) {
        // Rollback transaction if an error occurs
        connection.rollback(() => {
          console.error('Error deleting record, transaction rolled back:', err.stack);
        });
        return;
      }

      // Commit the transaction
      connection.commit((err) => {
        if (err) {
          console.error('Error committing transaction:', err.stack);
        } else {
          console.log('Transaction committed, record deleted');
        }
      });
    });
  });

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

Output:

Transaction committed, record deleted

 

Summary

The DELETE statement in MySQL allows you to remove one or more rows from a table based on specific conditions. It can be used with WHERE, LIMIT, and ORDER BY to manage which rows get deleted. For safety, always use a WHERE clause to avoid deleting all records, and consider using transactions for atomicity when performing deletions in critical applications.