Prevent overlapping time ranges with Ecto and Postgres

Happy whatever day you’re reading this! Before we begin, let me ask you:

What's the best present you can ever give?

A broken drum.

Nobody can beat that.

Alright. Now, let’s get down to business. Here’s another question for you: Imagine you build an appointment scheduling service. A service that allows patients to book an appointment with their doctor. You want to prevent double-booking of a doctor for the same time slot. So, if one patient has an appointment on Tuesday from 9am to 10am, another patient shouldn’t be able to book that same time slot. How would you implement this?

If you thought about adding the check somewhere in your Elixir code, you’re not wrong, but there’s a better way. If you thought: “Hey, I can do that in the database!”, congrats to you because that’s exactly what we will do today!

🔗 A quick dive into Exclusion Constraints

Raise your hand if you ever created a unique_index or added a not null to a field in your migration. Is that a hand up there? Good. That means you have worked with Constraints before. Even if you haven’t raised your hand, chances are that you will encounter a unique index or a non-null constraint soon. However, one constraint that you probably never heard about is an Exclusion Constraint.

A unique constraint guarantees that a database column never contains the same value twice. When used with multiple columns, it guarantees that the columns never contain the same combination of values twice. So, a unique constraint checks the existing data with the input data for equality and fails if you try to insert the same data again.

An exclusion constraint is a much more powerful extension of that. It can modify the existing values using many built-in functions before comparing the existing data with the input data. Instead of a simple is equal to comparison, you can define a list of complex comparisons to check for any condition you like, not only equality. If all comparisons return true, Postgres blocks the insertion of the data. The most common use of exclusion constraints is the X overlaps Y comparison. This comparison is especially useful when you want to prevent two DateTime ranges from overlapping.

We could implement the comparison in our Elixir application as well, but moving it to our database has a few advantages. If we wanted to implement this in Elixir, we would always need at least two database queries. First, we need to check whether an overlapping appointment exists already. Most likely, this would require checking the database with a rather complex query. If the query returns nothing, we go ahead and insert our data. With an exclusion constraint, you can combine both steps into one insertion step and let Postgres handle the complex comparison. Postgres’ indexes are highly optimized and written in super-fast C++, so using a constraint will probably always beat an Elixir-based implementation.

One caveat though is that you make yourself more dependent on the Postgres database if you use an exclusion constraint instead. You can’t simply migrate to another database that doesn’t support exclusion constraints.

With all that in mind, let’s see how we can implement an exclusion constraint for our appointment scheduling service.

🔗 The Schema

Our service has an Appointment-model, which looks like this:

schema "appointments" do
  field :doctor_id, :integer
  field :canceled, :boolean, default: false

  field :from, :utc_datetime
  field :until, :utc_datetime
end

We create an appointment for a given doctor and two DateTime timestamps from and until. We could implement a custom TSRange type instead of using two individual timestamps, but this will do for now. Users are allowed to cancel an appointment, which is tracked in the canceled field.

🔗 The Changeset

Our appointment changeset is relatively simple:

def changeset(appointment, attrs) do
  appointment
  |> cast(attrs, [:doctor_id, :canceled, :from, :until])
  |> exclusion_constraint(
       :from, 
       name: :overlapping_appointments
     )
  |> validate_required([:doctor_id, :from, :until])
end

Note the exclusion_constraint/3 function here (Kudos to Michele Balistreri who implemented this function 👏). It instructs Ecto to convert any violations of the exclusion constraint into an Ecto.ConstraintError. If we don’t add this, Ecto will raise the constraint error as an unhandled exception instead. By using exclusion_constraint/3, we can catch and return proper error messages instead of crashing the process when we try to insert an overlapping appointment. Let’s create the constraint next.

🔗 The Migration

We create the exclusion constraint in the migration by executing some custom SQL code. Here’s the entire migration:

defmodule Demo.Repo.Migrations.CreateAppointments do
  use Ecto.Migration

  def up do
    create table(:appointments) do
      add :doctor_id, :integer
      add :canceled, :boolean, default: false

      add :from, :utc_datetime
      add :until, :utc_datetime
    end

    execute "CREATE EXTENSION IF NOT EXISTS btree_gist;"

    execute """
      ALTER TABLE appointments
      ADD CONSTRAINT overlapping_appointments
      EXCLUDE USING GIST (
        doctor_id WITH =, 
        tsrange("from", "until", '[)') WITH &&
      ) WHERE (NOT canceled);
    """
  end

  def down do
    drop table(:appointments)
  end
end

Since we execute custom SQL code with execute, we need to divide the migration into up and down functions instead of using a single change function. Otherwise, Ecto wouldn’t know how to roll back the custom code.

Now, let’s go through the migration. First, we create the appointments table. Nothing fancy here. Then, we enable the btree_gist extension, if it isn’t active already. We need that extension to create an index for the exclusion constraint. Now comes the interesting part.

After creating the table, we alter it and add a constraint called overlapping_appointments. We define the constraint using the EXCLUDE USING instruction. We use a GIST index here, but you can use any other index type as well. Now, let’s look at the comparisons.

Each comparison has the format field WITH operator. You can use any field from your schema and any built-in operator. In our case, we first check the doctor_id for equality with the = operator. This effectively filters the existing data for the doctor_id for which we want to insert an appointment.

Next, we want to compare the appointment time slot using Postgres’ overlap operator &&. This operator is probably the most efficient method to compare overlapping time intervals, geometric shapes, or any other range type. To use it, we first need to create a timestamp range without a time zone from the from and until fields. We do that using the tsrange operator. Its parameters are the lower bound of the range from, the upper bound until, and a description of whether the bounds are inclusive or exclusive.

We want to create a time range up to, but not including, our upper bound and use the [) bounds. This way, we can insert more sane upper bounds like this:

%{
  from: ~U[2022-01-01 09:00:00Z],
  until: ~U[2022-01-01 10:00:00Z]
}

The result of the tsrange-function using these values and the [) bounds will create a time range from 09:00:00 until 09:59:59, because it excludes the upper bound. The next appointment can then start at 10:00:00 and end at 11:00:00. This makes the timestamp a bit more readable, but might confuse future-you because you’ll certainly forget about this detail and will wonder why you see overlapping timestamps in your database. So, use exclusive upper bounds mindfully.

If you want to have explicit timestamps, you can use the [] bounds instead. This will include your upper bound in the range. It means that you need to define the appointments with timestamps like this though:

%{
  from: ~U[2022-01-01 09:00:00Z], 
  # Note the `09:59:59` upper bound here
  until: ~U[2022-01-01 09:59:59Z]
}

The next appointment would then start at 10:00:00 and end at 10:59:59. It’s up to you which bounds to use.

Note: If you have timestamps with time zones, you can use the tstzrange-operator instead. BUT: For your own sanity, better use utc_datetime for all your DateTime data. Trust me. Timezones in the database are PAIN.

Now, that we created the range, we can use the &&-operator to check whether the existing and the input range overlap. The complete comparison looks like this:

tsrange("from", "until", '[)') WITH &&

The &&-operator also checks for partial overlaps (e.g. 09:00-10:00 and 09:30-10:30 overlap and 09:00-10:00 and 09:15-09:30 overlap also). So, whenever an existing range and the input range overlap, this operator returns TRUE and our insertion will fail. Neat!

Lastly, we want to ignore canceled appointments and add the where (NOT canceled)-condition. This instructs Postgres to ignore all canceled appointments in the exclusion constraint. We can now insert overlapping appointments if one of them is canceled.

🔗 Giving it a spin

With all this in place, let’s run our migrations with mix ecto.migrate and try it out!

Let’s create an appointment first:

iex> Demo.Appointments.create_appointment(
  %{
    doctor_id: 1,
    canceled: false,
    from: ~U[2022-01-01 09:00:00Z],
    until: ~U[2022-01-01 09:59:00Z]
  }
)
{:ok, _appointment}

Now, let’s try to insert an overlapping appointment and see what happens:

iex> Demo.Appointments.create_appointment(
  %{
    doctor_id: 1,
    canceled: false,
    from: ~U[2022-01-01 09:00:00Z],
    until: ~U[2022-01-01 09:59:00Z]
  }
)
{:error,
  #Ecto.Changeset<
    action: :insert,
    changes: %{...},
    errors: [
      from: {
        "violates an exclusion constraint",
        [
          constraint: :exclusion, 
          constraint_name: "overlapping_appointments"
        ]
      }
    ],
    data: #Demo.Appointments.Appointment<>,
    valid?: false
  >
}

Postgres and Ecto block the insertion and return a proper error message. Nice! Exactly what we wanted 💪

Next, let’s try to create an appointment that overlaps a canceled appointment:

iex> Demo.Appointments.create_appointment(
  %{
    doctor_id: 1,
    canceled: true,
    from: ~U[2022-01-01 09:00:00Z],
    until: ~U[2022-01-01 09:59:00Z]
  }
)
{:ok, _appointment}

iex> Demo.Appointments.create_appointment(
  %{
    doctor_id: 1,
    canceled: false,
    from: ~U[2022-01-01 09:00:00Z],
    until: ~U[2022-01-01 09:59:00Z]
  }
)
{:ok, _appointment}

This also works! Very nice 🥳

🔗 Another small benefit

You might have noticed that we don’t check the order of the from and until timestamps in our changeset. So, a user could insert an until that is earlier than the from, which is not what we want (but should expect). Preferably, we would check this in our changeset before executing any database action, but in case that we forgot about it, Postgres has got our back. Let’s try to insert a turned-around time range and see what happens:

iex> Demo.Appointments.create_appointment(
  %{
    doctor_id: 1,
    canceled: false,
    from: ~U[2022-01-01 09:59:00Z]
    until: ~U[2022-01-01 09:00:00Z],
  }
)

** (Postgrex.Error) ERROR 22000 (data_exception) range lower bound must be less than or equal to range upper bound

Interesting! Postgres checks the order of our two timestamps when it creates the tsrange. So, without writing any Elixir code, this sanity check for timestamps comes out of the box just by using the exclusion constraint. It is still a good idea to implement the order check in your changeset though.

🔗 Conclusion

And that’s it! I hope you enjoyed this article! If you have questions or comments, let’s discuss them on Twitter. Follow me on Twitter or subscribe to my newsletter below if you want to get notified when I publish the next blog post. Until then, thanks for reading!