New Features¶ ↑

• A pg_auto_constraint_validations plugin has been added, which automatically converts many constraint violations raised as exceptions to ValidationFailed exceptions when saving a model instance.

  The following constraint violation types are recognized and supported:

  • NOT NULL

  • CHECK

  • UNIQUE (except expression/functional indexes)

  • FOREIGN KEY (both referencing and referenced by)

  In the cases where the plugin cannot determine an appropriate validation failure for the constraint violation, it just reraises the original exception.

  This plugin is not intended as a replacement for other validations, it is intended as a last resort. The purpose of validations is to provide nice error messages for the user, and the error messages generated by this plugin are fairly generic. The error messages can be customized using the :messages plugin option, but there is only a single message used per constraint type.

• Database#check_constraints has been added on PostgreSQL. This returns metadata related to each check constraint on a table:

  DB.create_table(:foo) do
    Integer :i
    Integer :j
    constraint(:ic, Sequel[:i] > 2)
    constraint(:jc, Sequel[:j] > 2)
    constraint(:ijc, Sequel[:i] - Sequel[:j] > 2)
  end
  DB.check_constraints(:foo)
  # => {
  #  :ic=>{:definition=>"CHECK ((i > 2))", :columns=>[:i]},
  #  :jc=>{:definition=>"CHECK ((j > 2))", :columns=>[:j]},
  #  :ijc=>{:definition=>"CHECK (((i - j) > 2))", :columns=>[:i, :j]}
  # }
  

• Database#foreign_key_list now supports a :reverse option on PostgreSQL, which returns foreign keys referencing the given table, instead of of foreign keys in the given table referencing other tables:

  DB.create_table!(:a) do
    primary_key :id
    Integer :i
    Integer :j
    foreign_key :a_id, :a, :foreign_key_constraint_name=>:a_a
    unique [:i, :j]
  end
  DB.create_table!(:b) do
    foreign_key :a_id, :a, :foreign_key_constraint_name=>:a_a
    Integer :c
    Integer :d
    foreign_key [:c, :d], :a, :key=>[:j, :i], :name=>:a_c_d
  end
  DB.foreign_key_list(:a, :reverse=>true)
  # => [
  #  {:name=>:a_a, :columns=>[:a_id], :key=>[:id], :on_update=>:no_action,
  #   :on_delete=>:no_action, :deferrable=>false, :table=>:a, :schema=>:public},
  #  {:name=>:a_a, :columns=>[:a_id], :key=>[:id], :on_update=>:no_action,
  #   :on_delete=>:no_action, :deferrable=>false, :table=>:b, :schema=>:public},
  #  {:name=>:a_c_d, :columns=>[:c, :d], :key=>[:j, :i], :on_update=>:no_action,
  #   :on_delete=>:no_action, :deferrable=>false, :table=>:b, :schema=>:public}
  # ]
  

• Dataset#nowait has been added, which will make the query fail with a Sequel::DatabaseLockTimeout exception if it encounters a locked row, overriding the default database behavior that would wait until the lock was released. This method is supported on PostgreSQL, Microsoft SQL Server, Oracle, and MySQL 8+.

• Database#indexes now supports an :include_partial option on PostgreSQL, which will include partial indexes in the output (Sequel by default excludes partial indexes).

• Common table expressions and window functions are now supported when using MySQL 8+.

• Dataset#skip_locked is now supported on MySQL 8+.

• The connection_expiration extension now supports a Database#connection_expiration_random_delay attribute, which is used to randomize the expiration times, avoiding the thundering herd problem.

• The pg_enum extension now supports a rename_enum method for renaming existing enum types.

• Database#error_info on PostgreSQL now returns much more metadata regarding the error.

Other Improvements¶ ↑

• The dataset returned by the following dataset methods is cached, which can improve performance significantly in certain cases:

  • distinct (without arguments or block)

  • from_self (without options)

  • lateral

  • qualify (without argument)

  • returning (without arguments)

  • select_all (without arguments)

• If the primary_key serial: true, type: :serial, or type: :bigserial options are given on PostgreSQL 10.2+, use a serial primary key instead of an identity primary key. This change was included in Sequel 5.7.1.

• The :search_path Database option is now supported as a shard option on PostgreSQL, so different shards can use different search paths.

• The correct column order in Database#foreign_key_list on MySQL is now forced, fixing issues on MySQL 8+.

• When using case sensitive regexp matches on MySQL 8+, Sequel now uses the REGEXP_LIKE function instead of the REGEXP BINARY operator, to work around what appears to be a bug in MySQL 8+ related to the change in MySQL’s regexp engine.

• On MySQL 5.7+, the :extended option to Dataset#explain is now ignored, since the :extended option’s behavior in previous MySQL versions is now the default behavior.

• The MySQL HY000 generic SQL state error code is now ignored in the mysql2 adapter, so it falls back to using the more accurate backup error mapping in that case.

• The pg_enum extension’s schema modification methods now work correctly if the Database instance is frozen.

• The tactical_eager_loading plugin now respects the :allow_eager association option, and will not attempt to eagerly load associations when :allow_eager is false.

• Using multiple add_constraint calls and a set_column_null call in the same alter_table block on SQLite now works correctly. Note that if you are planning on ever modifying existing tables beyond adding columns, you should probably choose a database that natively supports such modification (SQLite does not).

• Hashes returned by Database#foreign_key_list on PostgreSQL now include a :schema entry, unless the support has been enabled to make the :table entry be a qualified identifier.

• Dataset#support_cte?(:insert) no longer returns true on SQLAnywhere. SQLAnywhere only supports common table expressions for INSERT … SELECT, not for all INSERT statements. INSERT … WITH … SELECT is already supported in Sequel using:

  DB[:t1].insert(DB[:t2].with(DB[:t3]))
  

• Model#_valid? is no longer made a public method in the error_splitter plugin.

Backwards Compatibility¶ ↑

• Calling the filter method on a proxy object returned by the association_proxies plugin now warns on ruby <2.6. This is because starting in ruby 2.6, the behavior will change and the method will be called on the array of associated objects instead of on the dataset, as Enumerable#filter is being added in ruby 2.6.