Rails validations: database level check constraints

One of the most common Rails tips is to back up your ActiveRecord model validations with database level constraints.

Because there are times when validations are skipped, it’s best to let your database be the last line of defense to maintain your data integrity. You can add a validates :name, presence: true line to your model, but if null values sneak into your database, your app will still throw an exception if you call name.downcase on nil.

The most common examples are pairing presence validations with non-null columns and unique validations with a unique database index.

But did you know you can go a step further using a database feature called “check constraints”.

Usage

Check constraints run when you attempt to store data into a column. If the data violates the constraint, an error is raised and Rails will rollback the transaction.

At Arrows, we help onboard customers by creating custom plans. Plans are based on a Template and we recently added a way to set the deadline to a fixed number of days after the Plan gets created.

We stored the deadline offset as an integer in the database. But in the context of our application, this number should never be negative. When we create a Plan from the Template, we want to write code like:

deadline = Date.current + @template.deadline_offset

But since the integer type in the database can be negative, we didn’t have a guarantee that the offset would be positive. Obviously, we don’t want the deadline to ever be before the plan is even created.

We can validate this at the model level, but we would also like the database to enforce this check.

class Template < ApplicationRecord
  validates :deadline_offset, numericality: {
        only_integer: true,
        greater_than_or_equal_to: 0
  }
end

As of Rails 6.1, you can specific check constraints straight from a migration.

class AddDeadlineOffsetCheckToTemplates < ActiveRecord::Migration[7.0]
  def change
    add_check_constraint :templates, "deadline_offset >= 0",
      name: "deadline_offset_non_negative"
  end
end

You pass in a name for the constraint and then the SQL condition to run for the check.

Now if you forgot the ActiveRecord model validation or accidentally bypass it by skipping callbacks, this check is enforced at the database level.

# Live dangerously and skip validations!
=> template.deadline_offset = -12
=> template.save(validate: false)

PG::CheckViolation: ERROR:  new row for relation "templates" violates check constraint "deadline_offset_non_negative" (ActiveRecord::StatementInvalid)

Other common examples

Here are a few more places you might want to use a check constraint:

Ensuring a minimum price as a safety mechanism: check that price > 100 to ensure no one accidentally adds a product below a minimum level
Validating fixed formats: storing a US zipcode? Make sure char_length(zipcode) = 5
Enforcing a relationship between two columns: add a constraint that start_date < end_date or sale_price <= price

Should you always add check constraints? I’ve never run into cases where I was unhappy having extra protection when it comes to data integrity. If you have mission-critical data requirements, I would highly recommend adding check constraints.

Are they strictly necessary for every little thing? Probably not. But you can weigh the headache of fixing data issues with the cost of adding constraints for each validation in your app.

References

Rails API: add_check_constraint

Thoughtbot blog: Validation, Database Constraint, or Both?

Boring Rails

Tip: Rails validations: database level check constraints

Usage

Other common examples

References