Post
Ask Question

How to Use Sequelize ORM in Node

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

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
  1. const Sequelize = require('sequelize');    
  2.     
  3. // Option 1: Passing parameters separately    
  4. const sequelize = new Sequelize('database', 'username', 'password', {    
  5.   host: 'localhost',    
  6.   dialect:'mysql'   
  7. });    
  8.     
  9. 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
  1. const sequelize = require('./config/db');    
  2. const express   = require('express');     
  3. const bodyParser = require('body-parser');    
  4. const path      =  require('path');          
  5.     
  6. var app = express();    
  7.   
  8. //setting the template engine     
  9. app.set('view engine','ejs');    
  10.   
  11. // provide the complete path of the views folder  
  12. app.set('views',path.resolve(__dirname,'views'));    
  13.   
  14. //fetch the form data from request   
  15. app.use(bodyParser.urlencoded({extended:false}));    
  16.     
  17. //test the database connection  
  18. sequelize    
  19.   .authenticate()    
  20.   .then(() => {    
  21.     console.log('Connection has been established successfully.');    
  22.   })    
  23.   .catch(err => {    
  24.     console.error('Unable to connect to the database:', err);    
  25.   });    
  26.     
  27.   app.get('/',(req,res)=>{    
  28.    console.log('working')    
  29.   })    
  30.     
  31.  //assign the port  
  32.   var port = process.env.PORT || 3000;    
  33.   app.listen(port,()=>console.log('server running at '+port));    
  34.     
  35.   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
  1. const Sequelize = require('sequelize');    
  2. const sequelize = require('../config/db');    
  3.     
  4. const member = sequelize.define('members', {    
  5.   mid:{    
  6. type:Sequelize.NUMBER,    
  7. allowNull:false,    
  8. primaryKey:true,    
  9. autoIncrement: true    
  10.   },    
  11.     // attributes    
  12.     name: {    
  13.       type: Sequelize.STRING,    
  14.       allowNull: false    
  15.     },    
  16.     country: {    
  17.       type: Sequelize.STRING,    
  18.       allowNull:false    
  19.       // allowNull defaults to true    
  20.     },    
  21.     language:{    
  22.         type:Sequelize.STRING,    
  23.         allowNull:false    
  24.     },    
  25.     salary:{    
  26.         type:Sequelize.NUMBER,    
  27.         allowNull:false    
  28.     }    
  29. },{ timestamps: false});    
  30.     
  31.   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
  1. var express = require('express');    
  2. var memberController  = require('../controllers/memberController');    
  3.     
  4. var router = express.Router();    
  5.     
  6. router.get('/getall',memberController.getMember);    
  7.     
  8. router.post('/add',memberController.addMember);    
  9.     
  10. router.get('/edit/:id',memberController.editMember);    
  11.     
  12. router.post('/update',memberController.updateMember);    
  13.     
  14. router.get('/delete/:id',memberController.deleteMember);    
  15.     
  16. module.exports = router;    
controller->memberController.js 
  1. var member  = require('../models/member');    
  2.     
  3.     
  4. var memberController = {   
  5.     getMember(req,res){    
  6.     member.findAll()    
  7.     .then(function(dataa){    
  8.         res.render('home',{data:dataa})    
  9.     })    
  10.   .catch(error=>console.log(`error occurred`,error));    
  11.     },    
  12.     addMember(req,res){    
  13.         console.log(req.body)    
  14.           member.create({name:req.body.name,country:req.body.country,    
  15.           language:req.body.language,salary:req.body.salary})    
  16.         .then(function(dataa){res.redirect('/member/getall')})    
  17.         .catch(function(error){    
  18.         console.log(`error occured`,err)    
  19.         });    
  20.       },    
  21.       editMember(req,res){    
  22.         console.log('id',req.params.id)    
  23.         member.findOne({where:{mid:req.params.id},raw: true})    
  24.         .then(function(dataa){    
  25.           if(!dataa){    
  26.             res.render('edit',{data:{}})    
  27.           }    
  28.           else{    
  29.             var x = JSON.stringify(dataa)    
  30.             console.log(JSON.parse(x))    
  31.             var temp=[];    
  32.             temp.push(JSON.parse(x))    
  33.             console.log('sd',temp)    
  34.             res.render('edit',{data:temp})    
  35.           }    
  36.       }).catch(function(error){    
  37.         console.log('error occured',error)    
  38.       })    
  39.     },    
  40.     updateMember(req,res){    
  41.         console.log(req.body)    
  42.         const query={    
  43.             name:req.body.name,    
  44.             country:req.body.country,    
  45.             language:req.body.language,    
  46.             salary:req.body.salary    
  47.         }    
  48.         member.update(query,{where:{mid:req.body.id}})    
  49.         .then(function(data){    
  50.           res.redirect('/member/getall')    
  51.         })    
  52.         .catch(function(error){    
  53.         console.log('error occured',error)    
  54.         });      
  55.     },    
  56.     deleteMember(req,res){    
  57.       console.log('delid',req.params.id)    
  58.         member.destroy({where:{mid:req.params.id}})    
  59.         .then(function(dataa){    
  60.           res.redirect('/member/getall')    
  61.         })    
  62.         .catch(function(error){    
  63.          console.log('error occured',error)    
  64.       });    
  65.     },    
  66. }    
  67.     
  68. 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
  1. <html lang="en">    
  2.         <head>    
  3.             <meta charset="UTF-8">    
  4.             <meta name="viewport" content="width=device-width, initial-scale=1.0">    
  5.             <meta http-equiv="X-UA-Compatible" content="ie=edge">    
  6.             <title>SequelizeDemo</title>    
  7.             <link rel="stylesheet" href="https://bootswatch.com/4/flatly/bootstrap.css">    
  8.             <link rel="stylesheet" href="https://bootswatch.com/_assets/css/custom.min.css">    
  9.         </head>    
  10.         <body>    
  11.                 <div>    
  12.                         <div class="fixed-top">    
  13.                               <nav class="navbar navbar-expand-lg navbar-dark bg-primary">    
  14.                                 <a class="navbar-brand" href="/">SequelizeDemo</a>    
  15.                                 <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarColor01" aria-controls="navbarColor01" aria-expanded="false" aria-label="Toggle navigation">    
  16.                                   <span class="navbar-toggler-icon"></span>    
  17.                                 </button>    
  18.                               </nav>    
  19.                         </div>    
  • views->partials->footer.ejs
  1. </body>    
  2. </html>    
  • views->home.ejs
  1. <%include ./partials/header%>    
  2.     <center><h4>AddMember</h4></center>    
  3.         <div class="container">    
  4.             <form style="display: block;border-style: solid;padding:10px;margin-top:0px;" action="/member/add" method="POST">    
  5.                 <div class="form-row">    
  6.                   <div class="form-group col-md-3">    
  7.                     <label for="name">Name</label>    
  8.                     <input type="text" class="form-control" name="name"  placeholder="Name">    
  9.                   </div>    
  10.                   <div class="form-group col-md-3">    
  11.                     <label for="country">Country</label>    
  12.                     <input type="text" class="form-control" name="country" placeholder="Country">    
  13.                   </div>    
  14.                   <div class="form-group col-md-3">    
  15.                     <label for="language">Language</label>    
  16.                     <input type="text" class="form-control" name="language" placeholder="Language">    
  17.                   </div>    
  18.                   <div class="form-group col-md-3">    
  19.                     <label for="salary">Salary</label>    
  20.                     <input type="text" class="form-control" name="salary" placeholder="Salary">    
  21.                   </div>    
  22.                 </div>    
  23.                 <button type="submit" class="btn btn-primary">Add Member</button>    
  24.               </form>    
  25.             </div>    
  26.             <%if(data.length>0){%>    
  27.               <center><h4>Members</h4></center>    
  28.             <div class="table-responsive">    
  29.                     <table class="table table-hover" >    
  30.                             <thead class="table-active">    
  31.                               <tr>    
  32.                                 <th scope="col">Name</th>    
  33.                                 <th scope="col">Country</th>    
  34.                                 <th scope="col">Language</th>    
  35.                                 <th scope="col">Salary</th>    
  36.                                 <th scope="col">edit</th>    
  37.                                 <th scope="col">delete</th>    
  38.                               </tr>    
  39.                             </thead>    
  40.                             <tbody>    
  41.                               <%for(var i=0;i< data.length; i++) {%>    
  42.                               <tr class="table-success">    
  43.                                  <td><%= data[i].name%></td>    
  44.                                 <td><%= data[i].country%></td>    
  45.                                 <td><%= data[i].language%></td>    
  46.                                 <td><%= data[i].salary%></td>    
  47.                                 <td><a href="/member/edit/<%= data[i].mid%>"><button type="button">edit</button></a></td>    
  48.                                 <td><a href="/member/delete/<%= data[i].mid%>"><button type="button">delete</button></a></td>    
  49.                               </tr>    
  50.                                <%}%>    
  51.                             </tbody>    
  52.                           </table>     
  53.             </div>    
  54.             <%}%>    
  55.           </div>    
  56. <%include ./partials/footer%>   
  • views->edit.ejs
  1. <%include ./partials/header%>    
  2.     
  3. <%if(data.length>0){%>      
  4.   <center><h4>Update Member Details</h4></center>     
  5. <div class="container">    
  6.         <form style="display: block;border-style: solid;padding:10px;margin-top:0px;" action="/member/update" method="POST">    
  7.             <div class="form-row">    
  8.               <div class="form-group col-md-3">    
  9.                 <label for="name">Name</label>    
  10.                 <input type="text" class="form-control" name="name"  value="<%= data[0].name %>">    
  11.               </div>    
  12.               <div class="form-group col-md-3">    
  13.                 <label for="country">Country</label>    
  14.                 <input type="text" class="form-control" name="country"value="<%= data[0].country%>">    
  15.               </div>    
  16.               <div class="form-group col-md-3">    
  17.                 <label for="language">Language</label>    
  18.                 <input type="text" class="form-control" name="language" placeholder="Language" value="<%= data[0].language%>">    
  19.               </div>    
  20.               <div class="form-group col-md-3">    
  21.                 <label for="salary">Salary</label>    
  22.                 <input type="text" class="form-control" name="salary" placeholder="Salary" value="<%= data[0].salary%>">    
  23.               </div>    
  24.               <input type="hidden" name="id" value="<%= data[0].mid %>">    
  25.             </div>    
  26.             <button type="submit" class="btn btn-primary">Update</button>    
  27.           </form>    
  28.         </div>    
  29. <%}%>    
  30. <%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.
  1. app.get('/',(req,res)=>{    
  2.    res.redirect('/member/getall');    
  3.  })    
  4.    
  5. app.use('/member',require('./routes/members')); 
Run the application by typing:
 
# node app.js
 
Click to watch a video tutorial