In the first part of this tutorial, we gave a walkthrough on Aurora Serverless and its use case. You can read the article here. For this tutorial, we will do some hands-on training and create an Aurora Serverless database.
1. Open the AWS console and go to RDS. Click the button “Create database”.
2. Choose Amazon Aurora. You can either choose MySQL or PostgreSQL compatibility. In this tutorial, we will use MySQL compatibility. As of this writing, there are two versions of MySQL that Aurora serverless supports. Under the Database Features, select serverless.
3. Under settings, input your credentials. Do not forget your master username and master password. You will be needing this when you connect to your database in the console.
4. Set a minimum and maximum ACU. In this tutorial, we will set the min to 1 ACU and max to 2 ACU.
There are two options under Additional scaling configuration, the first one gives you the ability to force capacity scaling when there is a failure in finding a scaling point. The second one enables you to pause or scale down to zero at a defined period of inactivity.
5. Next, configure your VPC, Subnet Group, and VPC security group. Don’t forget to check the Data API. Enabling this will allow you to perform SQL queries through a secure HTTP endpoint. This means we can run queries with AWS Lambda which we will do later.
6. Set your database name. We will use the default DB cluster parameter group. The backup retention period is set to 1 day by default but you can change this up to 35 days. We will use the default key but you are free to use your own. Note that Deletion protection is enabled by default in AWS Console. However, we don’t need to enable this in this tutorial.
After configuring, click the Create database button.
Now that we have set the required configurations for our Aurora Serverless database, it is time to see it in action!
In this tutorial, we will be creating a simple table in the database. It has two columns. The primary key is Courses which is a list of courses for the AWS associate level exams that Tutorials Dojo offers. The second column is the Description that describes each course.
1. We will create the table using the built-in query editor on the console. Go to the RDS Console and click Query Editor.
2. Select your Database cluster and enter your database username and password. Next, enter the name of your database. Refer to step number 6 in creating an Aurora Serverless database in case you forgot your database name. Then, click the Connect to database button.
3. Next, create a table named tdojo_associate_courses. Enter the following SQL Statements. Here is the link to the following SQL statements: https://github.com/carlo-acebedo/aurora-serverless-SQL/blob/master/sql.txt.
Click the Run button.
You should see the output with a Success status after running the SQL statements
Great! Now that we have successfully created a table, we will query it using the Data API, which uses a secure HTTP endpoint to connect to our database. We will be using AWS Lambda to perform a query to our database.
First, let’s go ahead and create a lambda function.
1. Enter a function name, and select Python 3.7 as the runtime. Click Create Function.
2. We need to set the proper roles for our Lambda function to successfully execute the code. On the Lambda UI, scroll down and edit the basic settings. Click the link enclosed with the red-bordered box. This will open a tab for the IAM console.
3. On the IAM console, click Add inline policy
4. Next, we set a role for RDS API. Follow the following configuration, click Review policy, and create the policy on the subsequent window.
5. Next, we set a role for Secrets Manager. Follow the following configuration, click Review policy, and create the policy on the subsequent window.
6. Next, let us code our lambda function. The code is available here:
There are three important parameters here:
- Database refers to the name of your database.
- secretArn refers to the secretArn which you can find on the Secrets Manager.
- resourceArn refers to your database Arn which you can find on the RDS console.
The “sql” parameter refers to the SQL statement you want to execute.
This function will execute an SQL statement that will query the table name “tdojo_associate_courses” which is the table that we created a few steps back.
Here is a detailed explanation of the rest of the parameters:
7. Save and run the function. If successful, you will see the output that contains the items we created in our table. Good job following along! We were able to query a relational database without a persistent connection to the DB cluster.
- You might encounter this message when you start sending requests to the database:
This is normal. This will occur when your database initializes from a pause state/ 0 ACU. Currently, it has a delay of around 30 seconds before you can start querying to the database. To avoid this, disable the pause compute capacity.
- Data API calls a time out and gets terminated by default if Aurora has not finished processing for 45 seconds. Use the continueAfterTimeout parameter to continue running the SQL statement after a call time out.
- Aurora Serverless has a slower failover than Aurora Provisioned.