How to Use Sequelize ORM In Node

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;

view raw db.js hosted with ❤ by GitHub

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');
	const 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
	const port = process.env.PORT || 3000;
	app.listen(port,()=>console.log('server running at '+port));
	module.exports = app;

view raw app.js hosted with ❤ by GitHub

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

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;

view raw members.js hosted with ❤ by GitHub

controller->memberController.js 

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

Create view

	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;

view raw memberController hosted with ❤ by GitHub

 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>

view raw header.js hosted with ❤ by GitHub

• views->partials->footer.ejs

	</body>
	</html>

view raw footer.js hosted with ❤ by GitHub

• 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%>

view raw home.ejs hosted with ❤ by GitHub

• views->edit.ejs

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

	<%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%>

view raw edit.js hosted with ❤ by GitHub

• 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'));

view raw updateapp.js hosted with ❤ by GitHub

Run the application by typing:

# node app.js

Click to watch a video tutorial