Transactions in supabase

I have two tables that reference each other. For the sake of example, say they're Posts and Comments:

Posts
-----
id           bigint
title        text
body         text

Comments
--------
id           bigint
post_id      bigint references Posts
body         text

In my app, it doesn't make sense to have a Post without at least one Comment. So if the user creates something, it will have to start with one Post and a Comment related to that (later on, they can add additional Comments to this Post).

In Supabase, is there a way to simultaneously create a new Post and a new Comment referencing this new Post?

supabase
  .from("posts")
  .insert({title: 'new post', body: 'body of new post'})
  .select({ id })
  .then()
  .from("comments")
  .insert({post_id: id, body: 'comment body'})

In Rails, I believe there is something like

transaction do 
  # ...
end

That's pretty much what I'm looking for in the supabase js client. If this doesn't exist, is there a way to do it in PostgreSQL (and then run it with Supabase.rpc())?

Solution

There are two ways of achieving this:

Encapsulate the logic in a Postgres function then call it an RPC. Something like:

CREATE OR REPLACE FUNCTION create_post_and_comment(
    post_title TEXT,
    post_body TEXT,
    comment_body TEXT
) RETURNS VOID AS $$
BEGIN
    -- Insert a new post into the "posts" table
    INSERT INTO posts (title, body)
    VALUES (post_title, post_body)
    RETURNING id INTO id;

    -- Insert a new comment into the "comments" table
    INSERT INTO comments (post_id, body)
    VALUES (id, comment_body);
END;
$$ LANGUAGE plpgsql;

Create a trigger on the post table to create the comment entry:

-- Create a function that inserts a comment
CREATE OR REPLACE FUNCTION insert_comment()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO comments (post_id, body)
    VALUES (NEW.id, 'Default comment text'); -- You can change the default comment text as needed
    RETURN NULL; -- Returning NULL because we are only inserting into "comments" and not modifying the "posts" table
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that fires after inserting into "posts"
CREATE TRIGGER after_insert_post
AFTER INSERT ON posts
FOR EACH ROW
EXECUTE FUNCTION insert_comment();

Then, when using the client library:

const { data, error } = await supabase.rpc('create_post_and_comment', {
   "post_title": 'my title',
   "post_body": "body of the post", 
   "comment_body": "this is a comment"});

Note that if you are storing all comments in each post in a JSON/JSONB field, the trigger would be a better approach.