Postgres Views in Rails

I ran into something recently where I wanted to access a Postgres view from a Rails 6 application. Lots of ways to do this, but I found a pattern that's really simple and feels right to me:

First, make an abstract class called ApplicationView. It's helpful for semantics, and also because everything inheriting it will be read only.

app/models/application_view.rb:

class ApplicationView < ApplicationRecord
  self.abstract_class = true
  def readonly?true
  end
end

Then, create a model and have it inherit from ApplicationView :

app/models/search_result.rb:

class SearchResult < ApplicationView
  self.table_name = "search_results"
end

Create your view as part of a regular Rails migration:

class AddSearchIndexes < ActiveRecord::Migration[6.0]
  def up
    execute <<~SQL.chomp
      CREATE VIEW search_results AS
        SELECT
          ...
        FROM table
          ...
    SQL
  end
  def down
    execute <<~SQL.chomp
      DROP VIEW search_results
    SQL
  end

Finally, you can access it like a regular Rails model:

SearchResult.where(name: "foo bar")

Bonus: if you're using this to build a gin or gist search index like I was, you can easily write a scope to execute the search. Assuming your gin index is on haystack_column:

class SearchResult < ApplicationView
  self.table_name = "search_results"
  scope :for_term, ->(needle) { where("plainto_tsquery('english', ?) @@ haystack_column", needle) }
end

Access like this:

SearchResult.for_term("foo bar")

The benefit of using a scope is that you can chain it to longer and more complex queries.

SearchResult
  .where(user: user)
  .for_term("foo bar")
  .order(score: :desc)
  .limit(10)

Boom.