Database Views & Rails Active Record: defining new Model classes out of views
Tags: rails, database, views, activerecord
Published: 2023-01-23 19:01:25


## 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`, …).

![Example of Result Table, contributions, generated from two other database tables.](https://cdn-images-1.medium.com/max/5052/1*5WgflfKZFabkwsV6SBkQVQ.png)

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.

![SQL subquery VS SQL view](https://cdn-images-1.medium.com/max/7514/1*ND6Gy96kG9BpQiF8kexeJg.png)

`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 `Model`class from the different `Model`classes 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.

![Proposed integration of database views in our Rails model](https://cdn-images-1.medium.com/max/6250/1*22d9z-yWmxCDUZxEFDgjFg.png)

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](https://github.com/scenic-views/scenic), 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](https://github.com/Albert-Markdown-Editor/Albert/pull/138).

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](https://github.com/Albert-Markdown-Editor/Albert/pull/138)

## References

* [Effectively Using Materialized Views in Ruby on Rails](https://pganalyze.com/blog/materialized-views-ruby-rails)

* [PostgreSQL Views](https://www.postgresql.org/docs/current/tutorial-views.html) Official Documentation

* [Announcing Scenic — Versioned Database Views for Rails](https://thoughtbot.com/blog/announcing-scenic--versioned-database-views-for-rails)