SchemaPlus provides ActiveRecord support for foreign keys, database defined validations and associations.
Clone or download
Pull request Compare This branch is 1 commit ahead, 614 commits behind SchemaPlus:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.




SchemaPlus is an ActiveRecord extension that provides enhanced capabilities for schema definition and querying, including: enhanced and more DRY index capabilities, support and automation for foreign key constraints, and support for views.

For added rails DRYness see also the gems schema_associations and schema_validations


SchemaPlus supports all combinations of:

  • rails 2.3, 3.0, or 3.1, or 3.2

  • MRI ruby 1.8.7, 1.9.2 or 1.9.3

  • PostgreSQL, MySQL (using mysql or mysql2 gem), or SQLite3 (using sqlite3 3.7.7 which has foreign key support)

Support for rails 2.3 will likely be dropped eventually.


Install from via

$ gem install "schema_plus"

or in a Gemfile

gem "schema_plus"


Here some examples that show off the high points. For full details see the RDoc documentation.


With standard rails migrations, you specify indexes separately from the table definition:

# Standard Rails approach...
create_table :parts do |t|
  t.string :name
  t.string :product_code

add_index :parts, :name     # index repeats table and column names and is defined separately
add_index :parts, :product_code, :unique => true

But with SchemaPlus you can specify your indexes when you define each column:

# More DRY way...
create_table :parts do |t|
  t.string :name,           :index => true
  t.string :product_code,   :index => :unique

Options can be provided index using a hash, for example:

t.string :product_code,     :index => { :unique => true, :name => "my_index_name" }

You can also create multi-column indexes, for example:

t.string :first_name
t.string :last_name,        :index => { :with => :first_name }

t.string :country_code
t.string :area_code
t.string :local_number      :index => { :with => [:country_code, :area_code], :unique => true }

If you're using Postgresql, SchemaPlus provides support for conditions, expressions, index methods, and case-insensitive indexes; see doc at SchemaPlus::ActiveRecord::ConnectionAdapters::PostgresqlAdapter and SchemaPlus::ActiveRecord::ConnectionAdapters::IndexDefinition

And when you query column information using ActiveRecord::Base#columns, SchemaPlus analogously provides index information relevant to each column: which indexes reference the column, whether the column must be unique, etc. See doc at SchemaPlus::ActiveRecord::ConnectionAdapters::Column

Foreign Key Constraints

SchemaPlus adds support for foreign key constraints. In fact, for the common convention that you name a column with suffix _id to indicate that it's a foreign key, SchemaPlus automatically defines the appropriate constraint.

You can explicitly specify foreign key constraints, or override the automatic ones, using the :references option to specify the table name (and optionally that table's key column name, if it's not id).

Here are some examples:

t.integer :author_id                                 # automatically references table 'authors', key id
t.integer :parent_id                                 # special name parent_id automatically references its own table (for tree nodes)
t.integer :author, :references => :authors           # non-conventional column name needs :references for a constraint
t.integer :author_id, :references => :authors          # same as automatic behavior
t.integer :author_id, :references => [:authors, :id]   # same as automatic behavior
t.integer :author_id, :references => :people         # override table name
t.integer :author_id, :references => [:people, :ssn] # override table name and key
t.integer :author_id, :references => nil              # don't create a constraint

You can also modify the behavior using :on_delete, :on_update, and :deferrable

t.integer :author_id, :on_delete => :cascade

SchemaPlus also creates foreign keys when used rails' t.references or t.belongs_to< which take the singular of the referenced table name and implicitly create the column suffixed with _id, and it accepts the same arguments.

The foreign key behavior can be configured globally (see Config) or per-table (see create_table).

To examine your foreign key constraints, connection.foreign_keys returns a list of foreign key constraints defined for a given table, and connection.reverse_foreign_keys returns a list of foreign key constraints that reference a given table. See SchemaPlus::ActiveRecord::ConnectionAdapters::ForeignKeyDefinition.


SchemaPlus provides support for creating and dropping views. For example:

create_view :uncommented_posts, "SELECT * FROM posts LEFT OUTER JOIN comments ON comments.post_id = WHERE IS NULL"
drop_view :uncommented_posts

ActiveRecord works with views the same as with ordinary tables. That is, for the above view you can define

class UncommentedPosts < ActiveRecord::Base

Column Defaults

SchemaPlus allows expressions to be used as column defaults. For example:

t.datetime :seen_at, :default => :now

resolves to

(DATETIME('now')) # SQLite3
invalid # MySQL

Arbitrary SQL expressions can also be specified by passing a hash with an :expr parameter:

t.datetime :seen_at, :default => { :expr => 'NOW()' }

In MySQL only the TIMESTAMP column accepts SQL column defaults and Rails uses DATETIME, so this is not possible at this time.

Standard default values can be specified verbosely:

t.datetime :seen_at, :default => { :value => "2011-12-11 00:00:00" }

But the standard syntax will still work as usual:

t.datetime :seen_at, :default => "2011-12-11 00:00:00"

Schema Dump and Load (schema.rb)

When dumping schema.rb, SchemaPlus orders the views and tables in the schema dump alphabetically, but subject to the requirement that each table or view be defined before those that depend on it. This allows all foreign key constraints to be defined within the scope of the table definition. (Unless there are cyclical dependencies, in which case some foreign keys constraints must be defined later.)

Also, when dumping schema.rb, SchemaPlus dumps explicit foreign key definition statements rather than relying on the auto-creation behavior, for maximum clarity and for independence from global config. And correspondingly, when loading a schema, i.e. with the context of ActiveRecord::Schema.define, SchemaPlus ensures that auto creation of foreign keys is turned off regardless of the global setting. But if for some reason you are creating your schema.rb file by hand, and would like to take advantage of auto-creation of foreign keys, you can re-enable it:

ActiveRecord::Schema.define do
    SchemaPlus.config.foreign_keys.auto_create = true
    SchemaPlus.config.foreign_keys.auto_index = true

    create_table ...etc...


  • Recent Release notes:

    * 0.4.0 - add :force for create_view (suggested by greglazarev[]).  cleanups by betelgeuse[]
    * 0.3.4 - bug fix: regression causing :default => false to be ignored
    * 0.3.3 - bug fix: properly handle boolean defaults in mysql
    * 0.3.2 - bug fix: make sure rake db:schema:load initializes schema_plus 
    * 0.3.1 - bug fix for PostgreSQL schema dump after change_column_default(... nil)
    * 0.3.0 - add :default => expressions (Thanks to Luke Saunders).  support rails 3.2 and ruby 1.9.3
    * 0.2.1 - suppress duplicate add_indexes.  compatibility with rails 3.2.0.rc2
  • SchemaPlus is derived from several “Red Hill On Rails” plugins originally created by harukizaemon ( with later contributions from

  • SchemaPlus was created in 2011 by Michał Łomnicki and Ronen Barzel


SchemaPlus is tested using rspec and rvm, with some hackery to test against multiple versions of rails and ruby and db adapters. To run the tests, after you've forked & cloned: Make sure you have Postgresql and MySQL running. Create database user “schema_plus” with permissions for database “schema_plus_unittest”. Then:

$ cd schema_plus
$ bundle install
$ rake postgresql:build_databases
$ rake mysql:build_databases
$ ./runspecs --install  # do this once, it runs 'bundle install' for all versions (slow)
$ ./runspecs # as many times as you like

See ./runspecs --help for more options. You can also manually pick a specific version of rails and ruby to use, such as:

$ rvm use 1.9.2
$ export BUNDLE_GEMFILE=gemfiles/Gemfile.rails-3.1
$ bundle exec rake spec

And you can run the specs for a specific adapter:

$ rake postgresql:spec # to run postgresql tests only
$ rake mysql:spec # to run mysql tests only
$ rake mysql2:spec # to run mysql2 tests only
$ rake sqlite3:spec # to run sqlite3 tests only

If you're running ruby 1.9, code coverage results will be in coverage/index.html – it should be at 100% coverage.


This gem is released under the MIT license.