Querying RDS MySQL DB With NodeJS Lambda Function

Recently, I tried AWS lambda for the first time. My task was to query an RDS MySQL database. In this blog post, I will share my experience on that.

First Attempt: Using the Inline Code Editor

Since I was using NodeJS, the first thing was to figure out a good MySQL node package. I found this MySQL library thanks to Douglas Wilson, and it has good documentation, as well. Writing the code was simple.

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: "<rds_endpoint>",
    user: "<rds_username>",
    password: "<password>",
    database: "<db_name>",
});
// console.log(connection);
exports.handler = (event, context, callback) => {
    connection.query('show tables', function (error, results, fields) {
        if (error) {
            connection.destroy();
            throw error;
        } else {
            // connected!
            console.log(results);
            callback(error, results);
            connection.end(function (err) { callback(err, results);});
        }
    });
};

Since I was able to get the code done quickly, I just jumped into the Lambda console inline code editor, copied and pasted the code, and tested it. And... it didn't work. The error I got was as follows:

Response:
{
 "errorMessage": "Cannot find module 'mysql'",
 "errorType": "Error",
 "stackTrace": [
 "Function.Module._load (module.js:417:25)",
 "Module.require (module.js:497:17)",
 "require (internal/module.js:20:19)",
 "Object. (/var/task/index.js:1:75)",
 "Module._compile (module.js:570:32)",
 "Object.Module._extensions..js (module.js:579:10)",
 "Module.load (module.js:487:32)",
 "tryModuleLoad (module.js:446:12)",
 "Function.Module._load (module.js:438:3)"
 ]
}

Request ID:
"69e6019e-f2ab-11e7-bc4f-c1c1fcbaf7c5"

Function Logs:
START RequestId: 69e6019e-f2ab-11e7-bc4f-c1c1fcbaf7c5 Version: $LATEST
Unable to import module 'index': Error
 at Function.Module._resolveFilename (module.js:469:15)
 at Function.Module._load (module.js:417:25)
 at Module.require (module.js:497:17)
 at require (internal/module.js:20:19)
 at Object. (/var/task/index.js:1:75)
 at Module._compile (module.js:570:32)
 at Object.Module._extensions..js (module.js:579:10)
 at Module.load (module.js:487:32)
 at tryModuleLoad (module.js:446:12)
 at Function.Module._load (module.js:438:3)
END RequestId: 69e6019e-f2ab-11e7-bc4f-c1c1fcbaf7c5
REPORT RequestId: 69e6019e-f2ab-11e7-bc4f-c1c1fcbaf7c5 Duration: 87.53 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 19 MB

With a couple of Google searches for the error, I figured out that the MySQL module that we used is not natively supported by AWS and thus we needed to provide a bundled ZIP file as the lambda function.

Second Attempt: Using a Bundled Node Project

We can simply use the node init command (assuming that NodeJS is installed and ready) to create a package.json file to initialize a node project. One important thing here is that AWS Lambda required the node project name to be same as the lambda name. So, make sure to use the same name.

Since we need the MySQL package, run the following command.

npm install mysql --save

My package.json was as follows.

{
  "name": "queryDatabase",
  "version": "1.0.0",
  "description": "Sample",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node index.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "mysql": "^2.15.0"
  }
}

Then, we need to create an index.js file and use the same code as we used above. Finally, we need to create a ZIP file (we can use the command zip -r query-db.zip) and upload that as the lambda function. Note that there should not be an inner directory inside the ZIP file. If you list the content of the ZIP file (unzip -l query-db.zip), it should be as follows.

Archive:  query-db.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
      293  2018-01-06 12:23   package.json
      465  2018-01-05 17:01   index.js
        0  2018-01-06 12:23   node_modules/
        0  2018-01-05 17:01   node_modules/bignumber.js/
    99742  2017-09-03 14:40   node_modules/bignumber.js/bignumber.js
     2893  2018-01-05 17:01   node_modules/bignumber.js/package.json
        0  2017-08-30 23:46   node_modules/bignumber.js/doc/
    85752  2017-08-30 23:46   node_modules/bignumber.js/doc/API.html
    17694  2017-09-03 14:40   node_modules/bignumber.js/bignumber.min.js
     1134  2017-05-04 00:03   node_modules/bignumber.js/LICENCE
    34548  2017-08-30 23:52   node_modules/bignumber.js/bignumber.js.map
    12346  2017-09-03 14:40   node_modules/bignumber.js/README.md
... ...

Once we upload this and test it, it should work. However, there were few issues for me — and one was a connection timeout issue with the following error.

Response:
{
 "errorMessage": "RequestId: dc5b8605-f2b4-11e7-b332-01a16e1fd35c Process exited before completing request"
}

Request ID:
"dc5b8605-f2b4-11e7-b332-01a16e1fd35c"

Function Logs:
START RequestId: dc5b8605-f2b4-11e7-b332-01a16e1fd35c Version: $LATEST
2018-01-06T07:40:36.766Z dc5b8605-f2b4-11e7-b332-01a16e1fd35c Error: connect ETIMEDOUT
 at Connection._handleConnectTimeout (/var/task/node_modules/mysql/lib/Connection.js:419:13)
 at Socket.g (events.js:292:16)
 at emitNone (events.js:86:13)
 at Socket.emit (events.js:185:7)
 at Socket._onTimeout (net.js:338:8)
 at ontimeout (timers.js:386:14)
 at tryOnTimeout (timers.js:250:5)
 at Timer.listOnTimeout (timers.js:214:5)
 --------------------
 at Protocol._enqueue (/var/task/node_modules/mysql/lib/protocol/Protocol.js:145:48)
 at Protocol.handshake (/var/task/node_modules/mysql/lib/protocol/Protocol.js:52:23)
 at Connection.connect (/var/task/node_modules/mysql/lib/Connection.js:130:18)
 at Connection._implyConnect (/var/task/node_modules/mysql/lib/Connection.js:461:10)
 at Connection.query (/var/task/node_modules/mysql/lib/Connection.js:206:8)
 at exports.handler (/var/task/index.js:11:16)
END RequestId: dc5b8605-f2b4-11e7-b332-01a16e1fd35c
REPORT RequestId: dc5b8605-f2b4-11e7-b332-01a16e1fd35c Duration: 10204.07 ms Billed Duration: 10300 ms Memory Size: 128 MB Max Memory Used: 27 MB 
RequestId: dc5b8605-f2b4-11e7-b332-01a16e1fd35c Process exited before completing request

While searching for this error, there were several good suggestions on this StackOverflow question. If you are facing similar issues, you might want to take a look at those, as well. In general, examine the following in case of issues.

1. Check Permissions on Lambda

You will need at least the AWSLambdaBasicExecutionRole, which grants permissions only for the Amazon CloudWatch Logs actions to write logs. You can use this policy if your Lambda function does not access any other AWS resources except writing logs. See more info here.

2. Check If Your RDS Instance's Security Group

Check if incoming connections are allowed from a Lambda or if there is a set of whitelisted IP addresses. The issue in my case was that the security group assigned to my MySQL RDS instance was only allowing to connect from my IP, and since lambda is trying from a different IP, the connection fails.

The problem with a set of whitelisted IP addresses and Lambda is that in Lambda IP address that the requests are sent from will not be the same. However, AWS Lambda supports executing your code from inside a VPC. With this ability, we're able to create a NAT (Network Address Translator) Gateway so that all outbound connections from our lambda functions will exit from the NAT which is assigned to a fixed IP address. This post outlines this solution.

3. Check If Your Lambda Function Uses VPC

With the above being said, if you are using VPC unintentionally, it might cause problems since when you enable VPC, your Lambda function will lose default internet access. If you require external internet access for your function, ensure that your security group allows outbound connections and that your VPC has a NAT gateway.

4. Check If Your Code Closes Connections Properly

Better to check if your connections are handled properly to avoid any possible connection leak issues.

I hope these will help you if you ever try querying a database with Lamda.

Cheers!