Supabase
Database transactions #526
Replies: 33 comments 42 replies
-
|
Need Transactions, BEGIN / COMMIT / ROLLBACK cannot find it anywhere in the docs? |
Beta Was this translation helpful? Give feedback.
-
|
@eifo Yes, they policies(and GRANT privileges) will work normally with functions. The |
Beta Was this translation helpful? Give feedback.
-
|
@joshnuss Thanks for the feedback! Yes, we're planning of supporting the transaction interface through PostgREST HTTP2. Though there are some concerns as you mentioned. My main one right now is that the clients could potentially leave the transaction open for too long. The idle_in_transaction_session_timeout pg setting is looking promising for solving this. |
Beta Was this translation helpful? Give feedback.
-
|
@steve-chavez is there an issue or anything we could track for progress on this transaction interface? I definitely have a couple of use cases for this. |
Beta Was this translation helpful? Give feedback.
-
|
@mstade Check this issue PostgREST/postgrest#286. |
Beta Was this translation helpful? Give feedback.
-
|
@steve-chavez any update on transactions support? |
Beta Was this translation helpful? Give feedback.
-
|
me too. I Do Need Transactions, BEGIN / COMMIT / ROLLBACK like: await supabase.transaction( supabase.from(...).update(...) When I ask the supabase.io tech team, I didn't get the solution for solving this problem and I only get suggest for using rpc. But I don't know how to really construct the rpc function for multiple database actions, including insert to the first table by multiple rows and then update the second table for different rows with different id and then update the third table for updating the data by key, if there is any error occurred during the THREE TABLEs transaction then ROLLBACK, otherwise COMMIT the transaction. Any solution or suggestion for writing the correct rpc function to handle the multiple tables' operations(insert, update, ...) in one transaction which can make sure the data consistency? Need help!! |
Beta Was this translation helpful? Give feedback.
-
|
more importantly the rpc functionality doesn't work well. if i call an rpc function (at least from python)... i get no success/failure. i can call rpc('whatever').execute() and get no failure! |
Beta Was this translation helpful? Give feedback.
-
|
@earonesty That sounds like a bug in the python lib. Consider reporting on https://github.com/supabase-community/postgrest-py/ |
Beta Was this translation helpful? Give feedback.
-
|
I find firebase's transactions and batched writes APIs to be very useful and developer-friendly. It would be fantastic to see something similar integrated into supabase. As it stands, this is a huge sticking-point for me when deciding to use supabase vs alternatives. Learning and writing RPCs each time we want to do a simple batched write / atomic transaction is not fun. Firebase docs on transactions / batched writes: https://firebase.google.com/docs/firestore/manage-data/transactions#batched-writes Example of firebase batching API from their docs: import { writeBatch, doc } from "firebase/firestore";
// Get a new write batch
const batch = writeBatch(db);
// Set the value of 'NYC'
const nycRef = doc(db, "cities", "NYC");
batch.set(nycRef, {name: "New York City"});
// Update the population of 'SF'
const sfRef = doc(db, "cities", "SF");
batch.update(sfRef, {"population": 1000000});
// Delete the city 'LA'
const laRef = doc(db, "cities", "LA");
batch.delete(laRef);
// Commit the batch
await batch.commit();Original comment on other thread: https://github.com/supabase/supabase-js/issues/87#issuecomment-999147766 |
Beta Was this translation helpful? Give feedback.
-
|
*Prisma has interesting APIs for this type of thing as well Prisma Transactions and batch queries |
Beta Was this translation helpful? Give feedback.
-
|
Supabase-cli is a step in the right direction in a CI/CD workflow but to suggest using RPC functions to get transactions is shamelessly trite. |
Beta Was this translation helpful? Give feedback.
-
|
Just to point out that we got all of this feedback guys. Long-running transactions is definitely on the roadmap: supabase/postgrest-js#219
Do note that it's not an apples to apples comparison, Prisma is a backend library AFAIK and you cannot use it as a frontend library. The point of PostgREST and the supabase client libraries is to access a safe subset of SQL directly in your web applications. |
Beta Was this translation helpful? Give feedback.
-
Agreed but can we have something for the server side logic in that case. I should be able to do transaction from lambda function or node.js based backend. |
Beta Was this translation helpful? Give feedback.
-
|
You can use node-postgres on the server side which supports transactions and connection pooling. |
Beta Was this translation helpful? Give feedback.
-
|
on backend you can do transactions in various ways, one just using postgres js clients as @jishnair pointed out. So prisma is not a solution to the original problem because we talk frontend. |
Beta Was this translation helpful? Give feedback.
-
|
It's sad we can't use the Supabase client for transactions, not even in the back end, and have to rely on an external library for this. It breaks the great DX experience of using the Supabase client and the ecosystem. |
Beta Was this translation helpful? Give feedback.
-
|
Bumping this because it would be nice to have this as a feature instead of having postgres rpc functions that fire with no knowledge if it failed or not. |
Beta Was this translation helpful? Give feedback.
-
All function calls report a failure on frontend. Maybe share what you've tried. |
Beta Was this translation helpful? Give feedback.
-
|
This makes me sad too. I hope the Supabase devs will prioritize this. Another strike against Supabase in seeing if I can use it in my production environment for my company. :( |
Beta Was this translation helpful? Give feedback.
-
|
Wanted to share a relatively new option to transactions in Supabase, and while this is not an ideal alternative to client transactions, it could be better in some ways than writing stored procedures: with release of Supabase Edge Functions - there is a way to connect to Postgres directly: https://supabase.com/docs/guides/functions/connect-to-postgres thus have access to transactions API. So instead of writing PL/pgSQL you can write JS/TS (or even Dart) and potentially reuse some of your code. |
Beta Was this translation helpful? Give feedback.
-
|
This is cool for an in-Supabase/edge function solution! I haven't tried this yet, so please correct me if I'm wrong : I think it's also possible to connect directly to the database using the connection string (available in your Supabase project settings) and use something like https://github.com/porsager/postgres on the server side. I don't know if we lose out on other things in the Supabase ecosystem with an approach like this, but I think it's another potential option. |
Beta Was this translation helpful? Give feedback.
-
|
@willdavidow yes, should be possible! I think what you lose is that you'll use admin access to Postgress so RLS will be limited (not working?) at its full potential. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for the tips guys. I can verify that this approach works. Good bit of setup required if you don't currently use edge functions, but definitely worth it from my perspective. Here's some example code: |
Beta Was this translation helpful? Give feedback.
-
|
@barbinbrad Hi, thanks for posting the link, it's very insightful. I'm working on a project where I need to use db transactions and I have a few questions to you. I sent you an email, hopefully you can find it :) |
Beta Was this translation helpful? Give feedback.
-
|
If somebody is using Dart and looking for implementing this approach: here is a package that we created to make it possible https://pub.dev/packages/deno_postgres_interop |
Beta Was this translation helpful? Give feedback.
-
|
Just following up here with my two cents after using transactions via
I'd wonder if @steve-chavez and team have made progress on the hard problem of doing stateful transactions through a stateless API? |
Beta Was this translation helpful? Give feedback.
-
|
We've been using We're using the query builder syntax, which works perfectly with the generated typing. However, I understand it could be more intimidating and complex than the Supabase client syntax. |
Beta Was this translation helpful? Give feedback.
-
|
Yeah I like Do you run I might be an outlier, but for me the things that are really valuable about supabase are 1/ unified auth across storage, REST, and realtime and 2/ isomorphic 3/ realtime. Are those important to you? |
Beta Was this translation helpful? Give feedback.
-
|
Early on, I was using the supabase client |
Beta Was this translation helpful? Give feedback.
-
|
We abandoned calling the DB and querying data directly from the front end. Various reasons and benefits for doing this, but it was mainly driven by the lack of transactions. The views are a smart solution, but we ended up defining those "views" as |
Beta Was this translation helpful? Give feedback.
-
|
Right now the only thing tying us to Supabase is the unified auth across features (edge functions, storage, db, etc). |
Beta Was this translation helpful? Give feedback.
-
|
How is this not a priority feature? |
Beta Was this translation helpful? Give feedback.
-
|
Can we please make this feature happen, it's essential for backend development. Not sure why it hasn't been developed already? |
Beta Was this translation helpful? Give feedback.
-
|
+1 for this feature, it amazes me too how its hasn't been delivered yet! |
Beta Was this translation helpful? Give feedback.
-
|
I totally agree with you |
Beta Was this translation helpful? Give feedback.
-
|
+1 for this feature |
Beta Was this translation helpful? Give feedback.
-
|
hello supabase team, i hope you are doing well if supabase implements this feature, it will hands down be the only BaaS i'll use till i die! Thanks |
Beta Was this translation helpful? Give feedback.
-
|
Any update about this feature? |
Beta Was this translation helpful? Give feedback.
-
|
Hello... anyone here?.... This discussion has gone silent 😮💨 , is there an ETA for this feature?? Thanks for your hard work, we appreciate the effort that went into supabase. |
Beta Was this translation helpful? Give feedback.
-
|
Read this and the rest of the thread supabase/postgrest-js#219 (comment) |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for mentioning this, here's my thoughts: Having to support transactions is not really something that's technically impossible, of course we'll be interacting with the actual postgrest DB via postgrest and all the api gateways infront, they can simply have a transaction open via postgrest and have an |
Beta Was this translation helpful? Give feedback.
-
|
In a need of transaction support too. |
Beta Was this translation helpful? Give feedback.
-
|
Did anyone of the maintainers shared some news/insights on this feature? |
Beta Was this translation helpful? Give feedback.
-
|
bro... transactions... please bro |
Beta Was this translation helpful? Give feedback.
-
|
up |
Beta Was this translation helpful? Give feedback.
-
|
You could use a function too, but it would take everything to the backend, what we really need is a way to start a transaction from the client. |
Beta Was this translation helpful? Give feedback.
-
|
Hello supabase team! Any updates on this feature? |
Beta Was this translation helpful? Give feedback.
-
|
This just came across my YouTube feed... Supabase is able to perform transactions now. The video goes over creating transactions and also ways of using the Supabase dashboard turn it into a Postgres function: https://youtu.be/xUeuy19a-Uw |
Beta Was this translation helpful? Give feedback.
-
|
Personally, I just don't have the ability to write the amount of business logic that I need my transactions to have in SQL. For example: https://github.com/barbinbrad/carbon/blob/main/packages/database/supabase/functions/post-receipt/index.ts |
Beta Was this translation helpful? Give feedback.
-
No offense but that request handler is outrageous! 🤣💀, you cant even start to debug where a problem is! anyways... 💀 about the original question, for me, its almost unholy to have business logic is pg function because 1, its... UGLY! 💀, in all seriousness though... its hard to debug and the codebase becomes split too! |
Beta Was this translation helpful? Give feedback.
-
|
No offense taken! I agree. But there's really nothing that can be removed. (And like you say, it's easier to debug in here than it is in SQL). That's the nature of transactions, I think. Otherwise the code would be nice and simple, and I wouldn't need a transaction. |
Beta Was this translation helpful? Give feedback.
-
You can have all your DB logic (tables, views, functions, etc.) as SQL migration files in the repo. Try running |
Beta Was this translation helpful? Give feedback.
-
|
I will spare ya'll the trouble and suggest the following: if you need transactions and you want to keep doing your dev in TS, you'll need a full backend that handles the DB interfacing with a lib that supports transactions (Prisma, Kysely, Knex, Drizzle, Sequelize, pg, etc... [I do not recommend TypeORM]), and your front end calls your backend logic via API. Believe me when I tell you I've tried to reason around using the Supabase JS client/PostgREST to call RPCs. It's not fun, no matter how much they try to auto-gen function names, type responses, and the rest. |
Beta Was this translation helpful? Give feedback.
-
|
Has anyone else in this thread experienced this error trying to run transactions via Kysley according to the tutorial from supabase: https://supabase.com/docs/guides/functions/kysely-postgres
I'm guessing this is a new error/feature of the connection pooler? |
Beta Was this translation helpful? Give feedback.
-
|
You can connect using drizzle-orm as a workaround: https://orm.drizzle.team/docs/transactions |
Beta Was this translation helpful? Give feedback.
and others
-
Update: we plan to support transactions. Follow issue PostgREST/postgrest#286 for the latest.
It would be handy if there was a way to execute multiple operations within a transaction. Similar to Ecto.Multi.
Maybe something like:
Would reduce the number of situations where a postgres function is needed.
Beta Was this translation helpful? Give feedback.
All reactions