The DELETE statement in MySQL is used to remove one or more rows from a table. When integrated with Node.js, it enables dynamic and programmatic deletion of data in your applications.
Why Use DELETE in MySQL?
- Remove Unwanted Data: Clean up old or irrelevant records from your database.
- Dynamic Deletion: Delete specific rows based on user actions or application logic.
- Efficient Data Management: Keep your database optimized by removing unnecessary data.
Prerequisites
MySQL Installed: Ensure MySQL is installed and running.
Node.js Installed: Install Node.js if you haven’t already.
MySQL Package Installed: Install the MySQL module using:
npm install mysql
Setting Up the Node.js Project
Initialize the Project:
npm init -y
Install the MySQL Package:
npm install mysql
Create a File: Create a file named app.js.
Connecting to the MySQL Database
To delete records, you need to establish a connection to your MySQL database.
Example: Connect to MySQL
const mysql = require('mysql');
// Create a connection to the database
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'nodejs_demo', // Replace with your database name
});
// Connect to MySQL
connection.connect((err) => {
if (err) {
console.error('Error connecting to MySQL:', err);
return;
}
console.log('Connected to MySQL successfully!');
});
Using the DELETE Statement in Node.js
The DELETE statement allows you to specify conditions for removing specific rows. Let’s explore different scenarios with examples.
Example 1: Delete a Single Record
Delete a row based on a specific condition.
const sql = 'DELETE FROM users WHERE id = ?';
const id = 1;
connection.query(sql, [id], (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted:', result.affectedRows);
});
// Close the connection
connection.end();
Explanation:
- WHERE id = ?: Ensures only the row with the specified id is deleted.
- affectedRows: Indicates how many rows were deleted.
Example 2: Delete Multiple Records
You can delete multiple rows by specifying a condition that matches more than one record.
const sql = 'DELETE FROM users WHERE age > ?';
const age = 30;
connection.query(sql, [age], (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted:', result.affectedRows);
});
// Close the connection
connection.end();
Explanation:
- WHERE age > ?: Deletes all users older than 30.
Deleting All Records (Use with Caution)
To delete all rows from a table, omit the WHERE clause. Be extremely careful with this operation.
Example 3: Delete All Records
const sql = 'DELETE FROM users';
connection.query(sql, (err, result) => {
if (err) {
console.error('Error deleting all data:', err);
return;
}
console.log('All rows deleted:', result.affectedRows);
});
// Close the connection
connection.end();
Warning:
- Without the WHERE clause, all rows in the table will be deleted.
Using DELETE with LIMIT
You can limit the number of rows to delete using the LIMIT clause.
Example 4: Delete a Limited Number of Rows
const sql = 'DELETE FROM users ORDER BY age ASC LIMIT 1';
connection.query(sql, (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted:', result.affectedRows);
});
// Close the connection
connection.end();
Explanation:
- ORDER BY age ASC: Sorts rows by age in ascending order.
- LIMIT 1: Deletes only the first row from the sorted result.
Combining DELETE with Other Clauses
You can combine DELETE with clauses like JOIN or WHERE to target specific rows.
Example 5: Delete Using a Subquery
const sql = `
DELETE FROM users
WHERE id IN (
SELECT id FROM users WHERE age < ? LIMIT 2
)
`;
const age = 20;
connection.query(sql, [age], (err, result) => {
if (err) {
console.error('Error deleting data:', err);
return;
}
console.log('Rows deleted using subquery:', result.affectedRows);
});
// Close the connection
connection.end();
Explanation:
- SELECT id FROM users WHERE age < ?: Fetches rows where age is less than 20.
- LIMIT 2: Deletes only two rows matching the condition.
Handling Errors
Always handle errors to ensure your application remains stable.
Example: Error Handling
const sql = 'DELETE FROM non_existing_table WHERE id = ?';
const id = 1;
connection.query(sql, [id], (err, result) => {
if (err) {
console.error('Error deleting data:', err.message);
return;
}
console.log('Rows deleted:', result.affectedRows);
});
Output:
Error deleting data: Table 'nodejs_demo.non_existing_table' doesn't exist
Real-World Use Case
Imagine building a user management system where admins can delete inactive users.
Example: Delete Inactive Users
const sql = 'DELETE FROM users WHERE last_login < NOW() - INTERVAL 1 YEAR';
connection.query(sql, (err, result) => {
if (err) {
console.error('Error deleting inactive users:', err);
return;
}
console.log('Inactive users deleted:', result.affectedRows);
});
// Close the connection
connection.end();
Explanation:
- last_login < NOW() – INTERVAL 1 YEAR: Deletes users who haven’t logged in for over a year.