AWS Lambda NodeJS call to SQL Server returns no data and no errors

Question

I am trying to connect to an AWS hosted SQL server with a lambda function, but when I test it in the lambda management console, or via an API call I get passed all the way through the function to get the final response of test here

This seems to indicate that the call hit no errors and returned no records. Since I know the table has data I would have at least expected an error or ideally, data. How can I troubleshoot this?

using CloudWatch console output I see 'called rdsquery' but nothing after that point

var sql = require("mssql");

// config for your database
var config = {
    user: 'xxuser',
    password: 'xxxx',
    server: 'mydns', 
    database: 'Antonio' 
};


module.exports.rdsquery = async event => {

console.log('called rdsquery')
  try{
    // connect to your database
    await sql.connect(config, function (err) {

      console.log('connected')
        if (err) 
          console.log('rdsquery: '+err)
          //return {statusCode: 200, headers: {'Access-Control-Allow-Origin': '*'},body: JSON.stringify('sql connect err: '+err)};

        // create Request object
        var request = new sql.Request();
        console.log('rdsquery: request crated')   
        // query to the database and get the records
        request.query('select CustomerNo from FROMMAS_AR_Customer', function (err, recordset) {

            if (err)
            console.log('rdsquery-sql: '+err)   
            //return {statusCode: 200, headers: {'Access-Control-Allow-Origin': '*'},body: JSON.stringify('sql err: '+err)};

            // send records as a response
            console.log('logging recordset')
            console.log(recordset);
            return {statusCode: 200, headers: {'Access-Control-Allow-Origin': '*'},body: JSON.stringify(recordset)};
            //return {statusCode: 200, headers: {'Access-Control-Allow-Origin': '*'},body: JSON.stringify(recordset)};
        });
    });
  }
  catch(e)
  {
    console.log('rdsquery-catch: '+e)   
    return {statusCode: 200, headers: {'Access-Control-Allow-Origin': '*'},body: JSON.stringify('ERR: '+e)};
  }

  //return {statusCode: 200, headers: {'Access-Control-Allow-Origin': '*'},body: JSON.stringify('test here')};
};

did you check AWS cloudwatch logs? you should console error instead of return them to help in debug — Adiii
– Adiii, Commented Aug 30, 2019 at 4:00
@Adiii I added console logs to my code, and it appears like the connection is never being established and failing invisibly — Rilcon42
– Rilcon42, Commented Aug 30, 2019 at 18:59
Assuming rdsquery is your handler function, wouldn't you still need to call the Lambda handler's callback somehow? — m90
– m90, Commented Aug 30, 2019 at 19:10
Yes, after some more digging (when I put the require mssql inside the try catch) I get a 'module not found error'. Seems like im not loading it into lambda correctly — Rilcon42
– Rilcon42, Commented Aug 30, 2019 at 19:10

Rilcon42 · Accepted Answer · 2019-09-02 03:43:09Z

2

NOTE THAT I AM NOT A LAMBDA EXPERT. Any of this info could be totally wrong. I would appreciate comments below if you find issues with how I solved the problem

So after a lot of digging I found that I had several problems with my lambda configuration:

My Lambda function timeout was too short by default. When I increased the timeout to 30 seconds I started getting useful errors like Could not connect MYIP when I ran tests inside lambda.
Then I found this answer on SO which had me add a VPC to my lambda function (so the function could access RDS)
Finally I had to add the policy AWSLambdaVPCAccessExecutionRole to my lambda user.

answered Sep 2, 2019 at 3:43

Rilcon42

9,87422 gold badges98 silver badges182 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

AWS Lambda NodeJS call to SQL Server returns no data and no errors

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related