🔗 MySQL with Node.js – Integration Guide with Code Explanations

---

🧲 Introduction – Why Use MySQL with Node.js?

Node.js is a powerful asynchronous runtime that excels at building scalable network applications. Combining it with MySQL gives you the ability to store, retrieve, and manage data efficiently for web applications, APIs, and microservices.

🎯 What You’ll Learn:

• How to set up MySQL in a Node.js project
• Perform CRUD operations using mysql2 library
• Use async/await for non-blocking code
• Secure your queries with prepared statements
• Apply best practices for real-world apps

---

🧰 Setup – Installing Required Modules

Install MySQL client for Node.js:

npm install mysql2

If you’re using promises or async/await (recommended):

npm install mysql2

---

⚙️ Connecting Node.js to MySQL

✅ Basic MySQL Connection

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database'
});

connection.connect(err => {
  if (err) {
    console.error('❌ Connection error:', err.message);
    return;
  }
  console.log('✅ Connected to MySQL');
});

🧠 Explanation:

• mysql.createConnection(): Initializes connection config.
• connect(): Opens connection to MySQL server.
• Handles errors gracefully with a callback.

---

🔁 CRUD Operations in Node.js + MySQL

---

🔍 SELECT – Retrieving Data

connection.query(
  'SELECT * FROM users WHERE country = ?',
  ['India'],
  (err, results) => {
    if (err) throw err;
    console.log(results);
  }
);

🧠 Explanation:

• Uses prepared statements (?) to avoid SQL injection.
• results contains array of matching rows.

---

➕ INSERT – Adding Records

const user = { name: 'John Doe', email: 'john@example.com' };
connection.query(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  [user.name, user.email],
  (err, results) => {
    if (err) throw err;
    console.log('Inserted ID:', results.insertId);
  }
);

🧠 Explanation:

• Uses placeholder ? for secure inputs.
• results.insertId gives you the new row ID.

---

📝 UPDATE – Modifying Records

connection.query(
  'UPDATE users SET email = ? WHERE id = ?',
  ['john.updated@example.com', 1],
  (err, results) => {
    if (err) throw err;
    console.log(`Rows updated: ${results.affectedRows}`);
  }
);

🧠 Explanation:

• Updates email where id = 1.
• affectedRows tells how many rows were updated.

---

❌ DELETE – Removing Records

connection.query(
  'DELETE FROM users WHERE id = ?',
  [1],
  (err, results) => {
    if (err) throw err;
    console.log(`Rows deleted: ${results.affectedRows}`);
  }
);

🧠 Explanation:

• Deletes user with ID = 1.
• Checks if delete was successful.

---

⚡ Using Async/Await with Promises

Switch to promise-based API:

const mysql = require('mysql2/promise');

async function run() {
  const conn = await mysql.createConnection({
    host: 'localhost',
    user: 'user',
    password: 'password',
    database: 'mydb'
  });

  const [rows] = await conn.execute('SELECT * FROM users WHERE country = ?', ['USA']);
  console.log(rows);
}

run().catch(console.error);

🧠 Explanation:

• Uses mysql2/promise for clean async/await code.
• conn.execute() replaces query() for promises.

---

📘 Best Practices for Node.js + MySQL

💡 Use Connection Pooling:

const pool = mysql.createPool({ host, user, password, database, waitForConnections: true, connectionLimit: 10 });

🔐 Prevent SQL Injection:

• Always use ? placeholders.
• Never concatenate user input into SQL strings.

⚙️ Use .env to Manage Configs:

DB_HOST=localhost
DB_USER=root
DB_PASS=secret

Then in Node.js:

require('dotenv').config();
const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  ...
});

---

📊 Real-World Use Cases

Application Type	MySQL + Node.js Role
RESTful APIs	User auth, CRUD routes
Admin Dashboards	Data visualizations, filters, pagination
E-commerce Backend	Orders, products, inventory management
IoT Systems	Sensor data logging
Messaging Systems	Chat and notification history

---

📌 Summary – Recap & Next Steps

🔍 Key Takeaways

• Use mysql2 for secure and fast integration
• Prefer async/await with mysql2/promise
• Secure inputs with placeholders
• Store configs outside code using .env

⚙️ Real-World Relevance
Node.js with MySQL is ideal for building modern web apps and microservices. Whether you’re building APIs or dashboards, this combo offers speed, scalability, and reliability.

---

❓ FAQ – Node.js MySQL Integration

❓ What’s the difference between mysql and mysql2?
✅ mysql2 is a faster, improved version that supports promises and modern syntax.

❓ Can I use MySQL with Express.js?
✅ Yes. You can call query() or execute() inside your route handlers.

❓ How to prevent SQL injection in Node.js?
✅ Always use parameterized queries (placeholders ?) instead of string concatenation.

❓ Which is better: Raw SQL or ORM?
✅ For flexibility and performance, raw SQL is great. For complex systems, ORMs like Sequelize may simplify development.

---

Share Now :