Getting started with the Amazon Aurora Serverless Data API

In this article with screencast videos, we’ll go over the three ways to create an Aurora Serverless Database with Data API. We’ll also cover the four ways to connect to your Data API enabled Aurora Serverless Database.

Let’s quickly go over what Amazon Aurora offers, why a serverless database, and answer, what is the Data API and why should I care?

Amazon Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud, that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases.

Why Amazon Aurora Serverless?
Amazon Aurora Serverless is an on-demand, auto-scaling configuration for Amazon Aurora (MySQL-compatible edition). The database will automatically start up, shut down, and scale capacity up or down based on your application’s needs. It’s a simple, cost-effective option for infrequent, intermittent, or unpredictable workloads.

What is Amazon Aurora Serverless Data API?
The Data API is a new managed API layer on top of an Aurora Serverless database, allowing you to connect directly with your MySQL or PostgreSQL database. It also allows you to execute SQL statements from any application over HTTP without using a MySQL driver, plugin, or need to manage a connection pool or VPC.

Think of the Data API as a fully-managed API for interacting with your relational data. As the Data API is connected to your Serverless Cluster, you also get inherited auto-scaling, available, and backups of your database along with pausing of the database while not in use.

Here’s a video version of this article. Enjoy!

My personal favorite is Option 2 CloudFormation for deployment as you can deploy predictable resources (not missing a step vs manual creation) and you can add additional AWS services and permissions in the CloudFormation template.

Option #1: Amazon RDS Console — Manual
The RDS Console is a quick way to deploy your resources and be done in just a few minutes following these instructions:

Create a new Aurora Serverless Cluster:

1. Launch the Amazon RDS Console.
2. Select Amazon Aurora engine.
3. Select MySQL 5.6-compatible (the only option for serverless) and select Next.
4. Select Serverless for the capacity type and provide a cluster name and master credentials and select Next.
5. Leave all defaults and choose Create database.

Enable the Data API (for an existing Aurora Serverless Cluster):
We are going to enable the Data API for the newly created Aurora serverless cluster manually via the RDS Management Console. Hopefully, this is temporary until we can enable the Data API via a CloudFormation template using the EnableHTTPEndpointAPI parameter once this puppy is out of BETA. In the meantime …

1. Launch the RDS Management Console
2. Select your cluster (even though it says database)
3. Select the Modify button on the upper right
4. Select “Data API” under the Network & Security section
5. Select Continue button
6. Select “Apply immediately” under Scheduling of modifications
7. Select “Modify cluster” radio button

Option #2: CloudFormation — Automated
CloudFormation is the best way to deploy a consistent environment in an automated way. This solution consists of creating a CloudFormation stack based on the CloudFormation template provided in this GitHub repo here.

The template has all the parameters defined for deploying an Aurora Serverless Database in just a few clicks. In addition to creating a serverless database, the template also creates an AWS Lambda function (written in Node.js 8.10) to access your Data API enabled database using the new AWS RDSDataService API. More on the Lambda function later when we connect to a Data API enabled database in Solution 3 below.

The CloudFormation template will provision the following resources:

• Aurora Serverless (MySQL) Cluster
• Aurora Serverless (MySQL) Database
• AWS Lambda function for connecting to your database via Data API using the AWS RDSDataService API.
• IAM Policies for Lambda execution of RDSDataService API, CloudWatch Logs, and read only from AWS Secrets to get database master credentials.

Get Started with CloudFormation
Follow the two (2) steps as outlined in this GitHub repo for deploying the resources in your AWS account.

The first step will deploy the resources via a CloudFormation Stack, and the second step walks through enabling the Data APIfor the created Aurora Serverless Database.

• Step 1: Deploy CloudFormation Stack via GitHub repo here.
• Step 2: Enable Data API following GitHub Step 2 instructions here.

Option #3: AWS CLI or SDK — Scripted
The AWS CLI or AWS SDK can be used to create your resources with just a few lines. Here’s a full AWS CLI statement for creating a new Aurora Serverless Cluster:

Once the Aurora Serverless Cluster has been created, follow Step 2: Enable Data API from the previous section and you now have an Aurora Serverless database with Data API enabled.

Solution #1: Data Query (via RDS Console)

1. Launch Amazon RDS Management Console.
2. Select Query Editor.
3. In the Connect to Database window, select your cluster, provide your master user, master password, and database name (optional).
4. Select Connect to database.

Note: When you provide the cluster credentials the first time, the service will create an AWS Secret automatically for you, then each subsequent access to this cluster via the Query Editor, the service will use this AWS Secret to pull in the master user credentials for this cluster.

Once in the Query Editor, select the Clear button and type:

use MarketPlace;
select * from Customers;

Select Run.

Here’s the result if you have a Customers table with data:

TIP: The Query Editor is a nice tool to have when you need to verify the contents of the table or just perform some quick SQL statements.

Solution #2: AWS CLI
Modify the provided AWS CLI script with your own cluster arn, secrets arn, database name, and the SQS select statement of your choice.

Solution #3: AWS Lambda function
Once you deploy an Aurora Serverless Data API enabled database, you can easily connect and execute any SQL statement against the database with a simple AWS Lambda function using the RDSDataService API.

This function does not need to be inside an Amazon VPC and it doesn’t need to have any MySQL drivers or worry about connection pooling. Just make SQL statements as HTTP request and Aurora Serverless takes care of the rest!

Get Started Using a Lambda function to connect via Data API:

You can use the deploy Option #2 — CloudFormation above that provisions a database, a Lambda function, and fills out the environment variables to get you started OR… you can copy this code and deploy to Lambda directly.

If you do this manually, you’ll need to fill-in the Lambda environment variables to match your Aurora Serverless environment like Cluster Arn, DB name, and AWS Secrets arn.

Here’s the entire code to make SQL statement against your Aurora Serverless Data API enabled database. Again, make sure to provide the environment variables and then pass in something like:

{ "sqlStatement": "SELECT * FROM <YOUR-TABLE-NAME>" }

The Lambda function will then make a connection to your database using the master credentials pulled from AWS Secrets and return a JSON response. It’s that easy!

Solution #4: AWS AppSync
When building a GraphQL API via AWS AppSync, you now have direct access to your serverless Data API enabled database as a datasource, and… AppSync will generate a GraphQL schema for you, based on the existing database table design!

For this solution, we’ll use the new add-graphql-datasourcepluginfor the AWS Amplify CLI that automatically takes your serverless database table(s) and creates/updates a GraphQL schema, generates the appropriate mutations, queries, and subscriptions, and sets your database as a GraphQL DataSource to an existing GraphQL API. Yes, please.

Before we use the plugin, we want to make sure our Aurora serverless database has at least one table. If a database doesn’t exist, let’s create those now by using the RDS Query Editor in the RDS Console and then we’ll switch over to the Amplify CLI + add-graphql-datasource plugin.

Here, we are going to use the RDS Query Editor to create a database and sample table to get us started. If you already have a database and table from previous steps, move onto the [Amplify CLI — Installing the CLI] section below.

• Launch Amazon RDS Management Console
• Select Query Editor
• In the Connect to Database window, select your cluster, provide your master user, master password, and database name (optional).
• Select Connect to database.

In the query editor window, run the following commands:

Create database ‘MarketPlace’ if you haven’t already.

CREATE DATABASE MarketPlace;

Create a new Customers table.

USE MarketPlace;
CREATE TABLE Customers (
  id int(11) NOT NULL PRIMARY KEY,
  name varchar(50) NOT NULL,
  phone varchar(50) NOT NULL,
  email varchar(50) NOT NULL
);

Now that we have a database and a Customers table, we can now use the add-graphql-datasource plugin to generate a GraphQL schema, add the database as a datasource, and add mutations, queries, and subscriptions based on the [Customers] table.

Here’s how the add-graphql-datasource plugin works:

Amplify CLI — Installing the CLI
If you haven’t installed the AWS Amplify CLI before, here’s a quick shortcut. If you have the AWS CLI installed, the Amplify CLI will utilize those credentials and therefore amplify configure is not necessary.

$ npm install -g @aws-amplify/cli
$ amplify configure

Amplify CLI — Init
Launch Mac Terminal in the root of your iOS project folder. Now, we’ll initialize our AWS backend project using the following Amplify command.

$ amplify init

You will be guided through the process of setting up the project.

Amplify CLI — Add API

$ amplify add api

Amplify CLI — Add GraphQL DataSource

$ amplify api add-graphql-datasource

Now, let’s create a new customer in the Customers table using the Queries tool in the AppSync Console.

We can then query the Customers table from the Query Editor in the RDS Console to double check.

Now that we have the schema, mutations, queries, subscriptions, and our serverless Aurora database as a datasource for our GraphQL API, we can start using a mobile or web client with the generated code to interact with this structured data!

There are limits to how much rows and data SQL query select calls can return through the RDS Data API(it’s 1000 rows or 1MB of data). An alternative to using RDS Data API is to use the independent MySQL Python client called PyMySQL, which offers you the flexibility to establish connection, query and obtain larger quantity of operation on MySQL db, in our case Serverless RDS DB. Try our Hands-on Lab for integrating Aurora with Lambda functions using Python and PyMySQL.

The Data API allows any developer to take advantage of structured collections of data by having more control over the database, less management, no drivers or connection pools, and executing SQL statements as HTTP requests is a game-changer.

Master modern tech skills, get certified, and level up your career. Whether you’re starting out or a seasoned pro, you can learn by doing and advance your career in cloud with ACG.