Heterogenous Rails models based on SQL views

Rails’ active record pattern maps application classes to database tables and that works great — for the most part. For all the dynamics in the Ruby programming language, it can be suprisingly tricky to combine the easy-of-use of ActiveRecord models with ad-hoc data relations, like you might be used to do when working directly with SQL without any ORM. As applications grow, these use cases tend to become more common. But we can, in fact, have our cake and eat it too, if we combine ActiveRecord models with database views.

Example: a documents page

Let’s say our system includes customers. When customers sign up, they upload a some identification document, so we store those as an attachment with active storage:

class Customer < ApplicationRecord
  has_many_attached :identification_files
end

Now we want to build a page for administrators listing all the customer’s documents. It would be straightforward to implement. In the controller, we load all attachments:

def index
  @customer = Customer.find(params[:customer_id])
  @documents = @customer.identification_files
end

And our view would render these files:

<table>
  <% @documents.each do |document| %>
    <tr>
      <td><%= document.filename %></td>
    </tr>
  <% end %>
</table>

Over time, more types of documents are added to the system. For example, we might add letters we’ve sent to the customer:

class Customer < ApplicationRecord
  has_many_attached :identification_files
  has_many_attached :letter_files
end

We can still make this work, kind of:

def index
  @customer = Customer.find(params[:customer_id])
  @documents = ActiveStorage::Attachment.where(record: @customer)
end

This still works, because we are still, in ORM-parlance, dealing with a single “thing”: an ActiveStorage::Attachment. And Rails will deduce from the database schema how that thing works.

But the systems keeps growing and we add more associated objects and they all have attached files, such as invoices:

class Invoice < ApplicationRecord
  belongs_to :customer
  has_one_attached :invoice_file
end

To support our general files page, we want to list all the files. Ideally, we get them in a single query from the database. Here’s what our controller should ideally look like:

def index
  @customer = Customer.find(params[:customer_id])
  @documents = customer.documents
end

To implement this, we want to have a single ActiveRecord model that encapsulates all of the customer’s uploaded files, whether they are attached directly or indirectly to the customer. A customer’s files can easily be related to a customer using the record_type and record_id fields; but it’s not quite so easy for invoices. Finding attachments for invoices belonging to a customer requires making some joins. ActiveRecord associations have no easy way to link different tables together using different paths. Luckily, this challenge is no match for our database using views.

Collecting heterogeneous data in a view

A view is nothing more than a pre-defined query we can query as if it were a regular table. We’ll start by assembling a view for all the customer’s documents:

create view documents as
select id, name, record_type, record_id
from active_storage_attachments

ActiveStorage uses a polymorphic relationship, so we don’t have a customer_id field that we can use as a foreign key. But we can add our own:

create view documents as
select id, name, record_type, record_id, record_id as customer_id
from active_storage_attachments
where record_type = 'Customer'

Now we have a view containing all attachments for a customer, regardless of their attachment name (identification_files or letter_files). Now we need to list the files belonging to other records, that are in turn associated to the customer:

select id, name, record_type, record_id, customer_id
from active_storage_attachments
join invoices on record_type = 'Invoice' and record_id = invoices.id

We join the invoices and active_storage_attachments tables together to list attachments for invoices and gain access to a customer_id field. Now we need to join the two results together: we will use a union for that.

create view documents as
select id, name, record_type, record_id, record_id as customer_id
from active_storage_attachments
where record_type = 'Customer'
union all
select id, name, record_type, record_id, customer_id
from active_storage_attachments
join invoices on record_type = 'Invoice' and record_id = invoices.id

As long as all our individual queries produce the same list of columns (names and types) we can join them together as a single relation. And all of a sudden, we have a single “thing” to query from our application.

Wrapping a model around a view

There is nothing stopping us from basing an ActiveRecord model on a database view. Rails will happily translate the Document class name to the documents “table” name and read from it:

class Document < ApplicationRecord
end

Document.find(123) # = #<Document ...>

We should take care with view-backed models though: although some views are auto-updatable, complex views such as the one we just made are not. That is to say: you can read from these views, but you cannot write to them (unless you implement rules). Therefore, we will make our model read-only by default:

class Document < ApplicationRecord
  after_initialize :readonly!
end

Now we can hook up documents to customers like usual:

class Customer < ApplicationRecord
  has_many :documents
end

class Document < ApplicationRecord
  belongs_to :customer
end

And we are ready to use our new Document model!

What is especially neat is how selecting the record_type and record_id fields in our views we maintain support for polymorphic associations:

class Document < ApplicationRecord
  belongs_to :customer
  belongs_to :record, polymorphic: true
end

Moving forward

Basing ActiveRecord models on views is a nice practice to hide complex query logic in the database, where it arguably belongs, rather than in application code. It allows us to refactor the database internals while presenting unified interface to the outside world. By using unions in such a view, we can work with heterogeneous data in our application in a way that Rails would not normally provide for. One other common example of querying a single relation of heterogeneous data is a global search function. Using the same approach outlined here one could pull together data from various different tables and provide a single searchable relation to the application.

A nice extra step to make our documents view even more useful is joining other data to it, such as the active_storage_blobs table with further details about a file, such as filename and file size. By including it here directly we remove one more opportunity for introducing those pesky N+1 query problems.

There are caveats, of course. Apart from non-trivial views not being writable, views tend to be a little harder to maintain than application code. You would typically write them in a migration:

class DefineDocumentsView < ActiveRecord::Migration[7.2]
  def up
    execute <<~SQL
      create view documents as select ...
    SQL
  end
  
  def down
    execute <<~SQL
      drop view documents;
    SQL
  end
end

Updating views can get tricky. Although you can new fields to an existing view, changing anything else about the definition requires replacing the view with a new one. That means: drop it and re-create it with a new definition. That is both unwieldy to write in a migration and requires careful releasing. Luckily, a gem like Scenic can help improve the developer experience around views.

When it comes to views using union in particular, there is also the performance impact: combining 10 queries with a union clause will, to the database, still be 10 queries. Hit them often enough and you might generate considerable load on the database. Optimising that is certainly possible, but that’s a topic for another time.