Enabling Aurora Serverless Data API and connecting from EC2 (AWS CLI) and Lambda (Boto3)
The following pages cover the basics of Aurora Serverless.
One of the features of Aurora Serverless v1 is the Data API.
By using the Data API for Aurora Serverless v1, you can work with a web-services interface to your Aurora Serverless v1 DB cluster. The Data API doesn’t require a persistent connection to the DB cluster. Instead, it provides a secure HTTP endpoint and integration with AWS SDKs.
Using the Data API for Aurora Serverless v1
This page uses the Data API to connect to Aurora Serverless from EC2 instances and Lambda functions.
Environment
Create three subnets within the VPC.
All of them are private subnets with no access to the Internet.
Create Aurora Serverless and associate it with two subnets with different AZs.
Specify the latest version of the MySQL type.
Enable Data API.
Create an EC2 instance.
This is the client that will connect to Aurora Serverless.
Specify the latest version of Amazon Linux 2 as the OS.
Create an endpoint for SSM.
This is for remote access to the EC2 instance using SSM Session Manager.
Also create an endpoint for RDS Data.
Use the Data API to connect to Aurora Serverless from your EC2 instance.
Create a Lambda function outside the VPC.
This is also a client that connects to Aurora Serverless.
Select Python 3.8 as the runtime environment and activate the Function URL.
CloudFormation template files
Build the above configuration with CloudFormation.
The CloudFormation templates are located at the following URL
Explanation of key points of the template files
This page covers how to enable the Data API of Aurora Serverless and connect to it from EC2/Lambda.
For basic information about Aurora Serverless, please refer to the page introduced at the beginning of this document.
Security Group
Resources:
InstanceSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
GroupName: !Sub "${Prefix}-InstanceSecurityGroup"
GroupDescription: Deny All.
VpcId: !Ref VPC
DBSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
GroupName: !Sub "${Prefix}-DBSecurityGroup"
GroupDescription: DBSecurityGroup.
VpcId: !Ref VPC
# SecurityGroupIngress:
# - IpProtocol: tcp
# FromPort: !Ref MySQLPort
# ToPort: !Ref MySQLPort
# SourceSecurityGroupId: !Ref InstanceSecurityGroup
EndpointSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
GroupName: !Sub "${Prefix}-EndpointSecurityGroup"
GroupDescription: Allow HTTPS from InstanceSecurityGroup.
VpcId: !Ref VPC
SecurityGroupIngress:
- IpProtocol: tcp
FromPort: !Ref HTTPSPort
ToPort: !Ref HTTPSPort
SourceSecurityGroupId: !Ref InstanceSecurityGroup
Code language: YAML (yaml)Define three security groups.
The first is for EC2 instances.
Similar to the page introduced at the beginning of this article, no inbound communication to the instance will occur in this configuration.
Therefore, nothing will allow inbound communication.
The second is for Aurora Serverless.
This one also does not allow any inbound communication.
In this case, since the Data API is used to connect to Aurora Serverless, there is no need to release the MySQL port.
Also, the Data API connection is made via HTTPS, which is an outbound communication from Aurora Serverless to the Data API endpoint, so there is no need to allow this in the security group either.
The third is for the VPC endpoint.
In this case, we will create a total of four endpoints, one for SSM and one for RDS Data.
All of them will have HTTPS communication from the instances, so the content will reflect that.
Aurora Serverless
Resources:
DBCluster:
Type: AWS::RDS::DBCluster
Properties:
DatabaseName: !Ref DBName
DBClusterIdentifier: !Ref DBClusterIdentifier
DBSubnetGroupName: !Ref DBSubnetGroup
EnableHttpEndpoint: true
Engine: !Ref DBEngine
EngineMode: serverless
EngineVersion: !Ref DBEngineVersion
MasterUsername: !Ref DBMasterUsername
MasterUserPassword: !Ref DBMasterUserPassword
StorageEncrypted: true
VpcSecurityGroupIds:
- !Ref DBSecurityGroup
Code language: YAML (yaml)The key point is the EnableHttpEndpoint property.
To enable Data API in Aurora Serverless, set this property to “true”.
This setting will provide an HTTP endpoint for the Data API.
No other special settings are required.
Secrets Manager
Resources:
Secret:
Type: AWS::SecretsManager::Secret
Properties:
Name: !Sub "${Prefix}-Secret"
SecretString: !Sub '{"username":"${DBMasterUsername}","password":"${DBMasterUserPassword}"}'
Code language: YAML (yaml)To connect to Aurora Serverless using the Data API, you must use Sercrets Manager.
Users don’t need to pass credentials with calls to the Data API, because the Data API uses database credentials stored in AWS Secrets Manager.
Using the Data API for Aurora Serverless v1
Specifically, the secret is created from information about the master user and password that were set when the Aurora DB cluster was defined.
Define a secret string in JSON format in the SecretString property.
The two pieces of information are set as the value of ussername and password respectively.
VPC Endpoints
Resources:
RDSDataEndpoint:
Type: AWS::EC2::VPCEndpoint
Properties:
PrivateDnsEnabled: true
SecurityGroupIds:
- !Ref EndpointSecurityGroup
ServiceName: !Sub "com.amazonaws.${AWS::Region}.rds-data"
SubnetIds:
- !Ref PrivateSubnet1
VpcEndpointType: Interface
VpcId: !Ref VPC
#S3Endpoint:
# Type: AWS::EC2::VPCEndpoint
# Properties:
# RouteTableIds:
# - !Ref PrivateRouteTable
# ServiceName: !Sub "com.amazonaws.${AWS::Region}.s3"
# VpcId: !Ref VPC
Code language: YAML (yaml)To connect to Aurora Serverless using the Data API, communication is made to the HTTP endpoint for the Data API.
EC2 instances in private subnets must create a VPC endpoint for RDS Data in order to communicate with the same endpoint.
For more information, please refer to the following official AWS page.
Unlike the page introduced at the beginning of this article, the VPC endpoint for S3 is not created.
This endpoint was used by an instance on a private subnet to access the yum repository built on S3 and install the MySQL client package.
This time, however, it will communicate with Aurora Serverless via HTTPS instead of MySQL communication.
Therefore, the client package is no longer needed, and this endpoint is also unnecessary.
EC2 Instance
Resources:
Instance:
Type: AWS::EC2::Instance
Properties:
IamInstanceProfile: !Ref InstanceProfile
ImageId: !Ref ImageId
InstanceType: !Ref InstanceType
NetworkInterfaces:
- DeviceIndex: 0
SubnetId: !Ref PrivateSubnet
GroupSet:
- !Ref InstanceSecurityGroup
#UserData: !Base64 |
# #!/bin/bash -xe
# yum update -y
# yum install -y mariadb
Code language: YAML (yaml)This is an EC2 instance as a client connecting to Aurora Serverless using the Data API.
As mentioned earlier, we do not need a MySQL client, so we will not perform package installation using user data.
Lambda Function
Resources:
Function:
Type: AWS::Lambda::Function
Properties:
Environment:
Variables:
DBCLUSTER_ARN: !Ref DBClusterArn
DBNAME: !Ref DBName
DBTABLE: !Ref DBTableName
REGION: !Ref AWS::Region
SECRET_ARN: !Ref SecretArn
Code:
ZipFile: |
import boto3
import json
import os
dbcluster_arn = os.environ['DBCLUSTER_ARN']
dbname = os.environ['DBNAME']
dbtable = os.environ['DBTABLE']
region = os.environ['REGION']
secret_arn = os.environ['SECRET_ARN']
sql = 'select * from {table};'.format(table=dbtable)
client = boto3.client('rds-data', region_name=region)
def lambda_handler(event, context):
response = client.execute_statement(
database=dbname,
resourceArn=dbcluster_arn,
schema='mysql',
secretArn=secret_arn,
sql=sql
)
return {
'statusCode': 200,
'body': json.dumps(response, indent=2)
}
FunctionName: !Sub "${Prefix}-function"
Handler: !Ref Handler
Runtime: !Ref Runtime
Role: !GetAtt FunctionRole.Arn
Code language: YAML (yaml)A Lambda function as a client connecting to Aurora Serverless using the Data API.
Define the code to be executed in inline notation.
For more information, please check the following page
In the Environment property, set parameters such as DB cluster, Secrets Manager secret, etc. as environment variables.
In the code, access the environment variables in os.envrion.
Create a client object for RDS Data using Boto3.
Connect to Aurora Serverless by executing execute_statement.
Pass the aforementioned parameters as arguments.
The SQL to be executed is to retrieve a table in a specific table (Pets).
The result of SQL execution is returned as the result of function execution.
Enable Function URL.
This function allows Lambda functions to be executed from a specific URL.
For more information on Function URL, please refer to the following page
IAM Role
Resources:
InstanceRole:
Type: AWS::IAM::Role
Properties:
AssumeRolePolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Action: sts:AssumeRole
Principal:
Service:
- ec2.amazonaws.com
ManagedPolicyArns:
- arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
FunctionRole:
Type: AWS::IAM::Role
Properties:
AssumeRolePolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Action: sts:AssumeRole
Principal:
Service:
- lambda.amazonaws.com
ManagedPolicyArns:
- arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
AuroraServerlessDataAPIPolicy:
Type: AWS::IAM::Policy
Properties:
PolicyName: !Sub "${Prefix}-AuroraServerlessDataAPIPolicy"
PolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Action:
- secretsmanager:GetSecretValue
Resource:
- !Ref SecretArn
- Effect: Allow
Action:
- rds-data:BatchExecuteStatement
- rds-data:BeginTransaction
- rds-data:CommitTransaction
- rds-data:ExecuteStatement
- rds-data:RollbackTransaction
Resource:
- !Ref DBClusterArn
Roles:
- !Ref FunctionRole
- !Ref InstanceRole
Code language: YAML (yaml)Create IAM roles for the EC2 instance and the Lambda function.
The permissions common to both are defined as IAM policies.
There are two points.
The first is access to the Secrets Manager, which gives access to the aforementioned secrets.
The second point is the permission to use the Data API, which is the minimum permission to execute SQL against the Aurora Server DB cluster mentioned above
This time, I referred to the following page for setting up.
Architecting
Using CloudFormation, we will build this environment and check the actual behavior.
Create CloudFormation stacks and check resources in stacks
Create CloudFormation stacks.
For information on how to create stacks and check each stack, please refer to the following page
After checking the resources for each stack, the following is the information for the main resources created in this case
- EC2 instance: i-02b21e07f33a5074c
- Lambda function: fa-059-function
- Function URL for Lambda function: https://7vgt4c3bmf7n3xp7htprqdhi5u0tfdms.lambda-url.ap-northeast-1.on.aws/
- Database name to be created in Aurora Serverless: testdb
- Table name to be created in Aurora Serverless database: Pets
- ARN of Aurora Serverless DB cluster: arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster
- ARN for Secrets Manager secret: arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs
The creation status of Aurora Serverless is also checked from the AWS Management Console.
Aurora Serverless has been successfully created.
And we can also confirm that Data API has been activated.
Checking Action
EC2 Instance
Now that everything is ready, access the EC2 instance.
Use SSM Session Manager to access the instance.
% aws ssm start-session --target i-02b21e07f33a5074c
Starting session with SessionId: root-0815f8b32141bb9b4
sh-4.2$
Code language: Bash (bash)For more information on SSM Session Manager, please refer to the following page
Connect to Aurora Serverless from the AWS CLI using the Data API.
First, create a Pets table in the Aurora Serverless database.
sh-4.2$ aws rds-data execute-statement \
--resource-arn "arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster" \
--schema "mysql" \
--secret-arn "arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs" \
--region ap-northeast-1 \
--sql "create table Pets(id varchar(200), type varchar(200), price float)" \
--database "testdb"
{
"numberOfRecordsUpdated": 0,
"generatedFields": []
}
Code language: Bash (bash)Successfully executed.
Next, add records to the created table.
sh-4.2$ aws rds-data execute-statement \
--resource-arn "arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster" \
--schema "mysql" \
--secret-arn "arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs" \
--region ap-northeast-1 \
--sql "insert into Pets values (111, 'dog', 123)" \
--database "testdb"
{
"numberOfRecordsUpdated": 1,
"generatedFields": []
}
Code language: YAML (yaml)This also executed successfully.
Check the added records.
sh-4.2$ aws rds-data execute-statement \
--resource-arn "arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster" \
--schema "mysql" \
--secret-arn "arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs" \
--region ap-northeast-1 \
--sql "select * from Pets" \
--database "testdb"
{
"records": [
[
{
"stringValue": "111"
},
{
"stringValue": "dog"
},
{
"doubleValue": 123.0
}
]
],
"numberOfRecordsUpdated": 0
}Code language: JavaScript (javascript)We were able to retrieve the saved records.
As described above, we were able to connect to Aurora Serverless from the AWS CLI using the Data API on an EC2 instance.
Lambda Function
The function is executed by accessing the Function URL.
The following is the execution result.
This function was also executed successfully.
As described above, the Lambda function was able to connect to Aurora Serverless using the Data API.
Summary
We have confirmed how to enable the Data API in Aurora Serverless.
We have also confirmed how to use the Data API from EC2 instances and Lambda functions.