Enhancing Developer Experience with Client-side Raw SQL Queries

[HTMHell]

Hello,

I wanted to bring up an idea that I believe could significantly improve our development experience with Supabase: the ability to run raw SQL queries directly from the client.

Now, I understand that we already have options like views, RPC, and edge functions to execute queries. However, it adds unnecessary complexity and work. Instead of creating a view / RPC / endpoint, you could simply query the data you need.

Of course, it's essential to address the security aspect associated with executing raw SQL queries from the client. To mitigate potential risks, I propose starting with a limited scope, allowing only SELECT queries initially. By doing so, we can ensure that we prioritize security while still reaping the benefits of this feature.

Some practical examples of how running raw SQL queries from the client can empower us to achieve things we currently cannot:

• sum
• group by
• distinct
• coalesce
• where exists(…)
• case when

Also, by allowing raw SQL queries from the client, we have the potential to achieve greater type safety, using something like: https://safeql.dev

What do you think?
There might be something I don't see here, would love to hear your thoughts.

[void-pulsar]

I really do agree with you as the supabase package is really quite useful but creating views all the time (and we still don't have an easy way to manage all current views from the UI) is quite annoying.

It's not an issue with Supabase per say, it's only the @supabase/supabase-js package. You can use https://www.npmjs.com/package/postgres if you want and achieve exactly what you want for eg:

async function getUsersOver(age) {
  const users = await sql`
    select
      name,
      age
    from users
    where age > ${ age }
  `
  // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
  return users
}