Connecting Node.js with MySQL

Connecting Node.js with MySQL allows a backend application to store, retrieve, update, and delete data from a relational database.
This is commonly used in web applications, APIs, and enterprise systems.

1. Why Use MySQL with Node.js?

• Reliable relational database

• Fast performance

• Structured data with tables

• Widely supported and scalable

2. Required Package

Node.js connects to MySQL using a driver such as:

• mysql2 (recommended)

• mysql (older)

Install MySQL Package

npm install mysql2

3. Creating a MySQL Connection

const mysql = require('mysql2');

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

4. Connecting to the Database

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

5. Executing SQL Queries

SELECT Query

connection.query('SELECT * FROM users', (err, results) => {
  if (err) throw err;
  console.log(results);
});

INSERT Query

const sql = 'INSERT INTO users (name, age) VALUES (?, ?)';
connection.query(sql, ['Alice', 25], (err, result) => {
  if (err) throw err;
  console.log('Record inserted');
});

✔ Prevents SQL injection using placeholders

UPDATE Query

connection.query(
  'UPDATE users SET age = ? WHERE id = ?',
  [26, 1]
);

DELETE Query

connection.query('DELETE FROM users WHERE id = ?', [1]);

6. Using Connection Pool (Best Practice)

A connection pool improves performance by reusing connections.

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

7. Using Promises with MySQL

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

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

const [rows] = await pool.query('SELECT * FROM users');

✔ Cleaner syntax with async/await

8. Integrating MySQL with Express.js

app.get('/users', async (req, res) => {
  const [rows] = await pool.query('SELECT * FROM users');
  res.json(rows);
});

9. Error Handling

try {
  const [rows] = await pool.query('SELECT * FROM users');
} catch (err) {
  console.error(err.message);
}

10. Best Practices

• Use connection pools

• Never hardcode credentials

• Use prepared statements

• Handle errors properly

• Close connections when needed

11. Real-World Flow

Client Request
     ↓
Express Route
     ↓
MySQL Query
     ↓
Database Response
     ↓
JSON Response

12. Summary

• Node.js connects to MySQL using drivers like mysql2

• Supports callbacks and promises

• Connection pooling improves performance

• Commonly used with Express for APIs