SQL Views are a very powerful tool, that is sometimes overlooked. With all these ORM frameworks, Spring Data, etc... we sometimes forget that the most powerful solution is using SQL.
In this short article, I will go over the advantages and disadvantages of using views. The decision to use them is up to you.
Why yes?
Business rules
Do you have a business logic (only show articles after their release date) that you are writing to most of the queries you use to interact with that business part? By placing these business rules into a VIEW you can be sure about a unified portrayal of the data. By doing this you will have fewer issues with this logic in the long term. Also when the logic changes you have just one place to change it.
Complex query logic
Had you ever wrote a Hibernate query so long and complicated you got lost reading it after few minutes? Simplify your queries by hiding the complicated logic behind the view definitions. SQL is the best choice of language for more specific use cases, your ORM of choice will not be able to handle more complex stuff like WINDOW functions, etc... Also, code maintenance will be much more simple, as you will be doing just the most CRUD operations.
Consitency
Have you ever changes the table structure in your database resulting in a long refactoring session, fixing all the queries referencing those tables? A view can act as an interface for your tables. When you change the tables, chances are you can just modify the view definitions to result in the same view.
Security
Have you ever needed to restrict access to some data in the table (like the user's personal data)? Or provide access to another team, not wanting them to have full access? With views you can have the table restricted, have they allowed data in the view and that be shared, provided to your team, etc...
Why no?
Query performance
It's easy to forget that there can be a very complex logic behind the view and that query needs to run every time you hit the view.
Some DB engines will allow you to create indexes over a view, or create something called materialized view (data from the query is stored in a table, that is refreshed based on some rules).
Data modifications
Not all views support data modifications. Only the most simple ones, must have a primary key and all the fields. When you have joins and multi tables structures you are out of luck.
You can still use the tables for data modification and views to query logic.
Portability
Views are part of your DB engine. They are written in the specific flavor of your DB engine of choice. If you need to support multiple engines maybe it's not exactly for you. Otherwise, I would go for it, switching engine once per 10 years is no excuse to use this awesome feature.
...
To get more stuff like this you can follow me on Twitter.
Top comments (0)