rake db:views to enhance your workflow.
On this Thanksgiving, 2013, I am sharing this post as the first in what I plan to become an annual tradition on this blog. Since I was a young teenager, I always looked forward to thanksgiving as a time to read and write. Now, I can share even more with you.
In this post, I will show you:
- What a SQL view, also known as a Virtual Relation, is in a Relational Database
- How to create one using a Rails migration
- How to define a Rails ActiveRecord model that sits on top of it just as if it were a normal table
Why I use SQL Views in my Development Practice
Before I became a full time Ruby on Rails developer in 2011, I had worked as a web developer on other technology stacks for a decade. In each of those stacks the part that has changed the least is the underlaying SQL server. Therefore, starting with MySQL 3 in 1999, I have grown to love the power and flexibility that SQL gives the developer for working with relational data. I have now worked with MySQL, Microsoft SQL Server, and PostgreSQL extensively in my career.
So with this background, I look for ways to use the full power of the database server when it makes sense to do so. To me, Ruby on Rails may well be the best general purpose web application development platform to date. ActiveRecord is a wonderful ORM, but there come times when the SQL code that it generates is just not the best tool for the job. The SQL server has capabilities that go beyond what ActiveRecord is going to ever ask it to do.
In my projects, I have used SQL views to compute complex statistics, to discover connections among objects in the system (think of a search index with data mining) and then even have the PostgreSQL database tell the Rails application which objects are related by computing polymorphic associations.
These sorts of situations are beyond the scope of what is needed to show you so that you can try this pattern out for yourself. Just keep in mind that this technique goes well beyond the simple report example that I am sharing with you today. SQL is powerful, flexible, and fast!
What is a SQL View?
In formal terms, a base table in a relational database is called a Relation. But the magic is that these tables are only one type of relation and that all relations are fundamentally interchangeable! This property is called the Principle of Interchangeability and it gives rise to the ability to implement powerful object modeling, inheritance, and composition all from within the relational database!
For an even more formal definition, let’s take a look at the definition given in the database textbook that was co-authored by one of my undergraduate research advisors, Dr. Sham Navathe.
A view in SQL terminology is a single table that is derived from other tables. These other tables could be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered a virtual table, in contrast to base tables, whose tuples are actually stored in the database. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view… (Elmasri, Navathe, Fundamentals of Database Systems Fourth Edition, 2004).
Drs. Elmasri and Navathe use the term Tuple, which is a formal computer science term for an ordered list of elements that have various formal properties. In this context, just think of it as a row in a database table.
The major SQL servers all have support for views, you can create one like this:
The query can be simple or complex. It can involve a single table or it can perform tens or hundreds of joins. It can expose all fields from the constituent relations, or just a relevant subset, or it can compute new derived fields that are then presented as a ordinary data element in the resulting relation. In other words, it is a very flexible capability of the database server.
Using a SQL View with Rails for a Report
At the most basic level, a SQL view is a stored query. It is a first class relation with its own name. To your Rails application, it is just like an ordinary table in the database that ActiveRecord can deal with without any special Ruby code.
Here is a concrete example of this pattern to create a reporting interface in a Rails application. The first example is a SQL view that I wrote for one of the reports in a project (in the migration that sets it up). The second is the Ruby example of the class that sits upon it.
The Migration to Set Up the SQL View
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Notice that the class is in the Report namespace and the view is given the appropriate relation name that ActiveRecord expects by default.
Read-only Report Model
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
That’s all there is to the basic model. Now, you can do any ActiveRecord calls with it that you want, except for those that would update the model being that it is a read-only relation.
The Report’s Controller
1 2 3 4 5 6 7 8
The Report’s Rails View
1 2 3 4 5 6 7 8 9 10 11 12
Preserving the SQL views in your schema
One last important step is to configure the application to store its schema in SQL because the default
schema.rb does not understand views.
Rails provides an option to configure the format that the schema is stored in, as explained in the Configuring Rails Applications Guide:
config.active_record.schema_format controls the format for dumping the database schema to a file. The options are :ruby (the default) for a database-independent version that depends on migrations, or :sql for a set of (potentially database-dependent) SQL statements.
Since SQL views are one of those SQL features that is not supported by all possible database servers that is the option that needs to be set.
In config/application.rb, uncomment the line that sets the schema format to SQL. Like this:
And then re-run you migrations with
That should build the
structure.sql file that replaces the default
schema.rb so that you views will be preserved in your test database. Finally remove the now unused
schema.rb from the project.
SQL views are a powerful capability of a relational database server. This functionality can be easily used to implement important reporting, data mining, and other functionality to a web application built with Ruby on Rails in a manner that may be faster and more concise than an implementation that uses only typical ActiveRecord patterns. The complexity of dealing with the relational data is abstracted away from the Rails application that is a consumer of the data, keeping the model, controllers, and Rails view concisely focused on their appropriate roles.
I invite you to experiment with this pattern and to consider using it in your next project. It is a powerful tool that has served me well over the years.
Invitation to the Web Application Topics Newsletter
This is an archived issue of the Web Application Topics Newsletter. If you are interested in having future issues sent directly to your e-mail, please sign up for free, today. For back issues, see the Web Application Topics category on this blog.
Additional Reading on this Topic
- On December 4 2013, Charles Hoffman published a response to this post called Counting shared tags (or other commonalities) with a SQL view (Wayback Machine Link). His SQL view that computes a has-and-belongs-to-many (HABTM) relationship is clever and practical.