> Postgres Views in Supabase Have Only One Problem
Get updates on future FREE course and blog posts!

Postgres Views in Supabase Have Only One Problem

6 min read

Jonathan Gamble

jdgamble555 on Sunday, October 23, 2022 (last modified on Sunday, December 17, 2023)

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:


and add your query after that.

Here is a tag cloud example:

SELECT tags.name, COUNT(name)
FROM tags
GROUP BY tags.name

or a post tags example:

SELECT * FROM posts p
  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


Related Posts

© 2024 Code.Build