Simplify complex SQL queries with Views in Postgres

Writing raw SQL can be extremely powerful, but queries that are joining tables and aggregating data can quickly increase in complexity. In this video, Jon Meyers steps through refactoring an existing application to query Views instead of Tables, greatly simplifying PostgreSQL queries.

00:00 Simplify complex SQL queries with Views in Postgres
01:26 Creating a free Supabase project
03:41 Refactor data fetching logic to use supabase-js
08:55 Create a PostgreSQL view using Supabase AI
12:01 Refactoring a complex query with multiple joins to use Postgres Views
25:47 Perform complex mutations using Postgres Functions and Transactions

💻 Videos to watch next:

👇 Learn more about Supabase 👇

📱 Connect with Us:

ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.

Build in a weekend, scale to millions.

#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[remi-avec-un-i]

Views are something I already heard of but never saw in action, thanks J

[canergurelsoy]

I'm just loving supabase more and more everyday . Nice job, pls keep bringing it.

[dshukertjr]

I didn't know that `returns()` function on the `select()` call existed! That's very handy!

[TheSlyProfessor]

I've been watching a lot of your videos and things are clicking. Thank you so much!

[Speechrezz]

Really helpful video, thank you! I've started using views in my own Supabase project now and it really simplified my queries!

[mrrolandlawrence]

Views are so under rated. So glad that feature is getting some love.

[xtz_]

we have views in our app but after adding ton and ton of data the pagination takes like 40 s but the raw query takes only 150ms 😢

so we switched to raw SQL in some places with zod for types

[loucasi]

Supabase videos in general are very helpful and the content is great, I really think it's time to start making these with an AI on the side bcs at this level of coding I think everyone would be better off learning the prompts we have to send in order to get the code. Tutorials should focus on: this is how you prime the model to be ready for a "Views + RLS on Supabase" conversation and that's how you prompt for each case. Instead of losing time to actually record themselves writing the code it would be much more SCALABLE advice to figure out where the AI model is prone for a mistake and the tutorial showing what to look for or how to avoid it all together. Thoughts?
Here they do it already a bit with the in-house supabase AI but I tried it and it does make mistakes - which I used Claude 3.5 to fix!

[slavenDj]

Great videos!
Is there going to be a video about encryption. Example i have not read anywhere that when i add security label via sql editor to a column it automatically creates trigger and views for decryption

[brotherly]

Compared to with-clause, which one is better practice?

[LM]

As somebody here in the comments mentioned, please do a video on encrypting data! Can't seem to figure out how to do it with next / supabase for my db.

[imho]

Can views also transform POSTGIS geography coordinates back to lat / lng integers?

[tonyoconnell]

I really love this tutorial. i learned so much both yourself and lee. i'm going to use it to replace charbot ui 's frontend (but not their supabase backend) so my users can have conversations with different ai models faster. i would really appreciate a link to a github repo of the code you wrote for supabase. i couldn't find your repo anywhere. if needed i will type the code code from the tuturial and still be happy.

[BlazeShomida]

Is it better to create a client for each function call over exporting a global constant of the client instance?

Like `export const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);`

[hawarhekmat]

Sign in with Google using server action with a client button won't work, when I log the data it gives back a url but in browser won't redirect to Google sign in page but in vscode console when I click on the url it does, maybe it's because of new Google sign in thing.

[rtorcato]

drizzle has support for views and coming soon foreign table support too, which is great for migrations.. Does Supabase use drizzle?

[nafg]

Would be nice if Supabase let you edit views

[Alex-bcxe]

Hmm I then see no reason to use an ORM cause supabase has pretty easy synthax

[Laflamablanca]

You are very enthusiastic… way too enthusiastic lol. Chill bruz