DynamoDB queries on secondary index, how to define the indexes

Question

I've been going around and around this and it's just not clear what to do.

I have a simple table where I want to make queries against several columns. As I understand it, that means creating a secondary index for each column there is to query against. I've defined the table -- using the Serverless framework serverless.yml -- and am getting a variety of strange error messages.

The current serverless.yml definition is:

resources:
  Resources:
    MessagesDynamoDBTable:
      Type: 'AWS::DynamoDB::Table'
      Properties:
        AttributeDefinitions:
          - AttributeName: messageId
            AttributeType: S
          - AttributeName: room
            AttributeType: S
          - AttributeName: userId
            AttributeType: S
        KeySchema:
          - AttributeName: messageId
            KeyType: HASH
          - AttributeName: userId
            KeyType: RANGE
        LocalSecondaryIndexes:
          - IndexName: roomIndex
            KeySchema: 
              - AttributeName: room
                KeyType: HASH
            Projection: 
              ProjectionType: "KEYS_ONLY"
          - IndexName: userId
            KeySchema: 
              - AttributeName: userId
                KeyType: HASH
            Projection: 
              ProjectionType: "KEYS_ONLY"
        ProvisionedThroughput:
          ReadCapacityUnits: 1
          WriteCapacityUnits: 1
        TableName: ${self:custom.tableName}

It's meant to be something like a Slack service - and therefore I want to query for entries from a room, by a user, and so forth.

This definition makes sense going by the documentation I've been able to find. One is supposed to declare Attributes for the columns used in indexes, and I've done so. The KeySchema - I really only need the messageId field, but an error message indicated it needed a RANGE index, so I added the userId field to the KeySchema just to shut that up. The secondary index fields look right based on the documentation I've been able to find.

With this definition I'm getting this error when trying to deploy using serverless deploy

An error occurred: MessagesDynamoDBTable - Property AttributeDefinitions is inconsistent 
with the KeySchema of the table and the secondary indexes.

I have tried several variations and gotten other strange errors as well. What follows is a few, but I don't remember what the corresponding changes were to the definition.

An error occurred: MessagesDynamoDBTable - One or more parameter values were invalid: 
Index KeySchema does not have a range key for index: userId (Service: AmazonDynamoDBv2; Status Code: 400;
Error Code: ValidationException; Request ID: 1KFA2IMASC12HRLLDPG753CU63VV4KQNSO5AEMVJF66Q9ASUAAJG).

An error occurred: MessagesDynamoDBTable - 1 validation error detected: Value '[com.amazonaws.dynamodb.v20120810.KeySchemaElement@aa4cdc91, 
com.amazonaws.dynamodb.v20120810.KeySchemaElement@d2cd6f64, com.amazonaws.dynamodb.v20120810.KeySchemaElement@4d7c1f9, 
com.amazonaws.dynamodb.v20120810.KeySchemaElement@d2cd6f64]' at 'keySchema' failed to satisfy constraint: Member must have length less
 than or equal to 2 (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: BOVVBQ1F35VA18CCF3L5MSKS1FVV4KQNSO5AEMVJF66Q9ASUAAJG).

An error occurred: MessagesDynamoDBTable - Property AttributeDefinitions is inconsistent with the KeySchema 
of the table and the secondary indexes.

An error occurred: MessagesDynamoDBTable - One or more parameter values were invalid: Index KeySchema does not have a range key for index:
 userIdIndex (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: KFS63VSPKDUC60DV6U2V47UP27VV4KQNSO5AEMVJF66Q9ASUAAJG).

An error occurred: MessagesDynamoDBTable - One or more parameter values were invalid: Table KeySchema does not have a range key,
 which is required when specifying a LocalSecondaryIndex (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: 86Q2JSPM6Q9UPNIEOVHALLIIQJVV4KQNSO5AEMVJF66Q9ASUAAJG).

Answer 1

The reason it isn't working is that the keys in a Local Secondary Index must have the same partition key as the table. So in your case, your Local Secondary Indexes must have messageId as its HASH key and room and userId as RANGE keys on their respective indexes. And since your table is already keyed by (messageId, userId) then you don't need the userId Local Secondary Index.

This setup would technically work:

MessagesDynamoDBTable:
  Type: AWS::DynamoDB::Table
  Properties:
    AttributeDefinitions:
      - AttributeName: messageId
        AttributeType: S
      - AttributeName: room
        AttributeType: S
      - AttributeName: userId
        AttributeType: S
    KeySchema:
      - AttributeName: messageId
        KeyType: HASH
      - AttributeName: userId
        KeyType: RANGE
    LocalSecondaryIndexes:
      - IndexName: roomIndex
        KeySchema:
          - AttributeName: messageId
            KeyType: HASH
          - AttributeName: room
            KeyType: RANGE
        Projection:
          ProjectionType: KEYS_ONLY
    ProvisionedThroughput:
      ReadCapacityUnits: 1
      WriteCapacityUnits: 1
    TableName: ${self:custom.tableName}

However if what you want to do is query by rooms and users, then you probably want to go with a different table design. What you are trying to do would end up requiring you to always query the table using the messageId as part of the query since it is the partition key. So you wouldn't be able to query by just room and userId. What you probably want are Global Secondary Indexes. In that case, this would work:

MessagesDynamoDBTable:
  Type: AWS::DynamoDB::Table
  Properties:
    AttributeDefinitions:
      - AttributeName: messageId
        AttributeType: S
      - AttributeName: room
        AttributeType: S
      - AttributeName: userId
        AttributeType: S
    KeySchema:
      - AttributeName: messageId
        KeyType: HASH
    GlobalSecondaryIndexes:
      - IndexName: roomIndex
        KeySchema:
          - AttributeName: room
            KeyType: HASH
        Projection:
          ProjectionType: KEYS_ONLY
        ProvisionedThroughput:
          ReadCapacityUnits: 1
          WriteCapacityUnits: 1
      - IndexName: userIndex
        KeySchema:
          - AttributeName: userId
            KeyType: HASH
        Projection:
          ProjectionType: KEYS_ONLY
        ProvisionedThroughput:
          ReadCapacityUnits: 1
          WriteCapacityUnits: 1
    ProvisionedThroughput:
      ReadCapacityUnits: 1
      WriteCapacityUnits: 1
    TableName: ${self:custom.tableName}

Note that making your ProjectionType: KEYS_ONLY means when you query roomIndex or userIndex what you would get back is just messageIds - you would then have to requery the table with the messageIds to get other attributes. You might want to use a different ProjectionType depending on what your usage pattern is.