Rails: validate date range uniqueness at database level

0
votes

I have a Rails app with these 3 models - RentingUnit, Tenant & Booking.

A Tenant can Book a RentingUnit by filling up a form for a new booking with these fields - renting_unit_id, tenant_id, start_date, end_date.

start_date & end_date together form the duration the renting_unit is booked for.

With that, I want to make sure that a renting_unit can not be booked for a duration that overlaps with any duration it's already booked for. (I'm using PostgreSQL database if that matters.)

I came across related answers with Model level validations but I want to enforce uniqueness at the database level too, to account for a possible race condition.

How can I go about implementing it?

2
ruby-on-railspostgresqlindexing
If you are going to store the range of dates, then I think you need a trigger to enforce uniqueness. - Gordon Linoff

2 Answers

1
votes

Enforcing this sort of thing in the database is indeed a sensible idea. Luckily, PostgreSQL makes this easy. I've no idea if there is built-in support in rails for this, if not you'll want to run some custom SQL.

CREATE TABLE booking (tenant_id int, unit_id int, start_date date, end_date date);
-- This is the constraint that prevents overlapping bookings
ALTER TABLE booking ADD constraint no_overlap 
  EXCLUDE USING gist (
    unit_id WITH =, 
    daterange(start_date, end_date, '[]') WITH &&
  );
-- These two abut, so that's fine
INSERT INTO booking VALUES (101, 1, '2015-01-01', '2015-01-31');
INSERT INTO booking VALUES (102, 1, '2015-02-01', '2015-02-31');
-- This one overlaps the second one, so it should fail
INSERT INTO booking VALUES (103, 1, '2015-02-01', '2015-02-01');
ERROR:  conflicting key value violates exclusion constraint "no_overlap"
DETAIL: ...

Things to note.

  1. You will need to install the btree_gist extension to support equality checks
  2. The daterange(..., '[]') says that both ends of the range are inclusive. You can of course choose and exclusive range.
  3. Arguably you should store the range directly rather than separate dates, since that is what you wish to model.
  4. I prefer plural table-names myself, but I don't know what rails prefers.

Details and examples are, as always, in the PostgreSQL Documentation.

0
votes

I had a similar problem and the answer above didn't work for me.

The following did:

execute <<~SQL
  ALTER TABLE booking
  ADD CONSTRAINT unique_time_range_per_unit_id
  EXCLUDE USING GIST (
    (unit_id::text) WITH =,
    tsrange(start_date, end_data) WITH &&
  );
SQL

Tested on Postgres 9.6