Skip to content

igorkasyanchuk/sql_view

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

37 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Rails + SQL View

Listed on OpenSource-Heroes.com

The easist way to add and work with SQL view in your app.

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.

Demo

Usage

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

Installation

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

Materialized view + concurrent update

  1. add index
  add_index SomeView.view_name, :user_id, unique: true
  1. refresh with parameter
  SomeView.sql_view.refresh(concurrently: true)
  1. profit :)

TODO

  • 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?

Testing

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

Contributing

You are welcome to contribute.

Credits

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.

License

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