AWS AppSync
AWS AppSync Developer Guide

Tutorial: Aurora Serverless

AWS AppSync provides a data source for executing SQL commands against Amazon Aurora Serverless clusters which have been enabled with a Data API. You can use AppSync resolvers to execute SQL statements against the Data API with GraphQL queries, mutations, and subscriptions.

Create cluster

Before adding an RDS data source to AppSync you must first enable a Data API on an Aurora Serverless cluster and configure a secret using AWS Secrets Manager. You can create an Aurora Serverless cluster first with AWS CLI:

aws rds create-db-cluster --db-cluster-identifier http-endpoint-test --master-username USERNAME \ --master-user-password COMPLEX_PASSWORD --engine aurora --engine-mode serverless \ --region us-east-1

This will return an ARN for the cluster.

Create a Secret via the AWS Secrets Manager Console or also via the CLI with an input file such as the following using the USERNAME and COMPLEX_PASSWORD from the previous step:

{ "username": "USERNAME", "password": "COMPLEX_PASSWORD" }

Pass this as a parameter to the AWS CLI:

aws secretsmanager create-secret --name HttpRDSSecret --secret-string file://creds.json --region us-east-1

This will return an ARN for the secret.

Note the ARN of your Aurora Serverless cluster and Secret for later use in the AppSync console when creating a data source.

Enable Data API

You can enable the Data API on your cluster by following the instructions in the RDS documentation. The Data API must be enabled before adding as an AppSync data source.

Create database and table

Once you have enabled your Data API you can ensure it works with the aws rds-data execute-statement command in the AWS CLI. This will ensure that your Aurora Serverless cluster is configured correctly before adding it to your AppSync API. First create a database called TESTDB with the --sql parameter like so:

aws rds-data execute-statement --resource-arn "arn:aws:rds:us-east-1:123456789000:cluster:http-endpoint-test" \ --schema "mysql" --secret-arn "arn:aws:secretsmanager:us-east-1:123456789000:secret:testHttp2-AmNvc1" \ --region us-east-1 --sql "create DATABASE TESTDB"

If this runs without error, add a table with the create table command:

aws rds-data execute-statement --resource-arn "arn:aws:rds:us-east-1:123456789000:cluster:http-endpoint-test" \ --schema "mysql" --secret-arn "arn:aws:secretsmanager:us-east-1:123456789000:secret:testHttp2-AmNvc1" \ --region us-east-1 \ --sql "create table Pets(id varchar(200), type varchar(200), price float)" --database "TESTDB"

If everything has run without issue you can move forward to adding the cluster as a data source in your AppSync API.

GraphQl schema

Now that your Aurora Serverless Data API is up and running with a table, we will create a GraphQL schema and attach resolvers for performing mutations and subscriptions. Create a new API in the AWS AppSync console and navigate to the Schema page, and enter the following:

type Mutation { createPet(input: CreatePetInput!): Pet updatePet(input: UpdatePetInput!): Pet deletePet(input: DeletePetInput!): Pet } input CreatePetInput { type: PetType price: Float! } input UpdatePetInput { id: ID! type: PetType price: Float! } input DeletePetInput { id: ID! } type Pet { id: ID! type: PetType price: Float } enum PetType { dog cat fish bird gecko } type Query { getPet(id: ID!): Pet listPets: [Pet] listPetsByPriceRange(min: Float, max: Float): [Pet] } schema { query: Query mutation: Mutation }

Save your schema and navigate to the Data Sources page and create a new data source. Select Relational database for the Data source type, and provide a friendly name. Use the database name that you created in the last step, as well as the Cluster ARN that you created it in. For the Role you can either have AppSync create a new role or create one with a policy similar to the below:

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "rds-data:DeleteItems", "rds-data:ExecuteSql", "rds-data:ExecuteStatement", "rds-data:GetItems", "rds-data:InsertItems", "rds-data:UpdateItems" ], "Resource": [ "arn:aws:rds:us-east-1:123456789012:cluster:mydbcluster", "arn:aws:rds:us-east-1:123456789012:cluster:mydbcluster:*" ] }, { "Effect": "Allow", "Action": [ "secretsmanager:GetSecretValue" ], "Resource": [ "arn:aws:secretsmanager:us-east-1:123456789012:secret:mysecret", "arn:aws:secretsmanager:us-east-1:123456789012:secret:mysecret:*" ] } ] }

Note there are two Statements in this policy which you are granting role access. The first Resource is your Aurora Serverless cluster and the second is your AWS Secrets Manager ARN. You will need to provide BOTH ARNs in the AppSync data source configuration before clicking Create.

Configuring Resolvers

Now that we have a valid GraphQL schema and an RDS data source, we can attach resolvers to the GraphQL fields on our schema. Our API will offer the following capabilities:

  1. create a pet via the Mutation.createPet field

  2. update a pet via the Mutation.updatePet field

  3. delete a pet via the Mutation.deletePet field

  4. get a single pet via the Query.getPet field

  5. list all pets via the Query.listPets field

  6. list pets in a price range via the Query.listPetsByPriceRange field

Mutation.createPet

From the schema editor in the AWS AppSync console, on the right side choose Attach Resolver for createPet(input: CreatePetInput!): Pet. Choose your RDS data source. In the request mapping template section, add the following template:

#set($id=$utils.autoId()) { "version": "2018-05-29", "statements": [ "insert into Pets VALUES ('$id', '$ctx.args.input.type', $ctx.args.input.price)", "select * from Pets WHERE id = '$id'" ] }

The SQL statements will execute sequentially, based on the order in the statements array. The results will come back in the same order. Since this is a mutation, we run a select statement after the insert to retrieve the committed values in order to populate the GraphQL response mapping template.

In the response mapping template section, add the following template:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[1][0])

Because the statements has two SQL queries, we need to specify the second result in the matrix that comes back from the database with: $utils.rds.toJsonString($ctx.result))[1][0]).

Mutation.updatePet

From the schema editor in the AWS AppSync console, on the right side choose Attach Resolver for updatePet(input: UpdatePetInput!): Pet. Choose your RDS data source. In the request mapping template section, add the following template:

{ "version": "2018-05-29", "statements": [ "update Pets set type='$ctx.args.input.type', price=$ctx.args.input.price WHERE id='$ctx.args.input.id'", "select * from Pets WHERE id = '$ctx.args.input.id'" ] }

In the response mapping template section, add the following template:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[1][0])

Mutation.deletePet

From the schema editor in the AWS AppSync console, on the right side choose Attach Resolver for deletePet(input: DeletePetInput!): Pet. Choose your RDS data source. In the request mapping template section, add the following template:

{ "version": "2018-05-29", "statements": [ "select * from Pets WHERE id='$ctx.args.input.id'", "delete from Pets WHERE id='$ctx.args.input.id'" ] }

In the response mapping template section, add the following template:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[0][0])

Query.getPet

Now that the mutations are created for your schema, we will connect the three queries to showcase how to get individual items, lists, and apply SQL filtering. From the schema editor in the AWS AppSync console, on the right side choose Attach Resolver for getPet(id: ID!): Pet. Choose your RDS data source. In the request mapping template section, add the following template:

{ "version": "2018-05-29", "statements": [ "select * from Pets WHERE id='$ctx.args.id'" ] }

In the response mapping template section, add the following template:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[0][0])

Query.listPets

From the schema editor in the AWS AppSync console, on the right side choose Attach Resolver for getPet(id: ID!): Pet. Choose your RDS data source. In the request mapping template section, add the following template:

{ "version": "2018-05-29", "statements": [ "select * from Pets" ] }

In the response mapping template section, add the following template:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[0])

Query.listPetsByPriceRange

From the schema editor in the AWS AppSync console, on the right side choose Attach Resolver for getPet(id: ID!): Pet. Choose your RDS data source. In the request mapping template section, add the following template:

{ "version": "2018-05-29", "statements": [ "select * from Pets where price > :MIN and price < :MAX" ], "variableMap": { ":MAX": $ctx.args.max, ":MIN": $ctx.args.min } }

In the response mapping template section, add the following template:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[0])

Run mutations

Now that you have configured all of your resolvers with SQL statements and connected your GraphQL API to your Serverless Aurora Data API, you can begin performing mutations and queries. In AWS AppSync console, choose the Queries tab and enter the following to create a Pet:

mutation add { createPet(input : { type:fish, price:10.0 }){ id type price } }

The response should contain the id, type, and price like so:

{ "data": { "createPet": { "id": "c6fedbbe-57ad-4da3-860a-ffe8d039882a", "type": "fish", "price": "10.0" } } }

You can modify this item by running the updatePet mutation:

mutation update { updatePet(input : { id:"c6fedbbe-57ad-4da3-860a-ffe8d039882a", type:bird, price:50.0 }){ id type price } }

Note that we used the id which was returned from the createPet operation earlier. This will be a unique value for your record as the resolver leveraged $util.autoId(). You could delete a record in a similar manner:

mutation { deletePet(input : {id:ID_PLACEHOLDER}){ id type price } }

Create a few records with the first mutation with different values for price and then run some queries.

Run Queries

Still in the Queries tab of the console, use the following statement to list all of the records you've created:

query allpets { listPets { id type price } }

This is nice but let's leverage the SQL WHERE predicate that had where price > :MIN and price < :MAX in our mapping template for Query.listPetsByPriceRange with the following GraphQL query:

query { listPetsByPriceRange(minPrice:1, maxPrice:11) { id type price } }

You should only see records with a price over $1 or less than $10. Finally, you can perform queries to retrieve individual records as follows:

query { getPet(id:ID_PLACEHOLDER){ id type price } }

Input Sanitization

We strongly encourage the developers to sanitize the arguments of GraphQL operations. One way to do this is to provide input specific validation steps in the request mapping template before execution of a SQL statement against your Data API. Let's see how we can modify the request mapping template of the listPetsByPriceRange example. Instead of relying solely on the user input you can do the following:

#set($validMaxPrice = $util.matches("\d{1,3}[,\\.]?(\\d{1,2})?",$ctx.args.maxPrice)) #set($validMinPrice = $util.matches("\d{1,3}[,\\.]?(\\d{1,2})?",$ctx.args.minPrice)) #if (!$validMaxPrice || !$validMinPrice) $util.error("Provided price input is not valid.") #end { "version": "2018-05-29", "statements": [ "select * from Pets where price > :MIN and price < :MAX" ], "variableMap": { ":MAX": $ctx.args.maxPrice, ":MIN": $ctx.args.minPrice } }

Another way to protect against rogue input when executing resolvers against your Data API is to use prepared statements together with stored procedure and parameterized inputs. For example, in the resolver for listPets define the following procedure that executes the select as a prepared statement:

CREATE PROCEDURE listPets (IN type_param VARCHAR(200)) BEGIN PREPARE stmt FROM 'SELECT * FROM Pets where type=?'; SET @type = type_param; EXECUTE stmt USING @type; DEALLOCATE PREPARE stmt; END

This can be created in your Aurora Serverless Instance using the following execute sql command:

aws rds-data execute-statement --resource-arn "arn:aws:rds:us-east-1:xxxxxxxxxxxx:cluster:http-endpoint-test" \ --schema "mysql" --secret-arn "arn:aws:secretsmanager:us-east-1:xxxxxxxxxxxx:secret:httpendpoint-xxxxxx" \ --region us-east-1 \ --sql "CREATE PROCEDURE listPets (IN type_param VARCHAR(200)) BEGIN PREPARE stmt FROM 'SELECT * FROM Pets where type=?'; SET @type = type_param; EXECUTE stmt USING @type; DEALLOCATE PREPARE stmt; END" --database "DB_NAME"

The resulting resolver code for listPets simplified since we now just simply call the stored procedure:

{ "version": "2018-05-29", "statements": [ "CALL listPets('$ctx.args.type')" ] }