PostgREST Transactions

Question

I am using PostgREST to expose DB entities to a Springboot app which consumes those.

I have two entities inside my DB which are Person and City.

I would like to save the Person entity and the City at the same time, if any of those two fails I would like the other one to not persist on the PostgREST.

I would like to achieve Transactional behaviour but on PostgREST. Is there any chance to achieve this natively from the tool or without programmatically delete the just created record on exception?

Answer 1

You could create a PL/pgSQL procedure that receives data from City and Person, and insert on both tables, and calls a ROLLBACK if anything fails. Here are the docs with some examples: https://www.postgresql.org/docs/11/plpgsql-transactions.html

Postgrest will expose the procedure in the /rpc/{function_name} endpoint. Here are the docs: https://postgrest.org/en/stable/api.html#stored-procedures