Sequelize ORM with NodeJS
Published: 2022-09-30 16:34:23

## 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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/38bxxdf1nh7qy05uj3rg.png)

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 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vrpuglex1t3c059zumnw.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uuuhadm9qzjzhnwe4cnn.png)

**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.