How to Use Sequelize ORM in Node.js with Express & MySQL

Prerequisites

  • Node.js
  • MySQL

What Is 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. Sequelize follows SEMVER and supports Node v6 and above to utilize ES6 features.

Express and Sequelize 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 your directory name.
  • Navigate into the folder by typing cd followed by the directory name.
  • Initialize a package.json file for the application: 
    npm init
    You will be prompted for details like project name and version. Once finished, package.json will be generated.

Express Setup

Next, install the Express framework along with essential dependencies:

npm install express body-parser ejs
  • These packages will be added to the dependencies section of your package.json.
  • Create a new file named app.js in the root directory as your entry point.
  • Inside package.json under scripts, add: "start": "node app.js".

Sequelize Setup

Ensure MySQL is installed on your system, then install Sequelize and the MySQL2 driver:

npm install --save sequelize
npm install --save mysql2

Go to your config folder and set up the database connection inside db.js, specifying your database name, username, and password:


const Sequelize = require('sequelize');

const sequelize = new Sequelize('utube', 'root', 'root', {
    host: 'localhost',
    dialect: 'mysql'
  });

module.exports = sequelize;

Initial Connection (app.js)

Open app.js and configure your initial setup. Run the application to verify the database connection:

node app.js

If successful, your console will output:


server running at 3000
Executing (default): SELECT 1+1 AS result
Connection has been established successfully.
working

Create Model (models/member.js)

Define the schema for your members table containing 4 fields: name, country, language, and salary:


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

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

module.exports = member;

Create Route and Controller

Routes (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 (controllers/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`, error)
        });
    },
    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)
            var temp = [];
            temp.push(JSON.parse(x))
            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;

Create Views

Create a partials folder for reusable layout templates containing header.ejs and footer.ejs.

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

Finalize app.js Configuration & Run Application

Update your app.js file by adding the default route and route middleware before assigning your server port:

  • app.get('/') handles the default landing page.
  • app.use('/member', require('./routes/members')) routes any requests starting with /member to your member routes.

Start your application using:

node app.js

Resources

Click For Code

Click here to watch the video tutorial

Comments

Post a Comment

Popular posts from this blog

How to Setup a Flask Project in Python: Step-by-Step Guide

Image
Prerequisite Python What Is Flask ? Flask is a lightweight Web framework written in Python. It is developed by  Armin Ronacher. Flask is based on the Werkzeug WSGI toolkit and Jinja2 template engine Setup Development Environment First Install Virtualenv In Python virtualenv  is a virtual python environment builder.virtualenv    is used to manage python packages for different projects. Using virtualenv allows you to avoid installing python packages globally which could break system tools or other projects. Install the virtualenv using the following command.This command needs administrator privileges # pip install virtualenv After installing the virtualenv . Make Folder create new folder using the following command  # mkdir flaskdemo change to the current created directory/folder # cd flaskdemo Create a Virtual Environment for Flask In Folder Now we will create a virtualenv .  The commands below will c...
Read more

How to Use Sequelize-CLI to Create Tables and Relationships in Node.js

Introduction In this article, we will explore how to use Sequelize-CLI for creating database tables and setting up powerful relationships between multiple tables using foreign keys in Node.js and MySQL. Project Structure |--------- config |--------- config.json |--------- models |--------- index.js |--------- department.js |--------- emp.js |--------- migrations |--------- [timestamp]-create-department.js |--------- [timestamp]-create-emp.js |--------- seeders |--------- app.js |--------- package.json Setup The Node Project Let's begin by initializing our workspace: Open your console and create a new directory: mkdir seqlcli Navigate into that directory: cd seqlcli Initialize the Node.js project by running: npm init This will prompt you for basic project metadata like name, version, and author, generating a package.json file. Install P...
Read more

Hashing Password With Bcrypt In Node

Image
Image by Jae Rue from Pixabay Introduction In this article we will be using the bcryptjs javascript library for hashing and compare password.Here we will build a simple api fore register and login.when we will hash the password when user register and then compare that password with hash when they login. What is bcrypt ? bcrypt is a  password hashing function  designed by  Niels Provos  and David Mazières, based on the  Blowfish  cipher, and presented at  USENIX  in 1999.Besides incorporating a  salt  to protect against  rainbow table  attacks, bcrypt is an adaptive function: over time, the iteration count can be increased to make it slower, so it remains resistant to  brute-force search  attacks even with increasing computation power. What is hashing ?  Hashing is a one way function (well, a mapping). It's irreversible, you apply the secure hash algorithm and you cannot ge...
Read more