Database Views: What You Need To Know

What Are Database Views?

Database Views have multiple advantages. For instance, they simplify complex SQL operations. They protect data, because with Database Views you can grant access to certain parts of the table — you don’t have to give access to the whole table. Additionally, Database Views can return formatted queries in a convenient way.

An Example Creating a Database Views

Why Did We Use Database Views in Our Project

• We wanted to keep the project simple, without using ElasticSearch;
• The business requirements called for sorting and filtering values by columns from another table. We also needed to implement search by several merged columns.
• We decided to use regular columns (i.e. not materialized), because the data in the table would be updated often.

What We Have Achieved

• We’ve implemented it in a week, because we didn’t have to dive into new technologies;
• Didn’t use third-party services;
• Made sure that adding new data fields would be agile and simple;
• Built views that are easy to operate;
• Cut down heavy SQL queries in the application.

Instruments

Database Views can be used as regular SQL queries, but we went with Scenic gem for its advantages, such as:

• A migration generator;
• Migration methods for create/update/drop;
• Being able to define primary_key;
• Ability to creating materialized views (materialized);
• Refresh command for materialized view.

A good use case of Database Views would be merging several Active Record (AR) models with specific table fields into a new Active Record model. Doing so, we’ve managed to preserve all AR model features except for recording and refreshing data. In our case, we needed to have a search across values from both tables and interaction between the AR-model and GraphQL. This made it possible for us to create a GraphQL query by requesting data from the new model directly.

We wanted to display the dashboard with data pulled from different tables and reuse the query in other parts of the application. For that, we’ve created 8 JOINs in a single query. Building the new model made it easier to search and filter data.

While working with GraphQL, we’ve simplified the type description. Now, the only thing you need to do is define Database View table fields. This made types look cleaner and easier to read.

This one’s a bit controversial, but some developers find it useful. You can put some of the logic behind the data display into a Database View SQL query. This works for string concatenation, conditions, etc.