Unique Constraints

[jcbdev]

Is your feature request related to a problem? Please describe.
I have lots of scenarios where I require unique values on non-primary key fields. Especially for public apps where you might want to not allow two people with the same phone number or email address and so forth

Describe the solution you'd like
It would be possible to add a directive such as @unique to a field or @unique(fields: ["email", "phone"]) to the type

First of all this would cause resources to be added:

• A table would have been created by the amplify CLI called "UniqueConstraints-APIID-env"

when this is added you can then change the VTL generated to do the following:

• operation is changed from PutItem in the VTL to "TransactWriteItems"
• The main record is a "Put" operation is added to the item list
• for every unique constraint (up to 24 which is max transaction),
  ** another "Put" item is added the Items but for TableName - "UniqueConstraints-APIID-env" and the id(pk) of "table#field#value"
  ** attribute_not_exist(id) is added the condition for this item

This would then only succeed if the field is successful. You would also need the update and delete templates to follow the equivalent logic

If the "value" is a complex type you could potentially use a hash of the value

Describe alternatives you've considered
I have used custom lambda resolvers and custom resolvers to implement this myself but it tedious for something I have to do over and over again

[jamesone]

Does anyone know if this is currently being developed by the amplify team?

[half2me]

I've only started out with Amplify, but it seems like a really basic requirement. There are plenty of times you would need to make sure a field(s) is unique. Having a directive like @unique seems to make sense.

[WillDent]

This is the best I could find specific to what you're asking for.

https://medium.com/@fullstackpho/ensuring-usernames-are-unique-in-your-aws-amplify-app-6fff963274

[connelhooley]

A real shame this hasn't been picked up, the medium link posted above works by running a Lambda that validates the data before (or just after?) it is inserted into the database. Uniqueness should be handled by the data store so it is guaranteed to be enforced.

[jcbdev]

@WillDent I tried similar methods but the method I suggested works better. I have modified my VTL template to use the method and it works perfectly (and is not that diffficult to do). It just takes a long time to do for every template and field combination when we could easily add directive to the schema that changes the vtl template generation slightly

[matansagee]

+1

[droidgeek1]

Even key field does not ensure uniqueness. I have following and duplicate email accepted even though I have indicated that as key. Worse part is that the backend corresponding dynamo table does not seem to take the duplicate record. So now, my client side sqlite is out of sync with dynamoDB. So I have to write code to check if the record exists..Sounds ugly unless someone can enlighten me please..

type AddUserToT
@model
@key(fields: ["userEmail"])
id: ID!
email: ID!

[mm87642]

+1

[shizhaojingszj]

+1

[DerekFei]

+1

[graniczny]

+1

[Wyfy0107]

+1

[JohnLamHK]

+1

[iltumio]

+1

[hackmajoris]

+1

[ilkerburakkurt]

+1

[acusti]

i notice this issue has the graphql-transformer-v1 label on it, but i want this feature for an amplify project i am working on that is using graphql transformer v2. should i create a new issue for that, or should this issue have the graphql-transformer-v2 label?

[nxia416]

+1

[acusti]

did the behavior of attribute_exists / attribute_not_exists dynamodb condition expressions change at some point? i was using attribute_not_exists in a CreateItem mutation (via condition: { field: { attributeExists: false } }), and i thought i had verified that it worked to ensure that the new item’s value for the specified field would be unique amongst all other items in the table, but i can verify that is not the case right now.

according to the dynamodb docs on comparison operators and functions, attribute_exists and attribute_not_exists are used to check only whether an item has or doesn’t have an attribute. it seems to have nothing to do with whether any other items in the table have or don’t have the same value for that attribute.

@jcbdev do you know if your technique of maintaining a UniqueConstraints table with the id of each item being a hash of table + fieldname + value still works? or maybe it only works because it’s being used as the primary key, and primary keys enforce uniqueness, so it has nothing to do with the attribute_not_exists(id) condition? in which case i suppose that condition passes because the item hasn’t been created yet so it doesn’t have an id? and lastly, have you added aDelete item for the previous value of the unique field on Update and Delete operations? it would be great to see a more complete example of how i could implement this manually by modifying the VTL templates.

[Voyager-Two]

This seems like major missing feature IMO.

For future readers:
One work-around is to set unique field as primary key:

username: String! @primaryKey

You can also do something like this (e.g. Discord usernames Bob#0001):

discordUsername: String! @primaryKey(sortKeyFields: ["discordNumber"])
discordNumber: Int!

[jcbdev]

did the behavior of attribute_exists / attribute_not_exists dynamodb condition expressions change at some point? i was using attribute_not_exists in a CreateItem mutation (via condition: { field: { attributeExists: false } }), and i thought i had verified that it worked to ensure that the new item’s value for the specified field would be unique amongst all other items in the table, but i can verify that is not the case right now.

according to the dynamodb docs on comparison operators and functions, attribute_exists and attribute_not_exists are used to check only whether an item has or doesn’t have an attribute. it seems to have nothing to do with whether any other items in the table have or don’t have the same value for that attribute.

@jcbdev do you know if your technique of maintaining a UniqueConstraints table with the id of each item being a hash of table + fieldname + value still works? or maybe it only works because it’s being used as the primary key, and primary keys enforce uniqueness, so it has nothing to do with the attribute_not_exists(id) condition? in which case i suppose that condition passes because the item hasn’t been created yet so it doesn’t have an id? and lastly, have you added aDelete item for the previous value of the unique field on Update and Delete operations? it would be great to see a more complete example of how i could implement this manually by modifying the VTL templates.

@acusti It did when I was using it but yes i think its because the column is the primary key. The trick was to create a uniqueconstraints table where all the unique constraints are written to and not to write the unique constraints to the same table your data is in (the dynamodb put requests can go to different tables in the transaction). But I do not use amplify anymore. I rewrote the platform on cdk and have never looked back. I found aws amplify terrible at scaling and just couldn't get to work on any project that wasn't a toy project. Too many limitations, compromises and assumptions that I spent more time maintaining and working around amplify in the project than developing new features.

[lewisdonovan]

Hi all. Just stumbled across this and think I might have found the answer. If you look at the generated schemas in AppSync, you'll see the schemas for the condition expression objects as well. For example, the schema to create a condition on a String input is:

input ModelStringInput {
	ne: String
	eq: String
	le: String
	lt: String
	ge: String
	gt: String
	contains: String
	notContains: String
	between: [String]
	beginsWith: String
	attributeExists: Boolean
	attributeType: ModelAttributeTypes
	size: ModelSizeInput
}

The first item in that schema ne means not equal to, which can be used to run a uniqueness comparison when putting an item. An example mutation would look like this:

mutation CreateTodoGraphQL($input: CreateTodoInput!, $condition: ModelTodoConditionInput) {
  createTodoGraphQL(input: $input, condition: $condition) {
    id
    name
    priority
    completedAt
  }
}

and the input variables would look like:

{
  "input": {
    "name": "test",
    "priority": "1",
    "completedAt": "2022-09-11T09:00:00.000Z"
  },
  "condition": { 
    "name": {
      "ne": "test"
    }
  }
}

If an item already exists with the name test then the condition expression would fail, and the item would not be added to the DB.

[Meerkov]

@lewisdonovan , isn't the condition specified on the client side? In other words, a client can cheat your system by removing that part of the request.

[lewisdonovan]

@lewisdonovan , isn't the condition specified on the client side? In other words, a client can cheat your system by removing that part of the request.

Yes, absolutely. But AppSync can be run on the back-end too. You could create a custom mutation, pass name as a parameter (rather than a filter variable), map the mutation to a Lambda and run the filter logic there.

[gavmck]

@lewisdonovan I'm not sure the condition method works, just given it a spin and it still lets me create/update the user.