MS SQL CRUD Operation using Sequelize Raw Queries and Express JS

Sequelize is a popular promise-based ORM for Express Js applications which supports multiple database servers like PostgreSQL, MySQL, SQLite and MSSQL. In this article let us see how to perform CRUD operations with MS SQL Server using Sequelize RAW Queries in a Express Js application.

Let’s start by adding Sequelize configuration in the config.{env}.json as below for connecting MS SQL Server.

"DBConnections": {
    "default": {
        "user": "sa",
        "password": "sql@123",
        "options": {
            "host": "localhost",
            "database": "testdb",
            "requestTimeout": 300000,
            "dialect": "mssql",
            "operatorsAliases": false,
            "logging": true,
            "dialectOptions": {
                "encrypt": false
            }
        }
    }
}

The dialect is used to specific database type (mysql, mssql,..). We need to install the sequelize package and in addition to that we need to install database package. Below is an example for MS-SQL.
"sequelize": "4.37.10"
"mssql": "4.1.0"

sequelize.config.ts

import { AppSetting, IConfig } from '../../config';
import * as SqlConnection from 'sequelize';
import { ConfigManager } from '../../config/config.manager';
export class SequelizeConfig {
    private sequelize: SqlConnection.Sequelize;
    public setConnection() {
        const config: IConfig = AppSetting.getConfig();
        const dbInfo = config.DBConnections['default'];
        let options = dbInfo.options;
        options['logging'] = console.log;
        this.sequelize = new SqlConnection(dbInfo.database, dbInfo.user, dbInfo.password, options);
    }

    public getSequelize() {
        return this.sequelize;
    }
}

export const sequelize = new SequelizeConfig();

SqlManager.ts

Wrapper class for the DML Operation

import { Logger } from '../../helpers/logger';
import { sequelize, SequelizeConfig } from './sequelize.config';
import * as SqlConnection from 'sequelize';

export class SqlManager {

    private _sequelize: SqlConnection.Sequelize;
    private params;

    constructor() {
        this._sequelize = sequelize.getSequelize();
    }

    public addInputParameter(paramName: string, value: any) {
        if (this.params == null) {
            this.params = {};
        }
        this.params[paramName] = value;
    }


    public ExecuteQuery(qry: string) {
        return this._sequelize.query(qry, { type: this._sequelize.QueryTypes.SELECT });
    }

    public Get(qry: string, vals: any = null) {
        let param = this.params ? this.params : vals;
        return this._sequelize.query({
            query: qry,
            values: param
        }, { type: this._sequelize.QueryTypes.SELECT });
    }

    public Insert(qry: string, vals: any = null) {
        let param = this.params ? this.params : vals;
        return this._sequelize.query({
            query: qry,
            values: param
        }, { type: this._sequelize.QueryTypes.INSERT });
    }

    public Update(qry: string, vals: any = null) {
        let param = this.params ? this.params : vals;
        return this._sequelize.query({
            query: qry,
            values: param
        }, { type: this._sequelize.QueryTypes.UPDATE });
    }

    public Delete(qry: string, vals: any = null) {
        let param = this.params ? this.params : vals;
        return this._sequelize.query({
            query: qry,
            values: param
        }, { type: this._sequelize.QueryTypes.DELETE });
    }

    public Ping() {
        return this._sequelize.authenticate();
    }
}

customer.manager.ts

CRUD operations performed on a entity named “ICustomer”

import { SqlManager } from '../helpers/sequelize/sql.manager';
import { ICustomer } from '../entities/ICustomer';
export class CustomerManager {
    private db: SqlManager;
    constructor() {
        this.db = new SqlManager();
    }
    public addCustomer(customer: ICustomer) {
        let query = "INSERT INTO customers (name,address) VALUES(:Name,:Address)";
        return this.db.Insert(query, customer);
    }

    public updateCustomer(customer: ICustomer) {
        let query = "UPDATE customers SET name=:Name, address=:Address WHERE Id=:Id";
        return this.db.Update(query, customer);
    }

    public deleteCustomer(id) {
        let query = "DELETE FROM customers WHERE Id=:id";
        this.db.addInputParameter("id", id);
        return this.db.Delete(query);
    }

    public getCustomers() {
        let query = "SELECT Id,Name,Address FROM customers";
        return this.db.Get(query, null);
    }

    public getCustomer(id) {
        let query = "SELECT Id,Name,Address FROM customers WHERE Id=:id";
        this.db.addInputParameter("id", id);
        return this.db.Get(query);
    }
}

If the parameter and json are same, you can pass the json object to the sqlmanager object. For any specific parameter, you can use the addInputParameter method as used in getCustomer.

customer.controller.ts

import { Router, Request, Response, NextFunction } from 'express';
import { Api } from '../helpers';
import { ICustomer } from '../entities/ICustomer';
import { CustomerManager } from '../data-manager/customer.manager';

export class CustomerController {
    public static route = '/customers';
    public router: Router = Router();
    constructor() {
        this.router.get('/', this.getCustomer);
        this.router.get('/:id', this.getCustomerById);
        this.router.post('/', this.addCustomer);
        this.router.put('/', this.updateCustomer);
        this.router.delete('/:id', this.deleteCustomer)
    }
    public getCustomer(request: Request, response: Response, next: NextFunction) {
        let manager = new CustomerManager();
        manager.getCustomers().then((result) => {
            return Api.ok(request, response, result);
        }, (err) => {
            next(err);
        });
    }

    public getCustomerById(request: Request, response: Response, next: NextFunction) {
        let manager = new CustomerManager();
        let id = parseInt(request.params['id'], 0);
        manager.getCustomer(id).then((result) => {
            return Api.ok(request, response, result[0]);
        }, (err) => {
            next(err);
        });
    }

    public addCustomer(request: Request, response: Response, next: NextFunction) {
        let manager = new CustomerManager();
        manager.addCustomer(request.body).then((result) => {
            return Api.ok(request, response, result[1]);
        }, (err) => {
            next(err);
        });
    }

    public updateCustomer(request: Request, response: Response, next: NextFunction) {
        let manager = new CustomerManager();
        manager.updateCustomer(request.body).then((result) => {
            return Api.ok(request, response, result[1]);
        }, (err) => {
            next(err);
        });
    }

    public deleteCustomer(request: Request, response: Response, next: NextFunction) {
        let manager = new CustomerManager();
        let id = parseInt(request.params['id'], 0);
        manager.deleteCustomer(id).then((result) => {
            return Api.ok(request, response, 'Ok');
        }, (err) => {
            next(err);
        });
    }
}