Sequelize ORM with NodeJS

Introduction

Sequelize is a promise based ORM (Object Relational Mapper) for NodeJS. We can use multiple databases with Sequelize like Oracle, Postgres, MySQL, MariaDB, SQLite and SQL Server, and more.

What does ORM actually mean?
An Object Relational Mapper represents the database records as objects. It lets you create and manipulate data from a database using an object oriented paradigm.

So, using Sequelize, you can perform DML operations like SELECT, INSERT, UPDATE, DELETE etc. using class methods. You can also define relationships on your database tables using class methods like hasOne(), belongsTo() and hasMany(), etc.

So, now let's get started

Create a NodeJS application

Create a new folder at your desired location and initialize this as your node.js app using below command

npm init

Keep pressing the enter key after adding the required information and your node.js app is ready.

Now install all the required dependencies using below command

npm install express mysql2 cors sequelize - save

The package.json file will look like below after you have all the dependencies successfully installed.

The next step is to create a new express web server. Add a filename.js file at the root of your folder and add below code.

const cors = require("cors");
const express = require("express");

const app = express();

var corsOptions = {
  origin: "http://localhost:8081"
};

app.use(cors(corsOptions));

// parse requests of content-type - application/json
app.use(express.json());

// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: true }));

// simple route
app.get("/", (req, res) => {
  res.json({ message: "Welcome to NodeJs App!!!" });
});

// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`Server is up and running on port ${PORT}.`);
});

Use the following command to execute the server

node filename.js

You will get the following message

Now, if you go to the browser and type the URL -> [http://localhost:8080/] you can see the application is up and running

Create a database

You can go to MSSQL Server and create a new database. In my case, I created it on Microsoft Azure. The creation of tables can be done with the help of Sequelize.

Next step is to put all the database configurations in a file. So, I have created a config.js file as below.

module.exports = {
HOST: "localhost",
USER: "root",
PASSWORD: "",
DB: "student_db",
dialect: "mysql",
pool: {//pool configuration
max: 5,//maximum number of connection in pool
min: 0,//minimum number of connection in pool
acquire: 30000,//maximum time in ms that pool will try to get connection before throwing error
idle: 10000//maximum time in ms, that a connection can be idle before being released
}
};

Initialize Sequelize

Create a new folder called models in the root directory and add a new file called index.js. Add below code there.

const dbConfig = require(“../config/config.js:);

const Sequelize = require(“Sequelize”);
const Sequelize = new Sequelize(dbCofig.DB, dbConfig.USER,
dbConfig.PASSWORD, {
    host: dbConfig.HOST,
    dialect: dbConfig.dialect,
    operationsAliases: false,
    pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
    }
};
const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.student= require(“./student.js”) (sequelize, Sequelize);

module.exports = db;
The user should not forget to summon the sync() method in the server.js.
const app = express();
app.use(....);

const db = require(“./models”);
db.sequelize.sync();

When you need to drop the existing tables and the database is required to be resynchronized, enter the force: true code like the below:

db.sequelize.sync({force: true}).then(() => {

console.log(“Drop and resync db.”);

});

We now need to create a new model named student.js

module.exports = (sequelize, Sequelize) => {
    const Student = sequelize.define("student", {
      name: {
        type: Sequelize.STRING
      },
      admission:{
        type:Sequelize.INTEGER
      },
      class: {
        type: Sequelize.INTEGER
      },
      city: {
        type: Sequelize.STRING
      }
    });

    return Student;
  };

Creating Controller

Below is the code for a controller.

const db = require(“../models”);
// models path depends on your structure
const Student= db.student;

exports.create = (req, res) => {
// Validating the request
if (!req.body.title) {
res.status(400).send ({
message: “Content can be placed here!”
});
return;
}

// Creating a Student
const student = {
name: req.body.name,
admission: req.body.admission,
class: req.body.class,
city: req.body.city
};

// Saving the Student in the database
Student .create(student). then(data => {
res.send(data);
}) .catch(err => {
res.status(500).send ({
Message:
err.message || “Some errors will occur when creating a student”
});
});
};

Retrieving Data

You can use below code to retrieve data.

exports.findAll = (req, res) => {

  const name = req.query.name;
  var condition = name ? { name: { [Op.like]: `%${name}%` } } : null;

  Student.findAll({ where: condition })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving data."
      });
    });

};

Now that we are done adding the controller and model, we need to have a route defined in our application that will execute the controller. Let's go ahead and create a route.

Defining Route

You can create a new folder called routes and add a new route.js file in it. Add below code in that file.

module.exports = app => {
    const students = require("../controllers/student.js");

    var router = require("express").Router();

    // add new student
    router.post("/", students.create);

    // view all students
    router.get("/", students.findAll);
 };

Now include the route in the student_server.js file using below code

require("./routes/routes.js")(app);

You can test the API by calling the routes in postman.

Thank you for reading.