DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Alexa Skill With Node.js
  • Alexa Skill With TypeScript
  • Troubleshooting Connection Issues When Connecting to MySQL Server
  • Building and Integrating REST APIs With AWS RDS Databases: A Node.js Example

Trending

  • Using Python Libraries in Java
  • Tired of Spring Overhead? Try Dropwizard for Your Next Java Microservice
  • The Smart Way to Talk to Your Database: Why Hybrid API + NL2SQL Wins
  • Vibe Coding With GitHub Copilot: Optimizing API Performance in Fintech Microservices
  1. DZone
  2. Software Design and Architecture
  3. Cloud Architecture
  4. Querying RDS MySQL DB With NodeJS Lambda Function

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.

By 
Rajind Ruparathna user avatar
Rajind Ruparathna
·
Jan. 12, 18 · Opinion
Likes (8)
Comment
Save
Tweet
Share
90.0K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Querying RDS MySQL DB with NodeJS Lambda Function

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

  • Like. Share. Appreciate and let others find this article.
  • Comment. Share your views on this article.
  • Keep in touch. LinkedIn, Twitter

Originally published at notebookbft.wordpress.com on January 9, 2018.

MySQL Node.js AWS Lambda code style Connection (dance)

Published at DZone with permission of Rajind Ruparathna, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Alexa Skill With Node.js
  • Alexa Skill With TypeScript
  • Troubleshooting Connection Issues When Connecting to MySQL Server
  • Building and Integrating REST APIs With AWS RDS Databases: A Node.js Example

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!