Querying Aurora serverless database remotely using Lambda - part 2

This post is part of a series

• Part 1 - VPC
• Part 2 - Aurora cluster
• Part 3 - Lambda function

In the previous part, we've set up the base layer to deploy our resources. At this point we can create the database cluster.

Aurora DB Cluster

Assuming we have our VPC ready with at least two subnets to comply with high availability best practices, let's create our cluster, I've put together a CloudFormation template that includes:

• AWS::EC2::SecurityGroup contains inbound traffic rule to allow port 3306
• AWS::RDS::DBSubnetGroup contains a group of subnets to deploy the cluster
• AWS::EC2::DBCluster contains all the parameters to create the database cluster

Here is the full template, the only required parameters are VpcId and SubnetIds, but feel free to override any of the database cluster parameters such as database name, user name, password, etc. The template outputs the IDs corresponding to newly created resources such as the database cluster DNS endpoint, port and the security group.

Description: Template to create a serverless aurora mysql cluster

Parameters: 
  DbClusterDatabaseName: 
    Type: String
    Default: dbtest
  DbClusterIdentifier: 
    Type: String
    Default: serverless-mysql-aurora
  DbClusterParameterGroup: 
    Type: String
    Default: default.aurora5.6
  DbClusterMasterUsername: 
    Type: String
    Default: master
  DbClusterMasterPassword: 
    Type: String
    Default: Aurora.2019
  DbClusterPort: 
    Type: Number
    Default: 3306
  VpcId: 
    Type: String
  SubnetIds: 
    Type: CommaDelimitedList

Resources:
  DbClusterSg:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow MySQL port to client host
      VpcId:
        Ref: VpcId
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: !Ref DbClusterPort
          ToPort: !Ref DbClusterPort
          CidrIp: 0.0.0.0/0

  DbSubnetGroup: 
    Type: "AWS::RDS::DBSubnetGroup"
    Properties: 
      DBSubnetGroupDescription: "aurora subnets"
      SubnetIds: !Ref SubnetIds

  AuroraMysqlCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      DatabaseName:
        Ref: DbClusterDatabaseName
      DBClusterParameterGroupName:
        Ref: DbClusterParameterGroup
      DBSubnetGroupName:
        Ref: DbSubnetGroup
      Engine: aurora
      EngineMode: serverless
      MasterUsername:
        Ref: DbClusterMasterUsername
      MasterUserPassword:
        Ref: DbClusterMasterPassword
      ScalingConfiguration:
        AutoPause: true
        MinCapacity: 2
        MaxCapacity: 4
        SecondsUntilAutoPause: 1800
      VpcSecurityGroupIds:
        - !Ref DbClusterSg
        
Outputs:
  DbClusterEndpointAddress:
    Value: !GetAtt AuroraMysqlCluster.Endpoint.Address
  DbClusterEndpointPort:
    Value: !GetAtt AuroraMysqlCluster.Endpoint.Port
  DbClusterSgId:
    Value: !Ref DbClusterSg

To deploy this stack we use the following command where we pass the parameters specific to our VPC (VpcId and SubnetIds).

$ aws cloudformation deploy --stack-name db-stack \
--template-file aurora_cluster_template.yml \
--parameter-overrides VpcId=vpc-0b442e5d98841996c SubnetIds=subnet-013d0bbb3eca284a2,subnet-00c67cfed3ab0a791

Waiting for changeset to be created..
Waiting for stack create/update to complete
Successfully created/updated stack - db-stack

Let's get the outputs as we'll need this information later. We have the cluster endpoint DNS name and the port as per our definition.

$ aws cloudformation describe-stacks --stack-name db-stack --query Stacks[*].Outputs
[
    [
        {
            "OutputKey": "DbClusterEndpointAddress",
            "OutputValue": "db-stack-auroramysqlcluster-1d1udg4ringe4.cluster-cnfxlauucwwi.eu-west-1.rds.amazonaws.com"
        },
        {
            "OutputKey": "DbClusterSgId",
            "OutputValue": "sg-072bbf2078caa0f46"
        },
        {
            "OutputKey": "DbClusterEndpointPort",
            "OutputValue": "3306"
        }
    ]
]

In the next part, we'll create the Lambda function to query this database remotely.