Rails and SQL Views – Part 2 – Migrations

March 2, 2013 Dirk Kelly

Last week I introduced that my pair and I have started using SQL Views more often in our project. This week I was going to discuss finer points on implementation, thankfully I found Josh Davis’ has_one view post at HashRocket.

The rocket example I discussed covered an activity log of all the rocket’s states RocketActivity, with a custom view being used to return the most recent of these RocketCurrentActivity. One of a few solutions to this in SQL is a sub select on the same activities table.

CREATE VIEW rocket_current_activities AS
    rocket_activities.status     AS status

A few weeks down the way we’re going to have some change in requirements, this is where we would drop the existing view a recreate it with the new SQL.

Getting started with migrations is helped immensely by lomba/schema_plus a gem which alongside index and foreign key improvements, also gives you a method for creating SQL views, which it then stores in you schema.rb.

class RocketActivityViewFixes < ActiveRecord::Migration
  def change
    create_view :rocket_activities, "SELECT * FROM...", force: true

Unfortunately views can get long and in-depth, can change multiple times before hitting any one environment and contain important information about available columns and data types. We needed more than a one line truncation of what a view is at any point in time.

To solve this we started to store copies of our sql and store them under db/views/name_of_view/timestamp_name_of_view.sql, and call them out something like this.

class RocketActivityViewFixes < ActiveRecord::Migration
  def change
    create_view :rocket_activities, view_sql('20130215155853', 'rocket_activities')

The view_sql method is rather simple, and just an example of how you could structure your query files.

ActiveRecord::Migration.class_eval do
  def view_sql(timestamp,view)

As simple as this is, if you can start to drop your data joining into the dbms without impacting on support for teams and multiple environments, you’re going to feel a lot less pain making the move to SQL views.

If this doesn’t convince you that views are the bees knees, maybe the next time I get around to posting about how we use views to power our search indexes will be exciting to you. Once again, stay tuned.

About the Author


Tracker Ecosystem: Tracker Tracker – cross project visibility and panel customization
Tracker Ecosystem: Tracker Tracker – cross project visibility and panel customization

Tracker Tracker is an open source web app that allows you to see and work with stories from across multiple...

BDD starts with a conversation
BDD starts with a conversation

Let’s talk about cargo culting. When I started doing BDD, I cargo-culted it. I basically had no idea what I...

SpringOne 2021

Register Now