Database Views and How to Use Them in a Rails Based Application?
Tags: database, sql, ruby, rails
Published: 2019-09-15 19:54:49


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:

```bash
$ 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:

```ruby
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.

```sql
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.

```ruby
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).

```ruby
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:

```ruby
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:

```ruby
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:

```ruby
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:

```ruby
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:

```bash
$ 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:

```ruby
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:

```sql
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:

```sql
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:

```ruby
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:

```ruby
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.

### Further reading

[Scenic gem documentation](https://github.com/scenic-views/scenic)

[PostgreSQL documentation - database views](https://www.postgresql.org/docs/9.3/tutorial-views.html)

[PostgreSQL documentation - materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html)

Photo by [Kevin Ku](https://unsplash.com/photos/w7ZyuGYNpRQ?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText) on [Unsplash](https://unsplash.com/search/photos/database?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText)

**This blog post was originally posted on [Netguru's Codestories](https://www.netguru.com/codestories/database-views-and-how-to-use-them-in-a-ror-based-app)**