AWS Lambda with RDS using pymysql
In this tutorial, I will show you how to create a Lambda function which queries data from your RDS database using the pymysql library.
1. AWS Account Setup
If you already have an AWS account setup, please skip ahead to section 2. If you do not, you can sign up for an account here. You will be required to enter payment details but you get 12 months of free tier access when signing up and nothing in this tutorial will exceed free tier limits.
Note: If you have exceeded your free tier usage for your account already, you may be charged for the usage of the AWS services described in this tutorial. If you keep more than one RDS instance running, you will exceed the free tier limit.
2. Create RDS Instance
In the AWS console, select RDS from the list of services. Select ‘Create database’ from the RDS dashboard. We will be creating a MySQL database so choose this as the Engine type, you can leave the Version as the default option.
Note: When creating your database, ensure that you have the desired region selected in the top right of the AWS console (this is typically the region that is geographically closest to you).
Select ‘Free tier’ as your Template. Under the Settings section, you need to give your RDS instance a name and provide a Master username and password which will be used for logging into your instance. Make sure that you remember the username and password that you use to set up your RDS instance.
Leave the DB instance size, Storage and Availability & durability sections as the defaults. In the Connectivity section, expand the Additional connectivity configuration options and check the Public access option to Yes.
The final configuration that you can optionally specify is in the Additional configuration section. If you want a database to be created when your RDS instance is created, you can specify an Initial database name in this section. I will create a database with the name ‘playlist’ in this tutorial. You can then click ‘Create database’. It may take a few minutes before your RDS instance is up and running (the Status is ‘Available’).
3. Connect to your database from MySQL Workbench
If you don’t already have MySQL and Workbench installed, you will need to download and install it from here. At a minimum, MySQL server and Workbench will need to be installed.
Note: Some basic level understanding of MySQL is assumed for this tutorial.
When your database instance has been created, select the database you just created and go to the Connectivity & security section. You will see the Endpoint and Port for your database instance here, which will be needed to connect to your database.
Before we can connect to our database instance from MySQL Workbench, we must edit the VPC security group rules for our RDS instance. Click on the default VPC security group for your instance in the Connectivity & security section. Select the Inbound rules tab for the security group and click ‘Edit inbound rules’. The security group is configured by default to allow all traffic from within the VPC security group, so we need to add another inbound rule to allow all traffic from any IP address.
Save the rules. You will now be able to add the connection in MySQL Workbench and connect to your RDS instance using the Endpoint address as the Hostname and the master username and password that you created.
Open the connection to the RDS instance and you will notice that the ‘playlist’ database has already been created for us in the SCHEMAS tab on the left. We will run a few simple SQL commands to create a table in our database and add some data to our table.
use playlist;create table playlist (
playlist_name varchar(100) not null,
song_name varchar(100) not null,
artist varchar(100) not null,
album varchar(100),
primary key (playlist_name, song_name, artist)
);insert into playlist (playlist_name, song_name, artist) values ('#1', 'Your Song', 'Elton John');
insert into playlist (playlist_name, song_name, artist) values ('#1', 'Hey Jude', 'The Beatles');
insert into playlist (playlist_name, song_name, artist, album) values ('#1', 'Hotel California', 'The Eagles', 'Hotel California');
insert into playlist (playlist_name, song_name, artist, album) values ('#1', 'Stairway to Heaven', 'Led Zeppelin', 'Led Zeppelin IV');
Now that we have created our database and inserted data into the table, we can switch our attention to setting up our Lambda function.
4. Create your Lambda function
Now it’s time to set up your Lambda function. Open the Lambda service and ensure the Functions dashboard is selected. I recommend creating your Lambda function in the same region as the RDS instance you have already created.
Select “Create function” and give your function a name, I used ‘get-playlist-rds’ in this tutorial. Use Python 3.8 as the Runtime and leave “Create a new role with basic Lambda permissions” as the Execution role. Create your function.
The pymysql library is not available to Lambda functions by default so we will need to import it so that it can be used to connect to RDS.
5. Add the pymysql library as Lambda layer
Before we can add the pymysql library as a Lambda layer, we must first package the library as a zip file. In this tutorial I will explain how to do this on Windows, but a similar process can be followed on Mac/Linux.
You will need python installed on your local Windows PC and the python version should match the Runtime version you selected for your Lambda function, in this case Python 3.8. You also need to install pip so that you can download the pymysql package. Here is a link to instructions on how to do this on Windows.
Once you have pip installed on your PC, open the command prompt and navigate to your desired directory and run the following command.
pip install --target ./python pymysqlThis will create a folder called python in the desired directory. You will then need to compress the python folder as a zip file so that we can upload it to AWS.
In the Lambda console window, select the Layers option from the left side menu and click ‘Create layer’. Give your layer a name of ‘pymysql’ and upload the zip file that you just created. Select Python 3.8 as a Compatible runtime and click ‘Create’.
Go back to the ‘get-playlist-rds’ function that you have already created and click on Layers in the Designer section. You can then add the Custom layer that you have just created (select version 1). You are now ready to configure the rest of your Lambda function.
Note: The alternative to adding a Lambda layer to your function is to directly upload the zip file to your Lambda function (under the Actions menu in the Function code section).
6. Connect to your database from your Lambda function
Now that the pymysql library is available to you Lambda function code, you can add the following code to allow you to connect to your RDS instance. The code sets the database connection properties which are imported from rds_config, creates the connection to the database and executes a simple query to get all data from the ‘playlist’ table we created. The lambda function returns this data in the body of a HTTP response which could be invoked from API Gateway (I have discussed this in a previous tutorial).
import json
import pymysql
import rds_configrds_host = rds_config.db_endpoint
name = rds_config.db_username
password = rds_config.db_password
db_name = rds_config.db_name
port = 3306try:
conn = pymysql.connect(host=rds_host,user=name,
passwd=password,db=db_name,
connect_timeout=5,
cursorclass=pymysql.cursors.DictCursor)except:
sys.exit()def lambda_handler(event, context): with conn.cursor() as cur:
qry = "select * from playlist"
cur.execute(qry) body = cur.fetchall() return {
'statusCode': 200,
'headers': {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Headers': 'Content-Type,X-Amz-Date,Authorization,X-Api-Key,X-Amz-Security-Token',
'Access-Control-Allow-Credentials': 'true',
'Content-Type': 'application/json'
},
'body': json.dumps(body)
}
The next step is to add the rds_config file to your Lambda function to get the database properties referenced in the Lambda function code. In the Function code section, select File > New File and name your file rds_config.py. You will add the configuration properties for your RDS instance here. The structure of the file is shown below, replace the variables with the values for your RDS instance.
db_endpoint = 'your_rds_endpoint_address'
db_username = 'your_master_username'
db_password = 'your_master_password'
db_name = 'your_database_name'Before you can communicate with your RDS instance you will need to add your Lambda function to the same VPC as your RDS instance. In order to do this, you will need to update your Lambda execution role to attach the NetworkAdministrator policy. In the Permissions tab of your Lambda function, click on the Role name to open the IAM console where you can attach the policy.
You can then go back to the Configuration tab and configure the VPC to match the RDS instance VPC configuration, which is shown in section 3 above.
You are now ready to test your Lambda function. In the top right of your Lambda function console window, select the ‘Select a test event’ drop down menu and click ‘Configure test events’. AWS comes with many test event templates already configured, you can use the ‘hello-world’ template. Give your test event a name and create. Click on the ‘Test’ button and verify that your function executes successfully.
6. Conclusion
You have now successfully created a simple RDS instance and set up a Lambda function to query the contents of the database by creating a pymysql layer.
