Rails: Force empty string to NULL in the database
Solution 1
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...
Solution 2
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
Solution 3
Another option is to provide custom setters, instead of handling this in a hook. E.g.:
def foo=(val)
super(val == "" ? nil : val)
end
Solution 4
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
Solution 5
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.
Comments
-
Thilo about 4 years
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.
-
Thilo over 12 yearsLooks good, thanks. Would it make sense to build this as a gem while I'm at it? I can't imagine I'm the only one with this problem.
-
Chowlett over 12 yearsIn your mixin code - does simply writing
NormalizeBlankValues
in theUser
model work? Don't you need toinclude NormalizeBlankValues
? -
Simone Carletti over 12 years@Chowlett It was a typo. Fixed.
-
Simone Carletti over 12 years@Thilo I wrote this piece of code in less than a couple of minutes. If a gem would exist, I wouldn't probably add the overhead of a new Gem just for such this code. If you want to package it as a gem you can do, but to me this solution seems too localized to your problem.
-
Thilo over 11 yearsI ended up using attribute_normalizer, which does nilification and a lot more besides, such as stripping whitespace, out of the box.
-
Totach over 11 yearsbefore_filter is a controller method. I had to change to before_save to support ActiveRecords. Also, the method doesn't treat well booleans. Consider adding a test case if the column type is string (and maybe others) before setting to nil
-
Jigar Bhatt almost 10 yearsThank you so much, this is very useful for me.
-
serghei over 9 years@SimoneCarletti but one thing..
false.present? == false
,false.to_s.present? == true
. It is important for model fields, which by default should be boolean (e.g. false) -
Cruz Nunez over 7 yearsAlso, instead of writing
self[column].present? || self[column] = nil
you could use a the.presence
method like so:self[column] = self[column].presence
-
Chris Edwards over 7 yearsSlight style update:
self[column] = nil if self[column].is_a?(String) && self[column].empty?
-
Fabiano Arruda over 6 yearsThis solution is much simpler (assuming you want to do this for a specific field).
-
jajavoli over 2 yearsThis solution is so simple and idiomatic that I'm kicking myself for not thinking of it and using callbacks instead