TL;DR
Retool is really good at handling individual database queries, but the ephemeral nature of our backend resource connectors and the restrictions introduced by prepared statements make it challenging to coordinate multiple queries - especially if you want to utilize transactions. The best workaround is to utilize parameterized stored procedures.
Example Scenario
In the below example, I've built a simple form that inserts records into two separate tables - orgs and users - upon submission.
I specifically want the failure of either INSERT statement to negate the entire transaction, which is nightmarishly complex if I go down the route of defining two separate database queries.
The Solution
Instead, I can brush up on my SQL skills and create a parameterized stored procedure that offloads all of the heavy lifting to the database itself. The following code block shows what I came up with!
It includes a few clarifying comments, but I also want to make it clear that you only need to define the procedure once. Think of a procedure as a function that is stored in the database itself and, after creation, is triggered using a CALL statement.
CREATE OR REPLACE PROCEDURE create_new_company( -- set procedure name and parameters
org_name text,
owner_name text,
org_zip int
)
LANGUAGE plpgsql
AS $$
DECLARE new_org_id bigint;
BEGIN -- start of transaction
INSERT INTO orgs (name, zip)
VALUES (org_name, org_zip)
RETURNING id INTO new_org_id;
INSERT INTO users (name, org_id)
VALUES (owner_name, new_org_id);
COMMIT;
END; -- end of transaction
$$;
Closing Thoughts
The example I've shared here is a simple one, but the same principles can be applied to any number of use cases. If you have ever struggled to coordinate multiple dependent database queries, wrapping those INSERT or UPDATE statements within a single transaction and storing it as a parameterized procedure is a great option.
I hope this is helpful! Let me know if you need me to clarify anything that I've shared here or have any general follow up questions.