MS SQL CRUD Operation using Sequelize Raw Queries and Express JS

Krishna KV
Technology Specialist
July 9, 2018
Rate this article
[Total: 1    Average: 5/5]
Views    147

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

The below is the configuration class used to create the Sequelize instance. If you are new to TypeScript for Express Js, check out my other article on “Creating an Express Js Application with TypeScript” for more details.
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);
        });
    }
}
Subscribe To Our Newsletter

Leave a comment