0

This is a follow-up/elaboration to a previous question of mine.

In the case of a collection of documents containing a time range represented by two timestamp fields (start and end), how does one go about guaranteeing that two documents don't get added with overlapping time ranges?

Say I had the following JavaScript on form submit:

var bookingsRef = db.collection('bookings')
                    .where('start', '<', booking.end)
                    .where('end', '>', booking.start);

bookingsRef.get().then(snapshot => {
    // if a booking is found (hence there is an overlap), display error
    // if booking is not found (hence there is no overlap), create booking
});

Now if two people were to submit overlapping bookings at the same time, could transactions be used (either on the client or the server) to guarantee that in between the get and add calls no other documents were created that would invalidate the original collection get query where clauses.

Or would my option be using some sort of security create rule that checks for other document time overlaps prior to allowing a new write (if this is at all possible)? One approach to guarantee document uniqueness via security rules seems to be exposing field values in the document ID, but I'm not entirely sure how exposing the start and end timestamp values in the ID would allow a rule to check for overlapping time ranges.

ghjunior
  • 119
  • 10

1 Answers1

0

I think transaction is proper approach. According to the documentation:

..., if a transaction reads documents and another client modifies any of those documents, Cloud Firestore retries the transaction. This feature ensures that the transaction runs on up-to-date and consistent data.

This seems to be an answer to your problem. All reads will be retried, if anything will change in the meantime. I think transaction mechanism is exactly for that reason.

vitooh
  • 4,132
  • 1
  • 5
  • 16
  • Thanks vitooh. The problem here being I'm not targetting a specific location in the database. I understand how transactions can be useful with direct paths to documents, but in this particular case I'm trying to abort the write based on the collection itself changing (if a potential new conflicting document was added after I had queried for it). For the time being I've opted for using Postgres which has a well known way of handling this particular problem, but will leave the question open in case I've overlooked anything. – ghjunior Apr 27 '21 at 11:42
  • I was even thinking that this use case will be easier with SQL database. So if you have possibility to use Postgres, in my opinion, it will be more convenient. – vitooh May 04 '21 at 08:53