DynamoDB | How to prevent creating a new item in PutItem if the item does exist

Question

I'm using @aws-sdk/client-dynamodb so the aws-sdk v3 with TS. My Table has a username and email attributes (among others). The username is a partition key and there's no sort key.

I want to create a new item but only if there exists no other item with the given username and email. I need them both to be unique and I don't want to override existing items.

I tried to use attribute_not_exists in ConditionExpression as well as other operators:

        const putItemCommand = new PutItemCommand({
            TableName: TABLE_NAME,
            Item: {
                username: { S: data.username },
                email: { S: data.email },
                description: { S: data.description },
                created_at: { S: new Date().toISOString() },
            },
            ConditionExpression: "attribute_not_exists(username) AND attribute_not_exists(email)",
        });

I also tried:

      const putItemCommand = new PutItemCommand({
            TableName: TABLE_NAME,
            Item: {
                username: { S: data.username },
                email: { S: data.email },
                description: { S: data.description },
                created_at: { S: new Date().toISOString() },
            },
            ConditionExpression: "username <> :username AND email <> :email",
            ExpressionAttributeValues: {
                ":username": { S: data.username },
                ":email": { S: data.email }
            }
        });

The problem is that the command only works if the username doesn't exist but it doesn't check the email. I can have many items with the same email but only one with a certain username. I need both of them to be unique always (a unique pair). When I set email as a sort key the behavior doesn't change.

How do I create a new item only if it has both a username and an email that doesn't exist?

Answer 1

The reason it does not check the email is because a condition is pointed to a single item, for which you have specified the partition key.

The simple solution to your issue is to make the email a sort key:

pk	sk	other
user123	example@example.com	other
user123	example@example.ie	other
user123	example@example.de	other
user123	example@example.co.uk	other

Now you can use ConditionExpression: "attribute_not_exists(sk)" to evaluate that the email does does not already exist for that given partition key.

If you need the email to be globally unique, then you must use transactions and keep an independent item with email as the partition key as explained in this blog