since 1999

 

3 minutes estimated reading time.

Adding a Rake Task for SQL Views to a Rails Project

—

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

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:

In /db/sql_views/report_state_popularities.sql

CREATE OR REPLACE VIEW report_state_popularities AS
  SELECT
      MAX(M.id) AS id,
      UPPER(A.state) AS state,
      country,
      COUNT(M.id) AS popularity
    FROM submitted_requests M INNER JOIN addresses A ON
      M.address_id = A.id
    WHERE A.state != ''
    GROUP BY UPPER(A.state), country
    ORDER BY popularity DESC;

In /models/reports/state_popularity.rb

  class Report::StatePopularity < ActiveRecord::Base
    self.table_name = 'report_state_popularities'
  end

Applying the SQL VIEW to the database

All I need to do is to run rake db:views.

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:

namespace :db do
  desc "Update and create SQL views"
  task :views => :environment do
    Dir["#{Rails.root}/db/sql_views/*.sql"].each do |file_name|
      STDERR.puts "Applying the SQL view at #{file_name}"
      source_file = File.new(file_name, 'r')

      if source_file and (sql_content = source_file.read)
        ActiveRecord::Base.transaction do
          # Each statement ends with a semicolon followed by a newline.
          sql_lines = sql_content.split(/;[ \t]*$/)
          if sql_lines.respond_to?(:each)
            sql_lines.each do |line|
              ActiveRecord::Base.connection.execute "#{line};"
            end
          end
        end # transaction
      end

    end # Dir.each
  end # task
end

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.

In conclusion

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 models/ folder.

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.