Express.js Working with MySQL

MySQL is a widely used relational database for structured data storage in web applications. Express.js, a minimal and flexible Node.js framework, integrates seamlessly with MySQL to create efficient and scalable backends. This tutorial will guide you through setting up MySQL with Express.js and implementing CRUD (Create, Read, Update, Delete) operations.

Setting Up Express.js with MySQL

To get started, set up an Express.js project and connect it to a MySQL database.

Install Required Packages

First, create a new Node.js project and install the necessary packages:

mkdir express-mysql-app  
cd express-mysql-app  
npm init -y  
npm install express mysql2  

• express: The Express.js framework.
• mysql2: A MySQL client for Node.js with support for Promises.

Create a MySQL Database

Open your MySQL client and run the following SQL commands to create the database and table.

CREATE DATABASE express_db;  
USE express_db;  

CREATE TABLE users (  
  id INT AUTO_INCREMENT PRIMARY KEY,  
  name VARCHAR(50) NOT NULL,  
  email VARCHAR(50) NOT NULL UNIQUE  
);  

Set Up the Database Connection

Create a file named db.js to set up the connection to the MySQL database:

const mysql = require("mysql2/promise"); // Import MySQL with Promises  

async function connectDB() {  
    const db = await mysql.createConnection({  
        host: "localhost",  
        user: "root", // Replace with your MySQL username  
        password: "", // Replace with your MySQL password  
        database: "express_db"  
    });  

    console.log("Connected to MySQL database");  
    return db;  
}  

module.exports = connectDB;  

Initialize Express.js

Create a file named app.js and initialize Express.js:

const express = require("express"); // Import Express.js  
const connectDB = require("./db"); // Import database connection  

const app = express();  
app.use(express.json()); // Enable JSON parsing  

const PORT = 3000;  
app.listen(PORT, () => {  
    console.log(`Server running on port ${PORT}`);  
});  

Run the server:

node app.js

If everything is set up correctly, the database connection will be established, and the Express server will start.

Defining MySQL Query Functions

Create a new file named userModel.js to handle database operations.

const connectDB = require("./db"); // Import MySQL connection  

// Insert a new user  
async function createUser(name, email) {  
    const db = await connectDB();  
    const sql = "INSERT INTO users (name, email) VALUES (?, ?)";  
    const [result] = await db.execute(sql, [name, email]);  
    return { id: result.insertId, name, email };  
}  

// Retrieve all users  
async function getAllUsers() {  
    const db = await connectDB();  
    const sql = "SELECT * FROM users";  
    const [users] = await db.execute(sql);  
    return users;  
}  

// Update a user by ID  
async function updateUser(id, name, email) {  
    const db = await connectDB();  
    const sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";  
    await db.execute(sql, [name, email, id]);  
    return { id, name, email };  
}  

// Delete a user by ID  
async function deleteUser(id) {  
    const db = await connectDB();  
    const sql = "DELETE FROM users WHERE id = ?";  
    await db.execute(sql, [id]);  
    return { message: "User deleted successfully" };  
}  

module.exports = { createUser, getAllUsers, updateUser, deleteUser };  

Implementing CRUD Operations

Create a new file userRoutes.js to define API routes.

Create a New User (POST)

const express = require("express");  
const router = express.Router();  
const userModel = require("./userModel"); // Import user model  

// Create a new user  
router.post("/users", async (req, res) => {  
    try {  
        const { name, email } = req.body;  
        const newUser = await userModel.createUser(name, email);  
        res.status(201).json(newUser);  
    } catch (error) {  
        res.status(500).json({ error: error.message });  
    }  
});  

Send a POST request to http://localhost:3000/api/users with the following JSON:

{
  "name": "Rahul",
  "email": "[email protected]"
}

Retrieve All Users (GET)

router.get("/users", async (req, res) => {
    try {
        const users = await userModel.getAllUsers();
        res.json(users);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

Access the endpoint in the browser:

http://localhost:3000/api/users

Update a User (PUT)

// Update a user by ID
router.put("/users/:id", async (req, res) => {
    try {
        const { id } = req.params;
        const { name, email } = req.body;
        const updatedUser = await userModel.updateUser(id, name, email);
        res.json(updatedUser);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

Send a PUT request to update a user:

{
  "name": "Emma",
  "email": "[email protected]"
}

Delete a User (DELETE)

// Delete a user by ID
router.delete("/users/:id", async (req, res) => {
    try {
        const { id } = req.params;
        const result = await userModel.deleteUser(id);
        res.json(result);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

Send a DELETE request to:

http://localhost:3000/api/users/1

Integrating Routes in app.js

Add the following code to app.js:

const userRoutes = require("./userRoutes"); // Import user routes
app.use("/api", userRoutes); // Prefix routes with /api

Testing Your Setup

Start the server:

node app.js

API Endpoints

Use tools like Postman or cURL to test the API endpoints.

Example:

curl -X POST http://localhost:3000/api/users \
-H "Content-Type: application/json" \
-d '{"name":"Rahul","email":"[email protected]"}'

Conclusion

You have successfully integrated MySQL with Express.js and implemented CRUD operations using mysql2. This setup allows you to build RESTful APIs that interact with structured data efficiently.

Key Takeaways:

• Use mysql2/promise for better performance and Promises support.
• Separate database logic (userModel.js) from routes (userRoutes.js).
• Implement CRUD operations with async/await.
• Organize API endpoints in app.js for scalability.

You can extend this setup further by adding authentication (JWT), input validation (Joi), pagination, and advanced filtering.