Using Aurora Serverless with AWS AppSync
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:
-
create a pet via the Mutation.createPet field
-
update a pet via the Mutation.updatePet field
-
delete a pet via the Mutation.deletePet field
-
get a single pet via the Query.getPet field
-
list all pets via the Query.listPets field
-
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, :TYPE, :PRICE)", "select * from Pets WHERE id = :ID" ], "variableMap": { ":ID": "$ctx.args.input.id", ":TYPE": $util.toJson($ctx.args.input.type), ":PRICE": $util.toJson($ctx.args.input.price) } }
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": [ $util.toJson("update Pets set type=:TYPE, price=:PRICE WHERE id=:ID"), $util.toJson("select * from Pets WHERE id = :ID") ], "variableMap": { ":ID": "$ctx.args.input.id", ":TYPE": $util.toJson($ctx.args.input.type), ":PRICE": $util.toJson($ctx.args.input.price) } }
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": [ $util.toJson("select * from Pets WHERE id=:ID"), $util.toJson("delete from Pets WHERE id=:ID") ], "variableMap": { ":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": [ $util.toJson("select * from Pets WHERE id=:ID") ], "variableMap": { ":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": $util.toJson($ctx.args.max), ":MIN": $util.toJson($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: ID_PLACEHOLDER, 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 delete { 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 petsByPriceRange { listPetsByPriceRange(min:1, max: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 onePet { getPet(id:ID_PLACEHOLDER){ id type price } }
Input Sanitization
We recommend that developers use variableMap
for protection against SQL injection attacks. If
variable maps are not used, developers are responsible for sanitizing the arguments of their 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": $util.toJson($ctx.args.maxPrice), ":MIN": $util.toJson($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 --database "DB_NAME" \ --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"
The resulting resolver code for listPets is simplified since we now simply call the stored procedure. At a minimum, any string input should have single quotes escaped.
#set ($validType = $util.isString($ctx.args.type) && !$util.isNullOrBlank($ctx.args.type)) #if (!$validType) $util.error("Input for 'type' is not valid.", "ValidationError") #end { "version": "2018-05-29", "statements": [ "CALL listPets(:type)" ] "variableMap": { ":type": $util.toJson($ctx.args.type.replace("'", "''")) } }
Escaping strings
Single quotes represent the start and end of string literals in an SQL statement,
eg. 'some string value'
. To allow string values with one or more single
quote characters ( '
) to be used within a string, each must be replaced
with two single quotes (''
). For example, if the input string is
Nadia's dog
, you would escape it for the SQL statement like
update Pets set type='Nadia''s dog' WHERE id='1'