The database acts as a single source of truth in most modern applications. Backend applications typically expose APIs for CRUD operations to query and mutate values in the underlying data store. Based on the authentication scope of the user, the users should be allowed to fetch/update/create/delete entities in the database. With strong Role-based access control(RBAC), CRUD operations can be resolved directly off of the database.
AppSync is a managed service from AWS that exposes a GraphQL interface to interact with the API. It collects data from and resolves queries and mutations from multiple data sources. An Aurora Serverless Cluster can be used as a data source. In this tutorial, I will take you through how to resolve mutations directly off of the Aurora in AppSync.
AppSync uses Apache VTL resolvers to transform GraphQL requests from the client into requests to the data source. It provides support for the reverse as well. It translates the response from the data source into a GraphQL response.
For example
mutation CreateNote {
createNote(
input: {
note: "Mow the lawn"
listId: 13
deadline: "2021-01-01T00:00:00.000Z"
}
) {
id
note
listId
deadline
done
}
}
The above request needs to be transformed into
INSERT INTO notes (note, list_id, deadline) VALUES
('Mow the lawn', '133', '2021-01-01T00:00:00.000Z');
This tutorial assumes that you have a good understanding of
In this tutorial, I will take you through how to
- resolve create mutations directly off of the database and return the newly created entity.
- resolve update mutations directly off of the database and return the updated entity.
- resolve delete mutations directly off of the database and return the deleted entity. (We will soft delete records from the database i.e "deleted_at = NOW()")
Starter Project
Please clone the following repository: https://github.com/wednesday-solutions/appsync-rds-todo-starter. This project
- consists of a CD pipeline that will create the required infrastructure (including the PostgreSQL DB) and deploy your AWS AppSync application using the serverless framework
- has queries to fetch users, notes, and lists.
- uses AWS Lambdas as a data source to resolve queries
Through the course of this tutorial, we will add support for mutations to this application.
Setup the database
Run the setup-local.sh script which will run the database migrations
./scripts/setup-local.sh
Adding PostgreSQL Aurora Serverless as an AppSync data source
Step 1
Create an rds
folder with a datasources.yml
file in the resources
folder
mkdir -p resources/rds
touch resources/rds/datasources.yml
Step 2
Copy the snippet below in the newly created datasources.yml
- type: RELATIONAL_DATABASE
name: POSTGRES_RDS
description: "Aurora Serverless Database for ToDo Application"
config:
dbClusterIdentifier: { Ref: RDSCluster }
databaseName: appsync_rds_todo_${env:STAGE}
awsSecretStoreArn: !Ref RDSInstanceSecret
serviceRoleArn: { Fn::GetAtt: [AppSyncRDSServiceRole, Arn] }
region: ${env:REGION}
The type of the data source is
RELATIONAL_DATABASE
and its name isPOSTGRES_RDS
The
awsSecretStoreArn
in theconfig
contains the credentials required for AppSync to access the database.
Step 3
Copy the snippet below in the serverless.yml
custom:
...
appSync:
...
dataSources:
...
- ${file(./resources/rds/datasources.yml)}
Step 4
Run yarn start-offline
. It should execute without any errors.
Commit the progress so far.
git add .
git commit -m 'Add Postgres as a data source.'
Exposing create mutations by adding them to the schema.graphql
Step 1
Add the mutations and types for create
# create mutation inputs
input CreateUserRequest {
name: String!
userRef: String!
}
input CreateNoteRequest {
note: String!
listId: ID!
deadline: AWSDateTime!
done: Boolean
}
input CreateListRequest {
name: String!
userId: Int!
}
# mutation responses
type MutatedList {
id: ID!
name: String!
userId: Int!
}
type MutatedUser {
id: ID!
name: String!
userRef: String!
}
type MutatedNote {
id: ID!
note: String!
listId: ID!
deadline: AWSDateTime!
done: Boolean!
}
type Mutation {
# create mutations
createNote(input: CreateNoteRequest!): MutatedNote!
createList(input: CreateListRequest!): MutatedList!
createUser(input: CreateUserRequest!): MutatedUser!
}
Step 2
Go to GraphiQL or any other GraphQL IDE.
For macOS, you can download it from here: https://www.electronjs.org/apps/graphiql
In the Docs
pane on the right, you will be able to see the newly added mutations
as shown below
Click on createNote
Click on MutatedNote
Go back and click on CreateNoteRequest!
Similarly, you can go through all the other newly created mutations
Running the mutation
mutation CreateNote {
createNote(
input: {
note: "Mow the lawn"
listId: 13
deadline: "2021-01-01T00:00:00.000Z"
}
) {
id
note
listId
deadline
done
}
}
Since the data source and resolvers for the mutations have not been wired in, invoking the mutation will result in an error
{
"data": null,
"errors": [
{
"message": "Cannot return null for non-nullable field Mutation.createNote.",
"locations": [
{
"line": 2,
"column": 3
}
],
"path": [
"createNote"
]
}
]
}
Commit the progress so far.
git add .
git commit -m 'Add mutations and types in the schema.graphql'
Add resolvers for create mutations
Step 1
Create a folder for mutation resolvers.
mkdir resolvers/mutations
Step 2
Create a new file for the createList
request resolver.
touch resolvers/mutations/createList.req.vtl
Copy the snippet below
#set( $cols = [] )
#set( $vals = [] )
## 1
#foreach( $entry in $ctx.args.input.keySet() )
## 2
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
#set( $discard = $cols.add("$toSnake") )
## 3
#if( $util.isBoolean($ctx.args.input[$entry]) )
#if( $ctx.args.input[$entry] )
#set( $discard = $vals.add("1") )
#else
#set( $discard = $vals.add("0") )
#end
#else
#set( $discard = $vals.add("'$ctx.args.input[$entry]'") )
#end
#end
## 4
#set( $valStr = $vals.toString().replace("[","(").replace("]",")") )
#set( $colStr = $cols.toString().replace("[","(").replace("]",")") )
## 5
#if ( $valStr.substring(0, 1) != '(' )
#set( $valStr = "($valStr)" )
#end
#if ( $colStr.substring(0, 1) != '(' )
#set( $colStr = "($colStr)" )
#end
{
"version": "2018-05-29",
"statements": ["INSERT INTO lists $colStr VALUES $valStr",
"SELECT * FROM lists ORDER BY id DESC LIMIT 1"]
}
We need to convert the incoming GraphQL into SQL statements to
- create a record in the database
- return the created record
According to convention, the GraphQL request is in camelCase. However, the database columns are snake_case.
- Iterate over the keys in the args.input
- Convert each key from camelCase to snake_case
- Boolean values are stored
SMALLINT
in the database. If the value for input[property] is boolean we convert it to 0/1, so it can be inserted into the database. - Stringify the values and columns array. Replace square braces
[]
with round braces()
- This is a hack because the velocityjs engine handles stringification slightly differently. So adding this makes sure that our resolvers work both locally as well as on the deployed instance.
Step 3
Create a new file for the createNote
request resolver.
touch resolvers/mutations/createNote.req.vtl
Copy the snippet below
#set( $cols = [] )
#set( $vals = [] )
#foreach( $entry in $ctx.args.input.keySet() )
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
#set( $discard = $cols.add("$toSnake") )
#if( $util.isBoolean($ctx.args.input[$entry]) )
#if( $ctx.args.input[$entry] )
#set( $discard = $vals.add("1") )
#else
#set( $discard = $vals.add("0") )
#end
#else
#set( $discard = $vals.add("'$ctx.args.input[$entry]'") )
#end
#end
#set( $valStr = $vals.toString().replace("[","(").replace("]",")") )
#set( $colStr = $cols.toString().replace("[","(").replace("]",")") )
#if ( $valStr.substring(0, 1) != '(' )
#set( $valStr = "($valStr)" )
#end
#if ( $colStr.substring(0, 1) != '(' )
#set( $colStr = "($colStr)" )
#end
{
"version": "2018-05-29",
"statements": ["INSERT INTO notes $colStr VALUES $valStr", "SELECT * FROM notes ORDER BY id DESC LIMIT 1"]
}
Step 4
Create a new file for the createUser
request resolver.
touch resolvers/mutations/createUser.req.vtl
Copy the snippet below
#set( $cols = [] )
#set( $vals = [] )
#foreach( $entry in $ctx.args.input.keySet() )
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
#set( $discard = $cols.add("$toSnake") )
#if( $util.isBoolean($ctx.args.input[$entry]) )
#if( $ctx.args.input[$entry] )
#set( $discard = $vals.add("1") )
#else
#set( $discard = $vals.add("0") )
#end
#else
#set( $discard = $vals.add("'$ctx.args.input[$entry]'") )
#end
#end
#set( $valStr = $vals.toString().replace("[","(").replace("]",")") )
#set( $colStr = $cols.toString().replace("[","(").replace("]",")") )
#if ( $valStr.substring(0, 1) != '(' )
#set( $valStr = "($valStr)" )
#end
#if ( $colStr.substring(0, 1) != '(' )
#set( $colStr = "($colStr)" )
#end
{
"version": "2018-05-29",
"statements": ["INSERT INTO users $colStr VALUES $valStr", "SELECT * FROM users ORDER BY id DESC LIMIT 1"]
}
Step 5
Create the response resolver for all the mutations
touch resolvers/mutations/response.vtl
Copy the snippet below in the newly created file
#set ( $index = -1)
#set ( $result = $util.parseJson($ctx.result) )
#set ( $meta = $result.sqlStatementResults[1].columnMetadata)
## 1
#foreach ($column in $meta)
#set ($index = $index + 1)
#if ( $column["typeName"] == "timestamptz" )
#set ($time = $result["sqlStatementResults"][1]["records"][0][$index]["stringValue"] )
#set ( $nowEpochMillis = $util.time.parseFormattedToEpochMilliSeconds("$time.substring(0,19)+0000", "yyyy-MM-dd HH:mm:ssZ") )
#set ( $isoDateTime = $util.time.epochMilliSecondsToISO8601($nowEpochMillis) )
$util.qr( $result["sqlStatementResults"][1]["records"][0][$index].put("stringValue", "$isoDateTime") )
#end
#end
#set ( $res = $util.parseJson($util.rds.toJsonString($util.toJson($result)))[1][0] )
#set ( $response = {} )
## 2
#foreach($mapKey in $res.keySet())
#set ( $s = $mapKey.split("_") )
#set ( $camelCase="" )
#set ( $isFirst=true )
#foreach($entry in $s)
#if ( $isFirst )
#set ( $first = $entry.substring(0,1) )
#else
#set ( $first = $entry.substring(0,1).toUpperCase() )
#end
#set ( $isFirst=false )
#set ( $stringLength = $entry.length() )
#set ( $remaining = $entry.substring(1, $stringLength) )
#set ( $camelCase = "$camelCase$first$remaining" )
#end
$util.qr( $response.put("$camelCase", $res[$mapKey]) )
#end
$utils.toJson($response)
- Convert the DateTime value from the database into an ISO Date Time. When using RDS as a data source AppSync isn't able to handle
AWSDateTime
out of the box. - Convert the snake_case column names to camelCase.
Step 6
Create the mutation mapping templates for the create
mutations
touch resources/mapping-templates/mutations.yml
Copy the snippet below in the newly created file
- type: Mutation
field: createNote
request: "mutations/createNote.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
- type: Mutation
field: createList
request: "mutations/createList.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
- type: Mutation
field: createUser
request: "mutations/createUser.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
Register the mutation mapping templates in the serverless.yml
custom:
...
appSync:
...
mappingTemplates:
...
- ${file(./resources/mapping-templates/mutations.yml)}
Run the application using yarn start-offline
and execute the newly created mutations
mutation CreateUser {
createUser(input: { name: "Mac", userRef: "mac-123" }) {
id
name
userRef
}
}
mutation CreateList {
createList(input: { name: "House chores", userId: 1 }) {
id
name
userId
}
}
mutation CreateNote {
createNote(
input: {
note: "Mow the lawn"
listId: 1
deadline: "2021-01-01T00:00:00.000Z"
}
) {
id
note
listId
deadline
done
}
}
Create User
Create List
Create Note
Commit the progress till here
git add .
git commit -m 'Add support for create mutations'
Exposing update mutations by adding them to the schema.graphql
Step 1
Add the mutations and types for update
# update mutation inputs
input UpdateNoteRequest {
id: ID!
note: String
listId: ID
done: Boolean
deadline: AWSDateTime
}
input UpdateListRequest {
id: ID!
name: String
userId: Int
}
input UpdateUserRequest {
id: ID!
name: String
userRef: String
}
type Mutation {
...
# update mutations
updateList(input: UpdateListRequest!): MutatedList!
updateNote(input: UpdateNoteRequest!): MutatedNote!
updateUser(input: UpdateUserRequest!): MutatedUser!
}
Add resolvers for update mutations
Step 1
Create a new file for the updateList
request resolver.
touch resolvers/mutations/updateList.req.vtl
Copy the snippet below
#set( $update = "" )
#set( $equals = "=" )
## 1
#foreach( $entry in $ctx.args.input.keySet() )
## 2
#set( $cur = $ctx.args.input[$entry] )
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
## 3
#if( $util.isBoolean($cur) )
#if( $cur )
#set ( $cur = "1" )
#else
#set ( $cur = "0" )
#end
#end
## 4
#if ( $util.isNullOrEmpty($update) )
#set($update = "$toSnake$equals'$cur'" )
#else
#set($update = "$update,$toSnake$equals'$cur'" )
#end
#end
{
"version": "2018-05-29",
"statements": ["UPDATE lists SET $update WHERE id=$ctx.args.input.id",
"SELECT * FROM lists WHERE id=$ctx.args.input.id"]
}
We need to convert the incoming GraphQL into SQL statements to
- update a record in the database
- return the updated record
According to convention, the GraphQL request is in camelCase. However, the database columns are snake_case.
- Iterate over the keys in the args.input
- Convert each key from camelCase to snake_case
- Boolean values are stored
SMALLINT
in the database. If the value for input[property] is boolean we convert it to 0/1, so it can be inserted into the database. - If
$update
already has a value append a comma.
Step 2
Create a new file for the updateNote
request resolver.
touch resolvers/mutations/updateNote.req.vtl
Copy the snippet below
#set( $update = "" )
#set( $equals = "=" )
## 1
#foreach( $entry in $ctx.args.input.keySet() )
## 2
#set( $cur = $ctx.args.input[$entry] )
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
## 3
#if( $util.isBoolean($cur) )
#if( $cur )
#set ( $cur = "1" )
#else
#set ( $cur = "0" )
#end
#end
## 4
#if ( $util.isNullOrEmpty($update) )
#set($update = "$toSnake$equals'$cur'" )
#else
#set($update = "$update,$toSnake$equals'$cur'" )
#end
#end
{
"version": "2018-05-29",
"statements": ["UPDATE notes SET $update WHERE id=$ctx.args.input.id",
"SELECT * FROM notes WHERE id=$ctx.args.input.id"]
}
Step 3
Create a new file for the updateUser
request resolver.
touch resolvers/mutations/updateUser.req.vtl
Copy the snippet below
#set( $update = "" )
#set( $equals = "=" )
## 1
#foreach( $entry in $ctx.args.input.keySet() )
## 2
#set( $cur = $ctx.args.input[$entry] )
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
## 3
#if( $util.isBoolean($cur) )
#if( $cur )
#set ( $cur = "1" )
#else
#set ( $cur = "0" )
#end
#end
## 4
#if ( $util.isNullOrEmpty($update) )
#set($update = "$toSnake$equals'$cur'" )
#else
#set($update = "$update,$toSnake$equals'$cur'" )
#end
#end
{
"version": "2018-05-29",
"statements": ["UPDATE users SET $update WHERE id=$ctx.args.input.id",
"SELECT * FROM users WHERE id=$ctx.args.input.id"]
}
Step 4
Copy the snippet below in the mapping-templates/mutations.yml
...
- type: Mutation
field: updateList
request: "mutations/updateList.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
- type: Mutation
field: updateNote
request: "mutations/updateNote.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
- type: Mutation
field: updateUser
request: "mutations/updateUser.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
Run the application using yarn start-offline
and execute the newly created mutations
mutation UpdateList {
updateList(input: { id: 1, userId: 1 }) {
id
name
userId
}
}
mutation UpdateNote {
updateNote(input: { id: 10, note: "This is a new note" }) {
id
note
listId
deadline
done
}
}
mutation UpdateUser {
updateUser(input: { id: 1, userRef: "mac-987" }) {
id
name
userRef
}
}
Update List
Update Note
Update User
Commit the progress till here
git add .
git commit -m 'Add support for update mutations'
Exposing delete mutations by adding them to the schema.graphql
Step 1
Add the mutations and types for delete
type Mutation {
...
# delete mutations
deleteList(id: ID!): MutatedList!
deleteNote(id: ID!): MutatedNote!
deleteUser(id: ID!): MutatedUser!
}
Add resolvers for delete mutations
Step 1
Create a new file for the deleteList
request resolver.
touch resolvers/mutations/deleteList.req.vtl
Copy the snippet below
{
"version": "2018-05-29",
"statements": ["UPDATE lists set deleted_at=NOW() WHERE id=$ctx.args.id",
"SELECT * FROM lists WHERE id=$ctx.args.id"]
}
We need to convert the incoming GraphQL into SQL statements to
- delete a record in the database
- return the deleted record
Step 2
Create a new file for the deleteNote
request resolver.
touch resolvers/mutations/deleteNote.req.vtl
Copy the snippet below
{
"version": "2018-05-29",
"statements": ["UPDATE notes set deleted_at=NOW() WHERE id=$ctx.args.id",
"SELECT * FROM notes WHERE id=$ctx.args.id"]
}
Step 3
Create a new file for the deleteUser
request resolver.
touch resolvers/mutations/deleteUser.req.vtl
Copy the snippet below
{
"version": "2018-05-29",
"statements": ["UPDATE users set deleted_at=NOW() WHERE id=$ctx.args.id",
"SELECT * FROM users WHERE id=$ctx.args.id"]
}
Step 4
Copy the snippet below in the mapping-templates/mutations.yml
...
- type: Mutation
field: deleteList
request: "mutations/deleteList.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
- type: Mutation
field: deleteNote
request: "mutations/deleteNote.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
- type: Mutation
field: deleteUser
request: "mutations/deleteUser.req.vtl"
response: "mutations/response.vtl"
dataSource: POSTGRES_RDS
Run the application using yarn start-offline
and execute the newly created mutations
mutation DeleteList {
deleteList(id: 1) {
id
name
userId
}
}
mutation DeleteNote {
deleteNote(id: 10) {
id
note
listId
deadline
done
}
}
mutation DeleteUser {
deleteUser(id: 1) {
id
name
userRef
}
}
Delete List
Delete Note
Delete User
Commit the progress till here
git add .
git commit -m 'Add support for delete mutations'
There it is, you know have created, update and delete mutations resolving directly off-of the database!
Auto-generating a postman collection
Step 1
Install the graphql-testkit (https://www.npmjs.com/package/graphql-testkit)
Step 2
Run the application using
yarn start-offline
Step 3
Generate the postman collection
graphql-testkit \
--endpoint=http://localhost:20002/graphql \
--maxDepth=4 \
--header="x-api-key:0123456789"
Import the newly created collection into Postman and test out your queries and mutations!
Where to go from here
To write tests in the postman collection and run them as part of the CI pipeline head over to our article on postman test
I hope you enjoyed this tutorial on resolving mutations directly off of the database using AppSync and Aurora Serverless. If you have any questions or comments, please join the forum discussion below.
➤This blog was originally posted on https://wednesday.is To know more about what it’s like to work with Wednesday follow us on: Instagram|Twitter|LinkedIn
Top comments (0)