How to specify a collation for a column when creating a model with rails

Question

Quick question: How to specify a collation for a specific column when creating a model with rails?

I haven't found any option in the rails API doc but I think it's very important to explicit declare a collation for some attributes.

Let's say that the default collation is utf8_unicode_ci. But when creating an Users model the password field's collation should be utf8_bin.

So, when using rails like this

rails g model Users username:string password:string

you can't specify password to use utf8_bin collation.

Any way to do it?

EDIT: The generated SQL should be something like this:

CREATE TABLE `user` (
  `id`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  COMMENT 'Unique ID',
  `username`         VARCHAR(32) NOT NULL                      COMMENT 'Nick/username',
  `password`         VARCHAR(32) COLLATE utf8mb4_bin           COMMENT 'Encoded password',

  PRIMARY KEY(`id`),
  INDEX(`username`)
);

I don't care if you can't do it directly via a command and you have to modify the db/timestamp_create_users.rb file. But the point is being able to use the rails migrations.

Answer 1

Sadly it seems that rails does not provide any high level way to set column-level collation so you have to fall back to SQL at least a little. Here's how I did it:

class CreateUsers < ActiveRecord::Migration                                     
  def change                                                                    
    create_table :users do |t|                                                  
      t.string   :password                                                      
    end                                                                         

    reversible do |dir|                                                         
      dir.up {                                                                  
        change_column :users, :password, "VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin"
      }                                                            
    end                                                                         
  end                                                                           
end   

The reversible block is there just to enable rollbacks because rails would not know how to revert change_column without the original value.

Then you can check the collation on the mysql cli with:

SHOW TABLE STATUS;

you should see your default collation there - probably utf8_unicode_ci. Then do:

SHOW FULL COLUMNS FROM users;

And there you should see utf8_bin at the password field.