AppSync: Resolving mutations directly off of Aurora Serverless

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 is POSTGRES_RDS
The awsSecretStoreArn in the config 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.graphic

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 GraphQL 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.


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'

‍

Before you move on, here’s a tip top C Execs already know: LeadReads is your source for exclusive digital product insights and stories. Don’t miss out!

Join here.

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 the 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


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 create, 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-tests.

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 on Twitter.