🛠️ Node.js – MySQL Create Database / Tables – Step-by-Step with SQL & Node Integration

---

🧲 Introduction – Why Create Databases and Tables from Node.js?

With Node.js and the MySQL module, you can not only run queries but also create databases and tables dynamically. This is useful for:

• Automating initial setup
• Running migrations
• Generating multi-tenant databases

🎯 In this guide, you’ll learn:

• How to connect to MySQL server
• Create a new database programmatically
• Create tables with various field types
• Handle duplicate creation and errors

---

⚙️ Step 1 – Connect to MySQL Server

Before you create a database, connect without selecting one:

const mysql = require('mysql');

const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: ''
});

db.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL Server');
});

---

🏗️ Step 2 – Create a Database

db.query('CREATE DATABASE IF NOT EXISTS testdb', (err, result) => {
  if (err) throw err;
  console.log('Database created or already exists');
});

🧪 Output:

Database created or already exists

---

🔗 Step 3 – Connect to the Created Database

Update your connection:

const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'testdb'
});

---

🧱 Step 4 – Create a Table (Users Table Example)

const sql = `
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`;

db.query(sql, (err, result) => {
  if (err) throw err;
  console.log('Users table created');
});

🧪 Output:

Users table created

---

📚 Add More Tables (Products Table Example)

const sql = `
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  stock INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`;

db.query(sql, (err) => {
  if (err) throw err;
  console.log('Products table created');
});

---

📜 SQL Data Types Quick Reference

Type	Use Case
INT	Whole numbers, IDs
VARCHAR(n)	Strings, emails, names
TEXT	Long descriptions
DECIMAL(a,b)	Prices, decimals with precision
TIMESTAMP	Record time of entry

---

🧱 Best Practices for Table Creation via Node.js

✅ Practice	💡 Why It’s Important
Use IF NOT EXISTS	Prevent duplicate errors
Always include PRIMARY KEY	Makes data uniquely identifiable
Add DEFAULT values where useful	Handles inserts without full data
Use UNIQUE for key columns	Prevents duplicate records (like email)
Add created_at timestamp	Tracks creation time for audit/logging

---

📌 Summary – Recap & Next Steps

Node.js and MySQL allow you to automate database and table creation using SQL statements. This is great for app initialization, setup scripts, and reusable deployment workflows.

🔍 Key Takeaways:

• Use CREATE DATABASE and CREATE TABLE from within Node.js
• Always use IF NOT EXISTS to avoid redundant errors
• Define strong schema types and constraints

⚙️ Real-world relevance:
Used in startup apps, cloud automation, CMS setup, SaaS tenant provisioning, and dev/test CI environments.

---

❓FAQs – Creating MySQL Databases and Tables with Node.js

---

❓ Can I create a database if it already exists?
✅ Yes, use CREATE DATABASE IF NOT EXISTS to avoid errors.

---

❓ How to run multiple table creations one after another?
✅ Chain them or wrap them in a function call to maintain sequence.

---

❓ Can I drop and recreate a table dynamically?
✅ Yes, use DROP TABLE IF EXISTS tableName followed by CREATE TABLE.

---

❓ How do I handle foreign keys in table creation?
✅ Define constraints during CREATE TABLE, or use ALTER TABLE later.

---

❓ Should I use migration tools instead of manual creation?
✅ For larger apps, tools like Sequelize or Knex.js are better for migrations, but raw SQL via Node.js is great for setup scripts and lightweight control.

---

Share Now :