---
title: Announcing Scenic - Versioned Database Views for Rails
teaser: |
  Database views can improve the performance, composability, and readability of
  your rails application and Scenic is here to help.
tags: postgresql,rails,web
author: Derek Prior
published_on: 2016-01-11
---

Database views are powerful SQL constructs that are unfortunately often
overlooked by Rails application developers because views lack out-of-the-box
support from Rails itself. Many of the more complex ActiveRecord scopes,
relationships, and query objects we've come across could more clearly and
composably be expressed as database views, and yet they seldom are.

We've used database views to generate complex reports, [implement full-text
search across multiple models][search], and promote concepts hidden in our data
to proper domain objects in our Rails applications.

[search]: https://robots.thoughtbot.com/implementing-multi-table-full-text-search-with-postgres

Don't take our word for the importance of SQL views. Here's what the [PostgreSQL
documentation] itself has to say on the matter:

[PostgreSQL documentation]: http://www.postgresql.org/docs/current/static/tutorial-views.html

> Making liberal use of views is a key aspect of good SQL database design. Views
> allow you to encapsulate the details of the structure of your tables, which
> might change as your application evolves, behind consistent interfaces.

[Scenic] adds first class support for database views to ActiveRecord migrations.
Scenic goes beyond basic `create_view` and `drop_view` support to offer a view
versioning system that makes maintaining views as they change as simple and
robust as managing tables via migrations. It also ships with extensive support
for [materialized views].

[Scenic]: https://github.com/thoughtbot/scenic
[materialized views]:http://www.postgresql.org/docs/current/static/rules-materializedviews.html
![scenic landscape](https://images.thoughtbot.com/announcing-scenic--versioned-database-views-for-rails/MRUcPsxrTGCeWKyE59Zg_landscape.png)

Scenic has been open source for quite some time now, but we kept it quiet while
we dog-fooded internally and gathered feedback on its use from our early users.
We released version 1.0.0 in late November and we've just recently shipped
version 1.1.0.

## Scenic by example

I recently worked with the [Upcase] team to improve site performance. One look
at the data from [Skylight] showed several N+1 queries that were resistant to
the typical prescription of eager loading. One such resistant N+1 query was in
an action that showed users flashcards that they had flagged as needing further
review.

[Upcase]: https://upcase.com/
[Skylight]: https://skylight.io

The code for the relationships at play looked like this:

```ruby
class Flashcard < ActiveRecord::Base
  has_many :attempts, -> { order(created_at: :desc) }

  def most_recent_attempt_for(user)
    attempts.where(user: user).first || NullAttempt.new
  end
end

class Attempt
  belongs_to :user
  belongs_to :flashcard
end
```

As you may have noticed, flashcards have many attempts by the same user.
Attempts have a `confidence` attribute and the intent was to track how a user's
confidence in a topic moves over time. The `most_recent_attempt_for` method
hints that there's a concept that is hiding in our data, as we're about to see.

The list of flashcards that a user did not have high confidence in was retrieved
using this query object:

```ruby
class FlashcardsNeedingReviewQuery
  CONFIDENCE_THRESHOLD = 4

  def initialize(user)
    @user = user
  end

  def run
    Flashcard.
      all.
      map { |q| q.most_recent_attempt_for(@user) }.
      select { |a| a.confidence > 0 && a.confidence < CONFIDENCE_THRESHOLD }.
      map(&:flashcard)
  end
end
```

For each flashcard in the system, we're getting the user's most recent attempt,
filtering to those that do not meet our confidence threshold, and finally
mapping back to the flashcard object itself. Not only is this an N+1 query, but
we're also generating thousands of short-lived ruby objects which increase
garbage collection pressure, rearing its head as a performance problem in other
unrelated actions.

The data we collect gives us a history of a users confidence level, but here our
application only cares about the confidence level of their most recent attempt.
Let's promote the idea of a most recent attempt on a flashcard to a proper
domain concept.

## Enter Scenic

With Scenic added to our Gemfile and installed, we have access to some handy
generators that are going to help us create a view to encapsulate this domain
object.

```sh
$ rails generate scenic:model latest_attempt
```

This creates a few things for us:

* A migration that runs `create_view :latest_attempts`.
* A SQL file for our view definition at `db/views/latest_attempts_v01.sql`
* A model that is backed by our view at `app/models/latest_attempt.rb`

Let's start by defining our SQL view in `latest_attempts_v01.sql`. We want this
view to return only the latest attempt for each user for each flashcard. Free of
ActiveRecord, we can confidently use all of the features of SQL to do this. In
this case, we'll use [`DISTINCT ON`][distinct], which keeps only the first row
of each set of rows where the provided columns are equal.

[distinct]: http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

```sql
SELECT DISTINCT ON (user_id, flashcard_id) *
FROM attempts
ORDER BY user_id, flashcard_id, updated_at DESC
```

With our view definition file saved, run `rake db:migrate` to create the view.
Let's skip the model code for a minute and update the query object to have the
code we wish existed using this new `LatestAttempt` model. How about something
like:

```ruby
def run
  LatestAttempt.by(@user).
    confidence_below(CONFIDENCE_THRESHOLD).
    includes(:flashcard).
    map(&:flashcard)
end
```

That looks like a decently composable API for LatestAttempt, so let's get to
implementing it in our model. Our model is backed by a database view, but we're
still able to use our familiar ActiveRecord syntax for querying.

```ruby
class LatestAttempt < ActiveRecord::Base
  belongs_to :user
  belongs_to :flashcard

  def self.by(user)
    where(user_id: user.id)
  end

  def self.confidence_below(level)
    where("confidence < ?", level)
  end
end
```

With all of that in place, our N+1 query is gone and we've significantly
decreased garbage collection pressure. A [benchmark] of both methods with
production data shows that the previously existing Ruby implementation is *85
times slower* than our new implementation that makes use of the view. Equally
important, we've promoted the previously hidden concept that flashcards have
most recent attempts by each user to a proper domain object that can be reused
elsewhere in our system.

[benchmark]: https://gist.github.com/derekprior/a499d6ca3ae57d60a261

## Next steps

Please check out [Scenic] today. We're confident you can find a use for database
views in your application that will improve the performance and readability of
your code.

If you'd like to hear more inside baseball about the development of Scenic,
check out [episode 45] of The Bike Shed, where Derek and Sean discuss the
challenges encountered in developing and versioning Scenic.

[episode 45]: http://bikeshed.fm/45