Database Views: What They Are, How They Work, and When to Use them
There comes a time in every developer's career when they submit a pull request and get that dreaded feedback.
"Hey I think this should be a view"
And if this is the first time getting a change request like this, you're probably asking, "wtf is a view?"...
Fear not, views are really easy to work with and create and you should have your PR back up in no time.
What is a Database View
A database view is a query that is saved in the database.
It acts as a virtual table that you can query just like any other table. A view can use joins and combine data from other tables too.
Views can differ slightly depending on your database engine. Generally, a view only exists as a query meaning every time your view is referenced its data is recomputed. But you can create materialized views that store the result of that query.
But most databases support materialized views, is that their data is precomputed which helps performance. You can think of a view as a query that is stored in the database, and a materialized view as the result of that query stored in the database.
Views are great for abstracting complicated queries and keeping your code DRY.
How to Write a Database View
Views are created almost exactly like tables.
We use the CREATE VIEW statement, pass in a name for the view define the query that we want this view to store in the database.
It might look something like this:
CREATE VIEW active_users AS
SELECT id, email, name, active
FROM user
WHERE active = true;
And just like any other table, we can now access our active_users view just like any other table.
For example, we could join our view like this:
SELECT *
FROM orders
JOIN active_users AS au
ON au.id = orders.user_id
Updating a View
Code changes quickly and there's a good chance that you will need to update a view at some point in the future.
This can be done with a similar SQL command. Instead of CREATE VIEW we call CREATE OR REPLACE VIEW and our new query will be saved to the database.
CREATE OR REPLACE VIEW active_users AS
SELECT id, email, name, active, created_at
FROM users
WHERE active = true;
Note, depending on the specific database you are using (and how old it is), it might not support CREATE OR REPLACE. If you encounter this, you may be able to call ALTER VIEW active_users AS ... instead. It's almost identical and is supported is SQL server and older databases.
If ALTER VIEW doesn't work I have one last solution, and that is to drop the view and create it again. You can drop and create the view like so:
DROP VIEW active_users;
CREATE VIEW active_users AS ...
Deleting a View
Deleting a view (also known as dropping a view) can be done the same way as a table.
Where tables can be removed with the DROP TABLE statement, we run DROP VIEW and pass the name of the view to drop.
DROP VIEW active_users;
After running this, we will no longer be able to access or join against our view because it will no longer exist in the database.