ActiveRecord, deliberately truncate string to db column width

Rails 4 has ActiveRecord and its MySQL adapter configured, so if you try to store an attribute in an AR model in a MySQL db where the attribute string length is too long for the MySQL column constraints, you will get an exception.

Fine! This is much better than Rails3, where it silently truncated the line.

However, sometimes I have an attribute that I clearly want to just truncate to the maximum size allowed by the db, no exception. I'm having trouble finding the best / supported way to do this with AR.

  • Ideally this should happen as soon as the attribute is set, but I would do it on save. (This is not really a "checkout" since I never want to raise, just cropping, but maybe a checkout system is the best way to do this?)

  • Ideally, it automatically calculates the db column width through AR db introspection, so if the db column width changed (on subsequent migration) the truncation limit would change accordingly. But if this is not possible, I will take a strictly limited reduction limit.

  • Ideally this would be a generic AR code that would work with any db, but if there is no good way to do it, I would take the code that only worked for MySQL


source to share

4 answers

You can trim your data before inserting into db with before_save

or before_validation

See Active Callbacks - Ruby on Rails Guides and ActiveRecord :: Callbacks

You can get the information in the table with MODEL.columns

and MODEL.columns_hash

. See ActiveRecord :: ModelSchema :: ClassMethods

For example (not tested):

class User < ActiveRecord::Base
  before_save :truncate_col

  def truncate_col
    col_size = User.columns_hash['your_column'].limit
    self.your_column = self.your_column.truncate(col_size)




I'm sure you can accomplish this with a combination of ActiveRecord and ConnectionsAdapters callbacks. ActiveRecord contains several callbacks that you can override to execute specific logic at different points during the save stream. Since an exception is thrown on save, I would recommend adding your logic to the method before_save

. Using the column ConnectionAdapter, you should be able to define the limit of the column you want to insert, although the logic will most likely be different for row versus ints, etc. At the top of your head, you probably want to implement something like:

class User < ActiveRecord::Base
    def before_save
        limit = User.columns_hash['attribute'].limit
        self.attribute = self.attribute[0..limit-1] if self.attribute.length > limit


The example above is for string, but this solution should work for all connection adapters, assuming they support the limit attribute. Hope this helps.



Here's my own auto-reply that truncates on a set of attributes (path to save). Curious if anyone has any feedback. It seems to work!

# An ActiveRecord extension that will let you automatically truncate
# certain attributes to the maximum length allowed by the DB. 
#     require 'truncate_to_db_limit'
#     class Something < ActiveRecord::Base
#        extend TruncateToDbLimit
#        truncate_to_db_limit :short_attr, :short_attr2
#        #...
# Truncation is done whenever the attribute is set, NOT waiting
# until db save. 
# For a varchar(4), if you do:
#    model.short_attr = "123456789"
#    model.short_attr # => '1234'
# We define an override to the `attribute_name=` method, which ActiveRecord, I think,
# promises to call just about all the time when setting the attribute. We call super
# after truncating the value. 
module TruncateToDbLimit

  def truncate_to_db_limit(*attribute_names)
    attribute_names.each do |attribute_name|
      ar_attr = columns_hash[attribute_name.to_s]

      unless ar_attr
        raise"truncate_to_db_limit #{attribute_name}: No such attribute")

      limit   = ar_attr.limit

      unless limit && limit.to_i != 0
        raise"truncate_to_db_limit #{attribute_name}: Limit not known")

      define_method "#{attribute_name}=" do |val|
        normalized = val.slice(0, limit)





I would like to highlight a few points:

If the datatype your_column

is text

, in Rails 4 User.columns_hash['your_column'].limit

will return nil

. Returns a number in case of int

or varchar


The text data type in MySQL has a storage limit of 64KB. The truncation value is not sufficient if the content has non-ascii characters, such as รง

which require more than 1 byte to store.

I ran into this problem quite recently, here is the fix:

before_save :truncate_your_column_to_fit_into_max_storage_size

def truncate_your_column_to_fit_into_max_storage_size
  return if your_column.blank?

  max_field_size_in_bytes = 65_535

  self.your_column = your_column[0, max_field_size_in_bytes]

  while your_column.bytesize > max_field_size_in_bytes
    self.your_column = your_column[0..-2]




All Articles