Row results from Tedious Request.on not being saved

I have a function queryDatabase() in my nodeJS application, which is responsible for querying an Azure SQL DB and returning the result, using the Tedious library to connect to the DB.

Currently, to return the data retrieved from the DB I am iterating through the rows returned by the query, saving the data to a var (an array of dicts, essentially JSON really), and returning it. However I’m experiencing an issue where the var is returned (empty) before anything can be assigned to it.

I’m pretty sure this would have something to do with the callback functions in request.on() executing after the return is called, but I am a bit inexperienced with this side of JS and haven’t had much luck with online documentation/tutorials.

function queryDatabase(){


 console.log("Reading rows from the Table...");

  // Read all rows from table
  const request = new Request(
    `select TOP 5 level, city, address from [dbo].[mainTable];`,
    (err, rowCount) => {
      if (err) {
        console.error(err.message);
      }
    }
  );

  var result = [];

  request.on("row", (columns) => {
    var entry = {};
    columns.forEach((column) => {
      entry[column.metadata.colName] = column.value;
    });
    result.push(entry);
  });

  connection.execSql(request);

  return result;
}

Any help would be greatly appreciated, cheers!

Answer

Request is an event emitter, so its all about waiting for all the events you care about to happen, then returning the result. This is what Promise‘s are for. A promise is an async action that will resolve at some future date. you need to wait for all row events to be emitted, collect them, then wait for the done event, and resolve the promise. I have modified your code to achieve this, and shown example for usage.

function queryDatabase(){

  console.log("Reading rows from the Table...");

  // Read all rows from table
  const request = new Request(
    `select TOP 5 level, city, address from [dbo].[mainTable];`,
    (err, rowCount) => {
      if (err) {
        console.error(err.message);
      }
    }
  );

  return new Promise((resolve,reject)=>{
    const result = [];
  
    request.on("row", (columns) => {
      const entry = {};
      columns.forEach((column) => {
        entry[column.metadata.colName] = column.value;
      });
      result.push(entry);
    });

    request.on('error',error=>reject(error));// some error happened, reject the promise
    request.on('done',()=>resolve(result)); // resolve the promise with the result rows.

    connection.execSql(request);

  });

}

Usage shown below:

queryDatabase().then(rows=>{
  // do something with the rows
})
.catch(error=>{
  // do something with the error
})