Skip to content
Learning

CRUD operations using Express.js with MySQL

A detailed guide to implement CRUD operations (Create, Read, Update, Delete) using Express.js with MySQL.

1. Prerequisites

Make sure you’ve completed these:

  • Installed Node.js, MySQL, and the required dependencies (express and mysql2).
  • Set up a MySQL database with a users table.

2. Setting Up the Project

Install express-generator

To quickly scaffold an Express.js project:

Terminal window
npx express-generator my-app

This creates a new directory called my-app with the following structure:

my-app
├── app.js
├── bin/
├── public/
├── routes/
├── views/
└── package.json
Terminal window
cd my-app

Install Dependencies

Terminal window
npm install express mysql2 express-validator

3. Set Up MySQL Database

Create a database and table:

CREATE DATABASE myapp;


USE myapp;


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

4. Configure MySQL in the Project

In my-app, create a new file db.js for database connection:

const mysql = require("mysql2");


const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "yourpassword",
  database: "myapp",
});


module.exports = pool.promise();

5. CRUD Routes with Validation

In routes/users.js, implement the CRUD operations:

const express = require("express");
const { body, validationResult } = require("express-validator");
const db = require("../db");


const router = express.Router();


// 1. Create a new user
router.post(
  "/",
  [
    body("name").notEmpty().withMessage("Name is required"),
    body("email").isEmail().withMessage("Invalid email address"),
  ],
  async (req, res) => {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }


    const { name, email } = req.body;


    try {
      const [result] = await db.query(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        [name, email]
      );
      res
        .status(201)
        .json({ message: "User created", userId: result.insertId });
    } catch (error) {
      res.status(500).json({ error: "Failed to create user", details: error });
    }
  }
);


// 2. Get all users
router.get("/", async (req, res) => {
  try {
    const [users] = await db.query("SELECT * FROM users");
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: "Failed to fetch users", details: error });
  }
});


// 3. Get a user by ID
router.get("/:id", async (req, res) => {
  const { id } = req.params;


  try {
    const [users] = await db.query("SELECT * FROM users WHERE id = ?", [id]);
    if (users.length === 0) {
      return res.status(404).json({ error: "User not found" });
    }
    res.json(users[0]);
  } catch (error) {
    res.status(500).json({ error: "Failed to fetch user", details: error });
  }
});


// 4. Update a user
router.put(
  "/:id",
  [
    body("name").notEmpty().withMessage("Name is required"),
    body("email").isEmail().withMessage("Invalid email address"),
  ],
  async (req, res) => {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }


    const { id } = req.params;
    const { name, email } = req.body;


    try {
      const [result] = await db.query(
        "UPDATE users SET name = ?, email = ? WHERE id = ?",
        [name, email, id]
      );
      if (result.affectedRows === 0) {
        return res.status(404).json({ error: "User not found" });
      }
      res.json({ message: "User updated successfully" });
    } catch (error) {
      res.status(500).json({ error: "Failed to update user", details: error });
    }
  }
);


// 5. Delete a user
router.delete("/:id", async (req, res) => {
  const { id } = req.params;


  try {
    const [result] = await db.query("DELETE FROM users WHERE id = ?", [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "User not found" });
    }
    res.json({ message: "User deleted successfully" });
  } catch (error) {
    res.status(500).json({ error: "Failed to delete user", details: error });
  }
});


module.exports = router;

6. Connect Routes

In app.js, add the following:

const usersRouter = require("./routes/users");


app.use(express.json()); // Parse JSON body
app.use("/users", usersRouter);

7. Run and Test the App

Start the Server

Run the server:

Terminal window
npm start

Test Endpoints

Use Postman or any REST client to test the following:

{
  "name": "John Doe",
  "email": "john.doe@example.com"
}
{
  "name": "Jane Doe",
  "email": "jane.doe@example.com"
}