ANN: Sequel 3.5.0 Released

Sequel is a lightweight database access toolkit for Ruby.

  • Sequel provides thread safety, connection pooling and a concise DSL
    for constructing database queries and table schemas.
  • Sequel also includes a lightweight but comprehensive ORM layer for
    mapping records to Ruby objects and handling associated records.
  • Sequel supports advanced database features such as prepared
    statements, bound variables, stored procedures, master/slave
    configurations, and database sharding.
  • Sequel makes it easy to deal with multiple records without having
    to break your teeth on SQL.
  • Sequel currently has adapters for ADO, Amalgalite, DataObjects,
    DB2, DBI, Firebird, Informix, JDBC, MySQL, ODBC, OpenBase, Oracle,
    PostgreSQL and SQLite3.

Sequel 3.5.0 has been released and should be available on the gem
mirrors. The 3.5.0 release adds numerous improvements:

New Plugins

  • A class_table_inheritance plugin has been added, supporting model
    inheritance in the database using a table-per-model-class approach.
    Each table stores only attributes unique to that model or subclass

    For example, with this hierarchy:

             /        \
          Staff     Manager

    the following database schema may be used (table - columns):

    • employees - id, name, kind
    • staff - id, manager_id
    • managers - id, num_staff
    • executives - id, num_managers

    The class_table_inheritance plugin assumes that the main table
    (e.g. employees) has a primary key field (usually
    autoincrementing), and all other tables have a foreign key of the
    same name that points to the same key in their superclass’s table.
    For example:

    • - primary key, autoincrementing
    • - foreign key referencing employees(id)
    • - foreign key referencing employees(id)
    • - foreign key referencing managers(id)

    When using the class_table_inheritance plugin, subclasses use joined

    Employee.dataset.sql # SELECT * FROM employees
    Manager.dataset.sql # SELECT * FROM employees
    # INNER JOIN managers USING (id)
    Executive.dataset.sql # SELECT * FROM employees
    # INNER JOIN managers USING (id)
    # INNER JOIN executives USING (id)

    This allows Executive.all to return instances with all attributes
    loaded. The plugin overrides deleting, inserting, and updating
    in the model to work with multiple tables, by handling each table

    This plugin allows and encourages the use of a :key option to mark
    a column holding the class name. This allows methods on the
    superclass to return instances of specific subclasses.

    a = Employee.all # [<#Staff>, <#Manager>, <#Executive>]

    This plugin requires the lazy_attributes plugin and uses it to
    handle subclass specific attributes that would not be loaded
    when calling superclass methods (since those wouldn’t join
    to the subclass tables). For example:

    a.first.values # {:id=>1, name=>‘S’, :kind=>‘Staff’}
    a.first.manager_id # Loads the manager_id attribute from the
    # database

    The class_table_inheritance plugin requires JOIN USING and
    therefore is not supported on H2 or Microsoft SQL Server, which do
    not support that SQL-92 feature.

  • An associations_dependencies plugin was added for deleting,
    destroying, or nullifying associated objects when destroying a
    model object. This just gives an easy way to add the necessary
    before and after destroy hooks. The following association types
    support the following dependency actions:

    • :many_to_many - :nullify (removes all related entries in join
    • :many_to_one - :delete, :destroy
    • :one_to_many - :delete, :destroy, :nullify (sets foreign key to
      NULL for all associated objects)

    This plugin works directly with the association datasets and does
    not use any cached association values. The :delete action will
    delete all associated objects from the database in a single SQL
    call. The :destroy action will load each associated object from the
    database and call the destroy method on it.

    The plugin call takes a hash of association symbol keys and
    dependency action symbol values. Alternatively, you can specify
    additional dependencies later using add_association_dependencies:

    Business.plugin :association_dependencies, :address=>:delete


    Artist.plugin :association_dependencies
    Artist.add_association_dependencies :albums=>:destroy,
    :reviews=>:delete, :tags=>:nullify

  • A force_encoding plugin was added that forces the encoding of
    strings used in model instances. When model instances are loaded
    from the database, all values in the hash that are strings are
    forced to the given encoding. Whenever you update a model column
    attribute, the resulting value is forced to a given encoding if the
    value is a string. There are two ways to specify the encoding.
    You can either do so in the plugin call itself, or via the
    forced_encoding class accessor:

    class Album < Sequel::Model
    plugin :force_encoding, ‘UTF-8’
    # or
    plugin :force_encoding
    self.forced_encoding = ‘UTF-8’

    This plugin only works on ruby 1.9, since strings don’t have
    encodings in 1.8.

  • A typecast_on_load plugin was added, for fixing bad database
    typecasting when loading model objects. Most of Sequel’s database
    adapters don’t have complete control over typecasting, and may
    return columns that aren’t typecast correctly (with correct being
    defined as how the model object would typecast the same column

    This plugin modifies Model.load to call the setter methods (which
    typecast by default) for all columns given. You can either specify
    the columns to typecast on load in the plugin call itself, or
    afterwards using add_typecast_on_load_columns:

    Album.plugin :typecast_on_load, :release_date, :record_date


    Album.plugin :typecast_on_load
    Album.add_typecast_on_load_columns :release_date, :record_date

    If the database returns release_date and record_date columns as
    strings instead of dates, this will ensure that if you access those
    columns through the model object, you’ll get Date objects instead of

  • A touch plugin was added, which adds Model#touch for updating an
    instance’s timestamp, as well as touching associations when an
    instance is updated or destroyed.

    The Model#touch instance method saves the object with a modified
    timestamp. By default, it uses the :updated_at column, but you can
    set which column to use. It also supports touching of associations,
    so that when the current model object is updated or destroyed, the
    associated rows in the database can have their modified timestamp
    updated to the current timestamp. Example:

    class Album < Sequel::Model
    plugin :touch, :column=>:modified_on, :associations=>:artist

  • A subclasses plugin was added, for recording all of a models
    subclasses and descendent classes. Direct subclasses are available
    via the subclasses method, and all descendent classes are available
    via the descendents method:

    c =
    c.plugin :subclasses
    sc1 =©
    sc2 =©
    ssc1 =
    c.subclasses # [sc1, sc2]
    sc1.subclasses # [ssc1]
    sc2.subclasses # []
    ssc1.subclasses # []
    c.descendents # [sc1, ssc1, sc2]

    The main use case for this is if you want to modify all models
    after the model subclasses have been created. Since mutable
    options are copied when subclassing, modifying parent classes
    does not affect current subclasses, only future ones. The
    subclasses plugin allows you get all subclasses so that you can
    easily modify them. The plugin only records subclasses
    created after the plugin call, though.

  • An active_model plugin was added, giving Sequel::Model an
    ActiveModel complaint API, in so much as it passes the
    ActiveModel::Lint tests.

New Extensions

  • A named_timezones extension was added, allowing you to use named
    timezones such as “America/Los_Angeles” (the default Sequel
    timezone support only supports UTC or local time). This extension
    requires TZInfo. It also sets the Sequel.datetime_class to
    DateTime, so database timestamps will be returned as DateTime
    instances instead of Time instances. This is because ruby’s
    Time class doesn’t support timezones other than UTC and local time.

    This plugin allows you to pass either strings or TZInfo::Timezone
    instance to Sequel.database_timezone=, application_timezone=, and
    typecast_timezone=. If a string is passed, it is converted to a
    TZInfo::Timezone using TZInfo::Timezone.get.

    Let’s say you have the database server in New York and the
    application server in Los A… For historical reasons, data
    is stored in local New York time, but the application server only
    services clients in Los A., so you want to use New York
    time in the database and Los A. time in the application. This
    is easily done via:

    Sequel.database_timezone = ‘America/New_York’
    Sequel.application_timezone = ‘America/Los_Angeles’

    Then, before timestamps are stored in the database, they are
    converted to New York time. When timestamps are retrieved from the
    database, they are converted to Los A. time.

  • A thread_local_timezones extension was added. This allows you to
    set a per-thread timezone that will override the default global
    timezone while the thread is executing. The main use case is for
    web applications that execute each request in its own thread, and
    want to set the timezones based on the request. The most common
    example is having the database always store time in UTC, but have
    the application deal with the timezone of the current user. That
    can be done with:

    Sequel.database_timezone = :utc

    In each thread:

    Sequel.thread_application_timezone = current_user.timezone

    This extension is designed to work with the named_timezones

  • An sql_expr extension was added that adds .sql_expr methods to
    all objects, giving them easy access to Sequel’s DSL:

    1.sql_expr < :a # 1 < a
    false.sql_expr & :a # FALSE AND a
    true.sql_expr | :a # TRUE OR a
    ~nil.sql_expr # NOT NULL
    “a”.sql_expr + “b” # ‘a’ || ‘b’

    Proc#sql_expr uses a virtual row:

    proc{[[a, b], [a, c]]}.sql_expr | :x

    (((a = b) AND (a = c)) OR x)

  • A looser_typecasting extension was added, for using to_f and to_i
    instead of the more strict Kernel.Float and Kernel.Integer when
    typecasting floats and integers. To use it, you should extend the
    database with the Sequel::LooserTypecasting module after loading
    the extension:

    Sequel.extension :looser_typecasting

    This makes the behavior more like ActiveRecord:

    a =>‘a’)
    a.num_albums # => 0

Other New Features

  • Associations now support composite keys. All of the :*key options
    options now accept arrays of symbols instead of plain symbols.

    Artist.primary_key # [:name, :city]
    Album.many_to_one :artist, :key=>[:artist_name, :artist_city]
    Artist.one_to_many :albums, :key=>[:artist_name, :artist_city]

    All association types are supported, including the built-in
    many_to_many association and the many_through_many plugin. Both
    methods of eager loading work with composite keys for all
    association types. Setter and add/remove/remove_all methods
    also now work with composite keys.

  • Associations now respect a :validate option, which can be set to
    false to not validate when implicitly saving associated objects.

    There isn’t a lot of implicit saving in Sequel’s association
    methods, but this gives the user the control over validation when
    the association methods implicitly save an object.

  • In addition to the regular association methods, the
    nested_attributes plugin was also updated to respect the
    :validate_association option. It was also modified to not validate
    associated objects twice, once when the parent object was validated
    and again when the associated object was saved. Additionally, if
    you pass :validate=>false to the save method when saving the parent
    object, it will not longer attempt to validate associated objects
    when saving them.

  • Dataset#insert and #insert_sql were refactored and now support the
    following API:

    • No arguments - Treat as a single empty hash argument
    • Single argument:
      • Hash - Use keys as columns and values as values
      • Array - Use as values, without specifying columns
      • Dataset - Use a subselect, without specifying columns
      • LiteralString - Use as the values
    • 2 arguments:
      • Array, Array - Use first array as keys, second as values
      • Array, Dataset - Use a subselect, with the array as columns
      • Array, LiteralString - Use LiteralString as the values, with
        the array as the columns
    • Anything else: Treat all given values an an array of values
  • Graphing now works with previously joined datasets. The main use
    case of this is when eagerly loading (via eager_graph) model
    associations for models backed by joined datasets, such as those
    created by the class_table_inheritance plugin.

  • Sequel.virtual_row was added allowing you to easily use the
    VirtualRow support outside of select, order, and filter calls:

    net_benefit = Sequel.virtual_row{revenue > cost}
    good_employee = Sequel.virtual_row{num_commendations > 0}
    fire = ~net_benefit & ~good_employee
    demote = ~net_benefit & good_employee
    promote = net_benefit & good_employee

  • When Sequel wraps exception in its own classes (to provide database
    independence), it now keeps the wrapped exception available in
    a wrapped_exception accessor. This allows you to more easily
    determine the wrapped exception class, without resorting to parsing
    the exception message.

    rescue Sequel::DatabaseError => e
    case e.wrapped_exception
    when Mysql::Error

    when PGError


  • The MySQL adapter now supports a Dataset#split_multiple_result_sets
    method that yields arrays of rows (one per result set), instead of
    rows. This allows you to submit multiple statements at the same
    time (or call a stored procedure that returns multiple result
    sets), and know which rows are related to which result sets.

    This violates a lot of Sequel’s internal assumptions and should be
    used with care. Existing row_procs are modified to work correctly,
    but graphing will not work on these datasets.

  • The ADO adapter now accepts a :conn_string option and uses that
    as the full ADO connection string. This can be used to connect to
    any datasource ADO supports, such as Microsoft Excel.

  • The Microsoft SQL Server shared adapter now supports a
    Database#server_version method.

  • The Microsoft SQL Server shared adapter now supports updating and
    deleting from joined datasets.

  • The Microsoft SQL Server shared adapter now supports a
    Dataset#output method that uses the OUTPUT clause.

  • Model#_save now calls either Model#_insert or Model#_update for
    inserting/updating the row in the database. This allows for easier
    overriding when you want to allow creating and updating model
    objects backed by a joined dataset.

  • Dataset#graph now takes a :from_self_alias option specifying the
    alias to use for the subselect created if the receiver is a joined
    but not yet graphed dataset. It defaults to the first source table
    in the receiver.

Other Improvements

  • Typecasting model attributes is now done before checking existing
    values, instead of after. Before, the code for the model attribute
    setters would compare the given value to the existing entry. If it
    didn’t match, the value was typecasted and then assigned. That led
    to the following situation:

    a = Album[1]
    a.num_tracks # => 10
    params # => {‘num_tracks’=>‘10’}
    a.changed_columns # => [:num_tracks]

    The new behavior typecasts the value first, and only sets it and
    records the column as changed if it doesn’t match the typecasted

  • Model#modified? is now always true if the record is new. modified?
    indicates the instance’s status relative to the database, and since
    a new object is not yet in the database, and saving the object
    would add it, the object is considered modified. A consequence of
    this is that Model#save_changes now always saves if the object is

    If you want to check if there were changes to columns since the
    object was first initialized, you should use
    !changed_columns.empty?, which was the historical way to handle
    the situation.

  • The DataObjects (do) adpater now supports DataObjects 0.10.

  • Dataset#select_more and Dataset#order_more no longer affect the
    receiver. They are supposed to just return a modified copy of the
    receiver instead of modifying the receiver itself. For a few
    versions they have been broken in that they modified the receiver
    in addition to returning a modified copy.

  • Performance was increased for execution of prepared statements
    with multiple bound variables on MySQL.

  • On MySQL, database errors raised when preparing statements or
    setting bound variable values are now caught and raised as

  • On MySQL, more types of disconnection errors are detected.

  • When altering columns in MySQL, options such as :unsigned,
    :elements, and :size that are given in the call are now respected.

  • MySQL enum defaults are now handled correctly in the schema dumper.

  • The schema dumper no longer attempts to use unparseable defaults
    as literals on MySQL, since MySQL does not provide defaults as
    valid literals.

  • The emulated offset support in the shared Microsoft SQL Server
    adapter now works better with model classes (or any datasets with

  • Microsoft SQL Server now supports using the WITH clause in delete,
    update, and insert calls.

  • Parsed indexes when connecting to Microsoft SQL Server via JDBC no
    longer include primary key indexes.

  • Dataset#insert_select now returns nil if disable_insert_returning
    is used in the shared PostgreSQL adapter. This makes it work as
    expected with model object creation.

  • Calling Model.set_primary_key with an array of symbols to set
    a composite primary key is now supported. You can also provide
    multiple symbol arguments to do the same thing. Before, specifying
    an array of symbols broke the Model.[] optimization.

  • Literalization of timezones in timestamps now works correctly on

  • FILE and LINE are now used everywhere that eval is called
    with a string, which makes for better backtraces.

  • The native MySQL adapter now correctly handles returning before
    yielding all result sets. Previously, this caused a commands out
    of sync error.

  • Table names in common table expressions are now quoted.

  • The Oracle adapter’s Dataset#except now accepts a hash, giving it
    the same API as the default Dataset#except.

  • When connecting to Microsoft SQL Server via ADO, allow
    Dataset#insert to take multiple arguments.

  • Fractional timestamps are no longer used on ODBC.

  • Schema parsing now works on MSSQL when the database is set to not
    quote identifiers.

  • Timezone offsets are no longer used on Microsoft SQL Server, since
    they only work for the datetimeoffset type.

  • Only 3 fractional digits in timestamps are used in Microsoft SQL
    Server, since an error is raised if the use the datetime type
    with more than that.

  • The integration test suite now has guards for expected failures
    when run on known databases. Expected failures are marked as

Backwards Compatibility

  • Graphing to an previously joined (but not graphed) dataset now
    causes the receiver to be wrapped in a subselect, so if you
    graph a dataset to a previously joined dataset, and then filter
    the dataset referring to tables that were in the joined dataset
    (other than the first table), the SQL produced will probably no
    longer be valid. You should either filter the dataset before
    graphing or use the name of the first source of the joined
    dataset (which is what the subselected is aliased to) if filtering

    In certain cases, this change can cause tables to be aliased
    differently, so if you were graphing previously joined datasets
    and then filtering using the automatically generated aliases, you
    might need to modify your code.

  • The DataObjects (do) adpater no longer supports DataObjects 0.9.x.

  • The Dataset#virtual_row_block_call private instance method has
    been removed.

  • Sequel’s timezone support was significantly refactored, so if you
    had any custom modifications to the timezone support, they might
    need to be refactored as well.

  • The SQL generation code was significantly refactored, so if you
    had any custom modifications in that area, you might need to
    refactor as well.