Prerequisites Create an Amazon RDS DB instance Create Lambda function and proxy Create a function execution role Create a Lambda deployment package Update the Lambda function Test your Lambda function in the console Create an Amazon SQS queue Create an event source mapping to invoke your Lambda function Test and monitor your setup Clean up your resources

Tutorial: Using a Lambda function to access an Amazon RDS database

In this tutorial, you use a Lambda function to write data to an Amazon Relational Database Service (Amazon RDS) database through RDS Proxy. Your Lambda function reads records from an Amazon Simple Queue Service (Amazon SQS) queue and writes a new item to a table in your database whenever a message is added. In this example, you use the AWS Management Console to manually add messages to your queue. The following diagram shows the AWS resources you use to complete the tutorial.

An instance of the AWS Management Console connects to an Amazon SQS standard queue, which connects to a Lambda function, which further connects to a RDS for MySQL database through RDS Proxy.

With Amazon RDS, you can run a managed relational database in the cloud using common database products like Microsoft SQL Server, MariaDB, MySQL, Oracle Database, and PostgreSQL. By using Lambda to access your database, you can read and write data in response to events, such as a new customer registering with your website. Your function, database instance, and proxy scale automatically to meet periods of high demand.

To complete this tutorial, you carry out the following tasks:

Launch an RDS for MySQL database instance and a proxy in your AWS account's default VPC.
Create and test a Lambda function that creates a new table in your database and writes data to it.
Create an Amazon SQS queue and configure it to invoke your Lambda function whenever a new message is added.
Test the complete setup by adding messages to your queue using the AWS Management Console and monitoring the results using CloudWatch Logs.

By completing these steps, you learn:

How to use Amazon RDS to create a database instance and a proxy, and connect a Lambda function to the proxy.
How to use Lambda to perform create and read operations on an Amazon RDS database.
How to use Amazon SQS to invoke a Lambda function.

You can complete this tutorial using the AWS Management Console or the AWS Command Line Interface (AWS CLI).

Prerequisites

Before you begin, complete the steps in the following sections:

Create an Amazon RDS DB instance

Tutorial workflow diagram showing you are the create database step.

An Amazon RDS DB instance is an isolated database environment running in the AWS Cloud. An instance can contain one or more user-created databases. Unless you specify otherwise, Amazon RDS creates new database instances in the default VPC included in your AWS account. For more information about Amazon VPC, see theAmazon Virtual Private Cloud User Guide.

In this tutorial, you create a new instance in your AWS account's default VPC and create a database namedExampleDB in that instance. You can create your DB instance and database using either the AWS Management Console or the AWS CLI.

To create a database instance

Open the Amazon RDS console and chooseCreate database.
Leave theStandard create option selected, then inEngine options, chooseMySQL.
InTemplates, chooseFree tier orSandbox.Free tier appears for free tier accounts.Sandbox appears for paid plan accounts.
InSettings, forDB instance identifier, enterMySQLForLambda.
Set your username and password by doing the following:
1. InCredentials settings, leaveMaster username set toadmin.
2. ForMaster password, enter and confirm a password to access your database.
Specify the database name by doing the following:
- Leave all the remaining default options selected and scroll down to theAdditional configuration section.
- Expand this section and enterExampleDB as theInitial database name.
Leave all the remaining default options selected and chooseCreate database.

Create Lambda function and proxy

Tutorial workflow diagram showing you are in the Lambda function step creating an execution role.

You can use the RDS console to create a Lambda function and a proxy in the same VPC as the database.

Note

You can only create these associated resources when your database has completed creation and is inAvailable status.

To create an associated function and proxy

From theDatabases page, check if your database is in theAvailable status. If so, proceed to the next step. Else, wait till your database is available.
Select your database and chooseSet up Lambda connection fromActions.
In theSet up Lambda connection page, chooseCreate new function.
Set theNew Lambda function name toLambdaFunctionWithRDS.
In theRDS Proxy section, select theConnect using RDS Proxy option. Further chooseCreate new proxy.
SelectSet up to complete the proxy and Lambda function creation.

The wizard completes the set up and provides a link to the Lambda console to review your new function. Note the proxy endpoint before switching to the Lambda console.

Create a function execution role

Before you create your Lambda function, you create an execution role to give your function the necessary permissions. For this tutorial, Lambda needs permission to manage the network connection to the VPC containing your database instance and to poll messages from an Amazon SQS queue.

To give your Lambda function the permissions it needs, this tutorial uses IAM managed policies. These are policies that grant permissions for many common use cases and are available in your AWS account. For more information about using managed policies, see Policy best practices.

To create the Lambda execution role

Open theRoles page of the IAM console and chooseCreate role.
For theTrusted entity type, chooseAWS service, and for theUse case, chooseLambda.
ChooseNext.
Add the IAM managed policies by doing the following:
1. Using the policy search box, search forAWSLambdaSQSQueueExecutionRole.
2. In the results list, select the check box next to the role, then chooseClear filters.
3. Using the policy search box, search forAWSLambdaVPCAccessExecutionRole.
4. In the results list, select the check box next to the role, then chooseNext.
For theRole name, enterlambda-vpc-sqs-role, then chooseCreate role.

Later in the tutorial, you need the Amazon Resource Name (ARN) of the execution role you just created.

To find the execution role ARN

Open theRoles page of the IAM console and choose your role (lambda-vpc-sqs-role).
Copy theARN displayed in theSummary section.

Create a Lambda deployment package

Tutorial workflow diagram showing you are in the Lambda function step creating a deployment package

The following example Python code uses thePyMySQL package to open a connection to your database. The first time you invoke your function, it also creates a new table calledCustomer. The table uses the following schema, whereCustID is the primary key:


Customer(CustID, Name)

The function also uses PyMySQL to add records to this table. The function adds records using customer IDs and names specified in messages you will add to your Amazon SQS queue.

The code creates the connection to your database outside of the handler function. Creating the connection in the initialization code allows the connection to be re-used by subsequent invocations of your function and improves performance. In a production application, you can also useprovisioned concurrency to initialize a requested number of database connections. These connections are available as soon as your function is invoked.


import sysimport loggingimport pymysqlimport jsonimport os# rds settingsuser_name = os.environ['USER_NAME']password = os.environ['PASSWORD']rds_proxy_host = os.environ['RDS_PROXY_HOST']db_name = os.environ['DB_NAME']logger = logging.getLogger()logger.setLevel(logging.INFO)# create the database connection outside of the handler to allow connections to be# re-used by subsequent function invocations.try:        conn = pymysql.connect(host=rds_proxy_host, user=user_name, passwd=password, db=db_name, connect_timeout=5)except pymysql.MySQLError as e:    logger.error("ERROR: Unexpected error: Could not connect to MySQL instance.")    logger.error(e)    sys.exit(1)logger.info("SUCCESS: Connection to RDS for MySQL instance succeeded")def lambda_handler(event, context):    """    This function creates a new RDS database table and writes records to it    """    message = event['Records'][0]['body']    data = json.loads(message)    CustID = data['CustID']    Name = data['Name']    item_count = 0    sql_string = f"insert into Customer (CustID, Name) values(%s, %s)"    with conn.cursor() as cur:        cur.execute("create table if not exists Customer ( CustID  int NOT NULL, Name varchar(255) NOT NULL, PRIMARY KEY (CustID))")        cur.execute(sql_string, (CustID, Name))        conn.commit()        cur.execute("select * from Customer")        logger.info("The following items have been added to the database:")        for row in cur:            item_count += 1            logger.info(row)    conn.commit()    return "Added %d items to RDS for MySQL table" %(item_count)

Note

In this example, your database access credentials are stored as environment variables. In production applications, we recommend that you use AWS Secrets Manager as a more secure option. Note that if your Lambda function is in a VPC, to connect to Secrets Manager you need to create a VPC endpoint. See How to connect to Secrets Manager service within a Virtual Private Cloud to learn more.

To include the PyMySQL dependency with your function code, create a .zip deployment package. The following commands work for Linux, macOS, or Unix:

To create a .zip deployment package

Save the example code as a file namedlambda_function.py.
In the same directory in which you created yourlambda_function.py file, create a new directory namedpackage and install the PyMySQL library.
```
mkdir packagepip install --target package pymysql
```
Create a zip file containing your application code and the PyMySQL library. In Linux or MacOS, run the following CLI commands. In Windows, use your preferred zip tool to create thelambda_function.zip file. Yourlambda_function.py source code file and the folders containing your dependencies must be installed at the root of the .zip file.
```
cd packagezip -r ../lambda_function.zip .cd ..zip lambda_function.zip lambda_function.py
```
You can also create your deployment package using a Python virtual environment. See Deploy Python Lambda functions with .zip file archives.

Update the Lambda function

Using the .zip package you just created, you now update your Lambda function using the Lambda console. To enable your function to access your database, you also need to configure environment variables with your access credentials.

To update the Lambda function

Open theFunctions page of the Lambda console and choose your functionLambdaFunctionWithRDS.
In theRuntime settings tab, selectEdit to change theRuntime of the function toPython 3.10.
Change theHandler tolambda_function.lambda_handler.
In theCode tab, chooseUpload from and then.zip file.
Select thelambda_function.zip file you created in the previous stage and chooseSave.

Now configure the function with the execution role you created earlier. This grants the function the permissions it needs to access your database instance and poll an Amazon SQS queue.

To configure the function's execution role

In theFunctions page of the Lambda console, select theConfiguration tab, then choosePermissions.
InExecution role, chooseEdit.
InExisting role, choose your execution role (lambda-vpc-sqs-role).
ChooseSave.

To configure your function's environment variables

In theFunctions page of the Lambda console, select theConfiguration tab, then chooseEnvironment variables.
ChooseEdit.
To add your database access credentials, do the following:
1. ChooseAdd environment variable, then forKey enterUSER_NAME and forValue enteradmin.
2. ChooseAdd environment variable, then forKey enterDB_NAME and forValue enterExampleDB.
3. ChooseAdd environment variable, then forKey enterPASSWORD and forValue enter the password you chose when you created your database.
4. ChooseAdd environment variable, then forKey enterRDS_PROXY_HOST and forValue enter the RDS Proxy endpoint you noted earlier.
5. ChooseSave.

Test your Lambda function in the console

Tutorial workflow diagram showing you are in the Lambda function step testing the function

You can now use the Lambda console to test your function. You create a test event which mimics the data your function will receive when you invoke it using Amazon SQS in the final stage of the tutorial. Your test event contains a JSON object specifying a customer ID and customer name to add to theCustomer table your function creates.

To test the Lambda function

Open theFunctions page of the Lambda console and choose your function.
Choose theTest section.
ChooseCreate new event and entermyTestEvent for the event name.

Copy the following code intoEvent JSON and chooseSave.


{  "Records": [{      "messageId": "059f36b4-87a3-44ab-83d2-661975830a7d",      "receiptHandle": "AQEBwJnKyrHigUMZj6rYigCgxlaS3SLy0a...",      "body": "{\n     \"CustID\": 1021,\n     \"Name\": \"Martha Rivera\"\n}",      "attributes":{        "ApproximateReceiveCount": "1",        "SentTimestamp": "1545082649183",        "SenderId": "AIDAIENQZJOLO23YVJ4VO",        "ApproximateFirstReceiveTimestamp": "1545082649185"      },      "messageAttributes":{},      "md5OfBody": "e4e68fb7bd0e697a0ae8f1bb342846b3",      "eventSource": "aws:sqs",      "eventSourceARN": "arn:aws:sqs:us-west-2:123456789012:my-queue",      "awsRegion": "us-west-2"    }  ]}

ChooseTest.

In theExecution results tab, you should see results similar to the following displayed in theFunction Logs:


[INFO] 2023-02-14T19:31:35.149Z bdd06682-00c7-4d6f-9abb-89f4bbb4a27f The following items have been added to the database:[INFO] 2023-02-14T19:31:35.149Z bdd06682-00c7-4d6f-9abb-89f4bbb4a27f (1021, 'Martha Rivera')

Create an Amazon SQS queue

Tutorial workflow diagram showing you are in the message queue step creating the queue.

You have successfully tested the integration of your Lambda function and Amazon RDS database instance. Now you create the Amazon SQS queue you will use to invoke your Lambda function in the final stage of the tutorial.

To create the Amazon SQS queue (console)

Open the Queues page of the Amazon SQS console and selectCreate queue.
Leave theType asStandard and enterLambdaRDSQueue for the name of your queue.
Leave all the default options selected and chooseCreate queue.

Create an event source mapping to invoke your Lambda function

Tutorial workflow diagram showing you are in the message queue step creating an event source mapping.

Anevent source mapping is a Lambda resource which reads items from a stream or queue and invokes a Lambda function. When you configure an event source mapping, you can specify a batch size so that records from your stream or queue are batched together into a single payload. In this example, you set the batch size to 1 so that your Lambda function is invoked every time you send a message to your queue. You can configure the event source mapping using either the AWS CLI or the Lambda console.

To create an event source mapping (console)

Open theFunctions page of the Lambda console and select your function (LambdaFunctionWithRDS).
In theFunction overview section, chooseAdd trigger.
For the source, selectAmazon SQS, then select the name of your queue (LambdaRDSQueue).
ForBatch size, enter1.
Leave all the other options set to the default values and chooseAdd.

You are now ready to test your complete setup by adding a message to your Amazon SQS queue.

Test and monitor your setup

Tutorial workflow diagram showing you are in the test and monitor step.

To test your complete setup, add messages to your Amazon SQS queue using the console. You then use CloudWatch Logs to confirm that your Lambda function is writing records to your database as expected.

To test and monitor your setup

Open theQueues page of the Amazon SQS console and select your queue (LambdaRDSQueue).
ChooseSend and receive messages and paste the following JSON into theMessage body in theSend message section.
```
{    "CustID": 1054,    "Name": "Richard Roe"}
```
ChooseSend message.
Sending your message to the queue will cause Lambda to invoke your function through your event source mapping. To confirm that Lambda has invoked your function as expected, use CloudWatch Logs to verify that the function has written the customer name and ID to your database table.
Open the Log groups page of the CloudWatch console and select the log group for your function (/aws/lambda/LambdaFunctionWithRDS).

In theLog streams section, choose the most recent log stream.

Your table should contain two customer records, one from each invocation of your function. In the log stream, you should see messages similar to the following:


[INFO] 2023-02-14T19:06:43.873Z 45368126-3eee-47f7-88ca-3086ae6d3a77 The following items have been added to the database:[INFO] 2023-02-14T19:06:43.873Z 45368126-3eee-47f7-88ca-3086ae6d3a77 (1021, 'Martha Rivera')[INFO] 2023-02-14T19:06:43.873Z 45368126-3eee-47f7-88ca-3086ae6d3a77 (1054, 'Richard Roe')

Clean up your resources

You can now delete the resources that you created for this tutorial, unless you want to retain them. By deleting AWS resources that you're no longer using, you prevent unnecessary charges to your AWS account.

To delete the Lambda function

Open the Functions page of the Lambda console.
Select the function that you created.
ChooseActions,Delete.
ChooseDelete.

To delete the execution role

Open theRoles page of the IAM console.
Select the execution role that you created.
ChooseDelete role.
ChooseYes, delete.

To delete the MySQL DB instance

Open theDatabases page of the Amazon RDS console.
Select the database you created.
ChooseActions,Delete.
Clear theCreate final snapshot check box.
Enterdelete me in the text box.
ChooseDelete.

To delete the Amazon SQS queue

Sign in to the AWS Management Console and open the Amazon SQS console athttps://console.aws.amazon.com/sqs/.
Select the queue you created.
ChooseDelete.
Enterdelete in the text box.
ChooseDelete.