Database Views: What You Need To Know
What Are Database Views?
Database Views are virtual tables that derive data dynamically, unlike static database tables.
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.
Overview Of Database Views
The main features of Database Views:
- Merge data from multiple tables;
- Structure data in the most convenient way;
- Filter out data by different parameters;
- Hide data;
- Restrict viewing access to users;
- Grant partial access to third party services;
- Optimize performance (for materialized views).
Types Of Database Views
- Materialized views physically store data in the database. Thus, it’s important to update them occasionally. Similar to updated often. The data can be transferred as fast as a few milliseconds.
- Views don’t take up space on the disk drive. The queries are made in real time, so they are as fast as regular SQL queries. On the plus side, they don’t need to be updated.
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.
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.
Pros & Cons Of Database View
- Convenient table merging;
- It’s possible to display filtered data or display data in a specific format;
- Using a DB view just like a native table;
- Updating and storing the table are free;
- It makes development easier, because we could build a model in a view.
- Query details are hidden and this may cause performance issues. Noticing that you’re using a view and not a regular table can be tricky, so the issue can be hard to trace. Also, legacy applications can be difficult to support if the engineers from the old team are gone.
Service We Produce
Service We ProduceDatabase Design & Development Services
Areas Of Database Views Application
- Search across multiple tables;
- Create reports;
- Create agile data models in the application;
- Grant third-party apps access to certain parts of the tables.
- Search across multiple tables if they aren’t updated often;
- Optimize heavy queries. Again, works if materialized views aren’t updated often.