How to create and use SQL view in Rails app

Before we start, here is a quick demo:

Imagine you have a Rails app using Postgres DB (or other) with 100,1000,10K... active users.

Depending on your project, eventually, you can get to the point when you need to add SQL views to your App (what is SQL view)

There are multiple reasons to do it:

• security (you want to hide the content of some tables)
• simplicity (you can create a more suitable format to work with your data)
• performance (you can "materialize" data of your views, it can make your app super fast)
• calculations (you can move some calculations/aggregations into real-time views)

How to add SQL view into the Rails app

There are several alternatives to how you can add and work with SQL view in the Rails app.

But all of them have in common the following steps:

1. write a migration with an SQL definition
2. create a model and "associate" it with your view
3. use it like a regular ActiveRecord model

For me, the most painful step is the first one. Where SQL has to be written :) I belong to people who write queries using ActiveRecord and then call .to_sql to get SQL code.

So because I didn't want to write plain SQL code, I decided to create a gem that could help me avoid it - sql_view.

The idea of it is very simple - every Rails developer can use ActiveRecord to query DB, you can use associations, conditions, etc. So why not combine existing skills and .to_sql method to create SQL views using a simpler approach.

The biggest advantage of it you don't need to write plain SQL view and it's easier to make modifications to it.

Let's have a quick look at how it works and how it can be helpful for you.

1. Add to Gemfile

gem "sql_view"

2. Create SQL view from the command line

rails g sql_view:view ActiveUsers 'User.confirmed.where(active: true)' --materialized

It will create a migration and Ruby class that you can use to work with SQL view.

class ActiveUserView < SQLView::Model
  materialized

  schema -> { User.confirmed.where(active: true) }

  extend_model_with do
    # sample how you can extend it, similar to regular AR model
    #
    # include SomeConcern
    #
    # belongs_to :user
    # has_many :posts
    #
    # scope :ordered, -> { order(:created_at) }
    # scope :by_role, ->(role) { where(role: role) }
  end
end

You can extend this class like a regular model (add scopes, associations, etc)

3. Run the migration

rake db:migrate

4. Start using SQL View as a regular model. For example, just open a rail console and run ActiveUserView.count.

Congratulation, you created a materialized SQL view.

More examples of how to use gem and create SQL views in the Rails app in README.