Create REST-API using sequelize-Mysql in Nodejs


We had many posts on API and I am moving on sequelize ORM which allows us to perform CURD operation in REST manner or using Modals, just like we done with mongoose.

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language. A workbench , which GUI tool to manage database object is available at MySQL website along with MySQL Downloads

Todo API

todos table in Workbench

Our API is used to create the todos ,store in MySQL DB , and perform all CURD operations using sequelize modal.

First we need to create the above table in your MySQL. Now lets create a basic Nodejs project.

md sequelize-api-mysql
cd sequelize-api-mysql
npm init -y
npm i sequelize express body-parser mysql mysql2 dotenv --save
npm i -D nodemon
Project structure

Our project structure look like above. So we need to create

  • package.json
  • .dotenv file
  • configuration file
  • modals
  • main indexjs

Package.json

The package file of the project look like the following , also we need a start script for running the project.

{
  "name": "sequelize-api-mysql",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1",
    "start": "nodemon index.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "dotenv": "^8.2.0",
    "express": "^4.17.1",
    "mysql": "^2.18.1",
    "mysql2": "^2.2.5",
    "sequelize": "^6.3.5"
  },
  "devDependencies": {
    "nodemon": "^2.0.7"
  }
}

Configuration

Under config folder create database.js with following content in it.

const Sequelize=require('sequelize')
module.exports=new Sequelize('todo_collections','root','123',{
    host:'localhost',
    port:3306,
    dialect:'mysql',
    pool:{
        max:5,
        min:0,
        acquire:30000,
        idle:10000
    }
})

In the above code we imported the sequelize and create basic configuration. Here todo_collection is our database, root is the user and 123 is password ( we have already created database). Remember to correctly configure the port.

Dotenv

dotenv is a npm module, which help us to keep configurations in seperate files and access it through process.env. Our .env file includes the port number. We can place Database URI, user,password and other configurations for our Node project

PORT=3004

Modal

Next thing we need to create the modal (Model/todo.model.js) ,which is a minimal modal for out ToDos.

const Sequelize=require('Sequelize')
const db = require('../config/database')

module.exports= db.define('todos',{
    item:{type:Sequelize.STRING,allowNull:false},
    description:{type:Sequelize.STRING}
})

In the modal we need to use the configuration , so we import it as db and create modal todo with two fields.

Our database had three more columns id, CreatedAt, UpdatedAt. The first one is auto generated field and rest is filled when adding or updating records.

Routes and index.js

We can implement routes in separate file, it help us to organize them in systematic manner. For simplicity I placed them along with index.js ( index is the starting point of our project which is defined pacakge.json)

const express = require("express");
const bodyParser = require("body-parser");
const db = require("./config/database");
require("dotenv").config();
const Todo = require("./models/todo.model");
const app = express();
app.use(bodyParser.urlencoded({extended:false})) 

//Test DB
db.authenticate()
  .then(() => console.log("Database connected"))
  .catch((e) => console.log("Error:" + e));
app.get("/", (req, res) => {
  res.send("Hello world");
});

app.get("/todo", (req, res, next) => {
  Todo.findAll()
    .then((model) => {
        res.json({
            error: false,
            data: model
        })
    })
    .catch(error => res.json({
        error: true,
        data: [],
        error: error
    }))
});

app.get("/todo/:id", (req, res, next) => {
    const id =req.params.id
    Todo.findByPk(id)
      .then((model) => {
          res.json({
              error: false,
              data: model
          })
      })
      .catch(error => res.json({
          error: true,
          data: [],
          error: error
      }))
  });

app.put('/todo/update/:id',bodyParser.json(),(req, res,next)=>{
    const id =req.params.id
    const {item,description}=req.body
    Todo.update({item:item},{where:{id:id}})
    .then((uTodo) => {
         console.log('got item and updated ' + uTodo);
        
        res.json({
            error: false,
            data: uTodo
        })
    })
    .catch(error => res.json({
        error: true,
        data: [],
        error: error
    }))
})

app.delete('/todo/delete/:id' ,(req, res,next)=>{
    const id =req.params.id
    
    Todo.destroy({where:{id:id}})
    .then((dTodo) => {
         console.log('got item and deleted ' + dTodo);
         
        res.json({
            error: false,
            data: dTodo
        })
    })
    .catch(error => res.json({
        error: true,
        data: [],
        error: error
    }))
})
app.post("/todo", bodyParser.json(),(req, res, next) => {
    const {item,description}=req.body
    Todo.create({item:item,description:description})
    .then((model) => {
      res.status(200).send(model);
      })
      .catch((e) => {
        res.status(400).send("Error:" + e);
      });
  });

const PORT = process.env.PORT || 5000;
app.listen(PORT, () => console.log(`server running on PORT ${PORT}`));

In the index.js, we first want to make sure the DB is working using authenticate method. Then we have the routes for the following

  • Add new todo – get request
  • Delete todo = delete request
  • get-todo – using id
  • get all todos – get request
  • update todo – put request

Routes specific use of JSON parsing

You may note that , we are used route specific body parsing. This is because we need body-parsing for two routes, update,save/add.

The global parsing will end up the JSON error in the route get (‘/todo/’) which fetch the items. So to avoid such an error we isolated JSON parser.

Note that all the CURD operations are done using modal , that is the beauty of the REST.

That is all you need to know. The complete source code of this project can be found at https://github.com/manojap/Node-Express-API-Mysql

You may like to read these API posts

Published by

Manoj

A developer,teacher and a blogger obsessed with Python,Dart and open source world from India

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.