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, Aug 30 '19 at 04:00
@Adiii I added console logs to my code, and it appears like the connection is never being established and failing invisibly — Rilcon42, Aug 30 '19 at 18:59
Assuming `rdsquery` is your handler function, wouldn't you still need to call the Lambda handler's `callback` somehow? — m90, Aug 30 '19 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, Aug 30 '19 at 19:10
First thing, the handler is missing in your code I was able to catch when I run it on my Side. dont have MSSQL to test but tested below code using mysql its working. ``` var mysql = require('mysql'); var connection = mysql.createConnection({ host : 'sdb.test.com', user : 'root', password : 'test' }); connection.connect(); exports.handler = (event, context) => { connection.query("SELECT * FROM table", function(err, rows, fields) { console.log("rows: " + rows); context.succeed('Success'); }); }; ``` — Adiii, Aug 30 '19 at 19:18
Check in your CloudWatch, do you see any error like `timed out`? Maybe your code takes to long time to connect to mssql server. — hoangdv, Sep 02 '19 at 06:14

score 2 · Accepted Answer · answered Sep 02 '19 at 03:43

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.

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

1 Answers1

Linked