Nodejs crashes when database connection fails

Question

While my database server is not available and any function of my node-express rest service like hiExpress is called, Nodejs crashes the node server and console of node reports

sql server connection closed

I do not want this to happen because either it should go to err function or at least it must be cautht by catch block. What could i do to avoid the crash of nodejs when database server is not available I am using following code which is absolutely fine as long as database server is available.

var sqlServer = require('seriate');

app.get('/hiExpress',function(req, res)
{
    var sr = {error:'',message:''};
    var sql= 'select * from table1 where id=? and name=?';
    var params = {id: 5, name:'sami'};    
    exeDB(res,sr,sql, params);//sent only 4 parameters (not 6)
});

function exeDB(res, sr, sql, params, callback, multiple) {
    try {
        var obj = {};
        for (p in params) {
            if (params.hasOwnProperty(p)) {
                obj[p] = {
                    type: sqlServer.VARCHAR,
                    val: params[p]
                };
            }
        };

        var exeOptions = {
            query: sql,
            params: obj
        };
        if (multiple) { 
            exeOptions.multiple = true;
        }

        sqlServer.execute(sqlServerConfigObject, exeOptions).then(function (results) {
            sr.data = results;
            if (callback)
                callback(sr);
            else
                res.json(sr); //produces result when success
        }, function (err) {
            //sr.message = sql;
            console.log(11);
            sr.error = err.message;
            res.json(sr);
        });
    }
        catch (ex) {
            console.log(21);
        sr.error = ex.message;
        res.json(sr);
    }
}

Why I preferred to use seriate

I had not been much comfortable with node-SQL, especially when when it came to multiple queries option even not using a transaction. It facilitates easy go to parameterized queries.

Down-vote is part of game :) SO users don't mind it. However there should be put a comment, so that the OP could improve the question and take care in next questions. — Sami, Aug 01 '16 at 16:05

Aikon Mogwai · Answer 1 · 2016-08-01T15:54:48.457

You can use transaction without seriate but with async like below

async.series([
    function(callback) {db.run('begin transaction', callback)},
    function(callback) {db.run( ..., callback)},
    function(callback) {db.run( ..., callback)},
    function(callback) {db.run( ..., callback)},
    function(callback) {db.run('commit transaction', callback)},

], function(err, results){
    if (err) {
        db.run('rollback transaction');
        return console.log(err);
    }

    // if some queries return rows then results[query-no] contains them
})

The code is very dirty. Pass req and res params to db-layer is not a good idea.

Try change exeDB. I'm not sure, but probably you don't set error catcher to promise

function exeDB(res, sr, sql, params, callback, multiple) {
    // It will execute with no error, no doubt
    var obj = {};
    for (p in params) {
        if (params.hasOwnProperty(p)) {
            obj[p] = {
                type: sqlServer.VARCHAR,
                val: params[p]
            };
        }
    };

    var exeOptions = {
        query: sql,
        params: obj
    };

    if (multiple) { 
        exeOptions.multiple = true;
    }

    // Potential problem is here. 
    // Catch is useless because code below is asynchronous.
    sqlServer.execute(sqlServerConfigObject, exeOptions).then(function (results) {
        sr.data = results;
        if (callback)
            callback(sr);
        else
            res.json(sr); //produces result when success
    }).error(function(err){ // !!! You must provide on-error
        console.log(err);
    };
}

Nodejs crashes when database connection fails

1 Answers1