NodeJS MySQL Drop Table

MySQL stores data in the form of one or many tables and sometimes these tables become useless but take up some space, to free up the space and get rid of these tables, we can delete them by simply dropping them from the MySQL database. Drop a table means deleting the table permanently from the database.

The SQL query for dropping a table is:

DROP TABLE tableName

Dropping Tables from MySQL with NodeJS

During the connection of NodeJS and MySQL, a connection object is created. This connection object has a method query() which can take a SQL query as an argument to execute it. We can pass the above SQL query for dropping a table inside this method which deletes the table whose name passes in the query.

This method also takes a callback where we can check for the error and define the rest of the functionality. An error is thrown if the table we pass to drop does not exist.

con.query("DROP TABLE tableName", function (err, result) {
    if (err) throw err;
    // callback body
});

where

con is the connection object,
tableName is the name of the table which you want to delete

Executing the SQL Query using NodeJS

Let’s see an example of dropping a table from the MySQL database.

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 temptable (id INT, name VARCHAR(255),email VARCHAR(255), age INT)", function (err, result) {
    if (err) throw err;
});

In the above code, we have connected the NodeJS to the MySQL server using the createConnection() and connect() methods which we have covered in MySQL connections using NodeJS. Then we created a new table. We have also covered the process of creating a new table in a separate tutorial NodeJS MySQL Create Table if you want to read them.

Run the Application:

Create a JavaScript file ‘app.js’ and write the above code inside it then locate the file in the terminal and type the below command to execute it.

node app.js

Make sure that you have 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.

Output:

We can check whether the table is successfully created by running a query SHOW TABLES. This return an array containing the list of table for the current connected MySQL database.

con.query("SHOW TABLES", function (err, result) {
    if (err) throw err;
    console.log(result);
});

Output:

This proves that the table “temp” is created.

Now, for dropping this table use the code snippet for dropping a table we explained earlier and pass the table name as “temp”.

con.query("DROP TABLE temp", function (err, result) {
    if (err) throw err;
    console.log("Table is deleted");
});

Output:

Validating the Dropped Table

To validate that the table is deleted we can again run the query SHOW TABLES.

con.query("SHOW TABLES", function (err, result) {
    if (err) throw err;
    console.log(result);
});

Output:

This time instead of getting tables we get an empty array, since we only have one table “temp” this indicates that the table “temp” is successfully deleted from the database.

Summary

Sometimes a table becomes irrelevant to the application, the best solution is to delete that table which also frees up the memory taken by it. For deleting a table we can pass the SQL query of dropping a table inside the query() method, this method is provided by the MySQL connection object which is created during the connection of NodeJS and MySQL database. This method runs the query directly in the NodeJS and drops the respective table. Hope this tutorial helps you to learn the process of dropping tables from MySQL database using NodeJS.