Create REST-API using sequelize -MariaDB ORM 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.

MariaDB

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system, intended to remain free and open-source software under the GNU General Public License. The standard installation come with Heidi SQL GUI which help us create and manage database and schemas.

You can download and install it from the official website

Todo API

todos table in Heidi SQL

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

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

md sequelize-api-mariyadb
cd sequelize-api-mariyadb
npm init -y
npm i sequelize express body-parser mariadb 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-mariyadb",
  "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",
    "mariadb": "^2.5.2",
    "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_manager','root','123',{
    host:'localhost',
    port:4001,
    dialect:'mariadb',
    pool:{
        max:5,
        min:0,
        acquire:30000,
        idle:10000
    }
})

In the above code we imported the sequelize and create basic configuration. Here todo_manager is our database, root is the user and 123 is password ( we have already created maria 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 error we isolate 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-REST-API-MariaDB

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.