How to configure sequelize with mssql?

Question

I want to connect with Microsoft Sql using sequelize. I found this link http://docs.sequelizejs.com/manual/installation/getting-started.html

and i wrote the code below with nodejs:

require('dotenv').config();
var express = require('express');
var app = express();
const Sequelize = require('sequelize');

const sequelize = new Sequelize(process.env.DB_NAME,null,null, {
dialect: 'mssql',
host: process.env.DB_HOST + "\\" + process.env.DB_SERVER,
operatorsAliases: false,

pool: {
 max: 5,
 min: 0,
 acquire: 30000,
 idle: 10000
}

});

sequelize.authenticate().then((err) => {
  console.log('Connection successful', err);
})
.catch((err) => {
  console.log('Unable to connect to database', err);
});

app.listen(process.env.PORT);
console.log('Starting Server on Port ', process.env.PORT);

But when I run the code i have an error:

sequelize deprecated String based operators are now deprecated. Please use 
Symbol based operators for better security, read more at 
http://docs.sequelizejs.com/manual/tutorial/querying.html#operators 
node_modules\sequelize\lib\sequelize.js:242:13
Unable to connect to database { SequelizeHostNotFoundError: Failed to 
connect to USER-PC\SQLEXPRESS:1433-getaddrinfo ENOTFOUND USER-PC\SQLEXPRESS
at Connection.connection.on.err (C:\Users\User\Desktop\loginApp\node_modules
\sequelize\lib\dialects\mssql\connection-manager.js:98:22)

what i did wrong and i cant connect to database ?

Answer 1

// You can do it with a string.
const Sequelize = require('sequelize');
const sequelize = new Sequelize("mssql://username:[email protected]:1433", 
 { pool: {
    "max": 10,
    "min": 0,
    "idle": 25000,
    "acquire": 25000,
    "requestTimeout": 300000
  },
  dialectOptions: {
    options: { encrypt: true }
  }
  });
      // This uses the Raw Query to query for all dbs for example
  sequelize.query(`  
                SELECT name, database_id, create_date  
                FROM sys.databases   
                GO `, 
  { type: sequelize.QueryTypes.SELECT})
    .then(async dbs => {
      console.log("dbs", dbs);
      return dbs;
    });```


Example above: mssql db hosted on Azure. 

Example below: mssql db on localhost. 

You can do it with key value pairs or a string.

`
var Sequelize = require("sequelize");

var sequelize = new Sequelize("sequelize_db_name", user, password, {
  host: "localhost",
  port: 1433,
  dialect: "mssql",
  pool: {
    max: 5,
    min: 0,
    idle: 10000
  },
  dialectOptions: {
    options: { encrypt: true }
  }
});`

Answer 2

ES6 format

import { Sequelize } from "sequelize";
import dotenv from 'dotenv';

dotenv.config();

const db = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASS,
  {
    host: process.env.DB_HOST,
    dialect: "mssql",
  }
);

export default db;

Answer 3

If you are still looking for an answer, here is what worked for me for Azure mssql. Please replace the username, password and server name. Replace the database with the name of database if you want to connect to a specific database within it, else remove it

const sequelize = new Sequelize("mssql://username:[email protected]:1433/database", {
  dialect: 'mssql',
  dialectOptions: {
    // Observe the need for this nested `options` field for MSSQL
    options: {
      // Your tedious options here
      useUTC: false,
      dateFirst: 1
    }
  }
});

Answer 4

Solution is to alter the PORT

In many cases, the 1433 (default) port is not the actual port. To get the correct port, Follow the below steps:

Right-click on TCP/IP and select properties and scroll to the end, there you'll find the dynamic IP and PORT. Use that port to connect to your database.

SQL Server Configuration Manager

const sequelize = new Sequelize('database', 'username', 'password', {
    dialect: 'mssql',
    host: "localhost",
    port: XXXXX,
    logging: false,
});

// Perform database operations using Sequelize here

sequelize.authenticate()
    .then(() => {
        console.log('Connection has been established successfully.');
    })
    .catch((err) => {
        console.error('Unable to connect to the database:', err);
    });

module.exports = sequelize;

---

Sequelize version: "^6.29.3",
Tedious version: "^15.1.3",
MSSQL version: "^9.1.1"