constraint_validations.rb

The constraint_validations extension is designed to easily create database constraints inside create_table and alter_table blocks. It also adds relevant metadata about the constraints to a separate table, which the constraint_validations model plugin uses to setup automatic validations.

To use this extension, you first need to load it into the database:

DB.extension(:constraint_validations)

Note that you should only need to do this when modifying the constraint validations (i.e. when migrating). You should probably not load this extension in general application code.

You also need to make sure to add the metadata table for the automatic validations. By default, this table is called sequel_constraint_validations.

DB.create_constraint_validations_table

This table should only be created once. For new applications, you generally want to create it first, before creating any other application tables.

Because migrations instance_exec the up and down blocks on a database, using this extension in a migration can be done via:

Sequel.migration do
  up do
    extension(:constraint_validations)
    # ...
  end
  down do
    extension(:constraint_validations)
    # ...
  end
end

However, note that you cannot use change migrations with this extension, you need to use separate up/down migrations.

The API for creating the constraints with automatic validations is similar to the validation_helpers model plugin API. However, instead of having separate validates_* methods, it just adds a validate method that accepts a block to the schema generators. Like the create_table and alter_table blocks, this block is instance_execed and offers its own DSL. Example:

DB.create_table(:table) do
  Integer :id
  String :name

  validate do
    presence :id
    min_length 5, :name
  end
end

instance_exec is used in this case because create_table and alter_table already use instance_exec, so losing access to the surrounding receiver is not an issue.

Here’s a breakdown of the constraints created for each constraint validation method:

There are some additional API differences:

• Only the :message and :allow_nil options are respected. The :allow_blank and :allow_missing options are not respected.

• A new option, :name, is respected, for providing the name of the constraint. It is highly recommended that you provide a name for all constraint validations, as otherwise, it is difficult to drop the constraints later.

• The includes validation only supports an array of strings, and array of integers, and a range of integers.

• There are like and ilike validations, which are similar to the format validation but use a case sensitive or case insensitive LIKE pattern. LIKE patters are very simple, so many regexp patterns cannot be expressed by them, but only a couple databases (PostgreSQL and MySQL) support regexp patterns.

• The operator validation only supports >, >=, <, and <= operators, and the argument must be a string or an integer.

• When using the unique validation, column names cannot have embedded commas. For similar reasons, when using an includes validation with an array of strings, none of the strings in the array can have embedded commas.

• The unique validation does not support an arbitrary number of columns. For a single column, just the symbol should be used, and for an array of columns, an array of symbols should be used. There is no support for creating two separate unique validations for separate columns in a single call.

• A drop method can be called with a constraint name in a alter_table validate block to drop an existing constraint and the related validation metadata.

• While it is allowed to create a presence constraint with :allow_nil set to true, doing so does not create a constraint unless the column has String type.

Note that this extension has the following issues on certain databases:

• MySQL does not support check constraints (they are parsed but ignored), so using this extension does not actually set up constraints on MySQL, except for the unique constraint. It can still be used on MySQL to add the validation metadata so that the plugin can setup automatic validations.

• On SQLite, adding constraints to a table is not supported, so it must be emulated by dropping the table and recreating it with the constraints. If you want to use this plugin on SQLite with an alter_table block, you should drop all constraint validation metadata using drop_constraint_validations_for(table: 'table'), and then readd all constraints you want to use inside the alter table block, making no other changes inside the alter_table block.

Dropping a table will automatically delete all constraint validations for that table. However, altering a table (e.g. to drop a column) will not currently make any changes to the constraint validations metadata.

Related module: Sequel::ConstraintValidations