Understanding callbacks through Asynchronous Execution of SQL request.query in node js

Introduction

Recently, I was working on a sample node js application. I wanted to connect to a SQL Server database and display the result on console/UI. Since node js is Asynchronous in nature (which is different from traditional server technologies), I faced certain issues because of this Asynchronous nature. I will be highlighting the problem faced and basic solution implemented with the help of an example.

Problem

Below is my sample code which connects to SQL Server database, executes the SQL query and gets the employee data from there and prints on console. For the sake of understanding, I am putting the complete code.

var myDBRequest;
var express = require('express');
var sql = require('mssql');

var app = express();

/******* Database operation ***********/
//Prepare a Database connection configuration object
var dbconfig = {
        server: '****',
        database: '****,
        user : '*****,
        password: '******'
    };

//Connect to database and create a sql request object
sql.connect(dbconfig,function(err){
     if(err) console.log(err);
    
     myDBRequest = new sql.Request();
});

//Use the sql request object to execute the query which will fetch the employees from database
var GetEmpList = function(){
         myDBRequest.query('select * from Employee', function(err,recordset){
             if(err) console.log('Request Error: '+ err);                          

              console.log(recordset);
         });            
    };

/********* Server operations *******************/
app.get('/getEmp',function(request,response){
    GetEmpList(); 
});

/*Creates a server and listens to requests*/
var server = app.listen('8800',function(){
    var port = server.address().port;
    console.log('Listening at http://localhost:%s',port);
});

This code works perfectly fine when you give a call to GetEmpList() function. It will print the recordset on console with proper data.

Now, we will just try to make our code more moduler by giving a DB fetch responsibility and display responsibility to two different functions. If we slightly tweak the above code, it will start giving you unexpected output. Below is the slight tweak.

var GetEmpList = function(){
         myDBRequest.query('select * from Employee', function(err,recordset){
             if(err) console.log('Request Error: '+ err);                          

             return recordset;  //Return a recordset from here rather than printing it
         });            
    };

/********* Server operations *******************/
app.get('/getEmp',function(request,response){
    var data = GetEmpList(); 
    console.log(data); // Accept a recordset here and print here
});

/*Creates a server and listens to requests*/
var server = app.listen('8800',function(){
    var port = server.address().port;
    console.log('Listening at http://localhost:%s',port);
});

Here, instead of doing console.log in GetEmpList function, we are returning the recordset from GetEmpList function and printing it in calling function. Everything looks fine, right?

But on console, the printed value will be 'undefined'. Just a small tweak and the application misbehaves.

This is happening because of the Asynchronous nature of node js. After giving a call to the GetEmpList() function, it is not concerned if the operation inside this function is completed or not. It is just assuming that the operation is completed and executing a next code block. So here the query execution is not completed and as well callback function in myDBRequest.query is not called when the control went back to calling function. And since callback function is not called inside myDBRequest.query, recordset value was not set and hence the final output is 'undefined'.

Solution

So what is the solution to the above problem? How do we make sure that the final output is not printed unless and until the value of recordset is set properly? The simple answer is to use the callback. Below is the code which is using the callback function to solve the above problem.

var GetEmpList = function(Callback){
         myDBRequest.query('select * from Employee', function(err,recordset){
             if(err) console.log('Request Error: '+ err);                          

             Callback(recorset);  //Call a callback function which is passed as a parameter
         });            
    };

/********* Server operations *******************/
app.get('/getEmp',function(request,response){
    GetEmpList(function(data){
            console.log(data); //Print a resultset inside a callback function
        });
});

/*Creates a server and listens to requests*/
var server = app.listen('8800',function(){
    var port = server.address().port;
    console.log('Listening at http://localhost:%s',port);
});

In the above code, you can see we are passing a function (which accepts data) as a parameter to GetEmpList and doing a console.log inside this function. In GetEmpList function, this parameter is accepted as callback. This callback is called inside the result of myDBRequest.query callback function and recordset is passed as a parameter.

The below image shows the callback operation.

This ensures that the execution of console.log happens only after the recordset value is set properly.