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”.

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.

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

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.

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

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 🙂