I have previously written about Using Rails and SQL Views for a Report. A practical consideration when employing SQL views, which create wonderfully fast read-only tables that can be used by ActiveRecord models seamlessly, in a Ruby on Rails project is where to maintain them in a project.
One approach is to use migrations, since that’s where database stuff normally goes. But a big downside is that this approach is not DRY because changing the SQL view requires a new migration that drops the old view and replaces it with the updated version. Simply changing a field in the SQL view requires copying and pasting the entire definition over again. That’s just annoying!
The second, and in my opinion better approach, is to treat SQL views more like models.
SQL Views like Models
With this approach, I like to keep my database views defined as
*.sql files in
db/sql_views/ in the Rails project. This has multiple benefits
- There is a single point of truth for each view and it may be updated over time
- Normal SQL syntax is used so that my text editor uses nice syntax highlighting, vs SQL being stored in a string in a Ruby file!
- I run
rake db:viewsanytime a view needs updating and that’s it.
Revisiting the State Popularity example
Using the example of State Popularity, adding a ActiveRecord model on to of a view requires less code with this approach. It’s just the following:
1 2 3 4 5 6 7 8 9 10 11
1 2 3
Applying the SQL VIEW to the database
All I need to do is to run
Wonderful, so how do I add this non-default behavior to my Ruby on Rails project?
The approach I take is to drop a custom rake task into the project, by creating a file called
lib/tasks/sql_views.rake with the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
I have it posted over on my Github as a sql_views.rake Gist.
What this process does is read each SQL file in the
db/sql_views/ folder, parsing each statement by looking for the closing semicolon, and then executing each statement with the SQL database server.
SQL views create read-only virtual tables that can consolidate information from many models in your Rail application’s database far faster than anything ActiveRecord will generate by default. When appropriate to use, SQL views should be thought of as business model code that runs on the database server that is just as much part of your application as Ruby in the
Placing read-only ActiveRecord models on top of the SQL views and adding a rake task to manage these SQL-based functions outside of standard migrations gives a project a clean single-source of truth and syntax highlighting!
In other words, it treats the database server as a first-class component of the application’s platform rather than just a mere dumb datastore. The database server is providing an API service to the application and we’re bringing more of the management of that into the Rails project itself, which is a win for architecture and optimization.