Open bdlukaa opened 2 years ago
Another alternative might be doing https://github.com/PostgREST/postgrest/issues/818#issuecomment-409981816.
I've been noticing that most of the transaction needs are because of inserts on tables that are related through foreign keys. Doing it this way would save the need for exposing a generic transaction interface and avoid any potential issues with clients leaving transactions open for too long.
An insert + update + update(use case from https://github.com/supabase/supabase-dart/issues/60) could also be solved with a "relational upsert" interface.
Related:
Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.
Describe the solution you'd like
Implement an easy way to create transactions. Something like:
db.runTransaction(() { // run the operations here })
Describe alternatives you've considered
Create a rpc function that runs the commands. This is suitable for small apps, but wouldn't work for large apps, since multiple functions would be created.
I totally agree with the viewpoint. I am developing a big project App and it need lots of database actions for handling the project's demands. The data structure complexities are not easy to handle by rpc for complex arguments which is also hard to maintain.
So
db.runTransaction(() { // run the operations here })
the above transaction functionality is urgent needed as the standard equipment for supabase.dart.
I appreciated!!
Hi! What's the status of this issue?
Also interested in if there has been any progress made on this? We are deciding on the backend for our stack and the lack of transactions will likely rule out supabase unless it is on the roadmap for this year.
@dreinon @cory-weiner I'll revisit this one after launch week(August).
@steve-chavez imho this feature is very important. I can't think of any non-trivial RDBMS use-case that can work without transactions - except simple apps without many relationships. Data is relational, so is interaction with data. Having to outsource almost everything into Postgres functions is a bit cumbersome.
Also wanted to agree with the previous comment that supabase without transactions is borderline unusable. No app with basic relationships can be reliably ran this way.
@dreinon @cory-weiner I'll revisit this one after launch week(August).
Do you have an update on this? really curious!
Yeah, I made a proposal here for the API side.
As a first step it will only allow doing multiple mutations(update, insert, delete) on a transaction.
dear, one month has passed now, do you have any idea if this great feature is being picked up? :)
dear, one month has passed now, do you have any idea if this great feature is being picked up? :)
friendly reminder, really curious! :)
It's been quite a while since any update, any headway on development and/or direction on this? Been keeping an eye on this as well as https://github.com/PostgREST/postgrest/issues/286 and https://github.com/PostgREST/postgrest/issues/818 (which I believe are all trying to solve the same/similar issue) and haven't seen anything yet.
Handling relationships in supabase is pretty awful (imo) without this, would love to see this come to being.
This is a deal breaker for my project. Everything I have to do, has some sort of transaction use cases. Writing complex bits of logic inside the database is scary and awful. Simple updates as triggers and all are fine, but anything beyond this is just very difficult.
This is really important for the backend development experience, we eagerly await this! 😄
Also throwing my vote in. This might end up being a dealbreaker on a significant project I'm about to embark on, sadly 😢 It's a shame; virtually everything else about supabase would make it a perfect fit
Supabase Team, you must be out of your mind to not give this issue more attention. A feature that is so integral, so critical to an RDBMS is missing from your stack. And the lack of requisite attention is astonishing.
For us, this is a deal breaker. We will be steering clear of Supabase for the foreseeable future when picking a BaaS for any of our projects, until we find that transactions have been added to the client SDKs - especially Javascript.
To be fair, you could always use a database function for that and trigger it via client SDK. This is how we did it. So its technically possible, just a bit inconvenient.
In our particular case, "bit inconvenient" is an understatement. For the complex business logic we're currently employing, using database functions is borderline infeasible.
In our particular case, "bit inconvenient" is an understatement. For the complex business logic we're currently employing, using database functions is borderline infeasible.
I wanted to be polite. Yes, using transactions from the client SDK would be huge.
@adifyr Another alternative would be to use a supabase edge function with a different client library such as https://github.com/porsager/postgres which supports transactions. You can keep your complex business logic in JS and use it there. Might be easier than trying to translate to plpgsql.
@evelant We recently tried incorporating a third-party library (not the one you linked to), and had a less than ideal experience. Nevertheless, thanks for the link. Will try out your library as well.
The Supabase client seems more geared toward exposing the base of Postgres's features (via PostgREST). Hence, requesting client-side transactions is more of a DX issue since one can get transactions
by defining Postgres SQL functions and calling them via rpc
.
For some context, database transaction handling is not a trivial feature. Even some full-fledge dedicated ORMs and query builders struggle with this feature and often even define their APIs around the limitations and requirements of transactions. I've used JS/TS ORMs like Prisma, TypeORM, Sequelize, Knex, and believe me; transactions are not easy. Implementation and maintenance of this feature are critical since faulty logic can have very detrimental and even catastrophic consequences to data performance and integrity (DB deadlocks, loss of data, connection pooling issues, etc). I am sure the Supabase team knows this well, and they probably would rather leverage PostgREST if it solves this problem.
So here are my suggestions, given the complexity of the feature and unknown timeframe:
functions
or rpc
calls anyway. The exceptions could then be some simple inserts done UI side. But for complex transactional logic, I'd use edge functions or DB functions. If not, you risk complicating and completely exposing your proprietary business logic, and I would advise against this.rpc
. You'll have more control and a much more robust solution that uses the full power of SQL. The downsides are the DX is different, the learning curve is steeper, and it takes a bit more time and effort to set up and iterate.rpc
.transactions
(and potentially other DB features while you're at it) to connect to the DB and run custom logic. We've opted to use Kysely and I cannot recommend it enough. The issues here, though, are the DB connection and model setups, potentially the auth, and RLS policies, which are some things to look at.To the Supabase team, if DX is the issue, perhaps dedicating time to ease the pain of developers wanting to leverage PostgreSQL fully can help a lot. 😃 Some ideas that come to mind are:
INSERT
or UPDATE
that handles the basic logic when a relation is added to a table. This makes the function automatically available from the Supabase client when creating a relationship. Users can then modify them to their needs.The Supabase client seems more geared toward exposing the base of Postgres's features (via PostgREST). Hence, requesting client-side transactions is more of a DX issue since one can get
transactions
by defining Postgres SQL functions and calling them viarpc
.
I would argue that as transactions are a Postgres feature, it's within the scope of Postgrest/Supabase. It's for sure a more complicated feature to implement safely, which has been discussed, but I'd argue the fundamental idea of exposing Postgres features to the client side doesn't exclude this. It's a choice to not support in favour of RPC. If RPC didn't exist, would you make the same arguement?
For some context, database transaction handling is not a trivial feature. ...
So here are my suggestions, given the complexity of the feature and unknown timeframe:
[Just use RPC / Write a backend]
I'm sorry, but I disagree completely with this idea. The issue is not that it's scary to use SQL, it's about keeping a consistent workflow and allowing for abstraction. Neither are possible when you're storing queries on RPC. Context switching from JS/TS to SQL is a pain generally, but if you've gotten so far into writing a query, then realised you need to use a transaction, something which should be trivial from a user's perspective, is not a great time to then need to rewrite in SQL. - if that's how we're to do things, why not write everything as an RPC? I imagine you'd experience less pain. It's silly. RPC's are a workaround, not a solution.
Additionally, Supabase's entire pitch is that it's an open source Firebase alternative, of which Transactions are available.
- Auto-generate a DB SQL function for
INSERT
orUPDATE
that handles the basic logic when a relation is added to a table. This makes the function automatically available from the Supabase client when creating a relationship. Users can then modify them to their needs.
I think this would be very helpful in the mean time while waiting on transaction support.
I would argue that as transactions are a Postgres feature, it's within the scope of Postgrest/Supabase. It's for sure a more complicated feature to implement safely, which has been discussed, but I'd argue the fundamental idea of exposing Postgres features to the client side doesn't exclude this. It's a choice to not support in favour of RPC. If RPC didn't exist, would you make the same argument?
I agree it would be a great feature. Still, my point is that the client isn't built as a full-fledge ORM, but a set of abstractions using other open-source tools made for Supabase, particularly the DB interface usesPostgREST
for its core DB feature abstractions. Until they solve missing transactions, it's unlikely that this feature will come from the Supabase team, and putting the onus on the Supabase client devs is not trivial. RPC is the "choice" made by PostgREST to use transactions until they implement some other API.
Additionally, Supabase's entire pitch is that it's an open-source Firebase alternative, of which Transactions are available.
Again, it's important to clarify transactions
are possible, just not using the same JS client-side API convenience as Firebase. For some, this is a deal-breaker; for others, it's a different approach required. Hopefully PostgREST can find a solution, or the Supabase team invests the effort to this for full feature parity.
Still, my point is that the client isn't built as a full-fledge ORM, but a set of abstractions using other open-source tools made for Supabase
I don't think anybody is asking for the js library to be a full-fledged ORM, neither do I believe transaction support would make it so.
it's unlikely that this feature will come from the Supabase team
Steve (the owner/maintainer of postgrest) works at Supabase so it quite literally would. Though admittedly I don't know to what capacity their relationship exists or whether Supabase dictates priority of certain issues/features Steve picks up.
Given the previous discussion in this issue and related, it sounds like it's on their todo list, I just don't think it's helpful to downplay the importance of such a feature. "use RPC" is not a solution, it's a bite-your-tongue-and-deal-with-it workaround. RPC should ideally be used for times where you need to circumvent row-level permissions, or execute SQL in a controlled way that you wouldn't want to just expose to the client-side... As an alternative to transactions? That makes real-world usage incredibly painful for even the most basic of relational data.
Also just to make it clear, I'm incredibly grateful for the work Steve does, and the Supabase team with developing PostREST and Supabase. I'm very much eagerly waiting in excitement for when I can take advantage of these features which I think are essential to real-world usage, as I said, I just don't want to downplay how important I think they are.
Edit: @bombillazo After re-reading, just want to apologise if I came across a little hostile, I don't mean to be. Just felt quite strongly.
@evelant Postgres.js is working just fine for us in the mean time. Thanks for the link. Awaiting updates from the Supabase Team on the inclusion of transactions natively in PostgREST.
Edit: @bombillazo After re-reading, just want to apologise if I came across a little hostile, I don't mean to be. Just felt quite strongly.
No worries, no hard feelings 🙂 I don't take things personally. I understand the frustration from the lack of this super convenient feature and the desire to get help to move this forward.
Hopefully, they can invest the time in a solution that makes the Supabase client stack up with Firebase and other ORMs alternatives.
The more I use supabase the more I realize most database operations are better done on the server anyways, so good to know we can still just access the database directly and get transaction support with something like postgres.js.
any updates on it ?
This is very, very, very important to my project.
Anyone here have time to prompt (ask) AI for a solution...
would be a very nice feature, dropping a comment later i'll check
+1 would like to see transactions for Supabase
@bombillazo Here are my arguments to understand the breadth of this issue further and I do have an inclination towards one solution approach that I would like to suggest here.
Sincere apologies if the above come of as rude / too strong.
TLDR: Make two implementations of the same sdk interface, one for use in the edge functions and the other for use in the frontend apps.
This way we can write code that is in a consistent syntax. Potentially we can move code written in the frontend to the backend when we need to without making any change. Can use full DB power in application code by using Atomic Transactions when in the edge functions. For frontend you can still use the PostgREST endpoints.
Hey, first, I have no stake in Supabase as a company or technology; ultimately, I have no personal matter in any current position in this issue other than the desire as a developer to get this feature. I may come off as dismissive, but I am being pragmatic and non-optimistic. I fully support resolving this issue to ease the development of transactional logic in my backend. That said, I am not holding my breath that this will be available anytime soon, nor am I architecting my solutions with that expectation.
My observation was a raw, objective assessment of the technology, how it stands, and how it's been managed since this issue was created. It's been four months since my original post, with the issue nearing two years, and I still see no signs of it being resolved anytime soon. My points are the practical alternatives ( given Supabase's approach to its client lib) while transactions are resolved.
Your suggestions on point 4 sound idealistic; the Supabase client should become (or behave like) an ORM. However, that goes back to one of my initial points: I don't think Supabase ever set out to build a custom ORM that incorporates all the advanced features. The Supabase team consciously decided to leverage PostgREST with its virtues and flaws to provide a solution. As I mentioned before, changing or adding the ORM paradigm in parallel is a lot of work. It's the ideal solution, but it's probably years off. As soon as I see any signs from Supabase that they are working on an ORM-like solution or PostgREST somehow resolves the transactional limitations, my sentiment about the delivery of this feature will change (hopefully, the Supabase team is secretly refactoring the whole client/approach and introducing another architecture apart from PostgREST).
So, after using Supabase for months now, I've come to the following conclusions:
If these are deal breakers, then yeah, Supabase made technological choices that do not provide the features you require to have the DX/programming experience you desire, and adding those features could be years away. While we wait for this feature, you can program around it with the alternatives I've shared or not use Supabase. Not the ideal state we want, but it's where we are now.
Auto-generate a DB SQL function for INSERT or UPDATE that handles the basic logic when a relation is added to a table. This makes the function automatically available from the Supabase client when creating a relationship. Users can then modify them to their needs.
@bombillazo @gitbugr @arpanpreneur What if we instead allow creating database objects from javascript?
// create a function
let { status } = await meta
.from('function')
.insert({
schema_name: 'public', name : 'subtract',
parameters: ['a integer', 'b integer'],
return_type: 'integer', language: 'sql',
definition: 'select a - b'
})
let { body, error } = await pub
.rpc('subtract', {a: 63, b: 13})
console.log(body);
50
For more details, see https://gist.github.com/steve-chavez/c7e99bc5d8e7acba7166dbf108f7e18f (this is an old idea that was never executed).
This would only be possible with the service_role
role and the JS wouldn't be exposed to clients.
service_role
though. This could also be prevented with the CLI providing a structure for the "database JS" files.On the ORM/db library point, I chose to use Kysely for all our Deno backend logic, and it's been a great experience, what I initially expected from the Supabase client.
Is it "clean" to have to manage two clients in tandem? No, but hey, it works and is a practical alternative given the options and "solves" this while we get native support from a Supabase client.
I would love to pay if the feature is on.
Hey there! It's been 2 years since this current thread, and longer since this solution was initially requested. Has there been any updates regarding this?
any updates?
I WILL become a carpenter if this doesn't get solved. You've been warned.
Would also appreciate if this gets built. Has anyone figured out a workaround for this? Is there a way to "finish" an insert, then pass off execution to update a foreign key in a different table using another supabase execution thread? (after initial insert has "completed")
Would also appreciate if this gets built. Has anyone figured out a workaround for this? Is there a way to "finish" an insert, then pass off execution to update a foreign key in a different table using another supabase execution thread? (after initial insert has "completed")
maybe this is enough for you https://supabase.com/docs/reference/javascript/rpc
@Keegan-lee
The canonical Supabase way of doing that in a transaction is using RPCs; there is no way to use the JS client transactionally. You can still mount that workflow logic with multiple JS client statements, but there will be no rollback/atomicity, and you will need to handle any intermediate states.
but then how do you deal with authorization? @bombillazo
According to the Supabase docs, using RLS and the auth.jwt()
helper.
transactions please
Really need transaction
Feature request
Is your feature request related to a problem? Please describe.
Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.
Describe the solution you'd like
Implement an easy way to create transactions. Something like:
Describe alternatives you've considered
Create a rpc function that runs the commands. This is suitable for small apps, but wouldn't work for large apps, since multiple functions would be created.
Additional context
https://github.com/supabase/supabase-dart/issues/60 https://www.postgresql.org/docs/8.3/tutorial-transactions.html https://github.com/supabase/supabase/discussions/526