Amplify-cli: Auto increment number (non primary key) with AppSync and DynamoDB

[YikSanChan]

* Which Category is your question related to? *
API

* What AWS Services are you utilizing? *
AppSync, DynamoDB

* Provide additional details e.g. code snippets *
Let's say I have below schema:

type Item @model {
  category: String!
  name: String!
  tagNumber: String!
}

What I want to implement:

| id | category | name | tagNumber |
|--------------------------------------|----------|--------------|-----------|
| 3c174388-054b-431f-a0f4-d835acdd273a | food | cheese cake | 1.1 |
| 63b189d0-597e-47f3-8dcf-996da87101a8 | book | harry porter | 2.1 |
| 87749278-acae-4de1-b0ff-84d6b86506cc | food | steak | 1.2 |
| fd2334a9-ff76-4ae2-9ca5-834d998f91e7 | book | bad blood | 2.2 |

If I want to add a new item, which is a book called Joke, it should have a tagNumber 2.3, because it belongs to the 2nd category in the table, and it is the 3rd item in that category.

Why do I want to implement this? Because the tagNumber is easiest for people, not computers, to look up items.

Is there a way to implement this with AppSync and DynamoDB? I can think of the hard way to do it: use pipeline resolver. If the category specified exists, find an item with the latest createdAt timestamp in that category with tagNumber x.y, and assign x. to the new item. If the category specified doesn't exist, count categories N, and assign .1 to the new item.

[mikeparisstuff]

You can achieve this with a pipeline resolver but DynamoDB does not support auto-incrementing values like this in general. You can use DynamoDB UpdateItem operations along with an expression like "SET myNum = myNum + :val" to atomically increment an integer but this gets more complicated with your setup when using a tag like "1.1", "1.2", etc.

[YikSanChan]

Thanks @mikeparisstuff.

It is suggested by customer support that I maintain a table called "category_count".

| category | categoryId | categoryCount |
|----------|--------------|-----------|
| food | 1 | 2 |
| book | 2 | 2 |

Such that whenever I plan to add a new item with category=x to the backend, I lookup category_count table first.

• If exists a row where category=x: row.categoryCount+=1, return row.categoryId + "." + row.categoryCount.
• If not exists a row whether category=x: insert a new row with category=x, categoryId=count(category_count), categoryCount=1, return row.categoryId + "." + row.categoryCount.

The lookup requires a type CategoryCount @model and a custom pipeline resolver for mutation getTagNumber(category: String): String.

The solution is out there but it is really painful to deal with pipeline resolver... Let me try :(

[mikeparisstuff]

@YikSanChan We are working on designs that will make working with pipeline resolvers a lot easier. Please see #1055 and offer feedback. It is going to go through another round of review and design and it would be great to cover this use case.

[stewartduffy]

@YikSanChan did you manage to get this working?

[YikSanChan]

I didn’t use aws amplify any more and didn’t solve the problem

Stewart Duffy [e-mail]于2020年1月29日 周三下午2:53写道：

@YikSanChan https://github.com/YikSanChan did you manage to get this
working?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aws-amplify/amplify-cli/issues/720?email_source=notifications&email_token=AEDOKNPABRYAOQLDQDQR2Y3RAICFFA5CNFSM4GQJGGLKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKJBSAI#issuecomment-580000001,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AEDOKNLJWDYQLFDAFGQCSETRAICFFANCNFSM4GQJGGLA
.

[stewartduffy]

@YikSanChan OK thanks. Anyone else?

[dabit3]

Coming back to this, if anyone wants a working example of an atomic counter (increment, decrement) with DynamoDB and AppSync, here's something I'm using:

{
    "version": "2018-05-29",
    "operation": "UpdateItem",
    "key" : {
      "id" : $util.dynamodb.toDynamoDBJson($context.arguments.id)
    },
    "update": {
      "expression" : "set #upvotes = #upvotes + :updateValue",
      "expressionNames" : {
           "#upvotes" : "upvotes"
       },
       "expressionValues" : {
           ":updateValue" : { "N" : 1 }
       }
    }
}

[egreenmachine]

I took the logic that @dabit3 wrote and created a bit more a generic approach. It is documented on #3681.