CODEWITHSUNDEEP
amazon-dynamodbboto3python-3.7
Mihika
Mihika

Reputation: 545

Conditional insert in Dynamodb

I am creating a leave tracker app where I want to store the user ID along with the from date and to date. I am using Amazon's DynamoDB as the database, and the user enters a leave through a custom command. Eg: apply-leave from-date to-date

I want to avoid duplicate entries in the database. For example, if a user has already applied for a leave between 06-10-2019 to 10-10-2019 and applies for a leave between the same dates again, they should get a message saying that this already exists and a new record should not be created for the same.

However, a user can apply for multiple leaves and two users can take a leave between the same dates.

I tried using a conditional statement as follows:

table.put_item(
            Item={
                'leave_id': leave_id,
                'user_id': user_id,
                'from_date': from_date,
                'to_date': to_date,
            },
            ConditionExpression='attribute_not_exists(user_id) AND attribute_not_exists(from_date) AND attribute_not_exists(to_date)'
        )

where leave_id is the partition key. However, this does not work and a new row is added every time, even if it is the same dates. I have looked through similar other questions, but haven't been able to understand how to get this configured correctly.

Any ideas on how I should go about this, or if there is a different design that I should follow?

Upvotes: 0

Views: 332

Answers (1)

Tommy
Tommy

Reputation: 1034

If you are calling your code with the leave_id that doesn't yet exist in the table, the item will always be inserted. If you call your code with leave_id that does already exist in your table you should be getting An error occurred (ConditionalCheckFailedException) when calling the PutItem operation: The conditional request failed error message.

I have two suggestions:

If you don't want to change your table, you can create a secondary index with user_id as the partition key and then query the index for all the items where the given user has some from_date and to_date attributes. Like this: 

table.query(
    IndexName='user_id-index',
    KeyConditionExpression=Key('user_id').eq(user_id),
    FilterExpression=Attr('from_date').exists() & Attr('from_date').exists()
)

Then you will need to check for overlapping leave requests, etc. (eg. leave request that starts before the one that is already in place finishes). After deciding that the leave request is a valid one you will call put_item.

Another suggestion and probably a better one would be to create a composite primary key on your table with user_id as a partition key and leave_id as a sort key. That way you could execute a query for all leave requests from a particular user without the need to create a secondary index.

Upvotes: 1

Related Questions