Build Node.JS RESTful APIs with MySQL using Sequelize in simple steps: Build Node.JS RESTful APIs with MySQL using Sequelize in simple steps

In this tutorial, we will learn how to create a RESTful API using Node.JS and MySQL.

For information about RESTful APIs you can visit my previous Build Node.JS RESTful APIs with MongoDB in Simple Steps Blog.

Tools:

Node.JS
MySQL
Text Editor (Visual Studio Code, Sublime, Atom, etc.)
PostMan

Getting Started

For the purpose of this tutorial, I'll create RESTful APIs. To achieve this, I'll use the above-mentioned tools.

Building a Node.JS RESTful APIs is a four-step Process. Following are the steps given below to build Node.JS RESTful APIs.

Step 1: Create a Folder Name say todolist_mysql_blog. You can give whatever name you would like to your folder. Get inside your created folder and then run the init command.

mkdir todolist_mysql_blog
cd todolist_mysql_blog
npm init -y

After doing these steps. Your directory should look something like this

|_todolist_mysql_blog
|__package.json

The "package.json" file may consist of all the project settings and other npm package dependencies. If you open that file it should look like this

{
  "name": "todolist_mysql_blog",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Step 2: Create a "server.js" file. In this file, we'll create our server.

Inside that same folder, we'll create 3 more folders named "Controller", "Routes", and "Models".

After doing this step, your directory will look like this

Step 3: Installing npm modules for Server

At this point, let's set up our server. For which, I'm using express, nodemon and body-parser. I'm also using nodemon module which helps develop Node.js based applications by automatically restarting the node application when file changes in the directory are detected. For this purpose, I'm using the npm command

npm install express body-parser --save
npm install nodemon --save-dev

After installing these packages your "package.json" file will look like this. I've added a start script in our "package.json" file. Now instead of running node server.js, now I'll run the npm start command to start my server.

{
  "name": "todolist_mysql_blog",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "nodemon server.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.20.0",
    "express": "^4.18.1"
  },
  "devDependencies": {
    "nodemon": "^2.0.19"
  }
}

Step 4: Server Setup

For setting up the server, open your "server.js" file, and let's make some changes to that file.

const express = require('express');
const bodyParser = require('body-parser');
const PORT = process.env.PORT || 3000;

const app = express();

// using bodyParser to parse JSON bodies into JS objects
app.use(bodyParser.json());

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

After setting up your server, when you start the server using the npm start command this will produce the following output

CONGRATULATIONS.....You've successfully set up your SERVER file. Let's move to the next part of creating the model.

Step 5: Installing sequelize and mysql2

As I already told you that I'm using MySQL as my database for which we've to install the sequelize module.

What is Sequelize?

Ssequelize is an easy-to-use and promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, DB2, Microsoft SQL Server, and Snowflake. It features solid transaction support, relations, eager and lazy loading, read replication, and more.

Let's install Sequelize using the following command in our project.

npm i sequelize --save

What is mysql2?

mysql2 is free from native bindings and can be installed on Linux, Mac OS, or Windows without any issues.

Let's install Sequelize using the following command in our project.

npm i mysql2 --save

After installing this package, your "package.json" file has a new module namely sequelize and mysql2 under the dependencies section.

Step 6: Create a Database

After installing the sequelize module into our project. Let's first create a database. In MySQL, we can create the database using the following command

CREATE DATABASE todolist;

Step 7: Connecting MySQL

After successful installation of the MySQL module and database creation. Let's connect the database with our server. Let's make some changes to that file for connecting the server with the database. I've created a database name "todolist" which may contain all the tables which we'll make into our database. I've created an "index.js" file under the Models folder.

const { Sequelize, DataTypes } = require('sequelize')

const dbName = 'todolist'
const dbUserName = 'root'
const passWord = 'Jatin1996'

const sequelize = new Sequelize(dbName, dbUserName, passWord, {
    host: 'localhost',
    dialect: 'mysql'
})

sequelize.authenticate().then(() => {
    console.log('SQL Database Connected!!')
}).catch((error) => {
    console.log('index authenticate error => ', error)
})

const db = {};
db.Sequelize = Sequelize
db.sequelize = sequelize

db.sequelize.sync().then(() => {
    console.log('DataBase Synced!!')
}).catch((error) => {
    console.log('sync error => ', error)
})

module.exports = db;

We've used different functions like Sequelize, DataTypes, new Sequelize, authenticate, sync, etc.., To learn more about these functions, you can refer to the official Sequelize Documentation. After making that file. Let's import that file into our "server.js" file.

After successful import of the database file, our project may console the above messages which are as follows

Server is running at 3000 port
SQL Database Connected!!
DataBase Synced!!

Step 8: Setting up the Schema

After a successful connection with MySQL. Let's set up the schema for task table named "todo.js". The task will be the table in our database.

module.exports = (sequelize, DataTypes) => {
    const Task = sequelize.define("task", {
        taskname: {
            type: DataTypes.STRING,
            allowNull: false
        },
        priority: {
            type: DataTypes.INTEGER,
            allowNull: false
        },
        completed: {
            type: DataTypes.BOOLEAN,
            allowNull: false
        }
    }, {
        timestamps: true
    })

    return Task;
};

After setting up the schema. Let's include it in our database. For including any schema in the database, we simply include that file in the "index.js" file of the Models folder with sequelize and DataTypes as their parameters. Just before the "db.sequelize.sync" line.

...
db.task = require('./Todo/todo')(sequelize, DataTypes)...

After adding that above line into the "index.js" file. When you checked your database, the "tasks" named table is available inside your database.

Step 9: Setting up the Routes

After the Creation of Schema. Let's move forward to API Routes creation.

Before making the routes. Let's discuss something about the Routes and their methods.

Routing defines the way in which the client requests are handled by the application endpoints.

A Routing method is derived from one of the HTTP methods and is attached to an instance of the express class.

Express supports methods that correspond to all HTTP request methods: get, post, and so on. For a full list. For more, you can visit expressApp.Methods

For the API Routes, I've created an "index.js" file under the Routes folder which we've previously created.

After creating that file. Let's import that file into our "server.js" file. After importing the "index.js" file of the Routes folder our "server.js" file will look like this

const express = require('express');
const bodyParser = require('body-parser');
const PORT = process.env.PORT || 3000;
const db = require('./Models/');
const publicRoute = require('./Routes');

const app = express();

// using bodyParser to parse JSON bodies into JS objects
app.use(bodyParser.json());

app.use('/api', publicRoute);

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

After importing the "index.js" file from the Routes folder. Let's make some API routes into that file.

For every route, there will be a controller function which we'll import from the "index.js" file from the Controller folder.

const router = require('express').Router();
const Controller = require('../Controller');

router.get('/home', Controller.homecontroller);
router.post('/createtask', Controller.createTaskController);
router.get('/gettask', Controller.fetchAllTaskController);
router.delete('/removetask/:taskId', Controller.removeTaskByTaskIdController);
router.patch('/updatetask', Controller.updateTaskController);
router.patch('/changestatus', Controller.changeTodoStatus);

module.exports = router;

As I've attached the Controller index file to our routes index file. Let's make some changes to Controller's index file.

const Services = require('../Models/Todo/Services');

const homecontroller = async (req, res, next) => {
    try {
        res.status(200).send('Welcome to Home Page Controller !!');
    } catch (error) {
        next(error);
    }
};

const createTaskController = async (req, res, next) => {
    try {
        const isPriorityExist = await Services.checkIsPriorityExist(req.body.priority);
        if (isPriorityExist) {
            return res.status(200).json({
                error: true,
                message: 'Same Priority Already Exists!!'
            });
        }

        let reqObj = {
            priority: req.body.priority,
            taskname: req.body.taskName,
            completed: req.body.completed
        };

        const response = await Services.createNewTask(reqObj);

        if (response) {
            return res.status(201).json({
                error: false,
                data: response,
                message: 'Task Created Successfully!!'
            });
        } else {
            return res.status(200).json({
                error: true,
                message: 'Unable to Create any Task!!'
            });
        }
    } catch (error) {
        next(error);
    }
};

const fetchAllTaskController = async (req, res, next) => {
    try {
        const response = await Services.fetchAllTask();

        if (response.length) {
            res.status(200).json({
                error: false,
                message: 'Available Task',
                data: response
            });
        } else{
            res.status(200).json({
                error: false,
                message: 'No Task Available',
                data: []
            });
        }
    } catch (error) {
        next(error);
    }
};

const removeTaskByTaskIdController = async (req, res, next) => {
    try {
        const isTaskExist = await Services.checkIsTaskExist(req.params.taskId);

        if (!isTaskExist) {
            return res.status(200).json({
                error: true,
                message: 'No Such Task Avaiable!!'
            });
        }

        const response = await Services.removeTaskById(req.params.taskId);

        if (response) {
            return res.status(200).json({
                error: false,
                message: 'Task Removed Successfully!!'
            });
        } else {
            return res.status(200).json({
                error: true,
                message: 'Error Occurred While Removing the Task!!'
            });
        }
    } catch (error) {
        next(error);
    }
};

const updateTaskController = async (req, res, next) => {
    try {
        const isTaskExist = await Services.checkIsTaskExist(req.body.taskId);

        if (!isTaskExist) {
            return res.status(200).json({
                error: true,
                message: 'No Such Task Avaiable!!'
            });
        }

        const isPriorityAvailable = await Services.checkIsPriorityAvailable(req.body);

        if (isPriorityAvailable) {
            return res.status(200).json({
                error: true,
                message: 'Same Priority Already Exists!!'
            });
        }

        const response = await Services.updateAvailableTask(req.body);

        if (response) {
            return res.status(200).json({
                error: false,
                message: 'Task Updated Successfully!!'
            });
        } else {
            return res.status(200).json({
                error: true,
                message: 'Error Occurred While updating the Task!!'
            });
        }
    } catch (error) {
        next(error);
    }
};

const changeTodoStatus = async (req, res, next) => {
    try {
        const isTaskExist = await Services.checkIsTaskExist(req.body.todoId);

        if (!isTaskExist) {
            return res.status(200).json({
                error: true,
                message: 'No Such Task Avaiable!!'
            });
        }

        const response = await Services.updateStatus(req.body.todoId);

        if (response) {
            return res.status(200).json({
                error: false,
                message: 'Todo Status Updated!!'
            });
        } else {
            return res.status(200).json({
                error: true,
                message: 'Error Occurred while Changing the Todo Status!!'
            });
        }
    } catch (error) {
        next(error);
    }
};

module.exports = {
    homecontroller,
    createTaskController,
    fetchAllTaskController,
    removeTaskByTaskIdController,
    updateTaskController,
    changeTodoStatus
};

As I've used the Services file in the Controller. So, let's make some changes to that file too.

const { Sequelize } = require('sequelize');
const dataBase = require('../index');
const Tasks = dataBase.task;

const checkIsPriorityExist = async priority => {
    const isExist = await Tasks.findOne({
        where: {
            priority
        }
    });
    return isExist;
};

const createNewTask = async reqObj => {
    return await Tasks.create(reqObj);
};

const fetchAllTask = async () => {
    return await Tasks.findAll();
};

const checkIsTaskExist = async taskId => {
    const isTaskExist = await Tasks.findOne({
        where: {
            id: taskId
        }
    });

    if (isTaskExist) {
        return true;
    } else {
        return false;
    }
};

const removeTaskById = async taskId => {
    return await Tasks.destroy({
        where: {
            id: taskId
        }
    });
};

const checkIsPriorityAvailable = async body => {
    const isDataExist = await Tasks.findOne({
        where: {
            priority: body.priority
        }
    });

    if ((!isDataExist) || (isDataExist.id == body.taskId)) {
        return false;
    }

    return true;
};

const updateAvailableTask = async body => {
    return await Tasks.update(body, {
        where: {
            id: body.taskId
        }
    });
};

const updateStatus = async todoId => {
    const isDataExist = await Tasks.findOne({
        where: {
            id: todoId
        }
    });

    return await Tasks.update({
        completed: !isDataExist.completed
    }, {
        where: {
            id: todoId
        }
    });
}

module.exports = {
    checkIsPriorityExist,
    createNewTask,
    fetchAllTask,
    checkIsTaskExist,
    removeTaskById,
    checkIsPriorityAvailable,
    updateAvailableTask,
    updateStatus
};

Once you are done with that. CONGRATULATIONS.....You've successfully created all the APIs.

Testing via PostMan

Now that everything is now connected, let's test each of the routes as per their respective methods.

Once you open Postman or another application like the postman.

http://localhost:3000/home is the URL for home page to check whether the APIs are hitting or not. Once you hit this path, you'll get "Welcome to Home Page Controller !!" as an output.

Adding a Middleware

After hitting all these above-created APIs, what happens if we entered the wrong route? Say, if you entered a route like "http://localhost:3000/xyz" or "http://localhost:3000/xyz/ajs". It responds with a message "Cannot GET /xyz or /xyz/ajs". Let's add an express middleware that could help us to return more interactive messages.

Middlewares basically intercept incoming HTTP requests and as such, you can use them to perform several operations ranging from authentication to validations, etc. You can visit Middleware for more info.

To do this, open your "server.js" file and write the code snippet into it just before the "app.listen" line.

app.use(function(req, res) {
  res.status(404).send({url: req.originalUrl + ' not found'})
});

Just like that.

Adding an Error Handler

What if any API is throwing an error? For that purpose, we're going to add an error handler function inside our "server.js" file. If any API, will throw an error this handler will handle that error for us.

For that, we've to insert the following code just above the "app.listen" line.

.....// error handler
app.use(function (err, req, res, next) {
    return res.status(500).send({
        error: true,
        message: err
    });
});
.....

Just like that