Conditional insert in Dynamodb

Question

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?

Tommy · Accepted Answer · 2019-06-14 12:20:14Z

1

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.

answered Jun 14, 2019 at 12:20

Tommy

1,0344 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mihika Over a year ago

Thanks for the suggestion! leave_id is a random uuid to uniquely identify each row. So not sure how this can be used as a sort key, as it is unique for every row.

Tommy Over a year ago

I don't think it would be a problem, the point was to be able to query the table for all leave requests for a particular user. You can always try my first suggestion with an index if you can't/don't want to change how your table is defined.

Collectives™ on Stack Overflow

Conditional insert in Dynamodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related