How to create Database Views in Ruby on Rails?

View of Zugspitze mountain — Germany [Photo by me]

This article explains how we can create database views in Rails applications. To see a practical usage of database views, please refer to the article below:

Database View in Ruby on Rails

Suppose we have Post model in our application and the table looks like this:

“posts” table

And we want to create a Database view that expose only id and body columns. This can be achieved through 3 simple steps:

1. First, we generate a migration file:

rails g migration SimplifiedPosts

2. Add up and down methods to the migration file:

class SimplifiedPosts < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL.squish
      CREATE OR REPLACE VIEW simplified_posts AS
        SELECT
            "id",
            "body"
        FROM public.posts;
    SQL
  end

  def down
    execute <<~SQL.squish
      DROP VIEW IF EXISTS simplified_posts;
    SQL
  end
end

In this migration file, up method creates the view. If a view with the same name exists CREATE OR REPLACE VIEW replaces the existing one with the new one (it can also be used to update an existing database view). down method is used for rolling back the migration and in this case it drops the created view.

3. Now by running rails db:migrate the view will be created in the database.

Use ActiveRecord to query the View

Fortunately, we can utilize ActiveRecord to query the view, which makes our life much easier. To achieve this, we just need to create a regular model class:

class SimplifiedPost < ApplicationRecord
end

Now we can query the view by ActiveRecord model class:

SimplifiedPost.find_by(id: 1)
SimplifiedPost.where('id = ?', 1)

Database Views — under particular conditions — are updatable!!! That means DELETE FROM simplified_posts deletes all the records from posts table. For Postgres, look at Updatable Views section for more details.

If you want to prevent manipulation through the model, the simplest approach in Rails is to define (or override) the readonly? method for the model.

class SimplifiedPost < ApplicationRecord
  def readonly?
    true
  end
end

When you mark the model as read-only, it prohibits any actions such as create/update/delete/destroy as well as any methods that modify data:

# ERROR: It prohibits creation of a new record

SimplifiedPost.create body: 'Body for read-only model'
# => SimplifiedPost is marked as readonly (ActiveRecord::ReadOnlyRecord)

WARNING: Since readonly? is an instance method, The class-methods like delete_alland update_all will bypass the read-only check!

Switch schema format to SQL

If you’re using schema.rb format to represent your database schema in your codebase, the created View will not be stored in schema.rb file. Therefore, if you initialize the database using rails db:schema:load, the View will not be created. To have the view in database, we will need to run that specific migration again!

If we change the schema format to SQL, the structure.sql file can accurately represent all aspects of the real database. To switch the format, you only need to follow two simple steps. First, add the following line to the application.rb file:

module RailsApplication
  class Application < Rails::Application
    ...

    config.active_record.schema_format = :sql
  end
end

Afterward you should run rails db:schema:dump to generate structure.sql file, which will be based on the current state of the database. At this point schema.rb file can be removed.

Take a look at the generated file — structure.sql. It should represent the database in SQL format.

Another way: Using scenic

scenic is a gem that lets you to create and mange database views using Ruby methods. It also allows you to keep your schema file in Ruby format (schema.rb). Rather than repeating scenic’s documentation here, I encourage you to check out its Github page.

Conclusion

In this article, we learned how to create a database view in Rails applications, how to query it using ActiveRecord, and how to regenerate the database, including the views.

Is it better to use a gem to manage views? As always, it depends! Adding a gem to your project is not free, it requires ongoing maintenance (like updating it). Or If you’re going to have more complex schema, with things like Database Constraints or database specific configurations, switching to SQL format might be a better option. However, if you’re comfortable with scenic and want to use its additional features, then go for it

Database View in Ruby on Rails