Node.js MySQL: Deleting Records

Welcome to The Coding College! In this tutorial, you’ll learn how to delete records from a MySQL database using Node.js. Deleting data is an essential operation for maintaining and managing your database effectively.

Prerequisites

Ensure you have:

  1. Node.js Installed: Download Node.js.
  2. MySQL Installed: Download MySQL.
  3. A Populated Table: Use our Node.js MySQL Insert Into tutorial to add data to your table.

Step 1: Install MySQL Module

Install the MySQL module in your Node.js project:

npm install mysql

Step 2: Connect to MySQL Database

Create a file named deleteRecord.js and establish a connection to your MySQL database:

const mysql = require('mysql');

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

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

Step 3: Write a DELETE Query

To delete a specific record, use the DELETE statement with a WHERE clause to specify which record to delete. Here’s an example to delete a user with id = 1:

const deleteQuery = 'DELETE FROM users WHERE id = 1';

Step 4: Execute the DELETE Query

Run the DELETE query using the query() method:

connection.query(deleteQuery, (err, results) => {
  if (err) throw err;
  console.log('Record deleted successfully:', results.affectedRows);
});

The affectedRows property shows how many rows were deleted.

Step 5: Use Placeholders for Dynamic Data

To delete a record dynamically, use placeholders (?) to avoid SQL injection:

const userId = 2;
const deleteQueryDynamic = 'DELETE FROM users WHERE id = ?';

connection.query(deleteQueryDynamic, [userId], (err, results) => {
  if (err) throw err;
  console.log(`Deleted ${results.affectedRows} record(s) successfully.`);
});

Step 6: Close the Connection

Always close the database connection after operations:

connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Full Code Example

Here’s the complete script for deleting a record:

const mysql = require('mysql');

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

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

// Delete a record
const userId = 3;
const deleteQueryDynamic = 'DELETE FROM users WHERE id = ?';

connection.query(deleteQueryDynamic, [userId], (err, results) => {
  if (err) throw err;
  console.log(`Deleted ${results.affectedRows} record(s) successfully.`);
});

// Close the connection
connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Examples of DELETE Query Usage

1. Delete Multiple Records

To delete multiple records, use a condition like IN:

const deleteQueryMultiple = 'DELETE FROM users WHERE id IN (?)';
const userIds = [4, 5, 6];

connection.query(deleteQueryMultiple, [userIds], (err, results) => {
  if (err) throw err;
  console.log(`Deleted ${results.affectedRows} record(s) successfully.`);
});

2. Delete All Records

To delete all records from a table (without dropping the table):

const deleteAllQuery = 'DELETE FROM users';

connection.query(deleteAllQuery, (err, results) => {
  if (err) throw err;
  console.log(`Deleted ${results.affectedRows} record(s) successfully.`);
});

3. Use Caution with DELETE

Always use a WHERE clause when deleting records unless you intentionally want to delete all rows.

Best Practices

  1. Backup Data: Always create backups before performing delete operations.
  2. Use Transactions: For critical operations, use transactions to ensure data consistency.
  3. Log Changes: Keep logs of deleted records for auditing and recovery purposes.
  4. Test First: Run SELECT queries with the same conditions before executing DELETE queries to verify the records being targeted.

Conclusion

Deleting records with Node.js and MySQL is straightforward and powerful. By using the DELETE statement effectively, you can maintain a clean and well-organized database.

For more tutorials on Node.js and database management, visit The Coding College and stay ahead in your programming journey!

Leave a Comment