Connecting Express.js with MySQL

# CHAPTER 12

Connecting Express.js with MySQL #

1. Introduction #

2. Learning Objectives #

• Differentiate between NoSQL (MongoDB) and SQL (MySQL) paradigms.

• Install and configure the mysql2 package.

• Create a reusable MySQL connection pool.

• Execute raw SQL queries asynchronously using async/await.

3. Beginner-Friendly Explanation #

• MongoDB is a series of file folders. You can put a resume, a photo, and a contract all inside "Alice's Folder." It is flexible, but hard to generate complex reports.

• MySQL is a series of massive Excel Spreadsheets. Data must fit perfectly into rows and columns. "Alice" goes in the User sheet. Her "Purchases" go in the Order sheet. The sheets are linked together by ID numbers. It is strict, rigid, but incredibly powerful for math, accounting, and complex relationships.

4. Step 1: Installing the Driver #

npm install mysql2

5. Step 2: Creating a Connection Pool #

Create a file: config/db.js

const mysql = require('mysql2/promise'); // Note the /promise!

// Create the pool
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password123',
    database: 'my_store_db',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

module.exports = pool;

6. Step 3: Executing SQL Queries #

In controllers/productController.js:

const db = require('../config/db');

// READ ALL
const getProducts = async (req, res) => {
    try {
        // Execute the raw SQL string
        // mysql2 returns an array where the first item is the rows, and the second is metadata
        const [rows] = await db.query('SELECT * FROM products');
        
        res.status(200).json({ status: "success", data: rows });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
};

// CREATE (Using Parameterized Queries)
const createProduct = async (req, res) => {
    try {
        const { name, price } = req.body;
        
        // CRITICAL: Notice the question marks (?). This prevents SQL Injection!
        const sql = 'INSERT INTO products (name, price) VALUES (?, ?)';
        
        // Pass the variables in an array as the second argument
        const [result] = await db.query(sql, [name, price]);
        
        res.status(201).json({ 
            status: "success", 
            message: "Product created",
            insertId: result.insertId 
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
};

module.exports = { getProducts, createProduct };

7. Preventing SQL Injection #

8. Backend Workflow: Object Relational Mappers (ORMs) #

9. Best Practices #

• Environment Variables: Just like MongoDB, never hardcode your MySQL password into config/db.js. Always use process.env.DB_PASSWORD.

10. Common Mistakes #

• Forgetting Destructuring: The db.query() method returns an array with two things: the data rows, and the field metadata. Beginners often write const data = await db.query(...) and then try to return res.json(data). The frontend receives a massive, confusing object containing thousands of lines of metadata. Always destructure the first item from the array: const [rows] = await db.query(...).

11. Exercises #

1. 1. Explain the architectural and performance benefits of using a Connection Pool (mysql.createPool) versus creating a single connection (mysql.createConnection) for every incoming HTTP request.

12. Coding Challenges #

• Challenge: Write the controller logic for a getProductById function. Use db.query() with a SELECT statement and a WHERE id = ? clause. Extract the specific product from the returned array and send it back as JSON. Handle the case where the array is empty (product not found).

13. MCQs with Answers #

14. Interview Questions #

• Q: Explain the concept of SQL Injection. How does a hacker execute it, and how exactly do parameterized queries prevent it from occurring?

• Q: Compare and contrast the use case for a relational database (MySQL) versus a document database (MongoDB) when designing the backend for a new web application.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #