Tedious sql query returning empty object (can´t return custom array and callback not working)

Question

Im using Tedious in Node JS to return a simple SQL Query :

var Connection = require('tedious').Connection;  
var config = {  
    server: 'myserver.database.windows.net',
    authentication: {
        type: 'default',
        options: {
            userName: 'myusername',
            password: 'mypassword',
            rowCollectionOnDone: true,
            rowCollectionOnRequestCompletion: true
        }
    },
    options: {
        encrypt: true,
        database: 'mydatabase'
    }
};  
const connection = new Connection(config);  
connection.on('connect', function(err) { 
    if (err) {
        console.log(err);
    }else{
        console.log("Connected");  
    }
});  

var Request = require('tedious').Request  
var TYPES = require('tedious').TYPES;  
let results = [];

function checkId(cid) {  
    request = new Request("SELECT * FROM mytable WHERE id = @cid",function(err, rowCount, rows) {
        if (err) {
            console.log(err);
        }
    });  
    
    request.addParameter('cid', TYPES.NVarChar, cid);  
    request.on('row', function(row) {
        results.push(row);
    });
    console.log(results) // I can see results array perfectly here
    connection.execSql(request); 
    //return callback(null, results); // Tried setting callback as parameter too in this function but got error "callback is not a function"
}  

When I call the statement function outside like this:

var sqlquery = new checkId(passid);
console.log(sqlquery);

I get:

"sqlquery {}"

I other words, an [object Object] but empty (obviously, JSON.strinfigy in empty it's useless). I already tried to use callback as a workaround as commented in the code above but got error, how can I see results array when I call the function ?

Answer 1

Knowing that Tedious it's async and results won´t process on time to get them outside (reason why it´s empty object) and since there´s no specific answer to this anywhere (a lot of theory yes, but not a clear explanation due that some code it´s outdated like curiosly where you use callback as a set function, there´s no guidance of how to mix promises with Tedious properly, answers only show results in console.log or some people may be beginners with the sync/async concept), I post here a commented way that worked for me in case anyone may need it:

///Connection
    var Connection = require('tedious').Connection;  
    var config = {  
        server: 'myserver.database.windows.net',
        authentication: {
            type: 'default',
            options: {
                userName: 'myusername',
                password: 'mypassword',
                rowCollectionOnDone: true,
                rowCollectionOnRequestCompletion: true
            }
        },
        options: {
            encrypt: true,
            database: 'mydatabase'
        }
    };  
    const connection = new Connection(config);  
    connection.on('connect', function(err) { 
        if (err) {
            console.log(err);
        }else{
            console.log("Connected");  
        }
    });  
    var Request = require('tedious').Request  
    var TYPES = require('tedious').TYPES;  

Now , inside an async function you can call this example query with params (must be inside async function in order to use 'return await' and that everything syncs to avoid empty data :

    async your_function(){
                var id = 'id you want or user inputs';
                const allData = [];
                // We now set the promise awaiting it gets results
                await new Promise((resolve,reject) => {
                    const request = new Request("SELECT * FROM table WHERE field = @id", function(err, rowCount) {
                         if (err) {
                             return reject(err);
                         } else {
                             console.log(rowCount + ' rows');
                         }
                     });
                     
                     request.addParameter('id', TYPES.NVarChar, id); //Param id declaration
                     request.on('row', function(columns) {
                         columns.forEach(function(column) {
                             allData.push(column.value); //Push the result to array
                         });
                     });
             
                     request.on('doneProc', function (rowCount, more, returnStatus, rows) {
                         console.log('onDoneProc');
                         return resolve(allData); //Here we resolve allData using promise in order to get it´s content later
                     });
             
                     connection.execSql(request);
             
                 });

                 var mydata = allData;  // Now You can assign it or use the same object as well
    }

Hope it helps someone as it did to me.