Database transactions

[joshnuss]

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:

await supabase.transaction(
  supabase.from(...).insert(...),
  supabase.from(...).update(...)
)

Would reduce the number of situations where a postgres function is needed.

[eifo]

Need Transactions,

BEGIN / COMMIT / ROLLBACK

cannot find it anywhere in the docs?

[steve-chavez]

@mstade Check this issue PostgREST/postgrest#286.

[anatoliykmetyuk]

@steve-chavez any update on transactions support?

[geoguide]

Damn that's almost 4 years ago.

[alextbogdanov]

Such a basic thing. Instead of focusing on AI, how about you Supabase focus on actually making it useful

[SinaKarimi7]

This is very essential thing that we need in backend programming so I can't imagine why this feature not been implemented after 4 years!

[JasonChiu-dev]

me too.

I Do Need Transactions,

BEGIN / COMMIT / ROLLBACK

like:

await supabase.transaction(
supabase.from(...).insert(...),
if any error, rollback

supabase.from(...).update(...)
Commit
)

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

[earonesty]

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!

[steve-chavez]

@earonesty That sounds like a bug in the python lib. Consider reporting on https://github.com/supabase-community/postgrest-py/

[miketromba]

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

[miketromba]

*Prisma has interesting APIs for this type of thing as well Prisma Transactions and batch queries

[lab-bone-Max]

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.
I love Supabase and see potential but to evolve beyond 'demo' ToDo and chat applications then RPC is not workable. No project is going to be written in SQL and we tried PLV8 but that is just another layer of problems that are not integrated with version control, symbols, debugging etc.
We are building an ERM and have sufficient pre-flighting and low concurrency to live without transactions. I don't like it but can live with it however for the platform to evolve then needs to change.
It's not like transactions need to be built from the ground up as Postgres has done the herculean groundwork. I'm not trivialising the work as I know PostgREST does not have transactions but that's because it is stateless.
The supabase client runs local so state/session management during the transaction is far from impossible and easily enforceable as a predicate.

[steve-chavez]

Just to point out that we got all of this feedback guys. Long-running transactions is definitely on the roadmap: supabase/postgrest-js#219

Agreed. I've integrated Prisma client into my app for transactions and atomic operations...

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.

[ekansh005]

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.

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.

[jishnair]

You can use node-postgres on the server side which supports transactions and connection pooling.

[logemann]

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.

[bombillazo]

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.

[Angus-Moore-Dev]

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.

[steve-chavez]

postgres rpc functions that fire with no knowledge if it failed or not.

All function calls report a failure on frontend. Maybe share what you've tried.

[lordprana]

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. :(

[illia-romanenko]

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.

[illia-romanenko]

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

[barbinbrad]

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:

https://github.com/barbinbrad/carbon/blob/757aeeb78e0fef4d4df46d7224b0e8691816dc44/packages/carbon-database/supabase/functions/post-receipt/index.ts#L215-L249

[kstulgys]

@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 :)

[illia-romanenko]

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

[tortegao]

Gracias por los consejos chicos. Puedo verificar que este enfoque funciona. Se requiere una buena configuración si actualmente no usa funciones periféricas, pero definitivamente vale la pena desde mi perspectiva. A continuación, se muestra un código de ejemplo:

https://github.com/barbinbrad/carbon/blob/757aeeb78e0fef4d4df46d7224b0e8691816dc44/packages/carbon-database/supabase/functions/post-receipt/index.ts#L215-L249

The link no longer works, can you share the solution again please?

[barbinbrad]

Just following up here with my two cents after using transactions via kysely in Deno for a bit now:

• It's not horrible but it's not really a good DX either (dual DB clients).
• There's a fairly big learning to use two different database clients
• I haven't put in the time to move all of this into raw SQL because it seems like a bigger learning curve, but maybe that is the right way

I'd wonder if @steve-chavez and team have made progress on the hard problem of doing stateful transactions through a stateless API?

[bombillazo]

We've been using kysely for months now as well, and I really like it. We replaced all usages of the Supabase client to avoid the dual DB clients and haven't looked back. I find it more powerful and less restrictive than the PostgREST abstraction.

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.

[barbinbrad]

Yeah I like kysely too (after the learning curve). But like you, I don't care for the dual DB clients.

Do you run kysely client from the browser or are you pretty exclusively on the server side?

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?

[barbinbrad]

Early on, I was using the supabase client .select() method to do joins, but the typed results returned from that were pretty inaccurate, so now I just create a SQL view when I need to do joins and select("*") from the view.

[bombillazo]

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 kysely queries and exposing them via an API running on the Deno Edge Functions.

[bombillazo]

Right now the only thing tying us to Supabase is the unified auth across features (edge functions, storage, db, etc).

[A7DC]

How is this not a priority feature?

[zachbresler21]

Can we please make this feature happen, it's essential for backend development. Not sure why it hasn't been developed already?

[abdirahmn1]

+1 for this feature, it amazes me too how its hasn't been delivered yet!

[orlein]

I totally agree with you

[emmyduruc]

+1 for this feature

[abdirahmn1]

hello supabase team, i hope you are doing well
any updates on this feature?? is there an ETA?

if supabase implements this feature, it will hands down be the only BaaS i'll use till i die!

Thanks

[FajarAlnito]

Any update about this feature?

[nikitastryuk]

up

[abdirahmn1]

Hello... anyone here?.... This discussion has gone silent 😮‍💨 , is there an ETA for this feature??
is there anything on the pipelines to do batch operations??
@kiwicopple @joshnuss @steve-chavez

Thanks for your hard work, we appreciate the effort that went into supabase.

[bombillazo]

Read this and the rest of the thread supabase/postgrest-js#219 (comment)

[abdirahmn1]

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 idle time for when the transaction gets closed if there is no user response! idk why but it's such a deal breaker, the supabase idea just breaks and falls apart when you get beyond simple TODO apps!, The only thing it solved for me is not writing a bunch of GET endpoints!

[andrisole92]

In a need of transaction support too.

[ifzm]

+1 👍
Although it can be implemented using functions + orm at present, functions are still not very stable. I look forward to supbase-js native support. I checked the postgrest discussion and may expect them to complete it in the future. Is it possible for supabase officials to make some efforts in this regard?

[tortegao]

up

[NightTrek]

I came here looking to see why supabase does not support database transactions... turns out there is not a good reason. might need to switch to firebase my use case demands that i use many transactions to prevent overwriting data and to make sure it all fails or or all succeeds. disappointed to see this has been a request for years

[barbinbrad]

I think there's a good reason. It's because PostgREST doesn't implement transactions. See locked discussion: PostgREST/postgrest#286

But just personally, I don't love the response of "just use a function". Of course, it's no problem when you have low amounts of business logic. But when you have to implement a lot of custom logic, it's just untenable to do it all in SQL.

For example, if someone was willing to show me how easy it is to implement something like this in a SQL function, I would be happy to move in that direction:

https://gist.github.com/barbinbrad/5bf2d79b2ff75fd185009be40fb7d4fe#file-index-ts-L955

While SQL is definitely a turning-complete language -- there's a reason no one (guessing) uses it to do Advent of Code

[philmetzger]

Yeah fair point, depends a lot of use case and what you got there is a beast!
I unfortuately am not an expert at all in postgresql. I was looking for transactions for a use case of mine and was able to hack together a function with conflict management.
So yeah cant be of much more help sorry.
Though have you tried throwing that into ChatGPT?

[barbinbrad]

Here's claude's implementation (pretty impressive tbh). Not sure if it works, but it seems like there's enough there to get me going:

CREATE OR REPLACE FUNCTION process_purchase_invoice(
    p_invoice_id UUID,
    p_company_id UUID,
    p_created_by UUID,
    p_receipt_line_inserts JSONB,
    p_purchase_order_line_updates JSONB,
    p_journal_line_inserts JSONB,
    p_item_ledger_inserts JSONB,
    p_cost_ledger_inserts JSONB
) RETURNS VOID AS $$
DECLARE
    v_today DATE := CURRENT_DATE;
    v_accounting_period_id UUID;
    v_journal_id UUID;
    v_receipt_id UUID;
    v_readable_receipt_id TEXT;
BEGIN
    -- Start transaction
    BEGIN
        -- Get accounting period ID (you'll need to implement this function)
        SELECT get_accounting_period_id(v_today, p_company_id) INTO v_accounting_period_id;

        -- Process receipt lines
        IF jsonb_array_length(p_receipt_line_inserts) > 0 THEN
            FOR location_data IN SELECT * FROM jsonb_each(p_receipt_line_inserts)
            LOOP
                -- Generate readable receipt ID (you'll need to implement this function)
                SELECT get_next_sequence('receipt', p_company_id) INTO v_readable_receipt_id;

                -- Insert receipt
                INSERT INTO receipt (
                    "receiptId", "locationId", "sourceDocument", "sourceDocumentId",
                    "sourceDocumentReadableId", "externalDocumentId", "supplierId",
                    status, "postingDate", invoiced, "companyId", "createdBy"
                )
                SELECT 
                    v_readable_receipt_id,
                    (location_data.value->0->>'locationId')::UUID,
                    'Purchase Invoice',
                    p_invoice_id,
                    -- You'll need to get these values from somewhere
                    (SELECT "invoiceId" FROM purchase_invoice WHERE id = p_invoice_id),
                    (SELECT "supplierReference" FROM purchase_invoice WHERE id = p_invoice_id),
                    (SELECT "supplierId" FROM purchase_invoice WHERE id = p_invoice_id),
                    'Posted',
                    v_today,
                    TRUE,
                    p_company_id,
                    p_created_by
                RETURNING id INTO v_receipt_id;

                -- Insert receipt lines
                INSERT INTO receipt_line (
                    -- Add all necessary fields here
                    "receiptId"
                )
                SELECT 
                    -- Add all necessary fields here
                    v_receipt_id
                FROM jsonb_to_recordset(location_data.value) AS x(
                    -- Define the structure of your receipt line data here
                );
            END LOOP;
        END IF;

        -- Update purchase order lines
        FOR po_line IN SELECT * FROM jsonb_each(p_purchase_order_line_updates)
        LOOP
            UPDATE purchase_order_line
            SET -- Set the fields based on po_line.value
            WHERE id = (po_line.key)::UUID;
        END LOOP;

        -- Update purchase orders
        -- This part is complex and would require multiple nested loops and queries
        -- You may want to create a separate function for this logic

        -- Insert journal
        INSERT INTO journal (
            "accountingPeriodId", description, "postingDate", "companyId"
        ) VALUES (
            v_accounting_period_id,
            'Purchase Invoice ' || (SELECT "invoiceId" FROM purchase_invoice WHERE id = p_invoice_id),
            v_today,
            p_company_id
        ) RETURNING id INTO v_journal_id;

        -- Insert journal lines
        INSERT INTO journal_line (
            -- Add all necessary fields here
            "journalId"
        )
        SELECT 
            -- Add all necessary fields here
            v_journal_id
        FROM jsonb_to_recordset(p_journal_line_inserts) AS x(
            -- Define the structure of your journal line data here
        );

        -- Insert item ledger entries
        IF jsonb_array_length(p_item_ledger_inserts) > 0 THEN
            INSERT INTO item_ledger (
                -- Add all necessary fields here
            )
            SELECT * FROM jsonb_to_recordset(p_item_ledger_inserts) AS x(
                -- Define the structure of your item ledger data here
            );
        END IF;

        -- Insert cost ledger entries
        IF jsonb_array_length(p_cost_ledger_inserts) > 0 THEN
            INSERT INTO cost_ledger (
                -- Add all necessary fields here
            )
            SELECT * FROM jsonb_to_recordset(p_cost_ledger_inserts) AS x(
                -- Define the structure of your cost ledger data here
            );
        END IF;

        -- Update purchase invoice
        UPDATE purchase_invoice
        SET
            "postingDate" = v_today,
            status = 'Submitted'
        WHERE id = p_invoice_id;

    -- End transaction
    EXCEPTION WHEN OTHERS THEN
        RAISE;
    END;
END;
$$ LANGUAGE plpgsql;

[qhkm]

@barbinbrad How do you debug this?

[barbinbrad]

Right, no way I'm I'm using that.

[ggalmeida0]

+1, hopefully we can get this soon. Are there any work arounds if we don't use postgREST?

[alterjey9837]

any updates on transaction support?

[willdavidow]

It seems like this thread has devolved into a bunch of complaints around lack of support for transactions.

The folks at Supabase are surely aware that this is a very desired feature and have provided the bandaid of support via functions (yes I'm aware this is hard to maintain and less than ideal), but: complaining and one word comments, gifs, and so on are just useless noise.

I'm sure the feature is on their roadmap, or maybe there are technical roadblocks that would require too much work? I don't know, but this IS an open source platform. It seems like one path to figuring out how/if transactions can be added would be looking at what's missing from PostgREST, adding the functionality there and then integrating it into the various client libs.

I'm not saying that's easy, or even possible, but instead of complaints and gifs, maybe we could all figure out ways to contribute to the project/community and work together to try and make this feature happen.

[NinjaLukeI]

+1

[HungryDogStudios]

+1

[thibault-chausson]

Supabase response

Hi Thibault,

I think what you would like to know is when the library will support arbitrary queries. You can write any query within a database function and then call it from the front-end as an RPC. However, this does require some context switching: going from writing SQL to JS depending on the need.

In general, there aren't any imminent plans to implement this feature outside of RPCs. The PostgREST community has been going back and fourth on this for quite some time. It may be better to bring this up in PostgREST's GitHub to see if they can provide a more concrete answer.

Still, it's important for our teams to be aware of the demand for certain features. I'll share this with the PostgREST team to keep them informed.

Best,
Brian Brennglass
Supabase Support Engineer

[bombillazo]

Glad to see some official standing. Pretty much in line with what I wrote here 1 year ago.

[A7DC]

RPC functions are fine but how do we get it locally inside of our code and with version control? That solves the issue

[NickPuljic]

Using supabase cli and migrations has been pretty good for having version controlled RPC functions that deploy alongside our code. Not perfect by any means

[gerssivaldosantos]

Up

[ahmetkca]

+1
It is hard to debug Database functions and has the worst DX.

[damianesteban]

+1

Bottom line: Supabase needs support for transactions without having to use an RPC or Edge Function or whatever else..

[joshdance]

Welcome to the new year 2025! If anyone finds this looking for transaction support here is the latest info I could gather.

Currently not supported. Currently not on the roadmap. There are several workarounds.

You can encapsulate the logic in a Postgres SQL function then call it with rpc
You can do use supabase Edge functions to run a bunch of changes as a transaction - https://www.youtube.com/watch?v=xUeuy19a-Uw
You can use an ORM like Prisma - https://www.prisma.io/docs/orm/prisma-client/queries/transactions

This issue is 4 years old. So make your comment asking for it, vote up the other posts you find useful or want to support etc.

Did I miss anything?

[Eigilak]

Screeching coder noises....
I would love transactions!! 👀