Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
}
471 Views
