Execute SQL in Supabase API

[ltanady]

Is it possible execute SQL in supabase API? I have created a query to group many columns in my table, but I cannot do it using your API, I didn't find any example to extract years or months from dates and neither an example to group columns using SUM or COUNT for example.

[ltanady]

We do not allow direct execution of SQL from our APIs. There are two possible solutions:

1. You can create a function with the query specified and make RPC calls as such or
2. You can also generate a view with the query specified, then make a SELECT from there

The latter would be the simpler solution. Hope this helps!

[matart15]

@ox-harris nice work. Auto-Versioning looks interesting. I will try it.

[ox-harris]

Great. Happy to help with anything when you do try it out.

[steve-chavez]

After using a supabase with RPC and VIEWS, we realized there is 1 big problem
both RPC and VIEWS are separate from our codebase. this big problem introduces series of small problems.

You can put your SQL files in source control, overall migrations nowadays are better handled with "declarative schemas". See:

• https://supabase.com/docs/guides/local-development/declarative-database-schemas
• https://supabase.com/blog/declarative-schemas

[andrastoth-ws]

It baffles me when any company decides to shrink the rich language of SQL into a phrase book basically. When you can pass in raw SQL statements from, say, JavaScript, you augment the capabilities of both languages.

If you do joins and calculations on the backend because somebody hidden a basic SQL feature from you that could have been done in 1 query, you already wasted a ton of performance, for what exactly? From a formal language point of view, having the ability to execute raw queries would mean you lift JS into a multi-language system where JS (or other language) controls the control flow... Raw queries allow the full declarative grammar of SQL to be available in an imperative host!

Just for a connection pool I don't want to throw away the expressivity of a mature and feature-full language. Terrible design decision!

(There are players like slonik who embrace and enhance the raw SQL and they also provide their own connection pool. I worked with it and it was a great experience - I could run queries from inside my IDE on the dev cloud and so on, without even starting the backend.)

[steve-chavez]

There are players like slonik who embrace and enhance the raw SQL and they also provide their own connection pool.

The supabase-js library can be used from the frontend unlike any other ORM, this is why it has this constraint. Maybe worth repeating the first comment in this thread:

SQL cannot be fully exposed to frontend clients because it opens a door for forcing inefficient/slow queries that can starve your database of resources. Also a malicious user could do a Denial-of-Service.
https://github.com/orgs/supabase/discussions/3419#discussioncomment-1428169

[Johnrobmiller]

Thank you for posting this. Is there anybody who is able to clarify what it means to "generate a view"?

[Johnrobmiller]

Answering my own question: https://supabase.com/blog/postgresql-views

[MessiDaGod]

Hey have you heard of ChatGPT?

[Johnrobmiller]

Hey have you heard of ChatGPT?

I don't think chatGPT was launched when I posted the original comment, but I don't think your responses deserves 222 downvotes -- that seems excessive. I'm not so fragile that I can't take a little heat every now and then 😅

[elyobo]

It was an unhelpful and rude comment. The thumbs down seem like a mild and reasonable response.

[qubydev]

Hey have you heard of ChatGPT?

Grow up, kid.
If you believe ChatGPT can solve all your problems, then either you’re unaware of complex projects or you’re lying.

[revskill10]

Any udpate on this to use supabase-js to run raw sql query ?

[abdirahmn1]

here's a medium article showing how you can execute a custom sql statement by defining your logic in a postgres function before calling it with supabase.rpc('function-name')

https://medium.com/@razvanst/how-to-run-custom-sql-queries-using-functions-in-supabase-f81bfab780a7

[revskill10]

@abdirahmn1 I know about rpc. But please upvote to have built-in capability with supabase-js instead of managing tons of rpc functions.

[abdirahmn1]

You can use node-postgres to connect to your supabase DB and execute your SQL queries that way. IDK why you are hell bent on supabase-js having to support a way to execute raw SQL 🤷‍♂️

here's an article from supabase -

https://supabase.com/docs/guides/database/connecting-to-postgres

[revskill10]

@abdirahmn1 I deploy on Vercel on the edge.

Tell me how to run raw sql there with your node-postgres ?

[abdirahmn1]

I HATE to be that guy but please rtfm 👀, sorry if I'm being rude.

https://supabase.com/docs/guides/database/connecting-to-postgres

the docs have implemented a solution to your problem using an ORM and also the postgres lib.

node-postgres achieves practically the same thing that postgres does. here are the docs to node-postgres -

https://node-postgres.com/

stay awesome 🙂🤝

[davidosunkeye]

Can Supabase team just create wrapperJs to write raw SQL. I am moved from FireBase and I am just lost why it’s hard to achieve raw SQL on Supabase

[abdirahmn1]

here's a medium article showing how you can execute a custom sql statement by defining your logic in a postgres function before calling it with supabase.rpc('function-name')

https://medium.com/@razvanst/how-to-run-custom-sql-queries-using-functions-in-supabase-f81bfab780a7

[andredifs]

I just want to executre raw SQL, is it really so hard to make a built-in method for this? 🥲

[matart15]

I just want to executre raw SQL, is it really so hard to make a built-in method for this? 🥲

@andredifs My understanding is just executre raw SQL is hard because of security. Supabase client is designed to run in client side.

[louis030195]

Anyone figured out how to execute DYNAMIC queries?

I'm generating SQL queries using a LLM so I can't hard code the query in a function, perfect solution would be to have a function that let the user execute read only queries (don't care about RLS stuff)

CREATE OR REPLACE FUNCTION execute_raw_select_sql(query text)
RETURNS SETOF RECORD AS $$
DECLARE
    forbidden_pattern text[] := ARRAY['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'ALTER', 'DROP', 'CREATE', 'REVOKE', 'GRANT'];
BEGIN
    -- IF EXISTS (SELECT 1 FROM unnest(forbidden_pattern) pattern WHERE upper(query) LIKE '%' || upper(pattern) || '%') THEN
    --     RAISE EXCEPTION 'Query contains forbidden operations';
    -- END IF;
    -- TODO: the if is broken somehow

    RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;

Getting this

{
  code: '0A000',
  details: null,
  hint: null,
  message: 'materialize mode required, but it is not allowed in this context'
}

Any help appreciated 🙏

My workaround is to generate and execute JS code instead :/ pref SQL

[bahtiaryusuf10]

is this issue resolved now bro?

[ctwhome]

you can create a function that allows you to pass your raw SQL query as a parameter. Just be aware of the security implications it might bring.

CREATE OR REPLACE FUNCTION execute_dynamic_sql(sql_command TEXT)
RETURNS void AS $$
BEGIN
  EXECUTE sql_command;
END;
$$ LANGUAGE plpgsql;

and to run:

SELECT execute_dynamic_sql('UPDATE your_table_name SET your_data]');

mote that the function returns void, meaning nothing. if you want the function to return some data you need to change the function return from void to something you need like a JSON response

[hieuvo]

I tried the suggestion above, but any query with join or with as still produces error materialize mode required, but it is not allowed in this context

CREATE OR REPLACE FUNCTION execute_sql(query TEXT)
RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;

SELECT MAX(date) AS max_date
FROM checkins c1
WHERE c1.user_id = '6679b002-f0a8-47b1-84ba-d73a56d47fe5'
  AND NOT EXISTS (
      SELECT 1
      FROM checkins c2
      WHERE c2.user_id = c1.user_id
        AND c2.date = c1.date + INTERVAL '1 day'
  );

[just-abdou]

I was able to find a walk around it if ur still interested(sorry for the late response)

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.sql_query_runner(query text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
    result TEXT;
BEGIN
    BEGIN
        EXECUTE 'SELECT array_to_string(array_agg(row_to_json(t)::text), '','') FROM (' || query || ') t'
        INTO result;

        RAISE NOTICE 'Query result: %', result;

        RETURN result;

    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'An error occurred: %', SQLERRM;
            RAISE NOTICE 'Failed query: %', query;

            -- You can choose to return NULL or re-raise the exception
            RETURN SQLERRM;
    END;
END;
$function$
;

you can then decode the string in the client side