Database Views with Scenic

Scenic is a gem developed here at thoughtbot that lets you work with database views in Rails. Rails migrations have no support for views out of the box, but Scenic integrates with Rails to make working with views easy.

What is a database view?

A database view is a named, predefined query you can interact with as if it were a table. When you use it, the query is performed live, every time. Views are great for composing bits of data that exist in your schema, perhaps hidden in relationships, and can make application logic more visible from the database. They're fantastic for reports or any time you want to repeatably represent existing data.

Views in Upcase

In Upcase, topics, trails, and shows can be routed to directly off the root domain via their "slug". For example, upcase.com/foo may to go to a topic, a trail, or a slug. We have a SlugConstraint class (below) which prevents users from hitting records that don't exist. Unfortunately, we don't have any object in our system that represents the core "slug" concept.

class SlugConstraint
  def initialize(model)
    @model = model
  end

  def matches?(request)
    @model.exists?(slug: request.path_parameters[:id])
  end
end

We can represent that slug concept with a database view.

Your first Scenic view

After adding scenic to the Gemfile, we can generate a database-view-backed model using a Rails generator provided by Scenic:

$ rails generate scenic:model slug

We want a list of every slug in use, along with the corresponding type (Show, Repository, etc) that it belongs to. Here's the database view:

-- db/views/slugs_v01.sql
SELECT slug, type as model
FROM products

UNION ALL

SELECT slug, 'Trail' as model
FROM trails

After running rake db:migrate to add the view to the database, we can use our view-backed Slug model in the SlugConstraint.

class SlugConstraint
  def initialize(model)
    @model = model
  end

  def matches?(request)
    slug_type(request) == model.to_s
  end

  private

  attr_reader :model

  def slug_type(request)
    requested_slug = request.path_parameters[:id]

    RequestStore.store[:slug_type] ||= Slug.
      find_by(slug: requested_slug).
      try(:model)
  end
end

Thanks to our new database view, the slug query is now the same every time, and we can cache it.

For the real-life code we used on Upcase, check out our [Upcase commit][commit].

[commit]: https://github.com/thoughtbot/upcase/commit/3c0a490725a4f6e794fb4c151acf013896a76d2f

Versioning database views

We made a mistake in our database view: we're not pulling the slugs from the topics table. This is where Scenic really shines: it makes it easy to update our view using a new migration, maintaining a clear history and the ability to roll back easily. Let's generate a new view version:

rails generate scenic:view slug

This generates db/views/slugs_v02.sql (note the v02 in the filename). When we migrate the database, Scenic will change the view to use the latest version, just like standard Rails database migrations.

We add this to the end of the v02 view:

+
+ UNION ALL
+
+ SELECT slug, 'Topic' as model
+ FROM topics

Now we can run rake db:migrate to change the slugs view to match the latest version in db/views. Scenic can also revert to an older version with rake db:rollback. It's seamlessly integrated into the standard Rails database workflow.

Materialized database views

We don't use a materialized view in this video, but they can be helpful in certain cases.

[Materialized database views][mv] are very similar to views except rather than the backing query being run each time the view is used, it's run once when the view is created and the results are cached to a table. The materialized view can then be refreshed on-demand.

[mv]: https://en.wikipedia.org/wiki/Materialized_view

The advantage of a materialized view is that it can be directly indexed (like a "real" table). Simple views can take advantage of underlying indexes on the base tables, but materialized views with custom indexes will perform better in certain situations.

Scenic is totally unique (as far as we know!) in how it handles materialized views. For example, Scenic automatically reapplies indexes when a new version of a view is created, rather than silently dropping old indexes from a previous version.

Conclusion

Rails values database agnosticism: everything at the application layer, and very little at the database level. But sometimes, database agnosticism can hurt our app. Scenic's simple integration with Rails makes it easier than ever to use views in your app today.