Node Js MySql Rest API

Node Js MySql Rest API

In this tutorial, we will create a simple REST API that will enable us to create TODO Application.We shall create API’s to add new ToDo Item and perform other CRUD operations like Update,Delete and Get the TODO items from the database

We will be using Node.js, Express and MySql with Sequelize ORM to implement the TODO Application

Prerequisite Before starting the tutorial

Please make sure you have below software installed on your local system

  1. Node Js
  2. Mysql
  3. Postman (Client to test our API’s)

Steps that we will follow to complete the whole application

  1. Create a new Database
  2. Create a new NodeJs Project 
  3. Install Dependencies via npm
  4. Create a Node Server
  5. Setup Sequelize to generate models
  6. Generate todo Model
  7. Create Controller and Routes
  8. Create API to add a Route
  9. Create API to get all the Todo Items
  10. Create an API to Update Todo Item
  11. Create API to Delete Todo Item
  12. Conclusion

Step 1 Create a new Database

We can create a new Database either directly via the console/terminal or using any Tool for Mysql

To create via CMD/Terminal

Login to mysql

mysql -u root -p

Create Database

create database TodoApp;

Step 2 Create a new NodeJs Project 

Create a folder by name TodoApp in your local directory and open the folder in CMD/Terminal

Type and enter below command

npm init

After this follow the instructions shown on the screen , For the entry point type app.js

Once you have finished the above process a package.json file will be generated and saved into the root folder

After this follow the instructions shown on the screen , For the entry point type app.js

Step 3 Install Dependencies via npm

 We will be using below node modules in our project

  1. Express JS
  2. body-parser
  3. Sequelize
  4. Mysql 2

npm install express body-parser sequelize mysql2

Step 4 Create a Node Server

Now it’s time to create a web server we will run it on Port 3000, Create a new app.js file in the root folder and open it in VS code

Write below code is app.js and then run it using node app.js

If everything is working fine then you will see a message Server running on Port 3000

Step 5 Setup Sequelize to generate models

Now we have a basic server running now it’s time to start working on database related stuff

We shall generate models for the Todo using sequelize

First step in setting up sequelize is to install the sequelize CLI globally

npm install -g sequelize-cli

Now it’s time to initialize sequelize, Run below command to initialize

sequelize init

This will generate migrations, seeders, config and models directories and config file

Now check your folder you should see new directories 

Database Configuration

Check file at config/config.json in the development section change the dn name to todo  also change username and password if required

Step 6 Generate todo Model

After successfully setting up the sequelize now it’s time to generate model for the todo table

This can be done using sequelize CLI, It will create the model ,migration files for the table

We will add two columns in the todo table title and description Sequelize will add id, createdAt and updatedAt columns by itself 

run below command in Terminal/CMD

sequelize model:create –name todo –attributes title:string, description:string

Once the models are generated we can also edit it manually (make sure you make changes in both model and migration file) 

Now run below commands to create table in database

sequelize db:migrate

Step 7 Create Controller and Routes

After successfully creating the model now it’s time to create Controller and Routes file so that we can use the model to communicate with DB 

Create two folder by name controllers and routes, your folder structure should look like below

Now create a file in controllers folder by name todo.controller.js and another in routes folder by name todo.routes.js

Let’s add a sample function in controller to make sure that whole code is working fine, make sure todo.controller.js is looking like below

Now let’s test out first route run the app node app.js and open URL in browser (Or In Postman) http://localhost:3000/api/v1.0/todo

You should see below message 

Hello from the ToDo Controller

If you are able to see above message then it means you have successfully configured the app

Now let’s proceed to next step of adding actual functionality to Add and Manage ToDo List

Step 8 Create API to add a Route

To add a new Todo item in the Database we first need to get the data via routes from user , then pass it to controller which will do parsing and then with help of model it will store it in database

Add a new route of type POST in the routes file and create a function in the controller to save the todo information 

Now run the app and open Postman

To test the newly build API select Post type in Postman and add URL http://localhost:3000/api/v1.0/todo 

Step 9 Create API to get all the Todo Items

Now lets build the API to get all the todos from the database , Add below code in routes file

In Postman add URL http://localhost:3000/api/v1.0/todo/all and make a get request , you should see all your todos in response

Step 10 Create an API to Update Todo Item

To update a Todo we need below information

Updated todo title and description

Add below code in the routes js, :id is used to pass the todo id in the request url

Now Create a PUT Request in Postman for URL http://localhost:3000/api/v1.0/todo/1 (1 is the id of todo you can replace with your todo ID)

Step 11 Create API to Delete Todo Item

TO delete a todo we just need to send the todo id 

Add below code in the routes file

Now to test the API make a Delete request from Postman for URL http://localhost:3000/api/v1.0/todo/1

Congratulations you have successfully build your simple Todo API using Node Js and Mysql , you can deploy it on cloud and add other features like changes status of todos , add user logins so that user can manage there todos

Complete code can be found at

Leave a Reply