Aurora Server Tutorial – Part 2

Objective

To create an Aurora Serverless DB cluster, connect to it using the console, and query it programmatically from an AWS Lambda function using the Data API.

Step 1: Create the Cluster in the RDS Console

1. Navigate to the Amazon RDS console and click "Create database".
2. Select "Amazon Aurora" as the engine.
3. Choose your desired compatibility (e.g., MySQL 5.7 compatible).
4. For "Database features", select "Serverless".
5. Configure Settings:
   • DB cluster identifier: Give your cluster a unique name.
   • Master credentials: Set the master username and password. These will be stored automatically in AWS Secrets Manager.
6. Set Capacity:
   • Define the Minimum and Maximum Aurora Capacity Units (ACUs). This controls your cost and scaling range (e.g., Min 1 ACU, Max 2 ACUs).
   • In "Additional scaling configuration", check the box for "Pause compute capacity after consecutive minutes of inactivity" to enable scaling to zero.
7. Configure Connectivity:
   • Choose your VPC and Subnet Group.
   • Crucially, under "Connectivity", expand "Additional configuration" and check the box for "Data API". This enables a secure HTTP endpoint for querying.
8. Finalize Database Options:
   • Set an Initial database name.
   • Review backup retention and other settings.
   • Uncheck "Enable deletion protection" for easy cleanup in a tutorial environment.
9. Click "Create database".

Step 2: Querying with the Console Query Editor

The Query Editor is a web-based interface in the RDS console for running SQL commands, ideal for quick tests and administration.

1. In the RDS Console, navigate to "Query Editor".
2. Connect to your database:
   • Select your newly created Serverless DB cluster.
   • Enter the database username and password (or select the Secret from Secrets Manager).
   • Enter the database name you specified during creation.
3. Run SQL Commands: Use the editor pane to execute standard SQL.
   • Create a table:

     CREATE TABLE tdojo_associate_courses (
         Courses VARCHAR(255) NOT NULL,
         Description VARCHAR(255),
         PRIMARY KEY (Courses)
     );
     

   • Insert data:

     INSERT INTO tdojo_associate_courses (Courses, Description)
     VALUES ('Solutions Architect Associate', 'AWS Certified SAA-C03 Practice Exams');
     

   • Click "Run" and verify the success message.

Step 3: Querying Programmatically with Lambda & the Data API

The Data API is the recommended way to interact with a Serverless database from other AWS services like Lambda, as it doesn't require a persistent connection and works even when the database is paused.

3.1: Create the Lambda Execution Role

Your Lambda function needs permission to call the Data API and read the database credentials from Secrets Manager.

1. In the IAM Console, find the execution role associated with your Lambda function.
2. Add an inline policy with the following two permissions:
   • RDS Data Service:
     • Service: RDS Data Service
     • Actions: ExecuteStatement
     • Resources: The ARN of your Aurora Serverless cluster.
   • Secrets Manager:
     • Service: Secrets Manager
     • Actions: GetSecretValue
     • Resources: The ARN of the secret containing your DB credentials.

3.2: Write the Lambda Function (Python/Boto3)

1. In the Lambda function code editor, use the boto3 library to call the rds-data service.

2. The core function is execute_statement. The key parameters are:

   • resourceArn: The ARN of your Aurora cluster.
   • secretArn: The ARN of your credentials stored in Secrets Manager.
   • database: The name of the database to connect to.
   • sql: The SQL query you want to run.

   import boto3
   
   def lambda_handler(event, context):
       rds_data_client = boto3.client('rds-data')
   
       # Database details
       DB_CLUSTER_ARN = 'arn:aws:rds:region:account-id:cluster:your-cluster-name'
       DB_SECRET_ARN = 'arn:aws:secretsmanager:region:account-id:secret:your-secret-name'
       DB_NAME = 'your_database_name'
   
       # SQL statement
       sql_statement = "SELECT * FROM tdojo_associate_courses;"
   
       # Execute the statement
       response = rds_data_client.execute_statement(
           resourceArn=DB_CLUSTER_ARN,
           secretArn=DB_SECRET_ARN,
           database=DB_NAME,
           sql=sql_statement
       )
   
       return {
           'statusCode': 200,
           'body': response['records']
       }
   

3. Save and test the function. The output will contain the records from your table.

Key Considerations

• Cold Starts: When a paused database receives its first request, there can be a delay of up to 30 seconds while it resumes. Your application logic should account for this.
• Data API Timeout: The Data API has a default execution timeout of 45 seconds. For potentially longer queries, you can set the continueAfterTimeout parameter to True to allow the query to finish executing in the background.
  Sources