NodeJS MySQL Delete Record

NodeJS MySQL Delete Record

To Delete records from a MySQL table, the SQL query can be used. SQL stands for Structured Query Language and can be used to perform different operations on MySQL databases.

The below query is used to delete existing records from a table.

DELETE FROM tableName WHERE condition

where, 

  • tableName is the name of the table which record you want to delete, 
  • condition is an optional parameter that instructs to delete a unique record type

Also read: NodeJS MySQL Create Database

Deletion of Records from a MySQL Table using Node.js

The record can be deleted directly from a table using the MySQL connection object. The MySQL connection object has a method query() to run SQL queries in NodeJS. This method also uses a callback function as an argument to check for the error and define the rest of the functionality.

Delete All Records 

All the records of a table can be deleted by passing the query of deleting records without the WHERE parameter inside the query() method as shown below.

con.query("DELETE FROM tableName", function (err, result) {
    if (err) throw err;
    console.log("Records deleted");
});

where the con is the connection object.

Delete Unique Records

We can provide a condition after the WHERE parameter inside the query() method to delete a particular type of record. 

con.query("DELETE FROM tableName WHERE condition", function (err, result) {
    if (err) throw err;
    console.log("Records deleted");
});

where the con is the connection object.

Example of Deleting Records from a MySQL Table Using Node.js

Let’s see an example to delete records from a MySQL table using NodeJS. 

Step 1: Run the MySQL server in the background.

For this, you can use XAMPP which is extremely fast, easy to install, and can run a MySQL server in one click.  

XAMPP Control Panel 3

Step 2: Create a folder with a file “app.js” where we write our code and open it in a code editor.

Folder Structure 3

Step3: Open the terminal and type the below command to initiate NPM. 

npm init -y
Initiate NPM 2

Step 4: Type the below command to install the MySQL module.

npm i mysql
Install Sql 2

Step 5: Open the “app.js” file and import the MySQL module.

const mysql = require('mysql');

Step 6: Connect to the MySQL server using the createConnection() and connect() methods.

const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "newdatabase"
});

con.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log("Connected to MySQL!");
});

We have a separate tutorial on creating MySQL connections using NodeJS if you want to read it.

We specified the database as “newdatabase” which we have created in another tutorial NodeJS MySQL Create Database.

Step 7: Create a new table. We have a separate tutorial on NodeJS MySQL Create Table if you want to read it.

con.query("CREATE TABLE temp (id INT, name VARCHAR(255),email VARCHAR(255), age INT)", function (err, result) {
    if (err) throw err;
    console.log("Table created");
});

Step 8: Insert multiple records. We have covered the process of inserting multiple records in a separate tutorial NodeJS MySQL Insert Record if you want to read it.

var values = [  
    ['1', 'Aditya', 'aditya@gail.com', '22'],  
    ['2', 'Example', 'example@gmail.com', '22'], 
    ['3', 'Rack', 'rack@gmail.com', '17'],  
    ['4', 'Jack', 'jack@gmail.com', '15'],
]; 

con.query("INSERT INTO temp VALUES ?", [values], function (err, result) {
    if (err) throw err;
    console.log("Records inserted");
});

Step 9: Use the snippet we explain at the staring of this tutorial to run the query of deleting the unique records and pass a condition age < ’18’ to delete all the records where the age is less than 18.

con.query("DELETE FROM temp WHERE age < '18'", function (err, result) {
    if (err) throw err;
    console.log("Records deleted");
});

Step 10: Type the below command to run the application.

node app.js

Compete Code:

const mysql = require('mysql');

const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "newdatabase"
});

con.connect(function (err) {
    if (err) {
        throw err;
    }
});

con.query("CREATE TABLE temp (id INT, name VARCHAR(255),email VARCHAR(255), age INT)", function (err, result) {
    if (err) throw err;
});

var values = [  
    ['1', 'Aditya', 'aditya@gail.com', '22'],  
    ['2', 'Example', 'example@gmail.com', '22'], 
    ['3', 'Rack', 'rack@gmail.com', '17'],  
    ['4', 'Jack', 'jack@gmail.com', '15'],
]; 

con.query("INSERT INTO temp VALUES ?", [values], function (err, result) {
    if (err) throw err;
});

con.query("DELETE FROM temp WHERE age < '18'", function (err, result) {
    if (err) throw err;
    console.log("Records deleted");
});

Output:

Records Deleted

Verifying Deletion of Records from a MySQL Table

To verify that the records are successfully deleted you can use the below SQL query.

SELECT * FROM tableName

this will return an array of records of a table.

con.query("SELECT * FROM temp", function (err, result) {
    if (err) throw err;
    console.log(result);
});

Output:

Validate

Summary

MySQL connection object has a method query() which can execute the SQL queries of deleting records to delete records from a table. Hope this tutorial helps to learn the process of deleting records from a table.

Reference

https://www.npmjs.com/package/mysql