Easy functions and transactions using Postgres + PostgREST or Supabase

These days, I'm taking advantage of PostgreSQL's ability to handle logic, saving me all the work of an entire middleware layer. (Instead, using generalized middlewares like Graphile, Supabase, or Postgrest.)

A little gotcha in the Postgres learning curve has been working out the difference between stored procedures and functions, and realizing that the simpler table triggers and constraints (using with check) actually does a lot of what I need.

One place I got stuck is sql transactions, or grouping several sql statements into a group so that if one fails, they all "roll back" as if they didn't happen. PostgreSQL has some specific rules about using transactions in or out of functions and stored procedures -- but I found a simple way out.

It turns out that PostgREST, the middleware Supabase is based on, surrounds rpc() calls in a transaction already. So I could just define a function in sql with:

CREATE OR REPLACE FUNCTION public.topup(user text, amount integer)
    RETURNS integer
    LANGUAGE 'plpgsql'

AS $$
begin
  update users set balance = balance + amount;
  insert into "transactions" (user, amount, note) values (user, amount, 'top up');
  return 1;
end
$$;

In Supabase and PostgREST, this automatically exposes topup in the API. When called over the API, it's automatically wrapped in a transaction. (I tested this by placing a contraint on user.balance to not let it go above 100, and when the user's balance was higher than the amount in the rpc call, neither user.balance was updated, nor was a new record added to transactions.)

Now, I can write a lot of logic into the PostgreSQL database itself. This simplifies my middleware a lot, and often enables me to make apps that call Supabase/PostgREST/Graphile calls directly from my front-end components. An entire layer of devops and code removed.