Running a query inside the same connection in nodejs

There is a need to run the second sql query (query2) after building it using results of first query (query1) in mentioned space in code snippet, this second sql query will be generated according to the results of first query. but when I tried to run this, it builds the second query without any issue, but it is not executing. Can someone help to correct this.

let userNames ="'";
let query1="SELECT * FROM user WHERE userid in ('1','2','3','4')"

conn.connect().then(function(){
    let req1=new sql.Request(conn);
    req1.query(query1).then(function(recordset){            
        recordset.recordset.map((item)=>{
            userNames=userNames.concat(item.user_name+"','");
        })
        userNames=userNames.slice(0, -2);
                 
        query2=`SELECT * FROM INVOICEMASTER WHERE username IN (${userNames})`
        
        console.log('----------------------------------')   

            **//// Need to execute query2 here**
            
        console.log('before closing the con')
        conn.close();
        console.log('after closing the con')       
    })
    .catch(function(err){
        console.log(err)
        conn.close();
    });
})
.catch(function(err){
    console.log(err);
});

Answer

Node.js is Asynchronous so each function call will execute separately and all the other execute without waiting for the previous functions.

So when you execute:

req1.query(query2).then((data)=>{
     ** process your data **
});

conn.close();

The query will be executed in a separate thread but the event loop will continue and close the connection.

The solution to that would:

1. Async Await

   req1.query(query1).then(async function(recordset){            
        recordset.recordset.map((item)=>{
            userNames=userNames.concat(item.user_name+"','");
        });
        userNames=userNames.slice(0, -2);
   
        query2=`SELECT * FROM INVOICEMASTER WHERE username IN (${userNames})`;
   
        var query2Data = await req1.query(query2);
   
        ** process query2Data here **
   
        conn.close();
   
   });
   

2. Close the SQL connection in the second query execution callback function.

   req1.query(query1).then(async function(recordset){            
        recordset.recordset.map((item)=>{
            userNames=userNames.concat(item.user_name+"','");
        });
        userNames=userNames.slice(0, -2);
   
        query2=`SELECT * FROM INVOICEMASTER WHERE username IN (${userNames})`;
   
        req1.query(query2).then( function(data){
   
            ** process query2Data here **
            conn.close();
        });
   
   });