Allow long-running transactions at the client side or do relational inserts

[bdlukaa]

trafficstars

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:

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.

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

[steve-chavez]

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:

• https://github.com/supabase/supabase/discussions/4081
• https://github.com/supabase/postgrest-js/issues/237
• https://github.com/supabase/supabase/discussions/526

[JasonChiu-dev]

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!!

[dreinon]

Hi! What's the status of this issue?

[cory-weiner]

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.

[steve-chavez]

@dreinon @cory-weiner I'll revisit this one after launch week(August).

[steffenstolze]

@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.

[giladv]

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.

[unknown1337]

@dreinon @cory-weiner I'll revisit this one after launch week(August).

Do you have an update on this? really curious!

[steve-chavez]

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.

[unknown1337]

dear, one month has passed now, do you have any idea if this great feature is being picked up? :)

[unknown1337]

dear, one month has passed now, do you have any idea if this great feature is being picked up? :)

friendly reminder, really curious! :)

[gitbugr]

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.

[arpanpreneur]

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.

[yoont4]

This is really important for the backend development experience, we eagerly await this! 😄

[KenAKAFrosty]

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

[adifyr]

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.

[steffenstolze]

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.

[adifyr]

In our particular case, "bit inconvenient" is an understatement. For the complex business logic we're currently employing, using database functions is borderline infeasible.

[steffenstolze]

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.

[evelant]

@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.

[adifyr]

@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.

[bombillazo]

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:

1. If you have business-sensitive or critical logic to the point that you require transactions, I recommend keeping the code in your backend. This makes the issue of UI/FE use moot since you would use the Supabase client 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.
2. Invest in developing SQL skills. Supabase DB is PostgreSQL at its core, and PostgreSQL is wildly powerful and underestimated. Exposing many of its features (like transactions) via the Supabase client will be difficult and limiting compared to native, pure SQL. Leverage docs, communities, and ChatGPT to help you learn and explain how to implement tough logic. With this route, I recommend defining schema functions in SQL and calling them using 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.
3. If you still want to leverage some JS, you can use JS in Postgres! It's called PL/v8. I haven't used it myself, but it is possible to combine it. You can also call HTTP APIs from SQL. Like the tip above, you can define functions, implement them in JS, and call them usingrpc.
4. If SQL is too daunting or you must use JS/TS, another solution is to use edge functions with a DB interface package. Since Supabase is not a full fledge ORM, use a dedicated ORM/query builder library like Prisma or Sequelize that does support 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:

1. 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.
2. Make it easier to use JS in SQL functions by pre-generating the templates for a custom function
3. When creating a relation, the UI could suggest adding a function and creating the scaffolding for the function so people can add their logic.
4. Improving the creation and management of RPC/Edge function

[gitbugr]

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.

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.

1. 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.

I think this would be very helpful in the mean time while waiting on transaction support.

[bombillazo]

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.

[gitbugr]

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.

[adifyr]

@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.

[bombillazo]

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.

[mlynch]

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.

[akarabach]

any updates on it ?

[polenvinagre]

This is very, very, very important to my project.

Anyone here have time to prompt (ask) AI for a solution...