How do you make rake db:schema:dump have the charset and collation of the fields in schema.rb?

Question

One of our fields needs to be case sensitive. We can write a migration to change the collation which works fine but this change is not reflected in schema.rb. It will create issues for example when running tests and the cloned test database will not have the collation we want for that field.

We use mysql.

I have searched for a way to make this happen with no results..

I managed to find this on github but not sure how this was accomplished https://github.com/cantino/huginn/blob/db792cdd82eb782e98d934995964809d9e8cb77d/db/schema.rb

Answer 1

As mentioned in @house9's comment, you can use structure.sql instead. Add this to your project's application.rb:

config.active_record.schema_format = :sql

Then run bundle exec rake db:structure:dump to generate the actual SQL structure. This retains charsets and collations (which should ideally be there in schema.rb, but alas).

"structure" is by nature less portable than "schema", but it's usually a good thing for all team members and environments to be using the same database and version anyway.

Answer 2

I think there is no "official" way (provided by Rails or ActiveRecord gems) to accomplish that kind of dump. Following the git history, on the Huginn repo itself, you can find the code you need to achieve this dump. Take a look to this commit: https://github.com/cantino/huginn/commit/db792cdd82eb782e98d934995964809d9e8cb77d

The most relevant code is currently here: https://github.com/cantino/huginn/blob/master/lib/ar_mysql_column_charset/main.rb

So if you need this feature, you'll probably need to copy/paste this extension into your project.

UPDATE

I made a deeper review of Huginn repo (git history and issues), and as you can read in this comment, this functionality was extracted into a gem: https://github.com/kamipo/activerecord-mysql-awesome.