Querying RDS MySQL DB With NodeJS Lambda Function
Walk through my experience with querying an RDS MySQL database with AWS lambda for the first time, and the issues that I came across that you can avoid.
Join the DZone community and get the full member experience.
Join For FreeRecently, 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!
Call To Action
Originally published at notebookbft.wordpress.com on January 9, 2018.
Published at DZone with permission of Rajind Ruparathna, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments