Rails Framework is one of the greatest supporters for Rapid Application Development (RAD) which tends to abstract and simplify the web architecture so that Rails abstracts away the database through the Active Record which is the Object-relational mapping (ORM) for rails.

The Active Record is the layer of the system responsible for representing business data and logic. Active Record facilitates the creation and use of business objects whose data requires persistent storage to a database.

So, It helps in a way to manage all relations consistency and mapping in the class model without the need to write SQL statement to retrieve any data or the usage of CRUD (create, read, update, and delete) methods in general.

The significant problem

When you start your new web application, you will focus mainly to build the model and to make sure that everything goes well, then you’ll have lots of users and you’ll start collecting and generating lots of data about those users and your application’s operation in general.

After you start having a lot of users and different teams to manage the operations, marketing, and business development. These teams will start asking for more data analysis and reports to be generated. How many new users signed up every day? From which countries? What features, pages are heavily used and which ones are unused? And other sophisticated kinds of reports and analytics. Moreover, your users will start to ask for new features and some analytics may be and reports according to the business application you are building.

The normal method to do that by start developing the model methods to retrieve this data, restructure, and aggregate them or doing another data analysis functions, developing the views to render these results to this result stakeholders. And this can have acceptable response time and performance in the beginning when you have a small number of users, with time and after generating a lot of data, this kind of reports will consume a lot of your production environment resources and you will not be able to use the traditional way of implementation and calculate all things on the fly.

There are many ways in rails to optimize the data retrieval performance, for example, caching, eager loading, custom query execution, …etc. In this article, we will focus on Database views as there is no direct support from Rails Active Record domain-specific language (DSL) to create views like it contains for main entities models. While it is important to mention that, database layer is not the only resources consumer, web application performance can be affected by many reasons. I assume here that you did some analysis and conclude that you have a bottleneck at data retrieval methods.

Most relational databases support the concept of views, you can query a view just like any other database table, but the fields and values in a view are defined based on the query used to create the view. I will not discuss here the definition of the views as I suppose that if you reached this article you are already familiar with it and you have a good background of relational databases in general.

So, what will we do to enhance our reporting and analytics performance using the database views? here are the steps.

Prepare your environment

I tested view creation using Rails 4.1.16, Ruby 2.3.3, and PostgreSQL installed on Apple MAC, also deployed similar algorithm on Heroku cloud and it worked as expected.

Prerequisites and supporting gem

We need to install the scenic gem. You can do that by adding the following line to your gem file.

 gem 'scenic' 

Scenic is a very great gem to use as it handles the versions of views if you need to update them later and it also makes it easier to add the view creation on the schema.rb

You can visit scenic documentation to learn more about it as well.

Generating the View migration

You will do that similarly like any migration, while the syntax will be like the below after adding scenic.

rails g scenic:view Summary

Please remember that Rails is handling the Plural of the name so it will be created as summaries view name at the database layer.

Understanding the command output

After running this command, you will have two files created one for the migration and one for SQL view creation with

The migration file will look like:

20150525215715_create_summaries.rb

class CreateSummaries < ActiveRecord::Migration
def change
create_view :summaries
end
end

And SQL file will be like and it will be located in a newly created folder called views and DB folder

Summaries_v01.sql

And this file is the magic which you will use to add your view code.

Adding your SQL query to the SQL file

At this step, you will not need to handle any SQL creation function of the view as scenic will handle that, you will only need to write the SQL query which you need to generate your reports or the data you need to optimize, for example, here as we need to do some summaries for users for example, how many posts they have, how many comments they did, how many followers they wrote.

We will add the simple query to our SQL file

select users_tb.users_id as user_id, comments_count, posts_count, followers_count from
(
select users.id as user_id from users order by users.name) as users_tb
left join
(
select comments_tb.users_id as user_id, comments_count, posts_count, followers_count from
(
select user_id, count(id) as comments_count from comments group by user_id order by user_id) as comments_tb
on users_tb.user_id = comments_tb.company_id
left join
(
select user_id, count(id) as posts_count from posts group by user_id order by user_id) as posts_tb
on users_tb.user_id = posts_tb.company_id
left join
(
select user_id, count(id) as followers_count from followers group by user_id order by user_id) as followers_tb on users_tb.user_id = followers_tb.company_id) as counts_tb
on users_tb.user_id = counts_tb.user_id

You will need to test your query and ensure that it runs correctly and returns the data you need, you can do that by running rails db at the console and then add your query

Run the migration task

As any migration, you will write the following command to run the migration task

rake db:migrate

After that, you see that your migration has been added normally and then you can test your view at your database layer by normal command.

select * from summaries;

Integrate the view with the Rails app

At this step, we need to have the same abstraction the rails provided to retrieve the data from the view as well integrate it with our main User Model so it can be easily related to our model.

To do that, we will create the view class, at your model folder add a new file, called summary.rb or your view name according to your implementation and usage

In the model, you will write the class which will do this level of abstraction.

class Summary < ActiveRecord::Base
self.primary_key = "user_id"
belongs_to :user
end

The user_id here is used as reference key with your main User model, which is an important note to consider if you will do some relation with your main model, you may not need that if you will view the aggregation view without getting other data from the main model.

And at your User Model, you will need to add this line as well

class User < ActiveRecord::Base
has_one :summary
end

Now, if you open your console and run rails c, the type, for example, Summary.all

It will get you all the data at the created view similarly as you do with any other model.

Conclusion

You can now use the database view in your controller or active views as well, for example, you write a simple command like:

User.find(1).summaries
User.find(1).summaries.comments_count

This will get you all the summaries with enhanced performance without any need to do the calculations and counting functions every time. You can also do some delegations to call the attributes directly from the main model.

Assume you have this for thousands

of users or millions. What will be the situation then with the traditional way?

I believe that creating database views is a good pattern to be used in Ruby on Rails applications, and this can be used in different aggeration and data calculations and manipulations functions.

I hope that you can try it and let me know what was the effect of using it on your applications.