How do I create an UNSIGNED INT for MySQL in a Rails 5 migration?

Question

With activerecord (< 5.0, >= 3.2) I was able to use the activerecord-mysql-unsigned gem to create an UNSIGNED INT in my MySQL database, but there's been no updates to that gem and I can't find any documentation regarding native support for that in Rails 5.

Is there an options hash or something that can be called in the add_column method which allows this to be done?

Boarfish

Answer 1

There is an option for unsigned integer, bigint, decimal, and float in the schema adapter for MySQL as of Rails 5.1.x

Something like this in your migration will work in Rails 5.1.4

  def up
    create_table :unsigned_columns do |t|

      t.integer "positive", :unsigned => true

      t.timestamps
    end
  end

Overanxious

Is there a way to do this by default. i.e. Like overwriting the NATIVE_DATABASE_TYPES in Rails 3 and 4. So that by default new tables are created with a BigInt Unsigned id column? – Niel

@JoshuaPinter This is one of those places where I would not recommend overwriting the defaults and instead be explicit about these modifications in your migrations. You could modify a generator to automate a default change. Thus not having to write the migration by hand every time. edgeguides.rubyonrails.org/… – Overanxious

I really do think that unsigned should be the default for primary keys when using MySQL in Rails. I created a GitHub Issue to indicate this preference and it seems that Rafael agrees so I'll be writing a PR to get primary keys to be unsigned bigint as the default in Rails 6.2. Feel free to show your support on the issue: github.com/rails/rails/issues/40967 – Niel

I agree unsigned should be the default. Looks like from that GH Issue you already have a feature request to have it changed. The only thing I would add here is that performance regression testing is done. Roughly 4 billion id objects is good enough for most small and medium projects. Moving from 4 byte storage to 8 byte storage may linearly or even exponentially increase column traversal and retrieval times. – Overanxious

Definitely agree with that. But the default column type is already bigint. That was changed in Rails 5.1 (github.com/rails/rails/pull/26266). The only change I would be making is using unsigned instead of signed. That shouldn't have any change in performance, right? – Niel

Ah thank you for referencing that pull. I have a hunch unsigned primary key traversal should be faster than signed, but even so more than likely minuscule at best. Still worth a benchmark in my opinion. – Overanxious

You might be right. When I get around to that PR, I'll see if I can add some benchmarks for comparison. I love me a good benchmark! – Niel

works with 5.0.7 too – Auxin

Answer 2

If are using rails > 5.1 and you want to have all your primary keys being generated with unsigned integer as default type, all that you have to do is adapt your generator in your config/application.rb , adding the following code:

config.generators do |g|
      # ... other configurations
      g.orm :active_record, primary_key_type: :unsigned_integer
end

With that a call to rails generate model foo bar:string will create the id as unsigned.

Openwork