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

Copy after login

Copy after login

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);
   });
});

Copy after login

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'

Copy after login

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);
   });
});

Copy after login

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
}

Copy after login

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

Copy after login

Copy after login

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);
   });
});

Copy after login

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
}

Copy after login

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!