Postgres Views in Supabase Have Only One Problem

Postgres Views in Supabase are awesome and a pain at the same time. As I said in my previous article, for the moment you cannot use Subscriptions in Supabase with Views. However, that is not the real problem.

The Good

Supabase JS uses PostgREST under-the-hood. There are some shared developers on both projects, which is good. However, there are apparently over 100 feature requests, as you will still need at least one View for most apps. I think one of the problems with it's repo, is that it is written in Haskell. I imagine GO would be a better choice, but with over 2,000 commits and almost 100 contributors, that will probably never change.

PostgREST is freaking cool, especially when you realize the code required to use things like ts-query under-the-hood. However, you can't sort by an aggregation, do a join without it being a to_json, nested filters are limited, and as strong as the ts-query is, fuzzy text search does not work as expected.

That being said, PostgREST is really amazing. I prefer the nested joins, as I come from a graph database world. Everything is a graph, and it just makes more sense to me that way.

The way we solve these problems is with a View. Views are really cool because you can pretty much create anything you want. SQL is so powerful. A View is basically an index table that is locked to your data. This is both good and bad.

The Bad

That is the bad part. You cannot update your original table. You better know and be sure of what fields you're going to have on any parent tables. You are limited in changing anything at all including types, columns, indexes, names, etc.

Okay, so who cares? Well, if you have a production app, you will need to have downtime, and destroy and recreate your view immediately during that time. You must recall exactly what your view does, etc.

I think if Supabase made Views easier to modify in the Admin UI (like it does with Policies and Triggers), perhaps it would not be as bad. That being said, you're just talking about a basic SQL query here. The future hopefully has an all powerful supabase-js, which makes Views moot for everything except advanced usage.

I dream of a day where we can do all of these things in Supabase without knowing a lick of SQL. One day...

How to

To create a view, navigate to the SQL Editor and enter this:

CREATE OR REPLACE VIEW my_view_name AS

and add your query after that.

Here is a tag cloud example:

CREATE OR REPLACE VIEW tag_cloud AS
SELECT tags.name, COUNT(name)
FROM tags
GROUP BY tags.name

or a post tags example:

CREATE OR REPLACE VIEW posts_tags AS
SELECT * FROM posts p
JOIN (
  SELECT pid AS id, array_agg(name) AS tags
  FROM tags
  GROUP BY pid
) t USING (id);

None of these queries are of particular importance, but you can see how easy they are to create.

and of course, to drop a View:

DROP VIEW IF EXISTS posts_hearts_tags

J

Comments

[Johan Eliasson]

Have you considered using Nhost? We support subscriptions out of the box for views (with GraphQL).

A way to create views as easy as new tables is a really cool idea!