article illustration

Two Ways To Save Booleans In Database With Ruby-On-Rails

Marc Chamberlin
Marc Chamberlin
28 Apr 2024 3 min

Booleans are the most simple data type in programming. They can only have two values: true or false.

They are often used in forms to represent a yes/no question or to specify whether or not we want to be spammed when signing up on a website.

The most straightforward way to save this information would be to use a boolean column in the database. However, there is another way to save booleans in the database that is less known (despite widely used) but can be very useful in some cases: using a timestamp column.

Using A Boolean Column

With Ruby-on-Rails, it’s technically possible to store 3 values in a boolean column: true, false and nil. This can be useful in some cases, for example when we want to differentiate between users who have answered “yes” (true), those who have answered “no” (false) and those who have not yet answered (nil).

# == Schema Information
#
# Table name: weeks
#
#  id          :bigint      not null, primary key
#  monday      :boolean
#  tuesday     :boolean
#  wednesday   :boolean
#  thursday    :boolean
#  friday      :boolean
#  created_at  :datetime    not null
#  updated_at  :datetime    not null

class Week < ApplicationRecord
  def available_on(day)
    response = send(day)
    if response.nil?
      "Not yet answered"
    elsif response
      "Yes"
    else
      "No"
    end
  end
end

class WeeksController < ApplicationController
  def index
    @monday_available_weeks = Week.where(monday: true)
    @tuesday_unavailable_weeks = Week.where(tuesday: false)
    @friday_unanswered_weeks = Week.where(friday: nil)
  end
end

However, in some cases we want to restrict the possible values to only 2 states. For example, if we want to store the confirmation status of a user, we don’t really want to have to deal with the case where the user is neither confirmed nor unconfirmed. We can assume that an empty value means that the user is not confirmed but do we really want to have to make this kind of assumption? We also don’t want to have to specify false and nil all the time we want to get the unconfirmed users. Fortunately, it’s possible to avoid this kind of ambiguity by setting null constraint and default value at the database level.

# == Schema Information
#
# Table name: users
#
#  id          :bigint      not null, primary key
#  email       :string      not null
#  confirmed   :boolean     not null, default: false
#  created_at  :datetime    not null
#  updated_at  :datetime    not null

class User < ApplicationRecord
  def confirm
    update!(confirmed: true)
  end

  def unconfirm
    update!(confirmed: false)
  end
end

class UsersController < ApplicationController
  def index
    @confirmed_users = User.where(confirmed: true)
    @unconfirmed_users = User.where(confirmed: false)
  end
end

Something seems wrong here. Whether or not a user should be confirmed by default is a business decision. I would prefer to have this information in the model definition. So here comes the second way to save booleans in the database. Here comes the timestamp to the rescue.

Using A Timestamp Column

By definition, a timestamp can be either a date or nil. We can hence restrict logically the possible values to only 2 states. Let’s take the same example as before but this time we will use a timestamp column instead of a boolean column.

# == Schema Information
#
# Table name: users
#
#  id            :bigint      not null, primary key
#  email         :string      not null
#  confirmed_at  :datetime
#  created_at    :datetime    not null
#  updated_at    :datetime    not null

class User < ApplicationRecord
  def confirmed?
    confirmed_at.present?
  end

  def confirm
    touch(:confirmed_at)
  end

  def unconfirm
    update!(confirmed_at: nil)
  end
end

class UsersController < ApplicationController
  def index
    @confirmed_users = User.where.not(confirmed_at: nil)
    @unconfirmed_users = User.where(confirmed_at: nil)
  end
end

Here, we use a datetime column called confirmed_at instead of a boolean column called confirmed. When a user is confirmed, we set the confirmed_at column to the current time. When a user is unconfirmed, we set the confirmed_at column to nil. To check if a user is confirmed, we simply check if the confirmed_at column is not nil.

What about the default value? As we defined confirmed_at as a model attribute, it’s simple to deduct that the default state is when the user is not confirmed. If we wanted the opposite behavior, we would have define a column unconfirmed_at instead.

The bonus of using a timestamp column is that we have an additional information: the exact moment the user was confirmed. Unfortunately, it also introduce some complexity. To change the default behavior, we would need to change the column name, change all the timestamp values to nil and set a more or less random value to all the previously nil values.

Conclusion

As always, choosing which way to save booleans in the database depends on the context. Sometimes the solution is obvious. I need to know if the user answered yes, no or not yet? Go for the boolean column. I need a simple soft delete feature? No chance we’re gonna want to have a record deleted by default so go for the timestamp column.

Bonus: Usecases Examples For Timestamp Columns

← Back to Blog