Rails: Force empty string to NULL in the database

Is there an easy way (i.e. a configuration) to force ActiveRecord to save empty strings as NULL in the DB (if the column allows)?

The reason for this is that if you have a NULLable string column in the DB without a default value, new records that do not set this value will contain NULL, whereas new records that set this value to the empty string will not be NULL, leading to inconsistencies in the database that I'd like to avoid.

Right now I'm doing stuff like this in my models:

before_save :set_nil

def set_nil
  [:foo, :bar].each do |att|
    self[att] = nil if self[att].blank?
  end
end

which works but isn't very efficient or DRY. I could factor this out into a method and mix it into ActiveRecord, but before I go down that route, I'd like to know if there's a way to do this already.

Yes, the only option at the moment is to use a callback.

before_save :normalize_blank_values

def normalize_blank_values
  attributes.each do |column, value|
    self[column].present? || self[column] = nil
  end
end

You can convert the code into a mixin to easily include it in several models.

module NormalizeBlankValues
  extend ActiveSupport::Concern

  included do
    before_save :normalize_blank_values
  end

  def normalize_blank_values
    attributes.each do |column, value|
      self[column].present? || self[column] = nil
    end
  end

end

class User
  include NormalizeBlankValues
end

Or you can define it in ActiveRecord::Base to have it in all your models.

Finally, you can also include it in ActiveRecord::Base but enable it when required.

module NormalizeBlankValues
  extend ActiveSupport::Concern

  def normalize_blank_values
    attributes.each do |column, value|
      self[column].present? || self[column] = nil
    end
  end

  module ClassMethods
    def normalize_blank_values
      before_save :normalize_blank_values
    end
  end

end

ActiveRecord::Base.send(:include, NormalizeBlankValues)

class User
end

class Post
  normalize_blank_values
  
  # ...
end

Try if this gem works:

https://github.com/rubiety/nilify_blanks > Provides a framework for saving incoming blank values as nil in the database in instances where you'd rather use DB NULL than simply a blank string... > In Rails when saving a model from a form and values are not provided by the user, an empty string is recorded to the database instead of a NULL as many would prefer (mixing blanks and NULLs can become confusing). This plugin allows you to specify a list of attributes (or exceptions from all the attributes) that will be converted to nil if they are blank before a model is saved. > Only attributes responding to blank? with a value of true will be converted to nil. Therefore, this does not work with integer fields with the value of 0, for example...

Another option is to provide custom setters, instead of handling this in a hook. E.g.:

def foo=(val)
  super(val == "" ? nil : val)
end

My suggestion:

# app/models/contact_message.rb
class ContactMessage < ActiveRecord::Base
  include CommonValidations
  include Shared::Normalizer
end


# app/models/concerns/shared/normalizer.rb
module Shared::Normalizer
  extend ActiveSupport::Concern

  included do
    before_save :nilify_blanks
  end

  def nilify_blanks
    attributes.each do |column, value|
      # ugly but work
      # self[column] = nil if !self[column].present? && self[column] != false

      # best way
      #
      self[column] = nil if self[column].kind_of? String and self[column].empty?
    end
  end

end

Sorry for necroposting, but I didn't find exact thing here in answers, if you need solution to specify fields which should be nilified:

module EnforceNil
  extend ActiveSupport::Concern

  module ClassMethods
    def enforce_nil(*args)
      self.class_eval do
        define_method(:enforce_nil) do
          args.each do |argument|
            field=self.send(argument)
            self.send("#{argument}=", nil)  if field.blank?
          end
        end           
        before_save :enforce_nil
      end
    end
  end
end

ActiveRecord::Base.send(:include, EnforceNil)

This way:

class User
  enforce_nil :phone #,:is_hobbit, etc  
end

Enforcing certain field is handy when let's say you have field1 and field2. Field1 has unique index in SQL, but can be blank, so you need enforcement(NULL considered unique, "" - not by SQL), but for field2 you don't actually care and you have already dozens of callbacks or methods, which work when field2 is "", but will dig your app under the layer of errors if field2 is nil. Situation I faced with.

May be useful for someone.

Strip Attributes Gem

There's a handy gem that does this automatically when saving a record, whether that's in a user form or in the console or in a rake task, etc.

It's called strip_attributes and is extremely easy to use, with sane defaults right out of the box.

It does two main things by default that should almost always be done:

1. Strip leading and trailing white space:

    " My Value " #=> "My Value"
   

2. Turn empty Strings into NULL:

    ""  #=> NULL
    " " #=> NULL
   

Install

You can add it to your gem file with:

gem strip_attributes

Usage

Add it to any (or all) models that you want to strip leading/trailing whitespace from and turn empty strings into NULL:

class DrunkPokerPlayer < ActiveRecord::Base
  strip_attributes
end

Advanced Usage

There are additional options that you can pass on a per-Model basis to handle exceptions, like if you want to retain leading/trailing white space or not, etc.

You can view all of the options on the GitHub repository here:

https://github.com/rmm5t/strip_attributes#examples

I use the [attribute normalizer][1] gem to normalize attributes before they into the db.

[1]: https://github.com/mdeering/attribute_normalizer "attribute normalizer"