To drop MySQL tables in Node.js, we can use the mysql package to execute SQL queries.
Dropping tables in a MySQL database using Node.js involves executing SQL queries to remove existing tables. This operation can be useful when we need to reset the database schema, clean up unused tables, or perform other maintenance tasks.
Once the connection is established, we can execute SQL queries to drop tables. The SQL query to drop a table is straightforward: DROP TABLE IF EXISTS `table_name`. The IF EXISTS clause ensures that the query does not throw an error if the table doesn't exist.
First, create a MySQL connection to your MySQL server.
const mysql = require('mysql');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'your_database_name'
});
connection.connect((err) => {
if (err) {
console.error('Error connecting to MySQL:', err);
return;
}
console.log('Connected to MySQL');
});
Replace the connection options host, user, password, database with the appropriate values for your MySQL database server.
Once the connection is established, we can execute SQL queries to drop tables. Use the "query()" method of the connection object to execute SQL queries.
// SQL query to drop a table
const dropTableQuery = 'DROP TABLE IF EXISTS employee';
// Execute the SQL query
connection.query(dropTableQuery, (err, result) => {
if (err) {
console.error('Error dropping table:', err);
return;
}
console.log('Table dropped successfully');
});
This will execute the SQL query to drop the "employee" table if it exists. Handle errors and results appropriately within the callback function.
Finally, close the MySQL connection when you're done executing queries.
// Close the MySQL connection
connection.end((err) => {
if (err) {
console.error('Error closing MySQL connection:', err);
return;
}
console.log('MySQL connection closed');
});
Call the "end()" method of the connection object to close the connection. Optionally, we can provide a callback function to handle any errors that occur during the closing process.
Create the file name `db.js` and paste the below code.
const mysql = require("mysql");
function connectDB() {
// Create a connection to the MySQL database
const connection = mysql.createConnection({
host: "localhost", // MySQL server hostname
user: "root", // MySQL username
password: "password", // MySQL password
database: "your_database_name", // MySQL database name
});
// Connect to the MySQL server
connection.connect((err) => {
if (err) {
console.error("Error connecting to MySQL database:", err);
return;
}
dropTables(connection);
console.log("Connected to MySQL database");
});
}
function dropTables(connection) {
// SQL query to drop a table
const dropTableQuery = 'DROP TABLE IF EXISTS employee';
// Execute the SQL query
connection.query(dropTableQuery, (err, result) => {
if (err) {
console.error('Error dropping table:', err);
return;
}
console.log('Table dropped successfully');
// Close the MySQL connection when done
connection.end();
});
}
module.exports = connectDB;Create the file name `server.js` and paste the below code.
let http = require("http");
const dbConnection = require("./db");
http
.createServer((req, res) => {
res.writeHead(200, { "Content-Type": "text/html" });
res.end("Server Listening...");
})
.listen(3002, () => {
console.log(`server listening on port 3002!`);
dbConnection();
});