Preventing double-bookings at the database level

A rental shop's worst booking problem isn't a slow page. It's two customers paying for the same item on the same day.

Why checking first isn't enough

The obvious approach is to check whether the dates are free, then save the booking. But two people can check at the same moment, both see the dates as free, and both save.

Let the database refuse

The reliable fix is a rule inside the database itself, so the second booking is rejected however close together the two arrive. The customer who lost the race gets a clear message and picks other dates.

In PostgreSQL, one way to write that rule is an exclusion constraint: no two bookings for the same item may have overlapping dates.

create extension if not exists btree_gist;

alter table bookings
  add constraint no_overlapping_bookings
  exclude using gist (
    item_id with =,
    daterange(start_date, end_date, '[]') with &&
  );

With that in place, the app can stay simple. It tries to save the booking, and if the database refuses, it tells the customer those dates were just taken.

What the customer sees

• Dates that are already booked show as unavailable before they pick.
• If someone else books the same dates a second earlier, they get a plain message, not an error page.
• Nobody ever pays a deposit for something that isn't free.

“A booking system's first job is to never promise the same thing twice.”