Unlike other BaaS (Backend as a Service) providers like Firebase, Supabase is built on the relational Postgres database and exposes the full power of the database to you, the user. This means you can use the Supabase SQL Editor to do run Raw SQL queries on your Postgres instance, like Create a View in Supabase. A view is a virtual table that is based on the result of a SELECT statement. Views can be used for a variety of purposes, such as:
- Simplifying complex queries: A view can simplify a complex query by encapsulating the complexity and presenting the data in a simplified manner.
- Data Security: A view can be used to restrict access to certain columns or rows of a table, by allowing only a subset of the data to be displayed. This enables you to combine Row Level Security, RLS, with Column Level Security, similar to Hasura Column level permission.
- Data Abstraction: A view can be used to hide the underlying table structure and present the data in a different way, making it easier to work with.
- Data Consistency: A view can be used to ensure data consistency by providing a consistent view of the data regardless of any changes made to the underlying table.
To create a view in Supabase you can use the CREATE VIEW statement. The syntax for creating a view is as follows:
CREATE VIEW view_name WITH (SECURITY_INVOKER) AS
SELECT column1, column2, ...
FROM table_name
WHERE conditions;
For example, to create a view that returns the id, name, and email columns from the users table:
CREATE VIEW user_profiles WITH (SECURITY_INVOKER) AS
SELECT id, name, email FROM users;
This creates a virtual table named user_prfoiles that is based on the SELECT statement specified in the query. Once the view is created, you can query it as if it were a real table.
SELECT * FROM user_profiles;
You can also use the CREATE OR REPLACE VIEW statement to update the view definition if it already exists.
And since it is just Postgres, you can even create a more advanced View using a Supabase Group BY query in the View.
CREATE VIEW user_profiles WITH (SECURITY_INVOKER) AS
SELECT id, name, email, organization
FROM users
GROUP BY organization;Once you have created a View in Supabase you can use the Supabase JS Client library to query the view as if it was a table and it will apply as per usual.
Gotchas to look out for (aka Foot Guns)
The one major caveat with using Create View in Supabase is that Views do not respect RLS policies as you might assume by default. Views are executed using the Security Definer policy, which means they are executed with the permissions of the Postgres Role that created the View in the first place. This is most likely a Super User Role that bypasses Postgres RLS policies in Supabase completely. So any user can now query the view without any restrictions in place.
This means that not only can authenticated users query all user_profiles, but so can anon users.
If this is the desired behavior, then that is just Supa for you (sorry about that). However, if you need to restrict access to the view based on the RLS policies of the underlying Table(s), then you must set it to use SECURITY_INVOKER:
CREATE VIEW WITH (SECURITY_INVOKER) AS
SELECT....This will solve our problem by applying the RLS policies of the table to the View. And keep in mind that all RLS policies on any Join tables or Where clauses for the View Select will also apply.