To delete records from a MySQL database using Node.js. Now, we can use the mysql package to execute SQL queries. DELETE operation is crucial for removing unwanted data and outdated information, managing user data, or implementing cleanup processes.
DELETE operation is crucial for removing unwanted data and outdated information, managing user data, or implementing cleanup processes.
Once the connection is established, now we can execute SQL queries to delete records.
Use the "query()" method of the connection object to execute SQL queries.
This will execute the SQL query to delete records from the "employee" table where the email is 'alice@example.com'.
// SQL query to delete records
const deleteRecordQuery = 'DELETE FROM employee WHERE email = ?';
const values = ['alice@example.com'];
// Execute the SQL query
dbInstance.query(deleteRecordQuery, values, (err, result) => {
if (err) {
console.error('Error deleting records:', err);
return;
}
console.log('Records deleted:', result.affectedRows);
});
Replace the table name and column names with our specific table and column names.
Also, replace the values array with the values you want to delete in the table.
Handle errors and results appropriately within the callback function.
Create the file name `server.js` and paste the below code.
import the `deleteEmployee` functions from the `employeeController.js` file and place them inside `/delete-employee` conditions respectively.
let http = require("http");
const url = require("url");
const mysql = require("mysql");
const { deleteEmployee } = require("./employeeController");
let dbInstance = mysql.createConnection({
host: "localhost", // MySQL server hostname
user: "root", // MySQL username
password: "password", // MySQL password
database: "your_database_name", // MySQL database name
});
// Connect to the MySQL server
dbInstance.connect((err, result) => {
if (err) {
console.error("Error connecting to MySQL database:", err);
return;
}
console.log("Connected to MySQL database");
});
http
.createServer((req, res) => {
res.writeHead(200, { "Content-Type": "text/html" });
// Parse the requested URL
const parsedUrl = url.parse(req.url, true);
const { pathname, query } = parsedUrl;
// `/delete-employee?email=alice@gmail.com` so that will email value in query object.
// update if condition with if(req?.method === 'DELETE' && pathname === "/delete-employee")
if (pathname === "/delete-employee") {
deleteEmployee(req, res, dbInstance, query);
} else {
res.end("Server Listening...");
}
})
.listen(3002, () => {
console.log(`server listening on port 3002!`);
});Create the file name `employeeController.js` and paste the below code.
Here, we have created a function to delete records in the MySQL Table using Node.js.
async function deleteEmployee(req, res, dbInstance) {
// SQL query to delete records
const deleteRecordQuery = "DELETE FROM employee WHERE email = ?";
const values = ["alice@gmail.com"];
// Checking Connection is establish
if (dbInstance) {
// Execute the SQL query
// Checking Connection is establish
dbInstance.query(deleteRecordQuery, values, (err, result) => {
if (err) {
console.error("Error While updating record:", err);
res.end("Error While updating record:", JSON.stringify(err));
}
res.end(JSON.stringify(result));
});
}
}
module.exports = { deleteEmployee };
To delete employee data within Table, we often use the `DELETE` HTTP Method to delete data in DBs, but for testing, if you try to hit GET Request on `http://localhost:3002/delete-employee?email="alice@gmail.com"` from a web browser. it will return the deleted document count from the Table if it exists.