Dropping a MySQL Table using NodeJS

You can delete an existing table from MySql Database using the "DROP TABLE" statement in Node. Sometimes, we need to delete the whole table, though in corporates it is always advised to archive the tables which are not in used instead of deleting them.

While deleting a table, we have two scenarios −

• Deleting a table if it exists, else throw an error

• Deleting the table whether it exists or not.

We will discuss both the scenarios here.

Before proceeding, please check the following steps are already executed −

• mkdir mysql-test

• cd mysql-test

• npm init -y

• npm install mysql

The above steps are for installing the Node - mysql dependecy in the project folder.

Deleting a Table

• For deleting the table, you need to create an app.js file first.

• Now copy-paste the following code in the app.js file

• Run the code using the following command

>> node app.js

Example 1

var mysql = require('mysql');
   var con = mysql.createConnection({
      host: "localhost",
      user: "yourusername",
      password: "yourpassword",
      database: "mydb"
   });

con.connect(function(err) {
   if (err) throw err;
   //Delete the "customers" table:
   var sql = "DROP TABLE customers";
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log("Table deleted");
      console.log(result);
   });
});

The above snippet will throw an error since we donot have a table with the name customers. We have a table with the name - students

Output

Error: ER_BAD_TABLE_ERROR: Unknown table 'bo.customers'

Example 2

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "yourusername",
   password: "yourpassword",
   database: "mydb"
});

con.connect(function(err) {
   if (err) throw err;
   //Delete the "students" table:
   var sql = "DROP TABLE students";
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log("Table deleted");
      console.log(result);
   });
});

Output

Since the table exists, we will get the following output.

Table deleted
OkPacket {
   fieldCount: 0,
   affectedRows: 0,
   insertId: 0,
   serverStatus: 2,
   warningCount: 0,    // If table does exist, then the count = 0
   message: '',
   protocol41: true,
   changedRows: 0
}

Deleting a Table if Exists

So, how do we overcome the above situation. Well, In the above case we can use the clause "If Exists". This will only delete a table from the Database if it exists, else it will not throw an error, but give a warning count.

• Copy-paste the following code in the app.js file

• Run the code using the following command

>> node app.js

Example

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "yourusername",
   password: "yourpassword",
   database: "mydb"
});

con.connect(function(err) {
   if (err) throw err;
   //Delete the "customers" table:
   var sql = "DROP TABLE IF EXISTS customers";
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log("Table deleted");
      console.log(result);
   });
});

Output

Table deleted
OkPacket {
   fieldCount: 0,
   affectedRows: 0,
   insertId: 0,
   serverStatus: 2,
   warningCount: 1, // If table does not exist, then the count > 0
   message: '',
   protocol41: true,
   changedRows: 0
}