{ height: 1%; } - Ruby on Rails and User Interface Design

edit

On Rails Migrations and PostgreSQL data types

Posted by Richard White Mon, 30 Jan 2006 02:06:00 GMT

ActiveRecord::Migrations for Ruby on Rails is nice and easy way to keep track of sql changes in a SQL server agnostic way. I won’t get much into the basics of migrations since others have already done a good job of that, but I will say that it has made a mid-project switch from MySQL to PostgreSQL as easy as it really should be.

The problem is of course that this database neutrality comes at a price and of course that price is in the form of a lack of precision when defining your schema. Since migrations supports [all sorts] of databases it also ends up only supporting the lowest common denominator of their abilites.

Case in point, on a current project I was trying to setup migrations from an existing schema. Unfortunately that schema included bigint(int8) id columns, and migrations only offers you :integer and :float in the way of numeric types. Fortunately getting around these problems is trivial in ruby, if you are willing to give up some of the database portability of migrations.

All that is required is to override the method in the PostgreSQL adapter that defines the mappings of data types in migrations to database data types and add a mapping for :bigint.

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
 def native_database_types
 {
   <strong>:primary_key => "bigserial primary key",</strong>
   :string      => { :name => "character varying", :limit => 255 },
   :text        => { :name => "text" },
   :integer     => { :name => "integer" },
   :float       => { :name => "float" },
   :datetime    => { :name => "timestamp" },
   :timestamp   => { :name => "timestamp" },
   :time        => { :name => "time" },
   :date        => { :name => "date" },
   :binary      => { :name => "bytea" },
   :boolean     => { :name => "boolean" },
   <strong>:bigint      => { :name => "int8" }</strong>
 }
 end
end

Those of you that have looked at this method before will notice that I have also changed the :primary_key value from “serial primary key” to “bigserial primary key”. If you are familiar with PostgreSQL, or have just googled PostgreSQL datatypes like I did you will find that bigserial is the equivalent of a bigint with auto increment (or identity depending on where you come from).

Now all there is to do is to include the preceding code in each migration file. I simply created postgre_extensions.rb in the /lib directory of my rails app and added “require ‘postgre_extensions’” to the top of my migration files.