Delete MySQL table using NodeJS

You can delete an existing table from a MySql database using the "DROP TABLE" statement in Node.js. Sometimes, we need to delete entire tables, although in enterprises it is always recommended to archive unused tables instead of deleting them.

When deleting a table, we have two situations - p>

• Delete the table if it exists, otherwise throw an error

• Delete regardless of whether the table exists or not.

We will discuss both cases here.

Before proceeding, please check that the following steps have been executed -

• mkdir mysql-test

• cd mysql-test

• npm init -y

• npm install mysql

The above steps are in the project folder Install Node-mysql dependencies. p>

Delete table

• Deleting a table requires creating the app.js file first.

• Now copy and paste the following code into the app.js file

• Run the code using the following command

ul>

>> 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 code snippet will throw an error because we do not have a table named "customers". We have a table named - 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
}

Delete the table if it exists

So, how do we overcome the above situation. Well, in the above example we can use the "If Exists" clause. This will only delete the table from the database if it exists, otherwise no error will be thrown but a warning count will be given.

• Copy and 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
}

The above is the detailed content of Delete MySQL table using NodeJS. For more information, please follow other related articles on the PHP Chinese website!