When my nodejs app is tring to connect with SQL Server, nothing is showing on console, even if there is no error

Question

Below code is belongs to index.js file.

When I go to the link "localhost:300/admins/" according to the code it should connect with SQL Server and get back the result on console.

My Microsoft SQL Server Management Studio 2012 is running well and from Visual Studio it can operate smoothly.

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const path = require('path');
const hbs = require('hbs');
const sql = require('mssql');
const sqlConfig = {
  user: 'xxx',
  password: 'xxxx',
  database: 'xxxxx',
  server: '127.0.0.1',
  port: xxxx,
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000,
  },
};

// static file's path set up
app.use(express.static('public'));
app.use('/css', express.static(__dirname + '/public'));
app.use('/images', express.static(__dirname + '/public'));
app.use('/js', express.static(__dirname + '/public'));

const pagespath = path.join(__dirname, './templates/views');
const partialspath = path.join(__dirname, './templates/partials');

// Set Views and view engine

app.set('view engine', 'hbs');
app.set('views', pagespath);
hbs.registerPartials(partialspath);

app.get('/', (req, res) => {
  res.render('home', { title: 'Home' });
});
app.get('/admins', function (req, res) {
  var result;
  async () => {
    try {
      const pool = await sql.ConnectionPool(config);
      const result = await pool.query`select name from tbl_info_p_admin`;
      res._write(result);
      console.log(result);
    } catch (err) {
      console.log(err);
    }
    await sql.close();
  };
  res.render('./admin/masters', { title: 'ADMIN' });
});

app.listen(process.env.PORT || 3000, function (err) {
  if (err) {
    console.log(err);
  } else {
    console.log('Server Started At Port 3000');
  }
});

Answer 1

There are at least two problems in your code i can see

1. as already mentioned by @AlwaysLearning , your sql config object is defined as sqlConfig but you use sql.ConnectionPool(config)
2. Your code is basically doing nothing when the /admins endpoint is called (except from res.render("./admin/masters", { title: "ADMIN"}); You are defining an anonymous function with async ()=>{...} that is is never actually executed. To execute an anonymous function you could use an IIFE. In this case this would not be a great idea, since problems concerning asynchronous calls will arise. I suspect you did this so you can use async/await. To achieve this you would not wrap the relevant code in an anonymous async function but make the whole callback function async.

const express = require('express');
const app = express();
const bodyParser = require("body-parser");
const path = require('path');
const hbs = require('hbs');
const sql = require('mssql')
const sqlConfig = {
  user: "xxx",
  password:"xxxx",
  database: "xxxxx",
  server: '127.0.0.1',
  port:xxxx,
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  }
}

// static file's path set up
app.use(express.static('public'));
app.use('/css', express.static(__dirname + '/public'));
app.use('/images', express.static(__dirname + '/public'));
app.use('/js', express.static(__dirname + '/public'));

const pagespath = path.join(__dirname, './templates/views');
const partialspath = path.join(__dirname, './templates/partials');

// Set Views and view engine

app.set('view engine', 'hbs');
app.set('views', pagespath);
hbs.registerPartials(partialspath);

app.get("/", (req, res) => {
  res.render("home", { title: "Home" });
});

app.get('/admins', async function(req, res) {
  try {
    const pool = await sql.ConnectionPool(sqlConfig);
    const result = await pool.query`select name from tbl_info_p_admin`;
    res._write(result);
    console.log(result)
  } catch (err) {
    console.log(err);
  }
  await sql.close();
  res.render("./admin/masters", { title: "ADMIN"}); 
});

app.listen(process.env.PORT || 3000, function (err) {
  if (err) {
    console.log(err);
  } else {
    console.log("Server Started At Port 3000");
  }
});

I could not test this myself but I hope it helps.

Minor additions:

• you might wanna close the sql connection in a finally clause
• It might also be better to not close the connection after each request. But I am not sure since I am not too familiar with mssql.

Answer 2

1. MSSQL server connection configurations.

   const config = {
     user: 'XX', // sql user
     password: 'xxxxxx', //sql user password
     server: '127.0.0.1', // if it does not work try- localhost
     database: 'xxxxxx',
     options: {
       trustServerCertificate: true,
       useColumnNames: true,        
       rowCollectionOnDone: true,
       trustedconnection: true,
       enableArithAbort: false,
       instancename: 'MSSQLSERVER',  // SQL Server instance name
       cryptoCredentialsDetails: {
          minVersion: 'TLSv1'
       },
     },
     port: 1433
   }
   module.exports = config;