Rails + SQL View

If you are lazy and don't like to write SQL to create SQL view but you know AR use your skills to create views.

Production-ready.

The most simple way to add a view is to call a generator (examples below):

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

Depending on whether you need a materialized view or not add --materialized flag (later you can change in "view" class). Materialized views works in Postgres.

Generator will create a file similar to:

class ActiveUserView < SQLView::Model
  materialized

  schema -> { User.where(age: 18..60) }

  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

or if you want to use SQL to create a regular view:

class ActiveUserView < SQLView::Model
  schema -> { "SELECT * FROM users WHERE active = TRUE" }
end

or the same but materialized:

class ActiveUserView < SQLView::Model
  materialized
  schema -> { "SELECT * FROM users WHERE active = TRUE" }
end

Later with view you can work same way as with any model(ActiveRecord class). For example:

ActiveUserView.model.count
# or
ActiveUserView.count
# ----
ActiveUserView.find(42)
# you can apply scopes, relations, methods, BUT add them in extend_model_with block

ActiveUserView.model.by_role("admin").count
ActiveUserView.where(role: "admin").exists?
ActiveUserView.model.includes(:profile)

If you need to refresh materialized view - ActiveUserView.sql_view.refresh (if you need to do it concerrently - .refresh(concurrently: false).

It can also be used with your other models:

class Account < ApplicationRecord
  has_many :users

  has_one :account_stat_view, class_name: AccountStatViewView.model.to_s, foreign_key: :account_id
  has_many :active_users, join_table: :active_users_views, class_name: ActiveUserView.model.to_s, foreign_key: :account_id
end

More examples in this file: ./test/sql_view_test.rb

gem "sql_view"

And then execute:

$ bundle

And use generator. Or you can connect it to existing view with view_name=:

class OldUserView < SqlView::Model
  self.view_name = "all_old_users"

  materialized

  schema -> {  User.where("age > 18") }

  extend_model_with do
    scope :ordered, -> { order(:id) }

    def test_instance_method
      42
    end
  end
end

1. add index

  add_index SomeView.view_name, :user_id, unique: true

2. refresh with parameter

  SomeView.sql_view.refresh(concurrently: true)

3. profit :)

• CI with different versions of Rails/Ruby
• make unit tests works with rake test
• cascade option
• move classes to own files
• code coverage
• verify how it works with other DB's
• check if schema was changed on migrate or schema dump?

ruby ./test/sql_view_test.rb (because somehow rake test not works, not critical for now)

You are welcome to contribute.

I know about and actually using gem scenic, which is very nice and I tool some examples from it how to dump view into schema.rb but this gem was created to simplify life and reduce amount of time needed to write SQL to create a sql view.

The gem is available as open source under the terms of the MIT License.