Connect your relational database (MySQL) using Lambda, API Gateway and the Serverless framework.

In this tutorial we will see how to connect our RDS service using a MySQL database with Lambda function and API Gateway. We are going to use and configure Lambdas manually in order to work in a Virtual Private Cloud (VPC). Finally we will use the Serverless Framework to simplify this task.

To begin this tutorial we must have knowledge about how to create a relational database using RDS-AWS, also you need to know how to create a Lambda function, an API with API Gateway and finally have some knowledge about the use of the Serverless Framework. If you do not have idea how to create a relational database with RDS AWS, I suggest you go to my previous post.

First, we need to create an AWS's Lambda function. The next exercise has the purpose to show you how to manually configure the function to have permissions to access a VPC. After this, I'm going to show you how to simplify this task with Serverless framework. In the next figure (Fig 1) you will see where to configure your VPC, subnets and security groups manually inside Lambda Console.

Fig 1. In order to configure manually the VPC, the subnets and the security group, click on each of this options and select the correct one, this depends on your RDS configuration.

We are going to configure our .yml file. This file it is created when you type the next command in your terminal:

serverless create --template aws-nodejs

Now configure your Lambda in order to have access to your VPC, the subnets of this VPC and the security group. As soon as you have your file configured in this way, type in your terminal the sls deploy command, in order to see the changes in your Lambda…Now you have configured your Lambda to have access to your VPC without the use of AWS's console!!! Your .yml file should be in this way (Fig 2):

Fig 2

With this configuration you could avoid going to your AWS's console and do it manually. Remember that the subnetID (you must have at least 2 subnets ID selected) and security group ID will be in the RDS console once you have selected the DB instance (Fig 3).

Fig 3. In RDS console you could get the VPC and the subnets of this RDS Instance

Before continuing we will do a test using MySQL Workbench (You could use DataGrip of JetBrains). Connect the database with the option Manage Connections, if you have any doubt about this step, go to one of my previous post, click here (Fig 4).

Fig 4. If everything is right you should see this message.

The next step is to create a table, I use this query but you can create a table as you want.

CREATE TABLE curso (
ID int PRIMARY KEY NOT NULL AUTO_INCREMENT,
todo varchar(100) NOT NULL,
created_at timestamp DEFAULT current_timestamp
);

If you have any problem connecting your database, make sure your security group are configured correctly. First configure your security group allowing access to your IP, in order to test your connection in MySQL Workbench. Second and most importantly, configure your security group allowing any lambda function that has that security group selected (remember that we chose a security group manually in the Lambda console and also did it from the serverless framework) have inbound permissions. In the next image I show you how to do it (Fig 5).

Fig 5

This is the classic message that you will receive in Postman if you do not configure well the inbound permission in EC2 console or RDS Console.

{ "message" : "Internal server error" } 

Now, let's see how the code looks. If you look, at the first line of the code (Fig 6) of the Lambda function you will see that I installed MySQL, so in your terminal type the next command (inside the folder in which you are working):

npm install --save mysql

FIg 6.

Ready???, Finally we are going to create a Lambda function. This Lambda function has two principal components. The first one is a javascript file called connection.js, where I configure MySQL connection, this is a very common configuration that you can find easily in the internet. The second one is todos.js file, in this file I make a request to the database calling the database table called todos. One of the most important thing to mention in this file is (Fig 7):

context.callbackWaitsForEmptyEventLoop = false

Fig 7

This line of the code prevents the connection to the database from always be open. I'm going to use Postman in order to test the API. Once you have tested your API you should receive a blank array (Fig 8). This is because we don't have any data in our database. I'm going to use MySQL Workbench to add some data to the table.

Fig 8

In order to add some data to the table go to MySQL Workbench and use this Query

INSERT INTO curso (todo, created_at) VALUES ("correr",CURRENT_TIME())

Test it again (Fig 9)…

Fig 9

Congratulations you have just connected RDS service using Lambda function and API Gateway services.

If you have a question or comment, do not hesitate to contact me, my email is ca.ortiz.pacheco@gmail.com.