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

[bdlukaa]

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

supabase/supabase-dart#60
https://www.postgresql.org/docs/8.3/tutorial-transactions.html
supabase/supabase#526

[steve-chavez]

Another alternative might be doing PostgREST/postgrest#818 (comment).

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 supabase/supabase-dart#60) could also be solved with a "relational upsert" interface.

Related:

• Save many-to-many relationships via js sdk supabase#4081
• Add transactions for read and write operations on one or more documents #237
• Database transactions supabase#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 PostgREST/postgrest#286 and PostgREST/postgrest#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...

[Logaritma26]

would be a very nice feature, dropping a comment later i'll check

[hichana]

+1 would like to see transactions for Supabase

[arpanpreneur]

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

Reply to your suggestions

1. Business Sensitive logic should be a part of your backend. - 100% agree, but Supabase Edge functions also don't support transactions and the recommended way to interact with the database is via the same JS client API; which IMHO is a wrong design choice. Why would I ever want to interact with my DB using a REST api layer? The core issue is, REST is stateless (HTTP) and DB transactions are a highly stateful affair (reason why every ACID compliant DB uses TCP based stateful protocols). Every query is a REST request which runs in a separate DB connection, so transactions are impossible with PostgREST at least in any clean manner.
2. Invest in developing SQL skills - You are misappropiating a design flaw as a DX issue. I have never seen a backend system in my life that talks to its own ACID compliant DB over a REST protocol. The fact is, there are inherent advantages of keeping large code (core business logic with decision making and things like that) within your main codebase and not as DB objects. Also, such business logic is often imperative and hence more readable in a language designed for it like Python/TS instead of a language like PLPGSQL which is built for more declarative logic. You can design better, using SOLID principles and OOP. Not to mention it is heck easier to debug as well.
3. If you still want to use JS - Not even a point of discussion here. Also your suggestion in this regard donesn't solve my problem of not wanting to keep core logic inside database instead of application codebase.
4. Use an ORM library - At this point why bother using Supabase at all? I could more easily do that in a Node application or a custom backend (even maintain connection pools which we won't be able to do in edge functions). If I am going to make models and stuff in JS, there are ways to get APIs running similar to PostgREST/Supabase without even writing much code with Prisma and libraries like that. And I would use those client libraries in my frontend app instead. Not to mention, it gives more control over multi-tenancy than using RLS policies which are known to have serious scalability problems. And want serverless? I would happily deploy my node app in a Azure Container Apps or similar platforms and scale it in a serverless manner. Oh, those libraries you mentioned probably won't work with Deno platform that supabase edge functions run on.

Sincere apologies if the above come of as rude / too strong.

My Solution Proposal

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.

1. Take the current interface of the "client-sdk" and make package just the dumb interfaces in a JS package.
2. Make two implementations of the same interfaces, same methods. One for the frontend app that uses the PostgREST like it does now (so no change). Another for use in the edge functions where it will actually build queries and submit to the DB instead of relying on PostgREST. Extend these interfaces here with support for transaction control (just like Knex).
3. Make ways to maintain connection pool for reuse connections between edge functions invocations (possibly it already exposes a PgPool endpoint).
4. The suggestion in 2 in this section is very different from point 4 in the perivious section because we are not introducing any extra alien interface like Prisma or Knex which has a lot of overlapping features with Supabase. I am not even sure if Knex or Prisma would work in Deno to say the least. Then there are connection level params that Supabase library automatically sets on the DB connections from PostgREST for RLS to work correctly (read username and all there). Also, I want to write in a syntax more consistent with the UI library just with the perks of being in the backend.
5. Make features to not allow any access to certain tables from the frontend sdk but allow from backend sdk.
6. Backend sdk should also be subject/scoped to the RLS policies given for the tables.
7. Make it easier to set row-level multi-tenancy by auto adding Where clauses in the queries instead of having to do RLS for this. Somewhat like django-multitenant or acts-as-tenant. Most people are trying to build SaaS products probably.
   https://blog.satoricyber.com/why-maintaining-row-level-security-in-postgres-is-hard/

What would this enable?

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.

Potential pitfalls

1. If we screw up the new implementation in the server, developers relying on Supabase might get surprised when they see the edge functions that were running just fine has started giving issues.

[bombillazo]

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:

• Custom, secure client-side transactions are nearly impossible since it requires keeping a direct connection to the DB open and managed (unless there is a secure way to connect to the DB from the client that I need to learn of).
• Even then, if we use the client on the backend only, adding support for full transaction management means abandoning/replacing (fully or in parallel) PostgREST, which is a big part of the Supabase architecture.
• Until that happens, I don't expect support for transactions with the Supabase client, and if you need transactions in the JS space of your logic, you WILL need to use a true ORM/DB library that supports that.
• In the case of edge functions, I completely agree. It's another issue I've taken issue with some of Supabase's tech decisions, so they've kinda shot themselves in the foot in some ways; I agree setting up another ORM is very tedious, even more so when you use Deno functions + redundancy of the Supabase client.

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.

[bombillazo]

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.

[danaoairuike]

I would love to pay if the feature is on.

[aeynaud]

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?

[thebadking]

any updates?

[munshi9541]

I WILL become a carpenter if this doesn't get solved. You've been warned.

[Keegan-lee]

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")

[thebadking]

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

[bombillazo]

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

[thebadking]

but then how do you deal with authorization? @bombillazo

[bombillazo]

According to the Supabase docs, using RLS and the auth.jwt() helper.

[BYohann]

transactions please

[tajultonim]

Really need transaction

[colinemondswieprecht]

Most of the discussion so far has been about implementing multi-table data insertion or updating operations. I do agree that transactions are necessary in those cases, and I do agree that stored procedures, while helpful, can be annoying. But that's not what I want to talk about.

Not having transaction support in the client causes another nuisance: Supabase apps cannot use locks.

In many apps that have a central data repository (you know, a database), it is desirable to prevent concurrent editing of the same data. If you don't do this, you may end up with situations where two users open the same row for editing at the same time, and then user A saves, and then user B saves, overwriting A's changes.

So how do you prevent this? There's really two ways: pessimistic or optimistic.

In the pessimistic approach, you would lock the row when you start editing:

begin transaction;
select * from some_table where id = $ID for update;

Then, when the user is done editing, you just do

update some_table set foo = $SOMETHING where id = $ID;
commit;

This doesn't work in Supabase because you can't hold the transaction open. Running select ... for update, even using RPC, doesn't do anything, because Supabase will always auto-commit (or auto-rollback) every transaction after the command is run, releasing the lock.

There is a possible workaround. What does work is having a separate table some_table_row_locks and maintaining reified locks in there. But this is not compatible with Postgres's native locking system, and now every operation that runs on the backend also has to be aware of this additional table of locks, lest backend operations update data that is currently being edited.

In the optimistic approach, you assume that conflicts usually don't happen, and just fail if the data has been edited from under you. When the user opens the row for editing, remember the current version.

public async beginEditing(rowId) {
    this.rowId = rowId;
    this.rowVersion = await this.supabase.from('some_table').select('updated_at').eq('id', row_id);
    ...
}

Then, when the user submits, download the current version again, check if it's equal to the version you previously saw, and only if it is, then you perform the update.

public async submit() {
    const currentVersion = await this.supabase.from('some_table').select('updated_at').eq('id', this.row_id);
    if (currentVersion !== this.rowVersion) {
        alert('Cannot save, please reload page!');
        return;
    }
    await this.supabase.from('some_table').update({ name: this.name }).eq('id', this.row_id);
}

This doesn't work in Supabase either because without transactions, there is a race condition between the version check and the update: if another user submits after we downloaded currentVersion, but before we finished calling update(), then we will silently overwrite the other user's changes.

Again, a workaround is available: don't ever call update(), instead, do all updates using stored procedures and rpc(). Then each stored procedure that performs an update has to take the last seen row version as an additional parameter, and has to perform the check as above. Again, this does work, but is an awkward hassle.

Obviously, the pessimistic approach as outlined above has severe scaling problems, and I would understand if that was the reason that the team is hesitant to implement this. However, I'm not certain if maximum "web-scaleness" is what most of Supabase's customers want anyway? Although I suppose it's a pointless discussion, because that's not even a client-side issue: even Supabase's backend doesn't support this, because the load balancer just can't do this.

[steve-chavez]

@colinemondswieprecht I've opened #581 for the subject you've mentioned, let's continue there.