Allow custom column types in your Rails migrations

posted: April 7th, 2007 · by: Sven

in: Programming · tagged as: column, database, migrations, patch, rails, ruby, types · 12 comments »

We’ll all admit it. Rails’ migrations rock. Totally.

But when you try to change a database column to some vendor specific type (like varbinary for MySQL) that Rails doesn’t implement you notice that Rails doesn’t hand over control that easily here like it does elsewhere in ActiveRecord. You’re just not supposed to use any custom types.

This article explains how to easily change that behaviour.

There’s a workaround that allows you to append column definitions at the end (!) of your table definition:


class CreateForms < ActiveRecord::Migration
  def self.up
    create_table :cards do |t|
      # ...
      t.columns << 'value varbinary(255)'   
    end
  end
end

Obviously this might not be what everbody really wants. If you’re like me you prefer to keep your stuff tidy … and the column order in my tables therefor definitely matters.

No problem: Rails plugins to the rescue here. After some digging through the ActiveRecord source it turns out that it’s pretty easy to convince Rails to accept not only predefined column types (like :string) but also wildly arbitrary strings.

So, how about the following code?


ActiveRecord::ConnectionAdapters::SchemaStatements.module_eval do       
  def type_to_sql_with_custom_type(type, *params)
    return type unless native_database_types.has_key? type
    type_to_sql_without_custom_type(type, *params)
  end
  alias_method_chain :type_to_sql, :custom_type      
end

SchemaStatements#type_to_sql tries to look up the column type (like :string) and throws an error if it doesn’t find it in the native_database_types. What we’re doing with that unbelievable hack above is nothing else than silently navigate our custom column definition around this face check so that it gets inserted into the resulting SQL code literally.

So with this (not so) ugly hack plugin applied you can now do things like this:


class CreateForms < ActiveRecord::Migration
  def self.up
    create_table :cards do |t|
      t.column :value, 'varbinary(255)'   
    end
  end
end

… and use whatever column order you like :-)

Go, get it

Yes, you can download this stuff. Standing in your Rails home directory point your script/plugin installer to:


script/plugin install http://svn.artweb-design.de/stuff/rails/migration_custom_column_types/

12 Comments

jujudellago said February 14th, 2008 at 10:10 AM ¶

Sven !

you saved me again !! you are as cool as Fonzie ! I had a bug with a system that imports datas with excel sheets, values from the sheet are compared to the database before save to avoid duplications, and it gave me errors like

Mysql::Error: #HY000Illegal mix of collations (latin1swedishci,IMPLICIT) and (utf8generalci,COERCIBLE)

quickly found out that a binary field could help this, searched a bit, back to your blog, and tadaaaa :)

thanks a lot !

will definitely buy you a big beer next time I come to berlin !

cheers
Sven said February 16th, 2008 at 02:38 PM ¶

Hey jujudellago!

Fine, be sure to drop me a note when you come to Berlin next time and we’ll be having some beer and fun :)
jujudellago said February 20th, 2008 at 07:08 PM ¶

That’s for sure ! and soon I hope :)

I just had to digg into the mysql docs to solve a little problem using this plugin, for a simple search form based on a sql query. As it might be useful to someone who’ll read this article, I’ll post it here…

I used to have a sql condition like this in my controller:

sq=params[:search].downcase sql_conditions=”lower(items.name) like ‘%#{sq}%’ ”

works good anywhere I use the regular VARCHAR type, but it doesn’t work with VARBINARY types, a conversion is needed for that, so I used the CONVERT mysql function:

sq=params[:search] sql_conditions=”CONVERT(items.name USING utf8) like ‘%#{s}%’ ”

not even need to downcase searched word, or “lower” in mysql, the search becomes case insensitive :)

cheers
Sven said February 21st, 2008 at 12:35 PM ¶

Thanks for posting this tip here, jujudellago.

I’m sure this will prove useful :)
Dave said March 24th, 2008 at 05:19 PM ¶

Great tips! Does this work with the new Rails 2.0 migration formats?
Sven said May 11th, 2008 at 06:16 PM ¶

Hi Dave,

if you mean by “new Rails 2.0 migration formats” the “sexy” migrations a la

t.string :title

… then, no, that won’t work. It might be an interesting idea to extend the plugin for that if possible.

If you mean Rails edge/2.1’s timestamped migrations then, yes, that works.

hth :)
Mark C said April 14th, 2010 at 05:41 PM ¶

Rails migrations blow. Totally.

Seriously, how afraid of SQL do you need to be to hand over control over your database schema to Rails? It’s pathetic.
jack said January 23rd, 2011 at 11:39 AM ¶

thanks for that headsup. That’s a useful tip! I’ve never ran into that, but for sure that’s something quite some people will need a solution for. cheap vps
chat said March 31st, 2011 at 07:33 PM ¶

This code helped with the rails migration:

The following cleaned up the issue:

Dependencies.loadoncepaths -= Dependencies.loadoncepaths.select{|path| \ path =~ %r(^#{File.dirname(FILE)}) }
Okey oyunu said May 12th, 2011 at 04:05 PM ¶

Thanks for this article. Tüm dünya artik okey oyunu oynuyor. Yillardir bir çok oyun programi olmasina ragmen, içlerinden en güzeli olarak nitelendirebilecegimiz tek bir site göze çarpmaktadir. Diger tüm okey oyunu programlarinin aksine ücretsiz olmasi ve 3 boyutlu olarak hizmet vermesi mükemmel bir gelismedir. Sizlerde www.okey-oyunu.com adresinden bu essiz okey oyununu indirebilirsiniz. Kullanimi çok basit ve Türkçe dil seçenegi ile kolaylikla oyuna baslayabilirsiniz. Ister kendi ülkenizden, isterseniz dünyanin tüm farkli bölgelerinden dilediginiz oyun odalarini seçerek, oyuna hemen baslayabilirsiniz. Okey oyunu oynamak için artik arkadas bile aramaniza gerek kalmadan, bilgisayarinizdan 100 binlerce üye ile online olarak okey oyununu oynamanin zevkine varabilirsiniz.
porno said May 22nd, 2011 at 01:33 PM ¶

I do agree with all of the ideas you have presented in your post. They’re really convincing and will definitely work. Still, the posts are too short for newbies. Could you please extend them a bit from next time? Thanks for the post.
porno said May 22nd, 2011 at 02:18 PM ¶

good comment. thanks you friends.

I’ve surfed the net more than three hours today, however, I haven’t found such useful information. Thanks a lot, it is really useful to me

Sorry, comments are closed for this article.

Allow custom column types in your Rails migrations

Go, get it

12 Comments

About me

Topics

Subscribe

Tags

Search

Archives »

Admin