How to create a column of type tinyint(2) or tinyint(3) in Ruby on Rails?

Question

In Ruby on Rails, the following code in a migration creates a column of type tinyint(4) in MySQL:

create_table :great_table do |t|
    t.integer :step_position, :limit => 1 #tinyint
end

How would I create a column of type tinyint(2) or tinyint(3)?

Chrysarobin

I'd discourage you from using tinyint in the first place. It's overly-specific. I would venture to guess that it's actually LESS performant than using a plain integer field, as well. It might save you a negligible amount of space per-record, but I'm not even sure that's the case. I think you'd be hard-pressed to convince me that there was a reason to ever use it. – Popper

Answer 1

For tinyint(2)

create_table :great_table do |t|
  t.integer :step_position, :limit => 2
end

For tinyint(3)

create_table :great_table do |t|
  t.integer :step_position, :limit => 3
end

Entoblast

That does not create tinyints. A tinyint uses 1 byte for storage by definition. Here you're using 2 bytes (smallint) and 3 bytes (mediumint). dev.mysql.com/doc/refman/8.0/en/storage-requirements.html dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html – Foreyard

Answer 2

According to what I can see in the source code of the gem, you can't:

     # File activerecord/lib/active_record/connection_adapters/mysql_adapter.rb, line   540  
     540:       def type_to_sql(type, limit = nil, precision = nil, scale = nil)
     541:         return super unless type.to_s == 'integer'
     542: 
     543:         case limit
     544:         when 1; 'tinyint'
     545:         when 2; 'smallint'
     546:         when 3; 'mediumint'
     547:         when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
     548:         when 5..8; 'bigint'
     549:         else raise(ActiveRecordError, "No integer type has byte size #{limit}")
     550:         end
     551:       end

type_to_sql

Stralsund

Not sure this is a relevant snippet of code; what's in the superclass for this? Migrations do support a :tinyint type. This snippet of code is where Rails decides to ignore your :integer type specification and use an alternate type to represent smaller/larger integer values when both :integer and :limit are specified. – Popper

Afraid you are wrong, type_to_sql is called from add_column. You can see that in the code: api.rubyonrails.org/v2.3.8/classes/ActiveRecord/… – Stralsund

I think I may be wrong about there being any support for a tinyint type in the first place (other than indirectly due to an integer and limit combo). The code above is certainly applied when the type is integer. – Popper

Answer 3

There is no such thing as tinyint(4) in MySQL in the first place. tinyint is a one byte signed integer. You can check all integer types in the docs. You may see something like tinyint(1) even in the Rails source code, but I think it's a tautology as tinyint already implies one byte storage.

The Rails way to declare TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT in a migration is by using limit: with the appropriate byte size as can be seen in the source code.

Beware that Rails will treat one-byte integers as booleans by default though, as can be seen from the above link.

Millard

Actually, the number between parentheses has nothing to do with the number of bytes used for storage. It just specifies a display width, which applications displaying this number may or may not choose to honour. dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html – Foreyard

Answer 4

You can use :tinyint as the column type in a rails migration, and write limit: 2 or limit: 3 as written before.

Using your example:

create_table :great_table do |t|
    t.tinyint :step_position, :limit => 1
end

should work.

Coligny

Hey buddy, You cannot use tinyint in migration, infect it should have integer and limit 1 – Campney

@Campney Providing add_column :table_name, :column_name, :tinyint doesn't raise any exception and seems to go perfectly fine through the migration. I can imagine t.tinyint working in the same way. It could be that this is since the newer versions though, I've tested adding a tinyint column in Ruby on Rails 5.1. – Ornithic