Database Views and How to Use Them in a Rails Based Application?

Kamil Walkowiak — Sun, 15 Sep 2019 19:54:49 +0000

Most of the time when we need to query some data from our database in a Rails-based application, we just use ActiveRecord query interface. When the query is more complicated, we write it using the SQL language. Sometimes it may be needed to use such SQL query in more than one place. In such cases (to better stick to the DRY principle), we can consider using a database view. In this blog post, I will present what are the database views and how to easily use them in Ruby on Rails application with the help of a Scenic gem.

The database views

The database view is a result set of a query stored in the Database Management System (DBMS). Every time access to the data is required, the stored query must be executed. Though database view is not connected to any persisted data table, it is possible to query it as a regular table (that is why views are often called virtual tables).

There are a few types of database views:

read-only views - can be only used for querying purposes.
updatable views - can be used for querying and data management (INSERT, UPDATE, DELETE operations) purposes, there are some restrictions for this type of views such as only one FROM clause or no aggregate functions in view's query definition. This type of view maybe not supported by all DBMS.
materialized views - provide a static snapshot of data while accessing the view to improve the querying performance. The query related to such view is executed only after the view's creation and on-demand (materialized view's refresh operation). Materialized views can be also indexed as the regular data tables to achieve even better performance. This type of view may be also not supported by all DBMS.

Database views give us many benefits such as:

more DRY database queries - we can extract subqueries used in many places to a single view and use simple JOIN to include the view's data in the main query.
an additional level of abstraction - we can encapsulate pretty complex SQL queries into one, simple to use a view.
better-querying performance - when we know that the data will be more often read than updated or it is possible to proceed with some cached data instead of the most current one we can use materialized view (with additional indexing if needed). It will make the querying process more efficient.

The Scenic gem

Scenic is a gem that allows to easily use database views in a Ruby on Rails-based application without having to switch the database schema to SQL format. It supports versioning of views and provides out-of-the-box support for PostgreSQL. There are available additional adapters for other DBMS such as SQLite, MySQL or SQL Server. Below, we will take a look at the main features offered by the gem.

Creating a new view

After including Scenic in the Gemfile, we can use generators provided by the gem. Let's say that we have the Users table with one of its columns being called active and indicating that the user is active. We want to create a view that will return only active users (that have an active field equal to true). First, we need to create the view by typing in the console:

$ rails generate scenic:view active_users
   create  db/views/search_results_v01.sql # this is a view's SQL query file
   create  db/migrate/[TIMESTAMP]_create_active_users.rb # this is a migration file

As the effect of this command, we get two files. First, let's take look at the migration file:

class CreateActiveUsers < ActiveRecord::Migration
  def change
    create_view :active_users
  end
end

Here, we are using create_view function provided by the gem. As the argument, we need to provide the view's name. We don't need to do anything more in this file. Now let's move on to the SQL query file. There, we need to provide a SQL query of our database view. Let's write an easy query to fetch all of the active users from our database.

SELECT * FROM users WHERE active = TRUE;

It is important to mention that all the views' SQL queries are versioned. We need to create a new version if we want to modify the logic standing behind our view. Each query version can be used in many migrations (e.g. create_view function mentioned earlier takes an optional parameter version if no value is given it defaults to 1).

After providing SQL query of our view we can run the migration. After that, we are ready to use the newly created database view. Let's say that we have such records in the database.

irb(main):001:0> User.all
=> #<ActiveRecord::Relation [#<User id: 1, active: true>, #<User id: 2, active: false>]>

After calling our database view (by executing a raw SQL query) we get the only user with id 1 (as only this one is active).

irb(main):002:0> ActiveRecord::Base.connection.exec_query('SELECT * FROM active_users').rows
  SQL (1.2ms)  SELECT * FROM active_users
=> [["1", "t"]]

View as an ActiveRecord model

OK, but are we limited to use the views in SQL queries only? Fortunately, the answer is: no. We can create an ActiveRecord model based on our view and it will behave like a regular AR's model. There is only one exception: the data provided by such model are available in read-only mode. The simplest model based on our view can look like this:

class ActiveUser < ActiveRecord::Base
  self.primary_key = :id

  def readonly?
    true
  end
end

In this piece of code, we set our model's primary key to id returned by the view. It is not required but helps to better map our view to AR's model, without it we would get objects with id field always equal to nil. Then, we mark our model as read-only so AR will not even try to reach the database when we accidentally call save on an instance of such a model.

Now, we can go back to the Rails console and try to use the newly defined model:

irb(main):003:0> ActiveUser.all
=> #<ActiveRecord::Relation [#<ActiveUser id: 1, active: true>]>

Feels good, doesn't it?

Updating view

Let's say that we have added a new column (called full_name) to the Users table. Now, our data looks like this:

irb(main):004:0> User.all
=> #<ActiveRecord::Relation [#<User id: 1, active: true, full_name: 'Jan Kowalski'>, #<User id: 2, active: false, full_name: 'James Bond'>]>

Let's call out active users view:

irb(main):005:0> ActiveUser.all
=> #<ActiveRecord::Relation [#<ActiveUser id: 1, active: true>]>

As you can see, the new full_name column is not reflected in the database view. It's because some DBMS (like PostgreSQL which was used while preparing this example) is freezing the columns returned by the view. So even when we have used * selector, columns added after view creation will be not included in the result set. To get the full_name column, we need to update the view. To do so, we can use the existing SQL query version that we have used while creating the view, but this time (for academic purposes) we will create a new query version (this time without * selector to avoid confusion in the future).

Let's execute the same command that we have used for generating a view for the first time:

$ rails generate scenic:view active_users
   create  db/views/search_results_v02.sql # new SQL query version
   create  db/migrate/[TIMESTAMP]_update_active_users_to_version_2.rb # update migration file

As you can see, we got a different output than after executing the command last time. Scenic gem had recognized the existence of the requested view so it created an update migration file (instead of creating) alongside with a new SQL query version file. Let's take a look at the migration file:

class UpdateActiveUsersToVersion2 < ActiveRecord::Migration
  def change
    update_view :active_users, version: 2, revert_to_version: 1
  end
end

To update the view, we are using the update_view function. It will first drop the existing view version and then recreate it. As parameters, we are passing the current version (revert_to_version parameter) and the desired version of the view after update (version parameter).

To update the view but without dropping it at first, we can use the replace_view function. It accepts the same params as update_view but there are some restrictions resulting from usage of this function (e.g. we may only add additional new columns to the end of the columns list returned by the view). You can find more information about this function in the Scenic documentation

Now, let's move on to the newly generated SQL query file:

SELECT * FROM users WHERE active = TRUE;

The new query file has been populated with the SQL query from the previous version. Let's modify it so our view will return the id, active and full_name columns but without using * selector:

SELECT id, active, full_name FROM users WHERE active = TRUE;

Now, when we migrate the database, we should get the full_name column in the result set of our view. Let's test it in the Rails console:

irb(main):006:0> ActiveUser.all
=> #<ActiveRecord::Relation [#<ActiveUser id: 1, active: true, full_name: 'Jan Kowalski'>]>

Everything is working as expected.

Materialized views in Scenic

As mentioned earlier, materialized views can provide a performance boost by serving some kind of cached data instead of executing the query every time we refer to the view. They can be also indexed as regular DB tables. Scenic provides support for such type of views.

Let's say, that we want to migrate our active users view to a materialized form and add some indexes on top of it. The generator command and the function presented in the create view step accept an optional parameter for creating the materialized views. However, instead of removing the existing one and creating another version (with duplication of the SQL query) of active users view, let's try to migrate the existing one. For this purpose, let's create a new migration file:

class MigrateActiveUsersToMaterializedView < ActiveRecord::Migration
  def up
    drop_view :active_users
    create_view :active_users, version: 2, materialized: true

    add_index :active_users, :full_name
  end

  def down
    remove_index :active_users, :full_name

    drop_view :active_users, materialized: true
    create_view :active_users, version: 2
  end
end

As you can see, we are defining two methods: up (executed while running the migration) and down (executed while reverting the migration). In the up method, we first remove the existing view from our database and then re-create it in the materialized form (using the same SQL query version that was provided earlier). At this stage, the DBMS executes query related to the view and stores its result. When the materialized view is ready, we also add an index on the full_name column.

In the down method we do an opposed set of operations. First, we remove the index on full_name column, then we remove materialized view and finally we re-create active users as a regular database view.

After executing such migration, the active users view should be migrated to the materialized form. The querying performance boost for this view could be not so visible as this is a pretty simple view. For the more complicated ones, the improvement in the performance may be much more significant.

Summary

A database view is a powerful tool provided by the relational DBMS. We can benefit a lot when using it properly. Scenic gem allows us to use views easily in Ruby on Rails-based applications. We can also create an ActiveRecord model based on a view.

DEV Community: Kamil Walkowiak