Reading Time: 10 minutes

390 Views

Creating CRUD (Create, Read, Update, Delete) APIs is a critical component of developing web applications since it allows you to interact with and manage data in a methodical manner. The combination of Node.js, a popular runtime environment, and the Express.js framework provides a robust foundation for constructing such APIs.

We will walk you through the process of creating CRUD APIs with Node.js, Express.js, Sequelize, and MySQL, allowing you to create, retrieve, update, and delete data in a database using RESTful API endpoints.

Throughout this article, we’ll look at how to set up an Express.js server, define API routes, connect to a MySQL database, and execute CRUD operations.

Let’s get started.

What is an API?

An API, or Application Programming Interface, is a set of rules, protocols, and tools that enable various software applications to communicate with one another. It specifies the techniques and data formats that applications can employ to obtain and exchange data.

APIs are critical for integrating various systems, services, and applications, as well as facilitating data transfer and functionality across many platforms and technologies.

Project Setup

To work and create CRUD APIs we will do a node setup from scratch. And here we will use these following packages,

express
mysql2
sequelize
body-parser
nodemon

Step #1

Create an application folder with any name say “project” in your local machine. Open project into terminal and run this command to create package.json file.

npm init -y

Above command will create package.json file with all default settings. This file is very important for any node application.

Next,

Step #2

You need to install the needed packages. Run this command to install all packages in one line,

 npm install express nodemon mysql2 sequelize body-parser

Once all packages will be installed, you will see into package.json file as:

"dependencies": {
  "body-parser": "^1.20.2",
  "express": "^4.18.2",
  "mysql2": "^3.6.1",
  "nodemon": "^3.0.1",
  "sequelize": "^6.33.0"
}

Step #3

Create few more application files app.js, database.js, and routes.js

Once you create this file then you need to update the “main” property of object of package.json file. Here, is the view of application setup:

Node CRUD APIs: Database & Table Setup

Open PhpMyAdmin in your system. First create a database say “node_crud_apis“.

Next,

Open database.js file and write this complete code into it,

const { Sequelize, DataTypes } = require("sequelize");

// Database object
const sequelizeDbObject = new Sequelize("node_crud_apis", "<MySQL_USER>", "<MySQL_PASSWORD>", {
    host: "localhost",
    dialect: "mysql"
});

// Database check
sequelizeDbObject.authenticate().then((response) => {

    console.log("Database connected");
}).catch((error) => {

    console.log("Failed to connect with database");
});

// Create "users" table
const userTable = sequelizeDbObject.define("users", {
    name: {
        type: DataTypes.STRING(120),
        allowNull: false
    },
    email: {
        type: DataTypes.STRING(80),
        allowNull: false,
    },
    phone_number: {
        type: DataTypes.STRING(50),
        allowNull: true,
    },
    gender: {
        type: DataTypes.ENUM("male", "female", "other"),
        defaultValue: "male"
    }
}, {
    timestamps: false
});

// Sync table to create in database
sequelizeDbObject.sync();

module.exports = {
    "UserTable": userTable
};

Node CRUD APIs: Setup of APIs Routes

To create CRUD APIs we will use routes.js file.

Open routes.js and write this complete code into it,

const express = require("express");
const { UserTable } = require("./database");

const router = express.Router();

// Add User API
router.post("/add-user", (req, res) => {

    // email check
    UserTable.findOne({
        where: {
            email: req.body.email
        }
    }).then((user) => {

        if (user) {

            // user exists
            res.json({
                status: false,
                message: "User already exists with this email"
            });
        } else {

            // name, email, gender, phone_number
            UserTable.create({
                name: req.body.name,
                email: req.body.email,
                gender: req.body.gender,
                phone_number: req.body.phone
            }).then((response) => {

                res.json({
                    stauts: true,
                    message: "User created successfully"
                });
            }).catch((error) => {

                res.json({
                    status: false,
                    message: "Failed to create user"
                })
            });
        }
    }).catch((error) => {

        console.log(error);
    });
});

// List User API
router.get("/list-user", (req, res) => {

    UserTable.findAll().then((user) => {

        if (user.length > 0) {

            res.json({
                status: true,
                message: "Users found",
                data: user
            });
        } else {

            res.json({
                status: false,
                message: "No User found"
            });
        }
    }).catch((error) => {

        res.json({
            status: false,
            message: "Failed to get user data"
        })
    });
});

// Update User API
router.put("/update-user/:id", (req, res) => {

    // email check
    UserTable.findOne({
        where: {
            id: req.params.id
        }
    }).then((user) => {

        if (user) {

            // Update: name, gender, phone_number
            UserTable.update({
                name: req.body.name,
                gender: req.body.gender,
                phone_number: req.body.phone
            }, {
                where: {
                    id: req.params.id
                }
            }).then((response) => {

                res.json({
                    stauts: true,
                    message: "User updated successfully"
                });
            }).catch((error) => {

                res.json({
                    status: false,
                    message: "Failed to update user"
                });
            });
        } else {

            res.json({
                status: false,
                message: "No user data found"
            });
        }
    }).catch((error) => {

        res.json({
            status: false,
            message: "Failed to find user"
        });
    });
});

// Delete User API
router.delete("/delete-user/:id", (req, res) => {

    UserTable.findOne({
        where: {
            id: req.params.id
        }
    }).then((user) => {

        // user is empty or not
        if (user) {

            UserTable.destroy({
                where: {
                    id: req.params.id
                }
            }).then((response) => {

                res.json({
                    status: true,
                    message: "User deleted"
                });
            }).catch((error) => {

                res.json({
                    status: false,
                    message: "Failed to delete user"
                })
            })
        } else {

            res.json({
                status: false,
                message: "No user found"
            });
        }
    }).catch((error) => {

        res.json({
            status: false,
            message: "Failed to get user data"
        });
    });
});

// Welcome page route
router.get("/", (req, res) => {

    res.json({
        status: true,
        message: "Welcome to Landing page of Node Express CRUD APIs"
    })
});

module.exports = router;

Node CRUD APIs: Main Application File Settings

Here, you will see the main entry of file is app.js.

Open app.js and write this complete code into it,

const express = require("express");
const appRoutes = require("./routes");
const bodyParser = require("body-parser");

const app = express();
const PORT = 8087;

app.use(bodyParser.json());
app.use("/", appRoutes);

// Listen application requests
app.listen(PORT, () => {

    console.log("Application started");
});

Application Testing

Open project terminal and run this command,

npx nodemon

It will start development server.

Once you server will start. You will see your table called “users” automatically created inside database.

Add User API

URL – http://localhost:8087/add-user

Method – POST

Header –

Content-Type:application/json

Form data –

{
   "name": "Sanjay Kumar",
   "email": "sanjay.example@example.net",
   "gender": "male",
   "phone": "8529632140"
}

Screenshot –

List User API

URL – http://localhost:8087/list-user

Method – GET

Screenshot –

Update User API

URL – http://localhost:8087/update-user/2

Method – PUT

Header –

Content-Type:application/json

Form data –

{
   "name": "Ayasha Dubey",
   "gender": "female",
   "phone": "8526974105"
}

Screenshot –

Delete User API

URL – http://localhost:8087/delete-user/3

Method – DELETE

It will delete the user of given ID.

That’s it.

We hope this article helped you to learn about Building CRUD APIs with Node.js, Express, and Sequelize in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.