How Does Rails Determine the Data Type for a SQL Field?

November 1, 2021

Say I have a table like:

create_table :users do |t|
  t.string :username, null: false
  t.datetime :created_at, null: false
end

When I retrieve these fields from the database, Rails helpfully provides the values via classes that are appropriate for the data involved:

user = User.first
user.username.class
 => String
user.created_at.class
 => ActiveSupport::TimeWithZone

How does it know which classes to use?

Rails loads the column definitions when the app boots, so it knows the data type of the column for each attribute:

User.columns.detect { |c| c.name == 'created_at' }.type
 => :datetime

And all the data types from the database are registered into a type_map. Reviewing the private type_map on the connection shows that a datetime field will map to an ActiveRecord::Type::DateTime object:

User.connection.send(:type_map).lookup(:datetime)
 => #<ActiveRecord::Type::DateTime:0x00007fb314d4c0a0 @precision=0, @scale=nil, @limit=nil>

ActiveRecord::Type::DateTime inherits from ActiveModel::Type::DateTime. This class uses the ActiveModel::Type::Helpers::TimeValue module which will call in_time_zone to cast the value as an ActiveSupport::TimeWithZone object:

value = "2000-01-02T08:05:00.000-05:00"
value.in_time_zone.class
 => ActiveSupport::TimeWithZone

Rails decides how to cast values based on which column definitions the SQL field names line up with. You can even get into trouble if you accidentally use a column alias that matches a column name on the table:

# User ID 1 has a username of "Test"
User.where(id: 1).select('username AS my_alias').first.my_alias
 => "Test"  # The alias "my_alias" works and returns a String
User.where(id: 1).select('username AS created_at').first.created_at
 => nil     # Rails incorrectly tries turn "Test" into an ActiveSupport::TimeWithZone object

# User ID 2 has a username of "20300102"
User.where(id: 2).select('username AS created_at').first.created_at
 => Tue, 01 Jan 2030 18:00:00 CST -06:00  # Rails incorrectly casts the username as an ActiveSupport::TimeWithZone object

What about raw SQL statements? Say that we want to get the max created_at value and, instead of using User.maximum(:created_at), we use this SQL:

result = User.select('MAX(created_at) AS max_created_at').take
result.max_created_at.class
 => Time

Here the result is a Time instead of an ActiveSupport::TimeWithZone. What's going on? And how did it even know that this SQL result should be represented as a time?

In this case, Rails didn't do anything. The database adapter did all the work. Since I'm using MySQL, the mysql2 adapter determines the value. We could just use this adapter directly without Rails:

client = Mysql2::Client.new(
  host: 'localhost',
  port: 3306,
  username: 'my_user',
  password: 'my_password',
  database: 'my_database'
)
result = client.query('SELECT MAX(created_at) AS max_created_at FROM users LIMIT 1')
result.class
 => Mysql2::Result
result.first['max_created_at'].class
 => Time

It even knows the appropriate type when there's no column involved:

result = client.query('SELECT CAST("2021-11-01" AS datetime) AS my_datetime')
result.first['my_datetime'].class
 => Time

So how does the database adapter know the data type? It uses the MySQL C API to retrieve the field types. The mysql_fetch_fields function returns the field structure for a result set. The adapter can use this to decide how it should cast values into plain Ruby objects like Time. This casting is optional and can be skipped if desired.

To summarize:

  • Rails will attempt to match the field names from the SQL result with the column definitions to determine the data type. Be careful with aliases.
  • For values that don't match up with the column definitions, the database adapter will determine the data type based on the field structure provided by the database.

References

chadrschroeder
Copyright Chad Schroeder © 2024
A squirrelly image