We’ve covered the basics of Active Record, but before we start building our application, I want to introduce a few advanced features.

First up, let’s explore virtual columns.

Understanding Virtual Columns in SQLite

We’re back in an IRB session playground, and this time we have a slightly modified post schema. In addition to our title and body fields, I’ve added integer fields for up_votes and down_votes.

• Both fields cannot be empty due to database constraints
• They each have a default value of 0

Now, for the interesting part:

In addition to standard column types, we can define a virtual column using:

t.virtual :score, as: "(upvotes + 1.0) / (upvotes + downvotes + 2.0)", stored: true

This formula calculates a Bayesian average rating.

Stored vs. Dynamic Virtual Columns

If a virtual column is stored, its value is persisted in the database and can be indexed for faster queries.

If it’s dynamic, the value is computed at query time but is not stored.

If you don’t explicitly specify, it will default to dynamic.

Stored virtual columns behave like materialized views, but at the column level instead of the table level.

Creating and Using Virtual Columns in Rails

Let’s run this migration to create our table. Looking at the generated SQL, we see the score column is defined as:

score GENERATED ALWAYS AS (upvotes + 1.0) / (upvotes + downvotes + 2.0) STORED

This ensures that SQLite always computes and stores this value.

Now, let’s set up our Post model and test this in Rails:

Post.create!(upvotes: 0, downvotes: 0)

When we retrieve our post, we see that Active Record automatically includes the virtual column value:

post.score  #=> 0.5

This means we can order posts by score just like a regular column:

Post.order(score: :desc)

This is perfect for ranking content dynamically, such as in an index view.

When to Use Virtual Columns

Virtual columns are particularly useful when:

• You have computed values that depend only on columns within the same table
• You need indexing for faster reads
• You want database-level consistency rather than defining logic in the application

However, there are some restrictions:

• Non-deterministic functions (e.g., CURRENT_TIMESTAMP) cannot be used
• Aggregate functions (e.g., SUM(), COUNT()) are not allowed
• You cannot reference columns in other tables
• Virtual columns cannot be primary keys or have default values

Despite these limitations, stored virtual columns behave like any other column in Active Record. You can:

• Add constraints (e.g., NOT NULL, CHECK)
• Use them in queries
• Include them in indexes for optimized performance

Why Virtual Columns Are So Powerful

With Active Record, virtual columns integrate seamlessly, making them:

• Easy to use
• Performance-friendly (when stored)
• Ideal for ranking, filtering, and computed values

Since they are enforced at the database level, this ensures consistency across different layers of your application—whether you’re working in Rails, the Rails console, or directly in a database client like TablePlus.

I personally find them incredibly powerful and use them whenever possible because they make working with computed values both fun and efficient.

In the next video, we’ll explore another advanced Active Record feature—Enums—and see how they can further optimize our application’s data structure.