Is there documentation for the Rails column types?

Question

I'm looking for more than the simple type listing that is found on this page:

:primary_key, :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean

But is there any documentation that actually defines these fields?

Specifically:

• What's the difference between :string and :text?
• Between :float and :decimal?
• What are the distinguishing features of :time, :timestamp, and :datetime?

Are the nuances of these types documented anywhere?

EDIT: Points of DB-platform implementations are irrelevant to the question I'm trying to ask. If, say, :datetime does not have a defined intended meaning in Rails documentation, then what do db-adapter-writers go by when choosing a corresponding column type?

Answer 1

Guidelines built from personal experience:

• String:
• Limited to 255 characters (depending on DBMS)
• Use for short text fields (names, emails, etc)
• Text:
• Unlimited length (depending on DBMS)
• Use for comments, blog posts, etc. General rule of thumb: if it's captured via textarea, use Text. For input using textfields, use string.
• Integer:
• Whole numbers
• Float:
• Decimal numbers stored with floating point precision
• Precision is fixed, which can be problematic for some calculations; generally no good for math operations due to inaccurate rounding.
• Decimal:
• Decimal numbers stored with precision that varies according to what is needed by your calculations; use these for math that needs to be accurate
• See this post for examples and an in-depth explanation on the differences between floats and decimals.
• Boolean:
• Use to store true/false attributes (i.e. things that only have two states, like on/off)
• Binary:
• Use to store images, movies, and other files in their original, raw format in chunks of data called blobs
• :primary_key
• This datatype is a placeholder that Rails translates into whatever primary key datatype your database of choice requires (i.e. serial primary key in postgreSQL). Its use is somewhat complicated and not recommended.
• Use model and migration constraints (like validates_uniqueness_of and add_index with the :unique => true option) instead to simulate primary key functionality on one of your own fields.
• Date:
• Stores only a date (year, month, day)
• Time:
• Stores only a time (hours, minutes, seconds)
• DateTime:
• Stores both date and time
• Timestamp
• Stores both date and time
• Note: For the purposes of Rails, both Timestamp and DateTime mean the same thing (use either type to store both date and time). For the TL;DR description of why both exist, read the bottom paragraph.

These are the types about which confusion often exists; I hope this helps. I really don't know why there isn't official documentation about these. Also, I imagine these database adapters you referred to were written by the same people who wrote Rails, so they probably didn't need any documentation to go by when they were writing the adapters. Hope this helps!

Note: the presence of both :DateTime and :Timestamp, from what I can find, is included by Rails mostly for compatibility with database systems. For instance, MySQL's TIMESTAMP datatype is stored as a unix timestamp. Its valid range goes from 1970 to 2038, and the time is stored as the number of seconds that have elapsed since the last epoch, which is supposedly standard, but in practice can differ from system to system. MySQL later introduced the DATETIME datatype, which is stored as seconds (with optional fractional seconds as of 5.6.4) since "1000-01-01 00:00:00", at the cost of a size increase. The TIMESTAMP datatype was retained for backwards compatibility. Other database systems went through similar evolutions. Rails recognized that multiple standards existed, and provided interfaces to both. However, Rails ActiveRecord defaults both :Timestamp and :DateTime to UTC dates stored in MySql's DATETIME, so it makes no functional difference to Rails programmers. These exist so that users who wish to differentiate between the two can do so. (For a more in-depth explanation, see this SO answer).

Answer 2

From Rails master branch source code I found:

abstract mysql_adapter

#activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb

  NATIVE_DATABASE_TYPES = {
    primary_key: "bigint auto_increment PRIMARY KEY",
    string:      { name: "varchar", limit: 255 },
    text:        { name: "text", limit: 65535 },
    integer:     { name: "int", limit: 4 },
    float:       { name: "float" },
    decimal:     { name: "decimal" },
    datetime:    { name: "datetime" },
    timestamp:   { name: "timestamp" },
    time:        { name: "time" },
    date:        { name: "date" },
    binary:      { name: "blob", limit: 65535 },
    boolean:     { name: "tinyint", limit: 1 },
    json:        { name: "json" },
  }

  # Maps logical Rails types to MySQL-specific data types.
  def type_to_sql(type, limit = nil, precision = nil, scale = nil, unsigned = nil)
    sql = case type.to_s
    when 'integer'
      integer_to_sql(limit)
    when 'text'
      text_to_sql(limit)
    when 'blob'
      binary_to_sql(limit)
    when 'binary'
      if (0..0xfff) === limit
        "varbinary(#{limit})"
      else
        binary_to_sql(limit)
      end
    else
      super(type, limit, precision, scale)
    end

    sql << ' unsigned' if unsigned && type != :primary_key
    sql
  end    

# and integer ...

  def integer_to_sql(limit) # :nodoc:
    case limit
    when 1; 'tinyint'
    when 2; 'smallint'
    when 3; 'mediumint'
    when nil, 4; 'int'
    when 5..8; 'bigint'
    else raise(ActiveRecordError, "No integer type has byte size #{limit}")
    end
  end

 # and text ..

  def text_to_sql(limit) # :nodoc:
    case limit
    when 0..0xff;               'tinytext'
    when nil, 0x100..0xffff;    'text'
    when 0x10000..0xffffff;     'mediumtext'
    when 0x1000000..0xffffffff; 'longtext'
    else raise(ActiveRecordError, "No text type has byte length #{limit}")
    end
  end

# and binary ...

    def binary_to_sql(limit) # :nodoc:
      case limit
      when 0..0xff;               "tinyblob"
      when nil, 0x100..0xffff;    "blob"
      when 0x10000..0xffffff;     "mediumblob"
      when 0x1000000..0xffffffff; "longblob"
      else raise(ActiveRecordError, "No binary type has byte length #{limit}")
      end
    end

the super in type_to_sql method

#activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
  def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
    type = type.to_sym if type
    if native = native_database_types[type]
      column_type_sql = (native.is_a?(Hash) ? native[:name] : native).dup

      if type == :decimal # ignore limit, use precision and scale
        scale ||= native[:scale]

        if precision ||= native[:precision]
          if scale
            column_type_sql << "(#{precision},#{scale})"
          else
            column_type_sql << "(#{precision})"
          end
        elsif scale
          raise ArgumentError, "Error adding decimal column: precision cannot be empty if scale is specified"
        end

      elsif [:datetime, :time].include?(type) && precision ||= native[:precision]
        if (0..6) === precision
          column_type_sql << "(#{precision})"
        else
          raise(ActiveRecordError, "No #{native[:name]} type has precision of #{precision}. The allowed range of precision is from 0 to 6")
        end
      elsif (type != :primary_key) && (limit ||= native.is_a?(Hash) && native[:limit])
        column_type_sql << "(#{limit})"
      end

      column_type_sql
    else
      type.to_s
    end
  end