Sequelize ORM with NodeJS and MSSQL

“Sequelize ORM with NodeJS and MSSQL” article is for Backend developers who looking for Sequelize ORM implementation. Lets’ see what is Sequelize means

What is ORM ?

Object-relational mapping (ORM) is a programming technique in which a metadata descriptor is used to connect object code to a relational database. It means this is a programming technique for converting data between incompatible type systems using object-oriented programming languages. ( TechOpedia and Wikipedia )

What is Sequelize ORM?

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. There is a rich documentation. Click here to refer.

Create NodeJS Application

Using “npm init”, you can create a nodeJS application.

npm i sequelize

After that install MSSQL

npm install mssql

Then install the below npm packages

npm i body-parser
npm i cors
npm i express

Create a server.js file in your root folder. After that your server.js file look like this.

var Sequelize = require(‘Sequelize’);
var express = require(‘express’);
const bodyParser = require(“body-parser”);
const cors = require(“cors”);
const app = express();

app.use(bodyParser.urlencoded({extended:true}));
app.use(bodyParser.json());
app.use(cors());

const port = process.env.Port || 5000;

app.listen(port, () => {
console.log(“Server is up and running on port number ” + port);
});

Create DB connection String – Sequelize MSSQL DB Connection String

In my application I created a another file called “db.config.js” to implement Sequelize MSSQL DB connection string. Here is the code. Copy the below code.

const { Sequelize } = require(‘sequelize’);

const db = new Sequelize(‘db_name’, ‘username’, ‘password’, {
     host: ‘localhost’,
     dialect: ‘mssql’,
     dialectOptions: {
          options: {
               trustedconnection: false,
               encrypt: false,
               trustServerCertificate: false,
               enableArithAbort : false,
           }
     }
});
module.exports = db;

Replace with your username and the password. And also rename the server name and the database name. You can select your driver.

Make sure to false the encrypt. Because it will give you an error called “self signed certificates failed”.

Let’s get into Code – Controllers and Models

This is the create new Port function.

//CREATE A NEW PORT
exports.addPort = async (req , res) => {

try {
req.body.Id = uuidv4();

const response = await portModel.create(req.body)
.then(function(data){
const res = { success: true, data: data, message:”created successful” }
return res;
})
.catch(error=>{
const res = { Failed: false, error: error }
return res;
})
res.json(response);

} catch (e) {
console.log(e);
}

}

Let’s create get All function with pagination

//GET ALL PORTS
exports.getAllPorts = async (req , res) => {

const pageAsNumber = Number.parseInt(req.query.page);
const sizeAsNumber = Number.parseInt(req.query.size);

let page = 0;
if(!Number.isNaN(pageAsNumber) && pageAsNumber > 0){
page = pageAsNumber;
}

let size = 10;
if(!Number.isNaN(sizeAsNumber) && !(sizeAsNumber > 10) && !(sizeAsNumber < 1)){
size = sizeAsNumber;
}
const response = await portModel.findAndCountAll({
limit: size,
offset: page * size
});
res.send({
content: response.rows,
totalPages: Math.ceil(response.count / Number.parseInt(size))
});

}

Get one Port Function.

//GET ONE PORT
exports.getOnePort = async (req , res) => {
try {
const { id } = req.params;

const response = await portModel.findOne({ where: { id: id } })
.then( function(data){
const res = { success: true, data: data }
return res;
})
.catch(error => {
const res = { success: false, error: error }
return res;
})
res.json(response);
} catch (e) {
console.log(e);
}

}

Update function

//UPDATE PORT
exports.updatePort = async (req , res) => {

try {

const id = req.params.id;

const response = await portModel.update( req.body ,{ where: { Id: id} })
.then(function(data){
const res = { success: true, data: data, message:”updated successful” }
return res;
})
.catch(error=>{
const res = { success: false, error: error }
return res;
})
res.json(response);
} catch (e) {
console.log(e);
}

}

Delete function

//DELETE PORT
exports.deletePort = async (req , res) => {

try {

const { id } = req.params;

const response = await portModel.destroy({
where: { id: id }
})
.then( function(data){
const res = { success: true, data: data, message:”Deleted successful” }
return res;
})
.catch(error => {
const res = { success: false, error: error }
return res;
})
res.json(response);
} catch (e) {
console.log(e);
}

}

Thank you for reading. If you are interesting on my article, make sure to follow my other articles as well. Make sure to leave a comment.