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

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.