SQL Server query function times out or returns null on AWS Lambda-CodePudding

I am trying to run a query using the mssql node package on AWS Lambda. The problem is when I return the query results to the buildResponse function the Lambda function times out, returns null, or {}. in local testing simply nothing returns unless I console.log the result recordset.

I have tried using the .then() type syntax when I call my function but I have not had luck. Am I missing something obvious? Please help!


 const sql = require('mssql')


 const pool = new sql.ConnectionPool({

    user: process.env.USER,
    password: process.env.PASSWORD,
    server: proccess.env.SERVER,
    driver: process.env.SERVER,
    database: process.env.DATABASE,
    port: process.env.PORT,
        options: {
            ConnectionTimeout:30,
            CommandTimeout:30,
            encrypt: true, // for azure
            trustServerCertificate: true, // change to true for local dev / self-signed certs
        
        }

})

 async function getList(){
    var sqlQuery = "select * from table" //obfuscating my query for obvious reasons
    pool.connect(function(err){
        console.log("connected")
     if(err){
       console.log(err);
     }
     var req = new sql.Request(pool);
     console.log("request query in progress")
     req.query(sqlQuery ,function(err,result){
      if(err){
       console.log(err);
      }
      else{
       console.log("returning query results!")
       return buildResponse(200, result.recordset)
      }
       
      pool.close();
     });
    });
    
}


function buildResponse(statusCode, body) {
    return {
      statusCode: statusCode,
      headers: {
        'Content-Type': 'application/json',
        'Access-Control-Allow-Origin':'*',
        'Access-Control-Allow-Methods':'GET,OPTIONS',
        'Access-Control-Allow-Headers':'Content-Type,Authorization,X-Amz-Date,X-Api-Key,X-Amz-Security-Token'
      },
      body: JSON.stringify(body),
      isBase64Encoded: false
    }
}

getList()

CodePudding user response：

found out on my own. Short answer is to use callbacks.


  function getList(callback){
   
    pool.connect(function(err){
        console.log("connected")
     if(err){
       console.log(err);
     }
     var req = new sql.Request(pool);
     console.log("request query in progress")
     req.query("myquery",function(err,result){
      if(err){
       console.log(err);
      }
      else{
       console.log("returning query results!")
       callback(result.recordset)
      }
       
      pool.close();
     });
     
    });
   
}


function buildResponse(statusCode, body) {
    return {
      statusCode: statusCode,
      headers: {
        'Content-Type': 'application/json',
        'Access-Control-Allow-Origin':'*',
        'Access-Control-Allow-Methods':'GET,OPTIONS',
        'Access-Control-Allow-Headers':'Content-Type,Authorization,X-Amz-Date,X-Api-Key,X-Amz-Security-Token'
      },
      body: JSON.stringify(body),
      isBase64Encoded: false
    }
}

getList((callback)=>{
    console.log(callback)
})