How to Use Sequelize ORM in Node

This post will show how to use the Sequelize ORM for building apps in which we can add, edit, update, and delete data.

Introduction

 

In this article, 'sequelize' is explained, as well as how we can set up a project with sequelize from scratch. Furthermore, we will build a small application using the sequelize ORM through which we can add, edit, update and delete data. 

 

Prerequisites

1. Node
   
2. MySql

What Is Sequelize?

 

As mentioned on the sequelize site, 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. Sequelize follows SEMVER. It supports Node v6 and above to use ES6 features.

 

Project Structure 

 

 ----| config

               |--------- db.js

 

----| controllers

               |--------- memberController.js

 

----| models

               |--------- member.js

 

----| routes

               |-------- members.js

 

----| views

             | |------- partials

             |                 |------- header.ejs

             |                 |------- footer.ejs

             |---- home.ejs

             |---- edit.ejs

 

----| app.js

 

----| package.json

 

Project Setup

 

Let's begin by setting up our workspace

• Open the console and type mkdir followed by the directory name
• Now type cd followed by the directory name.
• Now type npm init to create a package.json file for our application.

You can learn about package.json file here. 

 

# npm init

 

After typing this command you will be prompted with a few things related to package.json file, such as name, version, etc. Once finished, a package.json file will be generated.

 

Express Setup

 

After generating the package.json file, we will install the express framework and some other packages.

 

# npm install express body-parser ejs 

 

The installed package will be put in the dependencies section of package.json.
Add a new file app.js in the root. This will be the starting point of our application.
Open the package.json file and in "scripts" write "start":" node app.js".
Now, add the new folders to the project.

 

 

Sequelize Setup

 

To use sequelize, we first need MySql installed on the system.

Let's start by installing sequelize in the project.

 

# npm install --save sequelize

# npm install --save mysql2   

 

After installing sequelize, go to the config folder and set up the database connection in the db.js.

 

config ->db.js

const Sequelize = require('sequelize');

// Option 1: Passing parameters separately
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect:'mysql'
});

module.exports=sequelize;

Here you have to provide the name of your database, the username, and the password of the server.

 

Now open the app.js file and put in the below code.

 

app.js

const sequelize = require('./config/db');
const express   = require('express');
const bodyParser = require('body-parser');
const path      =  require('path');

var app = express();

//setting the template engine
app.set('view engine','ejs');

// provide the complete path of the views folder
app.set('views',path.resolve(__dirname,'views'));

//fetch the form data from request
app.use(bodyParser.urlencoded({extended:false}));

//test the database connection
sequelize
  .authenticate()
  .then(() => {
    console.log('Connection has been established successfully.');
  })
  .catch(err => {
    console.error('Unable to connect to the database:', err);
  });

  app.get('/',(req,res)=>{
   console.log('working')
  })

 //assign the port
  var port = process.env.PORT || 3000;
  app.listen(port,()=>console.log('server running at '+port));

  module.exports = app;

Now type run the application and check the db connection. 

 

# node app.js

 

In the console, we can see the following if there is no error.

 

server running at 3000

Executing (default): SELECT 1+1 AS result

Connection has been established successfully.

working

 

Create Model

• member.js

const Sequelize = require('sequelize');
const sequelize = require('../config/db');

const member = sequelize.define('members', {
  mid:{
type:Sequelize.NUMBER,
allowNull:false,
primaryKey:true,
autoIncrement: true
  },
    // attributes
    name: {
      type: Sequelize.STRING,
      allowNull: false
    },
    country: {
      type: Sequelize.STRING,
      allowNull:false
      // allowNull defaults to true
    },
    language:{
        type:Sequelize.STRING,
        allowNull:false
    },
    salary:{
        type:Sequelize.NUMBER,
        allowNull:false
    }
},{ timestamps: false});

  module.exports = member;

Here we define the schema of our members table.

The table will contain 4 fields: name, country, language, and salary.
 

Create route and controller

 

routes -> members.js

var express = require('express');
var memberController  = require('../controllers/memberController');

var router = express.Router();

router.get('/getall',memberController.getMember);

router.post('/add',memberController.addMember);

router.get('/edit/:id',memberController.editMember);

router.post('/update',memberController.updateMember);

router.get('/delete/:id',memberController.deleteMember);

module.exports = router;

controller->memberController.js 

var member  = require('../models/member');


var memberController = {
    getMember(req,res){
    member.findAll()
    .then(function(dataa){
        res.render('home',{data:dataa})
    })
  .catch(error=>console.log(`error occurred`,error));
    },
    addMember(req,res){
        console.log(req.body)
          member.create({name:req.body.name,country:req.body.country,
          language:req.body.language,salary:req.body.salary})
        .then(function(dataa){res.redirect('/member/getall')})
        .catch(function(error){
        console.log(`error occured`,err)
        });
      },
      editMember(req,res){
        console.log('id',req.params.id)
        member.findOne({where:{mid:req.params.id},raw: true})
        .then(function(dataa){
          if(!dataa){
            res.render('edit',{data:{}})
          }
          else{
            var x = JSON.stringify(dataa)
            console.log(JSON.parse(x))
            var temp=[];
            temp.push(JSON.parse(x))
            console.log('sd',temp)
            res.render('edit',{data:temp})
          }
      }).catch(function(error){
        console.log('error occured',error)
      })
    },
    updateMember(req,res){
        console.log(req.body)
        const query={
            name:req.body.name,
            country:req.body.country,
            language:req.body.language,
            salary:req.body.salary
        }
        member.update(query,{where:{mid:req.body.id}})
        .then(function(data){
          res.redirect('/member/getall')
        })
        .catch(function(error){
        console.log('error occured',error)
        });
    },
    deleteMember(req,res){
      console.log('delid',req.params.id)
        member.destroy({where:{mid:req.params.id}})
        .then(function(dataa){
          res.redirect('/member/getall')
        })
        .catch(function(error){
         console.log('error occured',error)
      });
    },
}

module.exports = memberController;

• here we are using raw:true in findOne(). It will return the raw result. You can see it in detail here.

Create view

 

 Here we create a partial folder that will contain the part of the template used frequently. It will contain 2 files a). header.ejs, and b). footer.ejs.

We wil be using ejs for the views. To learn more about ejs, click here.

• views->partials->header.ejs

<html lang="en">
        <head>
            <meta charset="UTF-8">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <meta http-equiv="X-UA-Compatible" content="ie=edge">
            <title>SequelizeDemo</title>
            <link rel="stylesheet" href="https://bootswatch.com/4/flatly/bootstrap.css">
            <link rel="stylesheet" href="https://bootswatch.com/_assets/css/custom.min.css">
        </head>
        <body>
                <div>
                        <div class="fixed-top">
                              <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
                                <a class="navbar-brand" href="/">SequelizeDemo</a>
                                <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarColor01" aria-controls="navbarColor01" aria-expanded="false" aria-label="Toggle navigation">
                                  <span class="navbar-toggler-icon"></span>
                                </button>
                              </nav>
                        </div>

• views->partials->footer.ejs

</body>
</html>

• views->home.ejs

<%include ./partials/header%>
    <center><h4>AddMember</h4></center>
        <div class="container">
            <form style="display: block;border-style: solid;padding:10px;margin-top:0px;" action="/member/add" method="POST">
                <div class="form-row">
                  <div class="form-group col-md-3">
                    <label for="name">Name</label>
                    <input type="text" class="form-control" name="name"  placeholder="Name">
                  </div>
                  <div class="form-group col-md-3">
                    <label for="country">Country</label>
                    <input type="text" class="form-control" name="country" placeholder="Country">
                  </div>
                  <div class="form-group col-md-3">
                    <label for="language">Language</label>
                    <input type="text" class="form-control" name="language" placeholder="Language">
                  </div>
                  <div class="form-group col-md-3">
                    <label for="salary">Salary</label>
                    <input type="text" class="form-control" name="salary" placeholder="Salary">
                  </div>
                </div>
                <button type="submit" class="btn btn-primary">Add Member</button>
              </form>
            </div>
            <%if(data.length>0){%>
              <center><h4>Members</h4></center>
            <div class="table-responsive">
                    <table class="table table-hover" >
                            <thead class="table-active">
                              <tr>
                                <th scope="col">Name</th>
                                <th scope="col">Country</th>
                                <th scope="col">Language</th>
                                <th scope="col">Salary</th>
                                <th scope="col">edit</th>
                                <th scope="col">delete</th>
                              </tr>
                            </thead>
                            <tbody>
                              <%for(var i=0;i< data.length; i++) {%>
                              <tr class="table-success">
                                 <td><%= data[i].name%></td>
                                <td><%= data[i].country%></td>
                                <td><%= data[i].language%></td>
                                <td><%= data[i].salary%></td>
                                <td><a href="/member/edit/<%= data[i].mid%>"><button type="button">edit</button></a></td>
                                <td><a href="/member/delete/<%= data[i].mid%>"><button type="button">delete</button></a></td>
                              </tr>
                               <%}%>
                            </tbody>
                          </table>
            </div>
            <%}%>
          </div>
<%include ./partials/footer%>

• views->edit.ejs

<%include ./partials/header%>

<%if(data.length>0){%>
  <center><h4>Update Member Details</h4></center>
<div class="container">
        <form style="display: block;border-style: solid;padding:10px;margin-top:0px;" action="/member/update" method="POST">
            <div class="form-row">
              <div class="form-group col-md-3">
                <label for="name">Name</label>
                <input type="text" class="form-control" name="name"  value="<%= data[0].name %>">
              </div>
              <div class="form-group col-md-3">
                <label for="country">Country</label>
                <input type="text" class="form-control" name="country"value="<%= data[0].country%>">
              </div>
              <div class="form-group col-md-3">
                <label for="language">Language</label>
                <input type="text" class="form-control" name="language" placeholder="Language" value="<%= data[0].language%>">
              </div>
              <div class="form-group col-md-3">
                <label for="salary">Salary</label>
                <input type="text" class="form-control" name="salary" placeholder="Salary" value="<%= data[0].salary%>">
              </div>
              <input type="hidden" name="id" value="<%= data[0].mid %>">
            </div>
            <button type="submit" class="btn btn-primary">Update</button>
          </form>
        </div>
<%}%>
<%include ./partials/footer%>

Now update your app.js by adding the below line of code to it.

• app.get('/') will be the default route when our application starts. 
• app.use('/member',require('./routes/members')) - this is a route middleware.
• Whenever the coming request has a '/member' route, then the particular route method in routes/members will be activated.
• Put this code before assigning the port.

app.get('/',(req,res)=>{
   res.redirect('/member/getall');
 })

app.use('/member',require('./routes/members'));

Run the application by typing:

 

# node app.js

 

Click to watch a video tutorial