Can I use PostgREST api directly with fetch?
This isn't so much a help request as a curious question. I learned from perusing github that supabase's js client for the postgres db is based on PostgREST. So that made me curious if it is possible to access the PostgREST api "directly" – which is to say, in the manner I would normally use a REST API, with
fetch() and http-urls, as described in PostgREST docs.
I didn't see any mention of it in the supabase docs, but I imagine it would be possible. If it is, could someone give me a quick rundown of how? And also: which version of the PostgREST docs should I be looking at?
As to why I would want to do this – I have no need at the moment but I notice there are some things possible through PostgREST that aren't (apparently) possible via supabase's js client. Notably aggregate functions and transactions. So I was curious if it was possible to still use those features of postgres just in a more cumbersome way.10 Replies
You can take a look at the Api docs page and select bash in the top right corner for the curl requests. https://supabase.com/dashboard/project/_/api?page=auth
You'll face the same limitations as the sdk as the sdk's are simply making the same http requests underneath.
This sql-to-rest translator is also helpful https://supabase.com/docs/guides/api/sql-to-rest
Super - thanks this is very helpful!
Although I'm not sure what you mean by "You'll face the same limitations as the sdk " though. PostgREST supports transactions, for example (https://docs.postgrest.org/en/v13/references/transactions.html) – are you saying that supabase is using a cut-down/limited version of PostgREST?
PostgREST 13.0
Transactions
After User Impersonation, every request to an API resource runs inside a transaction. The sequence of the transaction is as follows: Access Mode: The access mode determines whether the transaction can modify the database or not. There are 2 possible values: READ ONLY and READ WRITE. Modifying the...
https://github.com/orgs/supabase/discussions/526#discussioncomment-12190267
rpc is the go to solution
Thanks yes I saw that comment - but it's confusing since it says "transactions is not a priority for the PostgREST team" but at the same time the PostgREST website clearly documents how to use transactions
I mean I could just try it but I was mainly curious for the future, which is why I asked.
You'll have to wait for someone else to answer this as I'm not familiar with postgrest.
No worries - appreciate the links and you taking the time all the same!
Ah! I made a mistake reading the PostgREST docs - they do discuss transactions but only as a factor of how PostgREST handles individual requests. There is no actual API for starting and ending a transaction. Too bad 🙁
Also - there does exist apis for aggregate functions, but they are apparently disabled by default (and presumably remain disabled in supabase).
So, in conclusion: Yes it's perfectly possible to use PostgREST apis with fetch directly, as illustrated by the cUrl examples in the dashboard. However, there are no additional capabilities available from doing that.
You can turn on the aggregate functions in Supabase with an SQL call to a setting. It is documented but I’m on phone and not going to search. Like you see RPC is only way to do your own transaction. The REST clients normally keep up with PostgREST versions within a few months for new features.
Thanks for the tip about turning on aggregate functions. Could well come in handy!
I was looking into this very same thing a few months ago regarding transactions through PostgREST. Since it doesn't support them directly, I ended up using RPC calls instead. You really got my hopes up, thinking I might have misunderstood the PostgREST documentation.
I believe this is what Gary might be referring to: https://supabase.com/blog/postgrest-aggregate-functions