Connect SQL database with Ruby on Rails using scenic gem interface

Connect SQL database with Ruby on Rails using scenic gem interface

Most of the time in a Rails-based framework when you need to retrieve any data from the database, you just use the ActiveRecord query interface. If the application is more complex you write it using the language of SQL. Often, using such SQL query in more than one position can be appropriate. For such cases to adhere closer to the DRY principle, you can consider using a view of the database.

In this article with the aid of a Scenic gem, let us present what the database views are and how to use them easily in the Ruby on Rails framework. ruby on rails course helps you to learn more effectively.

The Scenic gem interface for Ruby on rails

It is a gem framework. Scenic allows the use of database views in a Ruby on Rails based application. Moreover, it supports transfer without having to switch the Ruby 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, you will take a look at the main features offered by the gem.

Creating a new view for Ruby on rails

You can include Scenic in the Gemfile. For this you can use generators provided by the gem. Let’s say that you have a Users table with one of its columns being called active and indicating that the user is active. You want to create a view that will return only active users (that have an active field equal to true). First, you 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 # It is a migration cycle.

As the effect of this command, you get two files. First, let’s take a look at the migration file in ruby on rails.

class CreateActiveUsers < ActiveRecord::Migration

def change

create_view :active_users

end

end

Ruby on rails create view function

Here, you are using the create view function provided by the gem. As the argument, you need to provide the view’s name. You don’t need to do anything more in this file. Now let’s move on to connect ruby to SQL query file. There, you need to provide a SQL query of our database view. Let’s write a simple query to retrieve all the active users from our database. It is important to note that all SQL queries from the views are being versioned.

SELECT * FROM users WHERE active = TRUE;

If you want to change the reasoning behind our view you need to create a new version. One can use each query version in several migrations (e.g. The previously mentioned create view function takes an optional version of the parameter, if no value is given it defaults to 1).After you have to give your view with SQL query you can conduct a migration. Then the newly created view of the database is ready to use. Let’s assume you have some kind of record in the database. After executing raw SQL query after calling our Database View. ruby on rails online training India for more skills and techniques.

irb(main):001:0> User.all8

=> #, #]>

Ruby on rails SQL query

You get only users 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 for Ruby on rails

OK, but it is not limited to use the views in SQL queries only. You 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 models 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, you set the model’s primary key to the id returned by view. It is not required but helps to better map our view to AR’s model, without it you would get objects with an id field always equal to nil. Then, you mark our model as read-only so AR will not even try to reach the database when you accidentally call save on an instance of such model.

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

irb(main):003:0> ActiveUser.all

=> #]>

Updating view for Ruby on rails

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

irb(main):004:0> User.all

=> #, #]>

Let’s call out active users’ views.

irb(main):005:0> ActiveUser.all

=> #]>

You can see in the view of the database, the new full name column isn’t reflected. It’s because some DBMS like PostgreSQL used while this example was being prepared freezes the columns returned by the view. So even though you have used * selector, the result set does not have columns added after view creation. You need to update the view to get a full name column. To do so, you can use the existing SQL query version you used while creating the view. However, this time for academic purposes you will create a new query version this time without a * selector to avoid future confusion.

Let us execute the same command you first use to generate a view:

$ 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 # migrate update

Now you have got a different performance from the last time you executed the instruction. Scenic gem had recognized the existence of the requested view so instead of creating an update migration file alongside a new SQL query version file it created. Check out 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, you are using the update view function. It will first drop the existing view version and then recreate it. As parameters, you 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, you 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. you 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 database file from the previous version was filled in with the SQL query. Let’s modify it so that our view returns columns of ids, active and full names, but without using * selector.

SELECT id, active, full name FROM users WHERE active = TRUE;

Now, when you migrate the database, you 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

=> #]>

Everything is working as expected.

Materialized views in Scenic for Ruby on rails

As mentioned earlier, materialized views can provide a performance boost by serving some sort of cached data rather than executing the query whenever you refer to the view. Also, they can be indexed as standard DB tables. Scenic lends support to these views.

Let’s say, you want to migrate the view of our active users to a materialized type and add some indexes above it. The generator command and the function shown in the create view step accept an optional parameter for the materialized views. Instead of removing the existing one and creating another version of the active users view with SQL query duplication. Let’s try to migrate the existing one. To that end, 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, you describe two methods: up (executed when the migration is running) and down (executed when the migration is reverted). You first remove the existing view from our database in the up method, and then re-create it in the materialized form (using the same SQL query version previously provided). The DBMS executes a query related to the view at this point, and stores the response. You’ll also add an index to the full name column when the materialized view is ready.

You do an opposite set of operations in the down process. First, remove the index in the full name list, then delete the materialized view and finally re-create active users as a standard view of the database.

After such migration has been performed, the view of the active users should be transferred to the materialized form. The performance boost querying for this view may not be as obvious as this is a fairly basic view. The performance improvement may be considerably more significant for the more complicated ones.

Conclusion

I hope you reach a conclusion about SQL databases for Ruby on rails. You can learn more from Ruby on rails online training.