Tutorial: Using a Lambda function to access Amazon RDS in an Amazon VPC

In this tutorial, you use a Lambda function to write data to an Amazon Relational Database Service (Amazon RDS) database. 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.

With Amazon RDS, you can run a managed relational database in the cloud using common database products like Microsoft SQL Server, MySQL, 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 and database instance also scale automatically to meet periods of high demand.

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

1. Launch an Amazon RDS MySQL database instance in your AWS account's default Amazon Virtual Private Cloud (Amazon VPC).

2. Create and test a Lambda function that creates a new table in your database and writes data to it.

3. Create an Amazon SQS queue and configure it to invoke your Lambda function whenever a new message is added.

4. Test the complete set-up 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 Lambda to open a connection to an Amazon RDS database instance

• 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

Sign up for an AWS account

If you do not have an AWS account, complete the following steps to create one.

To sign up for an AWS account

1. Open https://portal.aws.amazon.com/billing/signup.

2. Follow the online instructions.

   Part of the sign-up procedure involves receiving a phone call and entering a verification code on the phone keypad.

   When you sign up for an AWS account, an AWS account root user is created. The root user has access to all AWS services and resources in the account. As a security best practice, assign administrative access to an administrative user, and use only the root user to perform tasks that require root user access.

AWS sends you a confirmation email after the sign-up process is complete. At any time, you can view your current account activity and manage your account by going to https://aws.amazon.com/ and choosing My Account.

Create an administrative user

After you sign up for an AWS account, create an administrative user so that you don't use the root user for everyday tasks.

Secure your AWS account root user

1. Sign in to the AWS Management Console as the account owner by choosing Root user and entering your AWS account email address. On the next page, enter your password.

   For help signing in by using root user, see Signing in as the root user in the AWS Sign-In User Guide.

2. Turn on multi-factor authentication (MFA) for your root user.

   For instructions, see Enable a virtual MFA device for your AWS account root user (console) in the IAM User Guide.

Create an administrative user

• For your daily administrative tasks, grant administrative access to an administrative user in AWS IAM Identity Center (successor to AWS Single Sign-On).

  For instructions, see Getting started in the AWS IAM Identity Center (successor to AWS Single Sign-On) User Guide.

Sign in as the administrative user

• To sign in with your IAM Identity Center user, use the sign-in URL that was sent to your email address when you created the IAM Identity Center user.

  For help signing in using an IAM Identity Center user, see Signing in to the AWS access portal in the AWS Sign-In User Guide.

Create an Amazon RDS database instance

An Amazon RDS database 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 Amazon VPC included in your AWS account. For more information about Amazon VPCs, see the Amazon Virtual Private Cloud User Guide.

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

AWS Management Console

To create a database instance and database (console)

1. Open the Databases page of the Amazon RDS console and choose Create database.

2. Leave the Standard create option selected, then in Engine options, choose MySQL.

3. In Templates, choose Free tier.

4. In Settings, for DB instance identifier, enter MySQLForLambda.

5. Set your username and password by doing the following:

   1. In Credentials settings, leave Master username set to admin

   2. For Master password, enter and confirm a password to access your database.

6. Specify the database name by doing the following:

   1. Leave all the remaining default options selected and scroll down to the Additional configuration pane.

   2. Expand this pane and enter ExampleDB as the Initial database name.

7. Leave all the remaining default options selected and choose Create database.

AWS CLI

To create a database instance and database (CLI)

• To create your database instance and database using the AWS CLI, choose your own password and run the following command. Lambda uses this password to access the database.

  aws rds create-db-instance --db-name ExampleDB --engine MySQL \
  --db-instance-identifier MySQLForLambda \
  --db-instance-class db.t2.micro --allocated-storage 5 --no-publicly-accessible \
  --master-username admin --master-user-password password

For Lambda to connect to your database, you need to know its host address (endpoint) and VPC configuration.

To find the database host address and VPC configuration

1. Open the Databases page of the Amazon RDS console.

2. Choose the database instance you just created (mysqlforlambda), and select the Connectivity and security pane.

3. Record the values of the endpoint, VPC subnets, and VPC security group.

Note

The host endpoint will not be available until your database instance has finished initializing and the status displayed in the console has changed from Creating to Backing up. This can take several minutes.

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 Amazon 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

1. Open the Roles page of the IAM console and choose Create role.

2. For the Trusted entity type, choose AWS Service, and for the Use case, choose Lambda.

3. Choose Next.

4. Add the IAM managed policies by doing the following:

   1. Using the policy search box, search for AWSLambdaVPCAccessExecutionRole.

   2. In the results list, select the check box next to the role, then choose Clear filters.

   3. Using the policy search box, search for AWSLambdaSQSQueueExecutionRole.

   4. In the results list, select the check box next to the role, then choose Next.

5. For the Role name, enter lambda-vpc-sqs-role, then choose Create 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

1. Open the Roles page of the IAM console and choose your role (lambda-vpc-sqs-role)

2. Copy the Role ARN displayed in the Summary section.

Create a Lambda deployment package

The following example Python code uses the PyMySQL package to open a connection to your database. The first time you invoke your function, it also creates a new table called Customer. The table uses the following schema, where CustID 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.

Note that 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 use provisioned concurrency to initialize a requested number of database connections. These connections are available as soon as your function is invoked.

import sys
import logging
import pymysql
import json

# rds settings
rds_host  = "mysqlforlambda.cdipnbm2csku.us-west-2.rds.amazonaws.com"
user_name = "admin"
password = "password"
db_name = "ExampleDB"

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_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()

logger.info("SUCCESS: Connection to RDS 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({CustID}, '{Name}')"

    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)
        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 MySQL table" %(item_count)
    

Note

In this example code, your database name, username, and password are hardcoded into your function. In a production application, you should not hardcode these parameters. Use AWS Secrets Manager to securely store database access credentials.

To include the PyMySQL dependency with your function code, create a .zip deployment package.

To create a .zip deployment package

1. Save the example code as a file named lambda_function.py. Use your own Amazon RDS host endpoint and replace the password in the example code with the password you chose when you created your database instance.

2. In the same directory in which you created your lambda_function.py file, create a new directory named package and install the PyMySQL library.

   mkdir package
   pip install --target package pymysql

3. 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 the lambda_function.zip file.

   cd package
   zip -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.

Create the Lambda function

Using the .zip package you just created, you now create a Lambda function using either the AWS CLI or the Lambda console.

AWS Management Console

To create the function (console)

1. Open the Functions page of the Lambda console and choose Create function.

2. Leave Author from scratch selected, and in Basic information, enter LambdaFunctionWithRDS for the function name.

3. Select Python3.9 as the runtime.

4. Choose Create function.

5. In the Code pane, choose Upload from and then .zip file.

6. Select the lambda_function.zip file you created in the previous stage and choose Save.

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

To configure the function

1. In the Functions page of the Lambda console, select the Configuration tab, then choose Permissions.

2. In the Execution role pane, choose Edit.

3. In Existing role, choose your execution role (lambda-rds-sqs-role) from the dropdown list.

4. Choose Save.

5. Configure the VPC settings by doing the following:

   1. In the Configuration tab, choose VPC, then select Edit.

   2. In VPC, choose your AWS account's default VPC from the dropdown list.

   3. In Subnets, select the checkboxes for the VPC subnets you noted earlier.

   4. In Security groups, select the checkboxes for the VPC security groups you noted earlier, then choose Save.

AWS CLI

To create the function (CLI)

• Run the following command with your own role ARN, VPC subnet IDs and VPC security group values.

  aws lambda create-function --function-name LambdaFunctionWithRDS --runtime python3.9 \
  --zip-file fileb://lambda_function.zip --handler lambda_function.lambda_handler \
  --role arn:aws:iam::111122223333:role/lambda-vpc-sqs-role \
  --vpc-config SubnetIds=subnet-1234567890abcdef0,subnet-abcdef01234567890,\
  subnet-021345abcdef6789,subnet-1234abcdef567890,SecurityGroupIds=sg-1234567890abcdef0

Test your Lambda function in the console

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 the Customer table your function creates.

To test the Lambda function

1. Open the Functions page of the Lambda console and choose your function.

2. Choose the Code tab.

3. In the Code source pane, choose Test and enter myTestEvent for the event name.

4. Copy the following code into Event JSON and choose Save.

   {
     "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"
       }
     ]
   }

5. Choose Test.

In the Execution results tab, you should see results similar to the following displayed in the Function 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

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.

AWS Management Console

To create the Amazon SQS queue (console)

1. Open the Queues page of the Amazon SQS console and select Create queue.

2. Leave the Type as Standard and enter LambdaRDSQueue for the name of your queue.

3. Leave all the default options selected and choose Create queue.

AWS CLI

To create the Amazon SQS queue (CLI)

• Run the following command and record the URL the AWS CLI returns:

  aws sqs create-queue --queue-name LambdaRDSQueue

In the next part of the tutorial, you will need the Amazon Resource Name (ARN) of the queue you just created.

To find the queue ARN

• Using the URL you recorded in the previous step, run the following command and record the ARN the AWS CLI returns:

  aws sqs get-queue-attributes \
  --queue-url https://sqs.us-west-2.amazonaws.com/111122223333/LambdaRDSQueue \
  --attribute-names QueueArn

Create an event source mapping to invoke your Lambda function

An event 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.

AWS Management Console

To create an event source mapping (console)

1. Open the Functions page of the Lambda console and select your function (LambdaFunctionWithRDS).

2. In the Function overview pane, choose Add trigger.

3. For the source, select Amazon SQS, then select the name of your queue (LambdaRDSQueue).

4. For Batch size, enter 1.

5. Leave all the other options set to the default values and choose Add.

AWS CLI

To create an event source mapping (CLI)

• To create an event source mapping using the AWS CLI, run the following command using the ARN for your own Amazon SQS queue:

  aws lambda create-event-source-mapping --function-name LambdaFunctionWithRDS --batch-size 1 \
  --event-source-arn arn:aws:sqs:us-west-2:111122223333:LambdaRDSQueue

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

Test and monitor your setup

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

1. Open the Queues page of the Amazon SQS console and select your queue (LambdaRDSQueue).

2. Choose Send and receive messages and paste the following JSON into the Message body in the Send message pane.

   {
       "CustID": 1054,
       "Name": "Richard Roe"
   }

3. Choose Send 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:

4. Open the Log groups page of the CloudWatch console and select the log group for your function (aws/lambda/LambdaFunctionWithRDS).

5. In the Log streams pane, 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

1. Open the Functions page of the Lambda console.

2. Select the function that you created.

3. Choose Actions, Delete.

4. Type delete in the text input field and choose Delete.

To delete the execution role

1. Open the Roles page of the IAM console.

2. Select the execution role that you created.

3. Choose Delete.

4. Enter the name of the role in the text input field and choose Delete.

To delete the MySQL DB instance

1. Open the Databases page of the Amazon RDS console.

2. Select the database you created.

3. Choose Actions, Delete.

4. Clear the Create final snapshot check box.

5. Enter delete me in the text box.

6. Choose Delete.

To delete the Amazon SQS queue

1. Sign in to the AWS Management Console and open the Amazon SQS console at https://console.aws.amazon.com/sqs/.

2. Select the queue you created.

3. Choose Delete.

4. Enter delete in the text input field.

5. Choose Delete.