AWS AppSync - Adding Global Secondary Index to DynamoDB and pagination using GSI sort key

Question

I am trying to create a structure that lists comments for a post with postId ordered w.r.t. their lastChangeTime descending.

The model in the schema is shared below.

type Comment {
  id: ID!
  postId: String!
  user: String!
  lastChangeTime: String
  commentBody: String
}

It has a backing DynamoDB table and generic CRUD resolvers for it already. And id field is the primary key in the table.

I plan to build a query as follows:

{
  "version": "2017-02-28",
  "operation" : "Query",
  "index" : "postId-index",
  "query" : {
    "expression": "post = :postId",
    "expressionValues" : {
      ":postId" : {
        "S" : "${ctx.args.postId}"
      }
    }
  },
  "limit": $util.defaultIfNull($ctx.args.first, 20),
  "nextToken": $util.toJson($util.defaultIfNullOrEmpty($ctx.args.after, null)),
  "scanIndexForward": false
}

To make it work, how should I add the Global Secondary Index (GSI) on postId (i.e. postId-index)?

Should I add a sort key on lastChangeTime when defining it and it would be ok? Or lastChangeTime field requires its own separate index to be sorted through?

Answer 1

It is easy. You can do it in two different ways or you can use both ways to have better flexibility. ( if you already resolved it I hope it will help someone else ).

Doing it this way you can set sortDirection dynamically using query arguments.

Detailed code is given below. Before that please note this point.

1. First point is re your Comment type - you are using

   type Comment {
     id: ID!
     postId: String!
     ## rest of your type definition
   }
   

This is not the best way to set up your Comment type linked to a Post.

Better way is:

type Comment {
    postID: ID!         ## select this as Primary key in DataSource in AppSync console
    commentID: String!    ## select this as Sort key in DataSource in AppSync console
    ## rest of your type definition
}

If you do this your DynamoDB table will have structure similar to one shown below ( from this AWS webpage).

( In your case UserId would be PostId and GameTitle would be CommentID )

This way, because all comments would be recorded next to each other (under same PostId ) AppSync response time would be much faster.

In AppSync docs page they also used this example:

2. As @Lisa M Shon mentioned you can initiate a GSI on CommentTable where PostId is partition key and addedTime is sort key . Call it 'postID-addedTime-index' if you want to use Resolvers provided below. MAKE SURE that you select 'Number' on addedTime in GSI.

Then in your schema you can define following types:

type Comment {
    postID: ID!
    commentID: String!
    content: String!
    addedTime: Int!
}

type CommentConnection {
    items: [Comment]
    nextToken: String
}

type Post {
    id: ID!
    postContent: String!
    addedTime: Int!
    ## Option 1. Gets Post details with all related Comments. 
    ## If 'startFromTime' is provided it will fetch all Comments starting from that timestamp.
    ## If 'startFromTime' is not provided it will fetch all Comments.
    comments(
        filter: TableCommentFilterInput,
        sortDirection: SortDirection,
        startFromTime: Int,
        limit: Int,
        nextToken: String
    ): CommentConnection
}

type Query {
    ## Option 2. It will fetch Comments only for a given PostId.
    ## If 'startFromTime' is provided it will fetch all Comments starting from that timestamp.
    ## If 'startFromTime' is not provided it will fetch all Comments.
    postCommentsByAddTime(
        postID: String!,
        startFromTime: Int!,
        sortDirection: SortDirection,
        filter: TableCommentFilterInput,
        count: Int,
        nextToken: String
    ): PaginatedComments
   ## your other queries
}

    ## rest of your schema definition

You can use both - Option 1 and Option 2 and use both.

Full schema code is here ( expand the snippet below ):

type Comment {
 postID: ID!
 commentID: String!
 content: String!
 addedTime: Int!
}

type CommentConnection {
 items: [Comment]
 nextToken: String
}

input CreateCommentInput {
 postID: ID!
 commentID: String!
 content: String!
 addedTime: Int!
}

input CreatePostInput {
 postContent: String!
 addedTime: Int!
}

input DeleteCommentInput {
 postID: ID!
 commentID: String!
}

input DeletePostInput {
 id: ID!
}

type Mutation {
 createComment(input: CreateCommentInput!): Comment
 updateComment(input: UpdateCommentInput!): Comment
 deleteComment(input: DeleteCommentInput!): Comment
 createPost(input: CreatePostInput!): Post
 updatePost(input: UpdatePostInput!): Post
 deletePost(input: DeletePostInput!): Post
}

type PaginatedComments {
 items: [Comment!]!
 nextToken: String
}

type Post {
 id: ID!
 postContent: String!
 addedTime: Int!
 comments(
  filter: TableCommentFilterInput,
  sortDirection: SortDirection,
  startFromTime: Int,
  limit: Int,
  nextToken: String
 ): CommentConnection
}

type PostConnection {
 items: [Post]
 nextToken: String
}

type Query {
 getComment(postID: ID!, commentID: String!): Comment
 listComments(filter: TableCommentFilterInput, limit: Int, nextToken: String): CommentConnection
 getPost(id: ID!): Post
 listPosts(filter: TablePostFilterInput, limit: Int, nextToken: String): PostConnection
 postCommentsByAddTime(
  postID: String!,
  startFromTime: Int!,
  sortDirection: SortDirection,
  filter: TableCommentFilterInput,
  count: Int,
  nextToken: String
 ): PaginatedComments
}

enum SortDirection {
 ASC
 DESC
}

type Subscription {
 onCreateComment(
  postID: ID,
  commentID: String,
  content: String,
  addedTime: Int
 ): Comment
  @aws_subscribe(mutations: ["createComment"])
 onUpdateComment(
  postID: ID,
  commentID: String,
  content: String,
  addedTime: Int
 ): Comment
  @aws_subscribe(mutations: ["updateComment"])
 onDeleteComment(
  postID: ID,
  commentID: String,
  content: String,
  addedTime: Int
 ): Comment
  @aws_subscribe(mutations: ["deleteComment"])
 onCreatePost(id: ID, postContent: String, addedTime: Int): Post
  @aws_subscribe(mutations: ["createPost"])
 onUpdatePost(id: ID, postContent: String, addedTime: Int): Post
  @aws_subscribe(mutations: ["updatePost"])
 onDeletePost(id: ID, postContent: String, addedTime: Int): Post
  @aws_subscribe(mutations: ["deletePost"])
}

input TableBooleanFilterInput {
 ne: Boolean
 eq: Boolean
}

input TableCommentFilterInput {
 postID: TableIDFilterInput
 commentID: TableStringFilterInput
 content: TableStringFilterInput
 addedTime: TableIntFilterInput
}

input TableFloatFilterInput {
 ne: Float
 eq: Float
 le: Float
 lt: Float
 ge: Float
 gt: Float
 contains: Float
 notContains: Float
 between: [Float]
}

input TableIDFilterInput {
 ne: ID
 eq: ID
 le: ID
 lt: ID
 ge: ID
 gt: ID
 contains: ID
 notContains: ID
 between: [ID]
 beginsWith: ID
}

input TableIntFilterInput {
 ne: Int
 eq: Int
 le: Int
 lt: Int
 ge: Int
 gt: Int
 contains: Int
 notContains: Int
 between: [Int]
}

input TablePostFilterInput {
 id: TableIDFilterInput
 postContent: TableStringFilterInput
 addedTime: TableIntFilterInput
}

input TableStringFilterInput {
 ne: String
 eq: String
 le: String
 lt: String
 ge: String
 gt: String
 contains: String
 notContains: String
 between: [String]
 beginsWith: String
}

input UpdateCommentInput {
 postID: ID!
 commentID: String!
 content: String
 addedTime: Int
}

input UpdatePostInput {
 id: ID!
 postContent: String
 addedTime: Int
}

schema {
 query: Query
 mutation: Mutation
 subscription: Subscription
}

3. (related to Option 1). On Schema page of AppSync console in right hand side panel find Post and opposite to 'comments(...): CommentConnection' click 'Attach', add 'CommentTable' as source and add following Resolver code in VTL:

In request mapping template:

    #set( $startFromTime = $util.defaultIfNull($context.args.startFromTime, 0) )
    {
        "version" : "2017-02-28",
        "operation" : "Query",
        "index" : "postID-addedTime-index",
        "query" : {
          "expression": "postID = :postID and addedTime > :startFrom",
            "expressionValues" : {
              ":postID" : { "S" : "$context.source.id" },
              ":startFrom" : { "N" : "$startFromTime" }
            }
        },
        "scanIndexForward":   #if( $context.args.sortDirection )
          #if( $context.args.sortDirection == "ASC" )
              true
          #else
              false
          #end
        #else
            true
        #end,

        #if( ${context.arguments.count} )
            ,"limit": ${context.arguments.count}
        #end
        #if( ${context.arguments.nextToken} )
            ,"nextToken": "${context.arguments.nextToken}"
        #end
    }

In response mapping template:

{
    "items": $utils.toJson($context.result.items)
    #if( ${context.result.nextToken} )
        ,"nextToken": "${context.result.nextToken}"
    #end
}

4. (related to Option 2). On Schema page of AppSync console in right hand side panel find Query and opposite to 'postCommentsByAddTime(...): PaginatedComments' click 'Attach', add CommentTable as data source and add following Resolver code in VTL:

In request mapping template:

{
    "version" : "2017-02-28",
    "operation" : "Query",
    "index" : "postID-addedTime-index",
    "query" : {
      "expression": "postID = :postID and addedTime > :startFrom",
        "expressionValues" : {
          ":postID" : { "S" : "${context.arguments.postID}" },
          ":startFrom" : { "N" : "${context.arguments.startFromTime}" }
        }
    }
    #if( ${context.arguments.count} )
        ,"limit": ${context.arguments.count}
    #end
    #if( ${context.arguments.nextToken} )
        ,"nextToken": "${context.arguments.nextToken}"
    #end
}

In response mapping template:

{
    "items": $utils.toJson($context.result.items)
    #if( ${context.result.nextToken} )
        ,"nextToken": "${context.result.nextToken}"
    #end
}

That is it.

Now you can use all of following queries:

query ListPosts {
  listPosts{
    items {
      id
      postContent
      ## all below arguments are nullable
      comments(startFromTime: 121111112222, count: 4
      ## default sortDirection is ASC, you can change it this way
      ## sortDirection: DESC
    ) {
        items {
          postID
          commentID
          content
          addedTime
        }
      }
    }
  }
}

query GetPost {
  getPost(id: "6548e596-d1ed-4203-a32f-52cfab8c9b20") {
    id
    comments (
    ## you can also add all three or any or none of these
    ## sortDirection: DESC,
    ## startFromTime: 189283212122
    ## count: 5
    ) {
        items {
        postID
        commentID
        content
        addedTime
      }
  }
  }
}

query GetCommentsByTime {
  postCommentsByAddTime(postID: "6548e596-d1ed-4203-a32f-52cfab8c9b20", startFromTime: 12423455352342, count: 2) {
    items {
      postID
      commentID
      content
      addedTime
    }
  }
}