CRUD RESTful API using Node.js (Express.js) and MySQL Database

Node JS (Express JS, MySQL) CRUD RESTful API

Here we are going to create a simple CRUD RESTful API using Node.js with Express.js Framework and MySQL Database.

And through this RESTful API, we will be inserting user name and email address into the MySQL Database (Instead of the MySQL DB, You can also use the MariaDB.).

The purpose of this tutorial is to give an idea of creating RESTful APIs using Node JS.

---

Follow the below steps to create this Node.JS CRUD RESTful API

1) Database Setup

First of all, we have to create our database and database table where we will store user information. In the following, the database information is given –

• Database Name – node_restful_api
• Table name – users

Now open your MySQL DB or MariaDB and create a new database called node_restful_api. And after creating this DB, use the following SQL code to create the `users` table and the structure of this table –

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
 `email` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

---

2) Node JS env setup & installing node packages

After completing the Database configuration, create a new folder on your Desktop, and name it whatever you want. Here I named it node-rest-api.

Now go inside the node-rest-api folder and initialize the NPM with the help of the npm init command.

After that, you have to install some Node Packages to create this application in my way. Use the following command to install those packages at once –

npm install --save express express-validator mysql2

After installing the above node packages my package.json file looks like the following –

{
  "name": "node-restful-api",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "Chandan Tudu",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "express-validator": "^6.6.0",
    "mysql2": "^2.1.0"
  }
}

---

3) Creating files & folders

Now time to create the application files & folders. But, before going further, let’s have a look at the node-rest-api folder structure. See the below image –

First, we will create the db-connection.js to create the MySQL database connection.

const mysql = require("mysql2");

const db_connection = mysql
  .createConnection({
    host: "localhost", // HOST NAME
    user: "root", // USER NAME
    database: "node_restful_api", // DATABASE NAME
    password: "", // DATABASE PASSWORD
  })
  .on("error", (err) => {
    console.log("Failed to connect to Database - ", err);
  });

module.exports = db_connection;

---

After making the Database connection, now we will create the validator.js for validating the request data.

To validating the requested data we will use the express-validator package. So if don’t know how to use this package to validate the requested data, then check out the following tutorial –

• Validate form data in Node JS (Express JS) with express-validator package

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

module.exports = {
  // User name and email Validation
  userInfo: [
    body("name", "The name must be of minimum 3 characters length")
      .optional()
      .isLength({ min: 3 })
      .trim()
      .unescape()
      .escape(),

    body("email", "Invalid email address")
      .optional()
      .trim()
      .unescape()
      .escape()
      .isEmail()
      .custom(async (value) => {
        // Checking that the email already in use or NOT
        const [row] = await db_connection.execute(
          "SELECT `email` FROM `users` WHERE `email`=?",
          [value]
        );
        if (row.length > 0) {
          return Promise.reject("E-mail already in use");
        }
      }),
  ],

  // User ID Validation
  userID: [param("id", "Invalid User ID").trim().isInt()],

  // Checking Validation Result
  result: (req, res, next) => {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(422).json({ errors: errors.array() });
    }
    next();
  },
};

---

Now we will create the routes.js for specifying the API endpoints.

const router = require('express').Router();
const validators = require('./validators');
const userController = require('./controllers/userController');

// Inserting User
router.post(
    '/insert-user',
    validators.userInfo,
    validators.result,
    userController.insert
    );

// Fetching all users
router.get(
    '/get-all-users',
    userController.getAllUsers
    );

// Fetching Single User By ID
router.get(
    '/get-user/:id',
    validators.userID,
    validators.result,
    userController.getUserByID
    );

// Updating User
router.patch(
    '/update-user/:id',
    [...validators.userID, ...validators.userInfo],
    validators.result,
    userController.updateUser
    );
    
// Deleting User
router.delete(
    '/delete-user/:id',
    validators.userID,
    validators.result,
    userController.deleteUser
    );

module.exports = router;

---

After creating the user routes, now we will create a controller for the routes. This controller is for handling user routes, that’s why we’ll name it userController.js.

For separating the controllers, we will create a new folder called controllers in the root of the application folder. And inside this folder, we have to create the userController.js.

const db_connection = require("../db-connection").promise();

// INSERTING USER
exports.insert = async (req, res, next) => {

  if (!req.body.name || !req.body.email) {
    return res.status(400).json({
      message: "Please fill in all the required fields.",
      fields: ["name", "email"],
    });
  }

  try {
      
    const [rows] = await db_connection.execute(
      "INSERT INTO `users`(`name`,`email`) VALUES(?, ?)",
      [req.body.name, req.body.email]
    );

    if (rows.affectedRows === 1) {
      return res.status(201).json({
        message: "The user has been successfully inserted.",
        userID: rows.insertId,
      });
    }

  } catch (err) {
    next(err);
  }
  
};

// FETCHING ALL USERS
exports.getAllUsers = async (req, res, next) => {
  try {

    const [rows] = await db_connection.execute("SELECT * FROM `users`");

    if (rows.length === 0) {
      return res.status(200).json({
        message:
          "There are no users in the database, please insert some users.",
      });
    }

    res.status(200).json(rows);

  } catch (err) {
    next(err);
  }

};


// FETCHING SINGLE USER
exports.getUserByID = async (req, res, next) => {

  try {

    const [row] = await db_connection.execute(
        "SELECT * FROM `users` WHERE `id`=?",
        [req.params.id]
    );

    if (row.length === 0) {
      return res.status(404).json({
        message: "No User Found!",
      });
    }

    res.status(200).json(row[0]);

  } catch (err) {
    next(err);
  }

};

// UPDATING USER
exports.updateUser = async (req, res, next) => {
  try {

    const [row] = await db_connection.execute(
        "SELECT * FROM `users` WHERE `id`=?",
        [req.params.id]
    );

    if (row.length === 0) {
      return res.status(404).json({
        message: "Invalid User ID",
      });
    }

    if (req.body.name) row[0].name = req.body.name;

    if (req.body.email) row[0].email = req.body.email;

    const [update] = await db_connection.execute(
      "UPDATE `users` SET `name`=?, `email`=? WHERE `id`=?",
      [row[0].name, row[0].email, req.params.id]
    );

    if (update.affectedRows === 1) {
      return res.json({
        message: "The User has been successfully updated.",
      });
    }

  } catch (err) {
    next(err);
  }

};

// DELETING USER
exports.deleteUser = async (req, res, next) => {

  try {

    const [row] = await db_connection.execute(
        "DELETE FROM `users` WHERE `id`=?",
        [req.params.id]
    );

    if (row.affectedRows === 0) {
      return res.status(404).json({
        message: "Invalid user ID (No User Found!)",
      });
    }

    res.status(200).json({
      message: "The user has been deleted successfully.",
    });
    
  } catch (err) {
    next(err);
  }

};

---

At the end of the files and folder creation, we will create the index.js, where we will create the server, apply routes middleware, and handling errors.

const express = require("express");
const app = express();
const allRoutes = require("./routes");

// It parses incoming requests with JSON payloads
app.use(express.json());

// Applying All Routes
app.use(allRoutes);

// Handling Errors
app.use((err, req, res, next) => {
  // console.log(err);
  err.statusCode = err.statusCode || 500;
  err.message = err.message || "Internal Server Error";
  res.status(err.statusCode).json({
    message: err.message,
  });
});

app.listen(3000, () => console.log("Server is running on port 3000"));

---

Testing of the Node.js CRUD RESTful API Application

First, start the server by running the nodemon index command and also make sure that your MySQL server is also running.

To test this API application I will use the Postman.

API URLs –

POST   - http://localhost:3000/insert-user

GET    - http://localhost:3000/get-all-users

GET    - http://localhost:3000/get-user/{user_id}

PATCH  - http://localhost:3000/update-user/{user_id}

DELETE - http://localhost:3000/delete-user/{user_id}

---

Inserting User

POST - http://localhost:3000/insert-user

---

Getting all users

GET - http://localhost:3000/get-all-users

---

Getting single user by ID

GET - http://localhost:3000/get-user/{user_id}

---

Updating User (id=1)

PATCH - http://localhost:3000/update-user/{user_id}

---

Result after updating the user

---

Deleting the user whose ID is 1

DELETE - http://localhost:3000/delete-user/{user_id}

---

Result after deleting the user 1

---