Database Views & Rails Active Record: defining new Model classes out of views

Learn about database views, their use, benefits and how to integrate them in your Rails application. Through a simple scenario you will understand their capability to bring new concepts to your data model based on information already present in it.

Introduction

A Database view is a persistent SQL query that other SQL queries can reference. views make queries reusable, reducing redundancy and complexity in our business logic. Its persistent nature implies their result tables behave as “virtual tables“: they are not persisted in the database but can be referenced anytime from the view. From a conceptual point of view, views can reveal hidden relations in our data model, defined from other existing models and represented by these result tables.

In Rails, database tables are mapped to Model classes. With this in mind, we can define new Model classes out of views virtual tables, consisting of the combination of attributes from multiple models. This allows working with different models simultaneously to perform certain operations more effectively and efficiently than using them separately.

In this post, you will learn about database views, their integration with Rails applications, and how they can help you enhance your data model via virtual tables and their respective read-only Model classes.

The Basics

SQL queries, subqueries, and views

We use the Structured Query Language (SQL) to perform CRUD operations (Create / Read / Update / Delete) in relational databases. Relational databases are made of tables (also called relations).

The SQL language is extremely powerful and allows developers to fetch any data they need from the database. Read operations are performed via SELECT statement queries, and return result tables. Result tables are composed of columns from other table columns in the database (or new ones defined for the specific query) and filled with records from these tables, which also match the set of conditions specified by other SQL statements in the query (WHERE, HAVING, …).

Sometimes, queries for fetching complex information become too long and difficult to handle. The language provides two powerful tools to design simpler queries that can return the same results: subqueries and views.

A subquery is a SQL query nested inside of another query. They split long queries into smaller ones, which are easier to handle. One of the limitations of subqueries is that they only exist in the SQL query in which they are declared. They are “one use“ only.

A view is a reusable SQL query. views are persistent and can be referenced from other SQL queries to build more complex queries, behaving similarly to a subquery. They remove redundancy from our SQL code via reusable encapsulated queries. views definitions (their SQL queries) are persisted within the database, and their result tables are generated each time a query that includes the view is executed. A view variant, materialized views, stores views result tables in the database, acting as a cache, which we will ignore to keep this article simple.

views are a powerful tool. Besides their reusability benefits, they also bring the possibility of discovering new hidden concepts in our data model. While result tables out of SQL queries are ephemeral, views result tables can be accessed anywhere from the database as if they were persistent tables. This opens the possibility of creating new “virtual“ tables and enriching our data model with new concepts resulting from synergies between existing ones.

What are the consequences of this? Let’s briefly explore how the Rails Model layer works first!

Rails, Active Record & SQL requests

Rails applications present a Model-View-Controller architecture. The Model layer is responsible for handling the data exchange with the database. For that, the framework counts with ActiveRecord, Rails ORM implementation. Object-Relational Mapping (ORM) maps connect our business logic classes with their respective database tables representation. For ActiveRecord, this means generating Model class instances out of database result table records and vice versa.

ActiveRecord classes include the QueryInterface module. It consists of high-level methods to fetch information from the database (find, where, includes, joins, etc). We use these methods to build SQL queries that fetch the records we need for our business logic. When we invoke a QueryInterface method, we get a Relation object in return. These objects are composed of an SQL query and the QueryInterface methods. We can see this by calling the .to_sql method, which returns the SQL query associated with the method invoked. Since Relation objects also have QueryInterface methods, we concatenate them with additional methods to assemble more complex SQL queries.

irb(main):002:0> Project.joins(:deliverables).where(name: "My project").to_sql
=> "SELECT \"projects\".* FROM \"projects\" INNER JOIN \"project_deliverables\" ON \"project_deliverables\".\"project_id\" = \"projects\".\"id\" WHERE \"projects\".\"name\" = 'My project'"

ActiveRecord processes the database result table when the SQL query is performed and parses the data to one or more Model class records of the class or object from which we initially invoked the query methods. Rails convention maps the database table to its equivalent Model class via their names.

irb(main):004:0> relation_object = Project.where.not(name: nil)
  Project Load (0.4ms)  SELECT "projects".* FROM "projects" WHERE "projects"."name" IS NOT NULL
=>                                                                                                          
[#<Project:0x000000010c0764f0                                                                               
...    

views **are persistent SQL queries, and result tables from these queries can be seen as “virtual tables“. Rails acknowledge this and allow us to map **Model **classes to **views result tables by their respective view name. As a result, we can create new classes out of existing ones. Since they are generated from views, these classes will be read-only, so no creation, update, or deletion operations can be performed on them.

Why view-based Model classes matter?

Model classes mapped from views are read-only, and their attributes are defined from other Model class attributes. This means view-based Models are useful for handling data from multiple models in single requests. How is it useful for our applications?

There are scenarios where we want to perform a certain operation over a heterogeneous data set. For example, we want to display a list of objects from different Model classes. In these situations, the convention one table one class Rails uses by default is ineffective since it requires us to fetch each set of Model class data separately. Things get even more complicated if we want to extend these lists with new features: how do we filter and sort these lists? what if we want to implement pagination? This would require implementing quite complex ad-hoc logic in our business model logic.

views can lower this burden. By defining a view-based Modelclass from the different Modelclasses we want to include in the list, we could fetch all the list items in one request. On top of that, additional operations such as those mentioned above could also be implemented within the fetch SQL request, resulting in a more efficient and simple list logic.

In conclusion, features requiring read operations over data sets including instances of different Model classes are potential candidates for using views. They significantly simplify the logic of handling records and provide an efficient way to do so.

The only additional cost of using views is their maintenance costs. views are persisted in the database, and as such, they require some maintenance. If the data model changes and tables associated with the view modify its composition, we might need to update our views. This will also require versioning our views and handling these updated versions via migrations, as we do with the rest of the database changes. This is a minor trade-off from views which is still important to mention before working with them.

Let’s now look at an example of how to implement views in Rails.

The Problem

We have an application that manages Projects. A Project can contain multiple Deliverables. Deliverables can be any sort of text based document: a Book, a BlogPost, etc. We want to create a list that displays all Deliverables of a Project, with the possibility to filter and sort them by multiple criteria.

The Solution

Model

This problem has two key concepts: the Project and the Deliverable. A Project contains multiple Deliverables. And what is a Deliverable? It can be anything. Right now, we identified Books and BlogPosts as deliverables, but what if we discover new kinds of deliverables in the future? We should be able to use them as Deliverables too. Each deliverable can be arbitrarily different from the rest. A Book can have pages, an index, different sections, etc. A BlogPost is way simpler, with its content, maybe the URL where it will be available, and not much more. Both of them might share some common attributes, like a title. So how do we define a Deliverable in our data model?

The answer is: we don’t! The Deliverable is just the concept of an artifact associated with a Project, which multiple classes can embody. The data of a Deliverable is its underlying Model class. It does not have specific data that would require us to define a new table in our data model. It is a model composed of data from other models, a perfect candidate to be represented by a view.

Let’s start generating our new Book and BlogPost model classes. We will add some fixtures for testing purposes and update the existing Project class with the corresponding associations.

# Rails generator commands run in terminal to generate new models
bundle exec rails g model BlogPost title:string summary:string release_date:date project:references
bundle exec rails g model Book title:string summary:string release_date:date project:references

# app/models/project.rb

class Project < ApplicationRecord
  ...
  # Associations
  has_many :blog_posts, dependent: :nullify
  has_many :books, dependent: :nullify
  ...
end

# app/models/book.rb

class Book < ApplicationRecord
  belongs_to :project, optional: true
end

# app/models/blog_post.rb

class BlogPost < ApplicationRecord
  belongs_to :project, optional: true
end

# app/test/fixtures/books.yml

# Book Fixture
recipes_book_project_book_1:
  title: How to cook pizza without burning your kitchen
  summary: Learn how make the best pizza in simple and secure steps
  release_date: <%= rand(10..360).days.from_now %>
  project: recipes_book

# app/test/fixtures/blog_posts.yml

# BlogPost Fixture
recipes_book_project_blog_post_1:
  title: "Introducting my new book: How to cook pizza without burning your kitchen"
  summary: Promo post to announce my new awesome book
  release_date: <%= rand(10..360).days.from_now %>
  project: recipes_book

# app/test/fixtures/projects.yml

# Project Fixture
recipes_book:
  title: My Recipes Book
  description: This is the best book about recipes you have ever read

To model our Deliverable class, we will need a view. We will use the popular scenic gem, which provides some useful generators for creating views with their respective migrations, and utilities to handle views versioning.

# gemfile
...
# DB
# --
# Database View Manager
gem "scenic"
...

# Running this command in the console will generate our Deliverables view
bundle exec rails g scenic:view project_deliverables

The view SQL query must return all Book and BlogPost records in our database. It must also handle commonly named attributes, returning the correct value for each data table record. Since it will return a result table as a response, we might also need a way to identify which table records are Books and which ones are BlogPosts.

All this can be easily achieved via a NATURAL OUTER JOIN and the COALESCE method. The JOIN method will merge both tables into a single one with the combined columns of each. We can then select the relevant columns for our final result table via the SELECT statement.

Since some columns share the same name, we can use the COALESCE method to select the corresponding column value for Book and BlogPost records. COALESCE admits an indefinite number of parameters, returning the first one which is not NULL. Joint table Book records will have BlogPost columns set to NULL and vice versa.

# app/db/views/project_deliverables_v01.sql

SELECT
    CASE
        WHEN books.id IS NOT NULL THEN 'Book'
        WHEN blog_posts.id IS NOT NULL THEN 'BlogPost'
    END as kind,
    COALESCE(books.id, blog_posts.id) as id,
    COALESCE(books.title, blog_posts.title) as title,
    COALESCE(books.summary, blog_posts.summary) as summary,
    COALESCE(books.release_date, blog_posts.release_date) as release_date,
    COALESCE(books.project_id, blog_posts.project_id) as project_id
FROM books NATURAL FULL OUTER JOIN blog_posts;

With our SQL view ready, we can now define the associated ActiveRecord Model class. Following the Rails conventions, it must have the same name as the view. And since views are read-only, our class must be read-only too. We will mark Model classes as read only via the readonly? method. Since we will likely define new view-based Model classes in the future, we will encapsulate this logic in a concern that we can later reuse for other new classes to make them view-based.

# app/models/concerns/view_based_model.rb

module ViewBasedModel
  extend ActiveSupport::Concern

  # View Models are made out of database views, so they are read only
  def readonly? = true
end

# app/models/project_deliverable.rb

class ProjectDeliverable < ApplicationRecord
  include ViewBasedModel

  # Ensures only deliverables with valid kinds are created
  enum kind: %w[Book BlogPost]
end

# app/models/project.rb

class Project < ApplicationRecord
  ...
  # Associations
  has_many :deliverables, class_name: "ProjectDeliverable"
  ...
end

All set; we can now test how the Project class instances can fetch all ProjectDeliverable instances associated with it. If we open the Rails console in the terminal (bundle exec rails c), we can see:

irb(main):001:0> Project.first.deliverables
  Project Load (1.1ms)  SELECT "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT $1  [["LIMIT", 1]]
  ProjectDeliverable Load (2.2ms)  SELECT "project_deliverables".* FROM "project_deliverables" WHERE "project_deliverables"."project_id" = $1  [["project_id", "f920b63a-a941-565f-92c4-6af6b0f107ad"]]
=> 
[#<ProjectDeliverable:0x00000001063e4188 kind: "Book", id: "ffbcc7d5-66cb-5095-b0d8-c3e5d2a16e29", title: "How to cook pizza without burning your kitchen", summary: "Learn how make the best pizza in simple and secure steps", release_date: Wed, 13 Sep 2023, project_id: "f920b63a-a941-565f-92c4-6af6b0f107ad">,
 #<ProjectDeliverable:0x00000001065b7730 kind: "BlogPost", id: "6acf2090-67dc-5d76-9dcc-c082ab0bc84b", title: "Introducting my new book: How to cook pizza without burning your kitchen", summary: "Promo post to announce my new awesome book", release_date: Sun, 06 Aug 2023, project_id: "f920b63a-a941-565f-92c4-6af6b0f107ad">]

The class returns all the Book and BlogPost records as ProjectDeliverable instances. Each instance has the attributes corresponding to its respective data table record. We can also take a look at the underlying SQL query and see how small it is in comparison to the view request, illustrating once more how views can simplify queries:

irb(main):004:0> Project.first.deliverables.to_sql
  Project Load (0.4ms)  SELECT "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT $1  [["LIMIT", 1]]
=> "SELECT \"project_deliverables\".* FROM \"project_deliverables\" WHERE \"project_deliverables\".\"project_id\" = 'f920b63a-a941-565f-92c4-6af6b0f107ad'"

Now, if we want to filter or sort ProjectDeliverables we can easily do it by just concatenating the .deliverable method invocation with the respective QueryInterface methods. For example, for sorting, we could use the following:

irb(main):007:0> Project.first.deliverables.order(name: :desc)
  Project Load (0.4ms)  SELECT "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT $1  [["LIMIT", 1]]
  ProjectDeliverable Load (0.4ms)  SELECT "project_deliverables".* FROM "project_deliverables" WHERE "project_deliverables"."project_id" = $1 ORDER BY "project_deliverables"."name" DESC  [["project_id", "f920b63a-a941-565f-92c4-6af6b0f107ad"]]
=>                                                                                                                 
[#<ProjectDeliverable:0x000000010816eeb0, kind: "BlogPost", id: "6acf2090-67dc-5d76-9dcc-c082ab0bc84b", name: "Introducting my new book: How to cook pizza without burning your kitchen", summary: "Promo post to announce my new awesome book", release_date: Sun, 06 Aug 2023, project_id: "f920b63a-a941-565f-92c4-6af6b0f107ad">,                                                             
 #<ProjectDeliverable:0x000000010816ed48 kind: "Book", id: "ffbcc7d5-66cb-5095-b0d8-c3e5d2a16e29", name: "How to cook pizza without burning your kitchen", summary: "Learn how make the best pizza in simple and secure steps", release_date: Wed, 13 Sep 2023, project_id: "f920b63a-a941-565f-92c4-6af6b0f107ad">]

Filtering is a more complex problem, but luckily I already wrote a detailed post about how to implement filters in Rails that you can follow to add filters to your application, and you can find the solution to it in the PR reference available here.

As a final touch, let’s rework our ViewBasedModel concern. Having an enum defining the Model compatible kinds is a pattern we want to do in all our view-based model classes. To document and enforce following this convention, we can extend our concern to define the enum for us and require developers to define a list of compatible kind classes per model. When you have to implement new views in the future, it will be easier to remember how to configure your models.

# app/models/concerns/view_based_model.rb

module ViewBasedModel
  extend ActiveSupport::Concern

  class_methods do
    def compatible_kinds
      raise "
        Model #{name} does not define `compatible_kinds` class method.
        Define method returning an array of strings corresponding to the model
        class names of the tables the view record is composed of. Check example
        in Project and ProjectDeliverable classes.
      "
    end
  end

  # View Models are made out of database views, they are read only
  def readonly? = true

  included do
    enum kind: compatible_kinds.index_with(&:to_s)
  end
end

# app/models/project_deliverable.rb

class ProjectDeliverable < ApplicationRecord
  class << self
    def compatible_kinds = %w[Book BlogPost]
  end

  include ViewBasedModel
end

As we can see, we ended up with a very simple, sustainable, and efficient solution to handle multiple Model classes at once. If, in the future, we want to add new deliverables to our Projects; we need to update the view SQL query with a new version and then set the proper model associations. If our existing ProjectDeliverable models change, we might need to update the query if any changes affect its fields.

Tests

To make sure the new feature works, we need to check Project instance deliverables method returns all associated deliverable records in it (books and blog_posts).

Rather than just using the Project fixtures to test .deliverables returns the same records as .books and .blog_posts together, we will use the ProductDeliverable .kinds method to get the list of available ProjectDeliverable kinds. Then we can use it to fetch all deliverable associations and compare them with the tested method. If new kinds are added in the future, the test will still be valid.

# app/test/models/project_test.rb

class ProjectTest < ModelTestHelper
  def setup
    @sample_project = projects(:recipes_book)
  end

  test ".deliverables return all project deliverables" do
    project_kinds = ProjectDeliverable.kinds.keys.map(&:underscore).map(&:pluralize)
    project_deliverables = project_kinds.map { |k| @sample_project.send(k) }.flatten.map { |d| d.id }.sort

    result = @sample_project.deliverables.pluck(:id).sort

    assert_equal project_deliverables, result
  end
  ...
end

Additional tests can be set to check that each deliverable kind is properly mapped to the ProjectDeliverable instance, but tests carry maintenance costs. The current test is good enough to ensure the system is working and very easy to maintain when new kinds are added to the view. If in the future we detect further issues in the logic, we can always add more tests to grant the correct functioning of it.

Finally, let’s add a test to our new concern. It will serve as a documented example of how to create a view-based model for us in the future:

# app/test/model/concerns/view_based_model_test.rb

require "test_helper"

class ViewBasedModelTest < ActiveSupport::TestCase

  class ValidViewBasedModel < ActiveRecord::Base

    def self.compatible_kinds = %w[Test]

  end

class InvalidViewBasedModel < ActiveRecord::Base

  end

test "ViewBasedModel concern requires included classes to define compatible_kinds class method" do

    assert_nothing_raised do

      ValidViewBasedModel.include(ViewBasedModel)

    end

assert_raises do
  InvalidViewBasedModel.include(ViewBasedModel)
end



    

    




end

test "ViewBasedModel concerns defines kinds enum in included class" do

    ValidViewBasedModel.include(ViewBasedModel)

assert ValidViewBasedModel.defined_enums.key?("kind")



    

    




end

test "ViewBasedModel concerns kinds enum includes the respective compatible_kind values" do

    ValidViewBasedModel.include(ViewBasedModel)

assert_equal ValidViewBasedModel.defined_enums.values.first.values.sort, ValidViewBasedModel.compatible_kinds.sort



    

    




end

end

Conclusion

views are a powerful tool for handling information from multiple relations in our database. Its persistent nature allows us to use them as “virtual tables“. As a result, we can model new result tables out of them to add new concepts to our data model without changing our database structure.

When working with Rails applications, QueryInterface methods are limited to fetching instances of one Model class at a time. With views, we can create new virtual models out of their respective result tables and define new Model classes from them. These classes can contain information from multiple classes, allowing us to overcome the limitation of one Model class per request.

This leads to more efficient use of database queries and a sustainable logic in our data model, which can be easily extended to include new kinds anytime needed. Considering the only additional cost of views is their maintenance (which should be zero if the data model is properly defined), this is one of the key tools to rely on when working with databases and Rails applications.

Code in Github

In response to the feedback gathered from my previous post, and to provide complementary material for better understanding my future post entries, I decided to share all the code associated to the work for each post in simple PR requests.

• 133 create a list with filter options for project items by AlbertoHdezCerezo · Pull Request #138 · Albert-Markdown-Editor/Albert

References

• Effectively Using Materialized Views in Ruby on Rails

• PostgreSQL Views Official Documentation

• Announcing Scenic — Versioned Database Views for Rails