Node.js + Express app in Azure hits 500 errors that fix themselves

Question

I have a Node.js 4.4.7 app using Express 3.4.4 that has this mysterious issue. If I navigate to www.mydomain.com sometimes I'm met with a 500 server error. It is important to note that the home page here makes a DB call. Immediately after this happens if I navigate to www.mydomain.com/login the page renders fine. No DB transactions occur on this page. From the login page, I can press Home in the navigation bar and I'll be brought to www.mydomain.com/ and the page renders fine.

It's almost as if the DB or Web App instance go to sleep and need to be awoken. So I enabled Always On in my application settings but this has not resolved the problem. I also created a few alert tests that push some traffic to the site so it never goes to sleep. This helped a bit but occasionally I get the alert email that my ping test has failed still.

When a ping test fails, I am able to immediately go to www.mydomain.com/login successfully, then navigate to "Home" from that page successfully. A few minutes later I get a notification that my app state is "Healthy again.

Route for mydomain.com/

exports.index = function (req, res) {
var query = "SELECT P.pid, P.title, P.tags, P.topic, P.body_preview FROM Posts P ORDER BY P.date_created DESC";

new req.sql.Request().query(query, function (err, recordset) {
    if (err) throw err;

    res.render('index', {
        rows: recordset
    });
});

Route for mydomain.com/login

exports.g_login = function (req, res) {
    res.render('auth/login');
}

Similarly, I have the site setup to deploy directly from my GitHub repository. Whenever I push to my master branch the site usually hits a 500 error for a few minutes then is fine. I KNOW it's not from breaking code because this can be duplicated by simply committing a comment in any file.

I've found this post which seems to experience similar symptoms, placing the fault on the Azure load balancer.

Azure Log Stream

My site was working fine right now, but to get the log stream to show an error I performed the following steps.

• cd MyApp/
• echo "test" >> delete-me.txt
• git add delete-me.txt
• git commit -m "test to make 500 error"
• git push

And as soon as I refreshed my website, to no surprise it hits a 500 error. I opened up the logs and this connection error is shown in the mssql Node module. Again, I was able to fix the 500 error by going to mydomain.com/login and then navigating to mydomain.com/.

Express server listening on port \\.\pipe\7a9d4df2-518e-4eb1-afbe-74064c927cbf
Sat Oct 14 2017 23:17:24 GMT+0000 (Coordinated Universal Time): Application has thrown an uncaught exception and is terminated:
ConnectionError: Connection is closed.
    at D:\home\site\wwwroot\node_modules\mssql\lib\main.js:1569:17
    at nextTickCallbackWith0Args (node.js:420:9)
    at process._tickCallback (node.js:349:13)

This is the code that connects to the DB from my app.js

var sql = require("mssql");

// Setup for Azure SQL Server
var config = {
    user: env.username,
    password: env.password,
    server: "mydomain.database.windows.net",
    database: "blog",

    // As per the npm docs, encrypt=true for Azure connections
    options: {
        encrypt: true
    }
};
sql.connect(config, function (err) {
    if (err) throw err;
});

Also of note, I have never hit this error when plugged into my Azure db but working locally, it is only the deployed version of my app that experiences this. I have read through this GitHub issue where many people are experiencing this problem. Is there any solution out there?

EDIT Adding middleware that reveals the SQL object.

app.use(function (req, res, next) {
    req.highlight = highlight;
    req.sessions = sessions;
    req.bcrypt = bcrypt;
    req.sql = sql;
    req.md = md;
    req.fs = fs;
    next();
});

Answer 1

My guess is that the problem lies in the data-base connection (Hence, why it happens when you initiate the app).

I would suggest to have a different module where you do the data-base connection and query logic, and then use it in your route.

A brief suggestion.

have another file called db.js, and do something similar to the following:

var sql = require("mssql");

// Setup for Azure SQL Server
var config = {
    user: env.username,
    password: env.password,
    server: "mydomain.database.windows.net",
    database: "blog",

    // As per the npm docs, encrypt=true for Azure connections
    options: {
        encrypt: true
    }
};

function query(query, callback){
   sql.connect(config, function(err) {
      if(err) throw err;
      new sql.Request().query(query, function (err, result){
         if(err) throw err;
         callback(result)
      });

   });
}

module.export = {query : query}

Then you require the file at your route logic, and you do:

var db = require('../db.js');


exports.index = function (req, res) {
var query = "SELECT P.pid, P.title, P.tags, P.topic, P.body_preview FROM Posts P ORDER BY P.date_created DESC";

db.query(query, function (recordset) {

    res.render('index', {
        rows: recordset
    });

});