Generated columns with ActiveRecord

I looked at the recent changes of Rails and Activerecord and spotted an interesting entry: Support for generated columns in PostgreSQL. In this post I’ll show an example how to use generated columns with Rails 7 and PostgreSQL.

Let’s create a new table Humans with the stored generated column bmi_stored:

class CreateHumans < ActiveRecord::Migration[7.0]
  def change
    create_table :humans do |t|
      t.integer :weight_in_kilos
      t.integer :height_in_cm

      # Generated columns
      bmi_sql_function = 'weight_in_kilos / (height_in_cm::float / 100) ^ 2'
      t.virtual :bmi_stored, type: :integer, as: bmi_sql_function, stored: true
    end
  end
end

Let’s create a fairly realistic row in the Rails console:

rails console

human = Human.create(weight_in_kilos: 83, height_in_cm: 174)

human.reload

puts human.bmi_stored => 27

So instead of calculating the bmi on the human model in a method we moved this logic to the database layer. Whenever we update the row, the bmi gets recalculated and saved in the bmi_stored column. There is one downside to using generated columns right now: You need to reload your model instance to get access to the updated value.

The following example demonstrates this:

human = Human.first

# loose some weight
human.decrement!(:weight_in_kilos, 15)

puts human.bmi_stored
=> 27

pust human.reload.bmi_stored
=> 22

It would be theoretically possible to avoid the reload and there is already an open pull request tackling this problem: Implement returning for postgresql

stored: true vs. stored: false

There’s also the non-stored variant of this type of column, which would be used if we dropped the stored: true argument or set it to false. This variant would instead of saving the calculated bmi calculate it on the fly whenever we want to read it. To understand the difference, I implemented the concepts of both variants in pure Rails:

While the non stored column would act more like a getter that calculates the value on the fly,

class Human
  def bmi
    (weight_in_kilos / ( height_in_cm.to_f / 100) ** 2).to_i
  end
end

the stored variant saves the calculated value whenever we update the row:

class Human
  before_save :calculate_bmi

  private

  def calculate_bmi
    self.bmi_stored = (weight_in_kilos / (height_in_cm.to_f / 100) ** 2).to_i
  end
end

Not every database does support both variants yet and there’s also not every type supported in ActiveRecord.

Current support in Rails

The following table represents the current support of the stored and non-stored column variants in Rails:

While newer versions of SQLite support both column variants, the ActiveRecord sqlite adapter does not support them yet.

Reach out to me if you have questions or comments