Sequelize

Class for working with Microsoft SQL database and sequelize.

note

Class should be used in backend

Import

ES5

const { Sequelize } = require("js-ts-kit");

ES6

import { Sequelize } from "js-ts-kit";

Connecting To Database

import sequelize from "sequelize";
import { Sequelize } from "js-ts-kit";

const dbUser = "";
const dbPassword = "";
const dbName = "";
const dbHost = "";
const sequelizeClass = new Sequelize(
  sequelize,
  dbHost,
  dbName,
  dbUser,
  dbPassword,
  {
    logging: (...msg) => console.log(msg),
    dialectOptions: {
      encrypt: true,
      options: { trustServerCertificate: true, requestTimeout: 300000 },
      ssl: {
        require: true,
        rejectUnauthorized: false,
      },
    },
  },
);

async function main() {
  await sequelizeClass.authenticate();
}

Executing Stored Procedure

import sequelize from "sequelize";
import { Sequelize } from "js-ts-kit";

const dbUser = "user";
const dbPassword = "password";
const dbName = "database";
const dbHost = "database.domain.com";
const sequelizeClass = new Sequelize(
  sequelize,
  dbHost,
  dbName,
  dbUser,
  dbPassword,
  {
    logging: (...msg) => console.log(msg),
    dialectOptions: {
      encrypt: true,
      options: { trustServerCertificate: true, requestTimeout: 300000 },
      ssl: {
        require: true,
        rejectUnauthorized: false,
      },
    },
  },
);

async function main() {
  try {
    await sequelizeClass.authenticate();
    await sequelizeClass.executeStoredProcedure(
      "EXEC usp_getUser :id",
      { id: 1 },
      sequelize.QueryTypes.SELECT,
    );
  } catch (err) {
    console.error(err);
  }
}

Executing SQL

import sequelize from "sequelize";
import { Sequelize } from "js-ts-kit";

const dbUser = "user";
const dbPassword = "password";
const dbName = "database";
const dbHost = "database.domain.com";
const sequelizeClass = new Sequelize(
  sequelize,
  dbHost,
  dbName,
  dbUser,
  dbPassword,
  {
    logging: (...msg) => console.log(msg),
    dialectOptions: {
      encrypt: true,
      options: { trustServerCertificate: true, requestTimeout: 300000 },
      ssl: {
        require: true,
        rejectUnauthorized: false,
      },
    },
  },
);

async function main() {
  try {
    await sequelizeClass.authenticate();

    const result = await sequelizeClass.executeSQL(
      `
    DECLARE @success bit;
    DECLARE @successMessage nvarchar(255);
    EXEC usp_getUser :id, @success OUTPUT, @successMessage OUTPUT;
    SELECT @success AS success, @successMessage AS successMessage;
  `,
      { type: sequelize.QueryTypes.SELECT, replacements: { id: 1 } },
    );

    const status = result[result.length - 1];
    if (!status.success) {
      throw new Error(status.successMessage);
    }
  } catch (err) {
    console.error(err);
  }
}

Reference

Create Object

new Sequelize(sequelize, username, password, database, host, options = {})

Name	Type	Default Value	Description
sequelize			instance from importing sequelize package
host	string		host of database
database	string		name of database
username	string		username for authenticating against database
password	string		password for authenticating agaomst database
options	object	{}	object for options

Object Properties

Name	Type	Static
sequelize		no

Methods

authenticate()

Parameters

No parameters.

Returns

Nothing is returned.

executeSQL(sqlString, options)

Parameters

Name	Type	Description
sqlString	string	SQL code
options	object for QueryOptions	Options for Sequelize#query

Returns

• Type: Promise

• Description: result from executing given sql string

executeStoredProcedure(sqlString, valuesJson, queryType)

Parameters

Name	Type	Description
sqlString	string	SQL statement for calling stored procedure with named parameter replacements
valuesJson	object for replacements	named parameter replacements in the format :param.
queryType	enum of QueryTypes	type of query for stored procedure

Returns

• Type: Promise

• Description: result from executing given stored procedure string