To create tables in MySQL using Node.js, we can use the mysql package to execute SQL queries.
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');
});
Once the connection is established, we can execute SQL queries to create tables.
// SQL query to create a table
const createTableQuery = `
CREATE TABLE IF NOT EXISTS employee (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT NOT NULL,
department VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
)`;
// Execute the SQL query
connection.query(createTableQuery, (err, result) => {
if (err) {
console.error('Error creating table:', err);
return;
}
console.log('Table created successfully');
});
Use the "query()" method of the connection object to execute SQL queries.
This will execute the SQL query to create a table named "employee" with "id", "name", "age", "department" and "email" columns.
Handle errors and results appropriately within the callback function.
Finally, close the MySQL connection when you're done executing queries.
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.
// Close the MySQL connection
connection.end((err) => {
if (err) {
console.error('Error closing MySQL connection:', err);
return;
}
console.log('MySQL connection closed');
});
we have created `createTables` function in `db.js` and invoked function after MySQL connection established connection.
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;
}
createTables(connection);
console.log("Connected to MySQL database");
});
}
function createTables(connection) {
// SQL query to create a table
const createTableQuery = `
CREATE TABLE IF NOT EXISTS employee (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT NOT NULL,
department VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
)`;
// Execute the SQL query
connection.query(createTableQuery, (err, result) => {
if (err) {
console.error("Error creating table:", err);
return;
}
console.log("Table created successfully");
// Close the MySQL connection when done
connection.end();
});
}
module.exports = connectDB;