In our previous article, we delved into the intricacies of linking an AWS Lambda function to a Virtual Private Cloud (VPC). Today, we will build upon that knowledge and delve deeper into the process of crafting a Lambda function capable of establishing a connection with a MySQL RDS instance and executing queries.
Additionally, we will guide you through the creation of a Lambda Layer that will seamlessly incorporate the PyMySQL python module into our function. If you missed our previous article on creating a Layer for AWS Lambda function, don't worry! We will provide a brief overview of the steps right here, specifically tailored to creating a layer for PyMySQL.
Let's briefly take a look at the architecture diagram below of what our implementation will look like.
In this example, you'll notice the presence of security groups attached to both the Lambda function and the RDS instance. Our configuration will involve setting up the RDS security group to exclusively allow MySQL traffic from the Lambda security group. Additionally, you'll also come across the VPC-to-VPC NAT and the Elastic Network Interface that seamlessly connects the Lambda function to your VPC. If you're interested in more details on connecting your Lambda function to a VPC or enabling egress access to the Internet, be sure to check out our informative article "How to Seamlessly Connect a VPC to Your Lambda Function".
Before we dive into the exciting process of connecting our Lambda function to a MySQL RDS instance, we need to ensure that we have a running MySQL RDS instance. This will serve as the foundation for our database connection and query execution. So, make sure you have your MySQL RDS instance up and running before we proceed with the rest of the steps.
Let's get started!
Security Group for our Lambda Function
Before we even create our Lambda function, let's start by creating the security group for our Lambda function and adding it as an inbound rule to the RDS security group. Navigate over to the security group page on the AWS console. You can do this by first going to the EC2 service console and looking for Network & Security on the left panel. Alternatively, you can navigate to the VPC service console, and look for Security on the left panel. Once you have navigated to Security Groups, click the yellow 'Create security group' button.
Choose a meaningful name for the security group that clearly reflects its purpose. This will help us easily identify and understand the role of this specific security group in our setup.
Now, let's proceed to define the outbound and inbound rules for our security group. As there won't be any inbound connection to the AWS Lambda function, we can keep the rules as they are. For the purpose of this exercise, we'll stick with the default outbound rules, allowing any IPv4 traffic for all protocols. However, if you prefer, you have the option to add an outbound rule specifically for MySQL traffic and set the source to the security group associated with the RDS instance you want to connect to.
After successfully creating the security group, we can now proceed to add it to the inbound rules of the security group that is associated with the RDS instance. Locate the security group connected to your database from the list of security groups, and click on the security group ID to access the details. From there, select 'Edit inbound rules' to include the newly created security group.
From the Edit inbound rules page, add a new rule and select MYSQL/Aurora as the type (or protocol). Then for the source box, select the security group you just created for your Lambda function.
Now we have our security groups configured, let's create a new Lambda function, which will eventually hold our code for communicating with the RDS instance.
Creating the Lambda Function
If you're already well-versed in the art of creating a Lambda function, feel free to skip ahead. However, if you're new to the process, don't worry! We'll guide you through the steps of creating a Python-based Lambda function. During this step, we won't be adding any code just yet. Instead, we'll create a blank Lambda function that will serve as a placeholder for now. This will allow us to focus on configuring other important parameters, such as the execution roles required to connect to a VPC and the lambda Layers needed to incorporate the PyMySQL module.
To begin the process of creating a new Lambda function, simply navigate to the AWS Lambda service console and locate the "Function" option in the left navigation panel. In the screenshot below, you'll notice a list of existing functions, but if this is your first time, the list may be empty. Don't worry, just click on the inviting yellow "Create function" button to get started on your Lambda journey.
When creating the Lambda function, we'll give it a descriptive name and select Python 3.11 as the runtime. For the architecture, I suggest choosing arm64 unless there's a specific need for x86_64. The arm64 architecture offers better cost-performance compared to its x86 counterparts. We will leave everything else as default.
With our Python-based Lambda function ready to go, it's time to shift our focus to configuring the VPC connection, Layers, and environmental variables. These crucial steps will ensure that our function is fully equipped to communicate with our RDS instance. Once we have everything set up, we can dive into adding the necessary code.
VPC Execution Role for Lambda
In order for your AWS Lambda function to seamlessly connect to a Virtual Private Cloud (VPC), it is essential to provide the Lambda function's role with the appropriate permissions to establish a network interface for VPC communication.
These permissions are:
Luckily, the necessary permissions for connecting your AWS Lambda function to a Virtual Private Cloud (VPC) are conveniently packaged into the AWS managed policy called AWSLambdaVPCAccessExecutionRole. It's important to note that these permissions are only required for creating network interfaces, not for invoking your VPC function. This means that even if you remove these permissions from the execution role, your VPC function can still be successfully invoked.
For a more detailed understanding of execution roles and permissions, you can refer to the AWS Developer Guide and our previous article on How to Seamlessly Connect a VPC to Your Lambda Function.
To incorporate this essential execution role, you'll need to modify the current execution role associated with your Lambda function. This can be done by accessing the IAM service console and locating the specific role linked to your Lambda function. Alternatively, you can go to your Lambda function, navigate to the Configuration tab, and select Permissions. Clicking on the role name will open a new tab or window displaying the IAM role. Take a look at the screenshot below for reference:
After accessing the IAM role for your Lambda function, the next step is to add a new policy. Simply click on the "Add permissions" dropdown and then select "Attach policies" to proceed.
Search for the AWSLambdaVPCExecution role and add the permission to your role.
With the necessary permission successfully added, we can now proceed to configure the Lambda function to establish a connection with the VPC where the RDS instance resides.
Connecting Lambda to VPC
Now, let's take the next step and establish a connection between our Lambda function and the VPC where our RDS instance is located. By default, Lambda functions are executed in a separate VPC, which means they cannot access private resources within the VPC. However, we can configure our Lambda function to connect to our VPC, allowing it to access these private resources, including our RDS instance. Take a look at the architecture diagram below for a visual representation.
To configure the Lambda function and establish this connection, go back to your Lambda function and navigate to the configurations tab. On the left side, you will find the option for VPC. Open the VPC option and click on 'Edit' to begin the configuration process.
Once you access the VPC configuration page, choose the VPC where your RDS instance is located. Then, select a subnet for your Lambda functions to be configured in. While you only need one subnet, it's recommended to have two for high-availability mode. Lastly, don't forget to choose the security group you created for your Lambda function to enable successful communication with the RDS instance.Once you've made these selections, simply hit the 'Save' button at the bottom of the page. Keep in mind that it may take some time for the VPC configuration to update, so keep an eye on the helpful blue message above. While the new configuration settings are being applied to our function, let's create a layer with our PyMySQL library and complete the setup of environmental variables.
Adding PyMySQL via Layers
Layers provide a powerful method for incorporating additional functionality into your Lambda function. By utilizing layers, you can easily add the PyMySQL library to your function, enhancing its capabilities and enabling seamless communication with your MySQL RDS instance.
To begin, we need to install the PyMySQL library locally in a folder. Here are the commands to create a new folder and install PyMySQL:
pip install pymysql -t python
We next need to compress the contents of the folder:
zip -r9 pymysql.zip python
Now that we have the compressed zip file ready, we can proceed to upload it to AWS to create our Layer. To get started, head back to the Lambda service console and select the Layer option from the left panel.
Select the yellow 'Create layer' button to add a new layer. Under Layer configuration, provide descriptive information about the Layer. In our case, PyMySQL and optionally a version. Selecting the compatible architectures and runtimes are optional, but I like to include them so I know which platform and runtime the Layer is meant for.
Once the Layer has been created, navigate back to your function and click on Layers just underneath the function name (see screenshot below):
When you click on 'Layers' your browser should automatically scroll down to the Layers section where you can add/remove Layers.
Click on 'Add a layer' to add a new layer to your function. On the Add layer page, select 'Custom layers' from 'Layer source', and choose the Layer you just created. You will also need to select the version for the custom layer you are adding (in our case version 1). Click 'Add' to add the new custom layer to your function.
With the PyMySQL module successfully incorporated through a layer, we are now ready to add environment variables and delve into the exciting world of coding.
Connecting to RDS and Making SQL Queries
In order to securely store our database connection information and credentials, we will utilize environment variables. While there are alternative methods such as AWS Secrets Manager for storing sensitive information, we will explore that in a future blog article.
To access the environment variables, navigate to the Configuration section of your Lambda function and select the Environment Variables option.
Click on the 'Edit' button to add new environment variables. If you change the key names, make sure you update the sample code to use the new key names.
With our environment variables properly configured, it's time to start coding!
To kick things off, we'll be importing the os and pymysql modules. The os module will allow us to easily access our environment variables, while the pymysql module will enable us to establish a connection to our MySQL database.
The provided code utilizes a sample table I established in MySQL, which contains primary key ID, name, email, and a timestamp. Remember to customize the code to align with your specific database and tables. Below is an example table definition:
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| created_at | timestamp | NO | | current_timestamp() | |
Here is the complete code base (also available as a gist link):
Once you have copied the above code, click 'Deploy' to make sure the most recent version of your code has been deployed and ready for testing. To test your function after deploying, click the green 'Test' button at the top of your Lambda code editor. If this is the first time to test this Lambda function (which it is) you will be prompted to create a new test event. Since we do not need to pass any information to your Lambda, we will create a new hello-world example but remove all key-value pairs. We will also give the test a name.
Click save, then click on the green 'Test' button again. Hopefully the Lambda function will run successfully and you see a similar result like the one below.
As we reach the conclusion of this tutorial, I hope it has provided valuable insights into the process of connecting a Lambda function to a VPC, enabling seamless communication with a MySQL RDS instance.
An important consideration to remember is that a Lambda function, when connected to a VPC, lacks internet access by default. To enable communication with external endpoints or AWS services, configuring service endpoints or a NAT gateway becomes essential. However, it's crucial to note that a NAT gateway incurs associated costs, prompting a balance between functionality and expenditure.
Stay tuned for our upcoming series of AWS technical guides and tutorials! We'll continue exploring and demystifying the intricate world of cloud services to empower your development journey.
Remember, if you require assistance with AWS, whether it's cloud configuration, management, cost optimization, or consulting, our team is here to support you every step of the way.
I founded KeyQ in March of 2020 with the vision of helping businesses achieve the next level of success through delivering innovative and meaningful cloud solutions. Since its inception, I have worked with several businesses, non-profit organizations, and universities to design and build cloud applications that have helped streamline their business processes and reduce costs.
Prior to KeyQ, I was a medical researcher at the University of Alabama at Birmingham (UAB) in the Division of Pulmonary, Allergy, and Critical Care Medicine. UAB is also where I worked on my doctoral thesis under the mentorship of Dr. Jessy Deshane and Dr. Victor Thannickal. During my doctoral work at UAB I was exposed to the “omics” and big data, which has influenced my career choice to develop data-driven analytics platforms in the cloud.
I also have to give a big shoutout to my undergraduate education at Worcester Polytechnic Institute (WPI), where I majored in biochemistry. WPI’s motto is “Lehr und Kunst,” which roughly translates to “Theory and Practice” or “Learning and Skilled Art.” WPI truly cherishes and upholds this pedagogy, which can be seen by the teaching styles and class sizes. The learning experience I had at WPI is unique and has shaped me to be who I am, being able to learn, practice and apply.
I love to learn innovative technologies and try new things. I have a broad area of interests that include serverless architectures, machine learning, artificial intelligence, bioinformatics, medical informatics, and financial technology. I am also working towards my CFA level 1 exam for 2021. Other interests and hobbies include traveling, rock climbing, rappelling, caving, camping and gardening!