Javascript Node.js

AWS Lambda Tutorial – RDS MySQL

In this tutorial, we’ll still stick around Lambda. I’m going to show you how to connect to RDS MySQL database from AWS Lambda function.

Getting Start

First, You’ll need to have Database instance running on RDS. Below is the database I have (I’m using MySQL Workbench to browse) Just only one employee table with couple rows. so I’m going to make the Lambda function connect to the database and select the first employee which is “Harry Potter”.

aws-lambda-mysql-1

We’ll need to use node.js MySQL module for this job which is not natively available on Lambda. So we need to code and test locally and then create a deployment package and upload it back to lambda.

Let’s begin with initializing the project with npm init and then install the mysql module with npm install

npm init
npm install mysql

we’ll create a new file for local run. Let’s name it local.js — and then load the mysql module using require

var mysql = require('mysql');

Connection Pool

Now, rather than creating and managing the database connection one-by-one, when working with Lambda, we should utilize the connection pool instead. So when your function was invoked many times in a short period. the connection can be reused without opening and closing excessively.

Let’s use the example code on MySQL github to create the pool.

var pool  = mysql.createPool({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret',
  database : 'my_db'
});

pool.getConnection(function(err, connection) {
  // connected!
});

For Host url, you can get it from your RDS console of your database instance.

aws-lambda-mysql-2

Now it’s not a very good practice to hard-coding host url, username and password in the source code, so I’m going to create a config JSON file to hold these value.

{
    "dbhost" : "your host url",
    "dbname" : "your DB name",
    "dbuser" :"username",
    "dbpassword" :"password"
}

Then load the config file from the js using require. Now we can access the value on config file like this

var config = require('./config.json');

var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
  });

Next, we’ll use getConnection method to get the connection from the pool and then use query method to execute the SQL. And in the callback, we’ll release the connection back to the pool for reuse. then throw the error if any or show the selected result to the console

var mysql = require('mysql');
var config = require('./config.json');

var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
  });

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query('SELECT emp_name from Employee where emp_id=1', function (error, results, fields) {
    // And done with the connection.
    connection.release();
    // Handle error after the release.
    if (error) throw error;
    else console.log(results[0].emp_name);
    process.exit();
  });
});

Local Testing

Now let’s test our code using node command

node local.js

aws-lambda-mysql-3

OK, that works fine. So the next step is to make change to our code a little bit so it can work with lambda. Let’s create a copy of this code to another file. First, add exports.handler and move getConnection inside it. Then replace the throw and console.log with lambda callback function.

The final code should look like this

var mysql = require('mysql');
var config = require('./config.json');

var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
  });

exports.handler =  (event, context, callback) => {
  //prevent timeout from waiting event loop
  context.callbackWaitsForEmptyEventLoop = false;
  pool.getConnection(function(err, connection) {
    // Use the connection
    connection.query('SELECT emp_name from Employee where emp_id=1', function (error, results, fields) {
      // And done with the connection.
      connection.release();
      // Handle error after the release.
      if (error) callback(error);
      else callback(null,results[0].emp_name);

    });
  });
};

Deploy to Lambda

We need to remove process.exit and replace it with context.callbackWaitsForEmptyEventLoop = false;
We need to do this because, the connection pool remain active for connection to be reuse and so if we do not disable this flag, the lambda callback will wait forever for the pool to shutdown and resulted in timeout.

Now, we’re ready, let’s create a deployment package by zipping everything you need.

aws-lambda-mysql-4

And then upload it from the console and test it on Lambda.

aws-lambda-mysql-5

You can see the final result in this video

That’s it! Hope this help getting start with the lambda MySQL connection. If you like this tutorial, please follow us on our Facebook and Youtube Channel to stay tune 🙂

15 comments

  1. Thank you for the concise tutorial, it has been extremely helpful. I am, however, getting an error when uploading to Lambda “Cannot read property ‘query’ of undefined”. Could you direct me as the proper way to set up the test procedure on Lambda? Thank you

          1. Thank you for your time. I have followed your code perfectly and it works great locally, just not when uploading to Lambda. My only guess it that it could be related to execution role? I set it for lambda_basic_execution.

          2. Issue with AWS Lambda resolved. The additional step I needed to take was to modify DB security group for incoming traffic.

  2. Not work here…
    “errorMessage”: “2017-11-17T17:53:18.656Z 2fc26d99-cbc0-11e7-a006-81b6c7dd40be Task timed out after 3.00 seconds”

  3. if lets say a hundred requests comes in and lets say they come in NOT fast enough for the same ‘warm’ lambda to be reused, does that mean the 100 connection pools are created ?

  4. Thank you for your exercise. I am using an external Mysql database, which I think should make no difference. I get two error on AWS Lambda environment window.

    context.callbackWaitsForEventLoop = False; — error “False is not defined, please fix or add/*Global False*/

    pool.getConnection(function(err, connection) { —error “Expected Error to be handled”

    When I test the Lamda – Details as below
    START RequestId: abb6f174-13f1-11e9-9a45-2d08003fc5fa Version: $LATEST
    2019-01-09T09:33:50.280Z abb6f174-13f1-11e9-9a45-2d08003fc5fa ReferenceError: False is not defined
    at exports.handler (/var/task/main.js:13:38)
    END RequestId: abb6f174-13f1-11e9-9a45-2d08003fc5fa
    REPORT RequestId: abb6f174-13f1-11e9-9a45-2d08003fc5fa Duration: 45.54 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 24 MB
    RequestId: abb6f174-13f1-11e9-9a45-2d08003fc5fa Process exited before completing request

    Anything you can suggest to fix this issue.

    Regards

    Shaun

Leave a Reply

Your email address will not be published. Required fields are marked *