We use cookies to ensure that we give you the best experience on our website.

CRUD API in Node JS + MySQL

How to Make CRUD API in Node JS + MySQL?

Here you will learn how to build a very simple CRUD application in Node JS with MySQL Database.

1. Create the MySQL Database first

First, create a database with the name you want, I named it node_api. After creating the database –

We have to create a table called posts, and there will be 6 columns inside the table – id, title, content, author, created_at, updated_at.

Use the following SQL code to create the posts table and its structure.

CREATE TABLE `posts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    `content` text NOT NULL,
    `author` varchar(30) NOT NULL,
    `created_at` date NOT NULL DEFAULT current_timestamp(),
    `updated_at` date NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

2. Building the Node JS CRUD REST API

First, create a folder called node-mysql-crud-api, this is our project folder. After that, run npm init -y on your terminal to initialize the npm into this folder.

Now you have to install three packages – express, express-validator, and mysql2.

npm i express express-validator mysql2

In this project, we will use es6 import, so you have to add "type": "module" in the package.json.

add type module in package.json file

Application folder structure

Here given in the below image are the files and folders that we have to create to build this Node JS CRUD API.

Node MySQL CRUD API project folder structure

Database Connection

database.js contains the code for the database connection.

import mysql from "mysql2";

const connection = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "",
    database: "node_api",
});

export default connection.promise();

Routes and Controllers

  • routes.js – contains all the routes with validation rules.
  • Controller.js – The Controller class contains all the callbacks that handle all the requests and perform all CRUD operations.
import { Router } from "express";
import { body, param } from "express-validator";
import Controller from "./Controller.js";

const routes = Router({ strict: true });

routes.post(
    "/create",
    [
        body("title", "Must not be empty.").trim().not().isEmpty().escape(),
        body("body", "Must not be empty.").trim().not().isEmpty().escape(),
        body("author", "Must not be empty.").trim().not().isEmpty().escape(),
    ],
    Controller.validation,
    Controller.create
);

routes.get("/posts", Controller.show_posts);
routes.get(
    "/post/:id",
    [param("id", "Invalid post ID.").exists().isNumeric().toInt()],
    Controller.validation,
    Controller.show_posts
);

routes.put(
    "/edit",
    [
        body("post_id", "Invalid post ID").isNumeric().toInt(),
        body("title", "Must not be empty.")
            .optional()
            .trim()
            .not()
            .isEmpty()
            .escape(),
        body("body", "Must not be empty.")
            .optional()
            .trim()
            .not()
            .isEmpty()
            .escape(),
        body("author", "Must not be empty.")
            .optional()
            .trim()
            .not()
            .isEmpty()
            .escape(),
    ],
    Controller.validation,
    Controller.edit_post
);

routes.delete(
    "/delete",
    [
        body("post_id", "Please provide a valid post ID.")
            .exists()
            .isNumeric()
            .toInt(),
    ],
    Controller.validation,
    Controller.delete_post
);

export default routes;
import { validationResult, matchedData } from "express-validator";
import DB from "./database.js";

const validation_result = validationResult.withDefaults({
    formatter: (error) => error.msg,
});

class Controller {
    static validation = (req, res, next) => {
        const errors = validation_result(req).mapped();
        if (Object.keys(errors).length) {
            return res.status(422).json({
                ok: 0,
                status: 422,
                errors,
            });
        }
        next();
    };

    static create = async (req, res, next) => {
        const { title, body, author } = matchedData(req);
        try {
            const [result] = await DB.execute(
                "INSERT INTO `posts` (`title`,`content`,`author`) VALUES (?,?,?)",
                [title, body, author]
            );
            res.status(201).json({
                ok: 1,
                status: 201,
                message: "Post has been created successfully",
                post_id: result.insertId,
            });
        } catch (e) {
            next(e);
        }
    };

    static show_posts = async (req, res, next) => {
        try {
            let sql = "SELECT * FROM `posts`";
            if (req.params.id) {
                sql = `SELECT * FROM posts WHERE id=${req.params.id}`;
            }
            const [row] = await DB.query(sql);
            if (row.length === 0 && req.params.id) {
                return res.status(404).json({
                    ok: 0,
                    status: 404,
                    message: "Invalid post ID.",
                });
            }
            const post = req.params.id ? { post: row[0] } : { posts: row };
            res.status(200).json({
                ok: 1,
                status: 200,
                ...post,
            });
        } catch (e) {
            next(e);
        }
    };

    static edit_post = async (req, res, next) => {
        try {
            const data = matchedData(req);
            const [row] = await DB.query("SELECT * FROM `posts` WHERE `id`=?", [
                data.post_id,
            ]);

            if (row.length !== 1) {
                return res.json({
                    ok: 0,
                    statu: 404,
                    message: "Invalid post ID.",
                });
            }
            const post = row[0];
            const date = new Date().toISOString();
            const title = data.title || post.title;
            const content = data.body || post.content;
            const author = data.author || post.author;
            await DB.execute(
                "UPDATE `posts` SET `title`=?, `content`=?,`author`=?, `updated_at`=? WHERE `id`=?",
                [title, content, author, date, data.post_id]
            );
            res.json({
                ok: 1,
                status: 200,
                message: "Post Updated Successfully",
            });
        } catch (e) {
            next(e);
        }
    };

    static delete_post = async (req, res, next) => {
        try {
            const [result] = await DB.execute(
                "DELETE FROM `posts` WHERE `id`=?",
                [req.body.post_id]
            );
            if (result.affectedRows) {
                return res.json({
                    ok: 1,
                    status: 200,
                    message: "Post has been deleted successfully.",
                });
            }
            res.status(404).json({
                ok: 0,
                status: 404,
                message: "Invalid post ID.",
            });
        } catch (e) {
            next(e);
        }
    };
}

export default Controller;

index.js – where the execution of the application will start

import express from "express";
import routes from "./routes.js";

const app = express();
const PORT = 3000;
app.use(express.json());
app.use(routes);

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

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

3. Testing of the Node JS CRUD API

End-Point: /create
Method: Post,
Payload:
{
    "title":"Post Title",
    "body":"Post Body",
    "author":"Author Name"
}
node crud api creating new post
# Fetch all Posts
End-Point: /posts
Method: GET,

# Fetch a single post 
/post/{post_id} -> /post/2
Method: Get
node crud api fetch all data and single data
End-Point: /edit
Method: PUT,
Payload: (post_id and at least one field is required)
{
    "post_id": "post id",
    "field_name (title | body | author)" : "New Value"
}
node crud api update data
End-Point: /delete
Method: DELETE,
Payload:
{
    "post_id": "post id"
}
node crud api delete data

Related Posts