Commit any set of mutations to PostgreSQL via an RPC as a transaction

commit_transaction.sql

/** @param {CrudEntry[]} transaction_input */
create or replace function public.commit_transaction(transaction_input jsonb)
  returns void
  language plpgsql
  set search_path = ''
as $$
declare
  _crud_entry jsonb;
  _table_name text;
  /** @type {'0' | '1' | '2'} */
  _update_type text;
  _update_data text;
  _table_info jsonb;
  _column_name text;
  _column_info jsonb;
  _column_value text;
  _sql text := '';
begin

for _crud_entry in select * from jsonb_array_elements(transaction_input) loop
  _table_name := _crud_entry->>0;
  _update_type := _crud_entry->>1;
  _update_data := _crud_entry->>2;

  /** @type {Record<column_name: string, {
   *    column_default: string | null;
   *    typename: string;
   *    is_generated: boolean;
   *  }>}
   */
  _table_info := (select json_object_agg(column_name, jsonb_build_object(
    'column_default', column_default,
    'typename', udt_name,
    'is_generated', is_generated = 'ALWAYS'
  )) as table_info
  from information_schema.columns where table_name = _table_name);

  if _update_type = '0' then
    /**
     * +---------+
     * | INSERTS |
     * +---------+
     */
    _sql := _sql || format('insert into "public"."%s"(', _table_name);

    -- INSERT COLUMN DEFINITIONS
    for _column_name, _column_info in
      select * from jsonb_each(_table_info) loop
      continue when (_column_info->>'is_generated')::boolean;
      _sql := _sql || format('"%s",', _column_name);
    end loop;

    -- REMOVE LAST COMMA
    _sql := (select substr(_sql, 1, length(_sql) - 1));

    _sql := _sql || ') select';

    -- INSERT FIELDS
    for _column_name, _column_info in
      select * from jsonb_each(_table_info) loop
      continue when (_column_info->>'is_generated')::boolean;

      _column_value := case _column_info->>'typename'
        when 'text' then format('rec->>''%s''', _column_name)
        when 'uuid' then format('uuid(rec->>''%s'')', _column_name)
        else format(
          '(rec->>''%1$s'')::%2$s',
          _column_name,
          _column_info->>'typename'
        )
      end;

      -- ADD THE DEFAULT VALUE IF REQUIRED
      if _column_info->>'column_default' is not null then
        _column_value := format(
          'coalesce(%1$s, %2$s)',
          _column_value,
          _column_info->>'column_default'
        );
      end if;

      _sql := _sql || format(' %s,', _column_value);
    end loop;

    -- REMOVE LAST COMMA
    _sql := (select substr(_sql, 1, length(_sql) - 1));

    -- PASS INSERT VALUES
    _sql := _sql || format(
      ' from jsonb_array_elements(%s::jsonb) as t(rec);',
      quote_literal(_update_data)
    );

  elsif _update_type = '1' then
    /**
     * +---------+
     * | UPDATES |
     * +---------+
     */
    _sql := _sql || format('update "public"."%s" as orig set', _table_name);

    -- UPDATE FIELDS
    for _column_name, _column_info in
      select * from jsonb_each(_table_info) loop
      continue when _column_name = 'id'
        or (_column_info->>'is_generated')::boolean;

      _column_value := case _column_info->>'typename'
        when 'text' then format('rec->>''%s''', _column_name)
        when 'uuid' then format('uuid(rec->>''%s'')', _column_name)
        else format(
          '(rec->>''%1$s'')::%2$s',
          _column_name,
          _column_info->>'typename'
        )
      end;

      _sql := _sql || format(
        ' "%1$s" = case when (rec->''%1$s'')::text is null then "%1$s" else %2$s end,',
        _column_name,
        _column_value
      );
    end loop;

    -- REMOVE LAST COMMA
    _sql := (select substr(_sql, 1, length(_sql) - 1));

    -- PASS UPDATE VALUES
    _sql := _sql || format(
      ' from jsonb_array_elements(%s::jsonb) as t(rec) where uuid(rec->>''id'') = orig.id;',
      quote_literal(_update_data)
    );

  elsif _update_type = '2' then
    /**
     * +---------+
     * | DELETES |
     * +---------+
     */
    _sql := _sql || format(
      'delete from "public"."%1$s" as rec where rec.id in (select uuid(jsonb_array_elements_text(%2$s)));',
      _table_name,
      quote_literal(_update_data)
    );
  end if;
end loop;

execute _sql;

return;
end;
$$;

commitTransaction.ts

import type { UuidString } from "@/src/uuid";
import type {
  PostgrestSingleResponse,
  SupabaseClient,
} from "@supabase/supabase-js";
import type { ReadonlyDeep } from "type-fest";
import type { SupabaseDatabase, TablesInsert, TablesUpdate } from "./types";

export type PublicTableName = keyof SupabaseDatabase["public"]["Tables"];

export const enum UpdateType {
  INSERT = 0,
  UPDATE = 1,
  DELETE = 2,
}

export type CrudEntry<TName extends PublicTableName = PublicTableName> =
  | [table: TName, type: UpdateType.INSERT, data: TablesInsert<TName>[]]
  | [table: TName, type: UpdateType.UPDATE, data: TablesUpdate<TName>[]]
  | [table: TName, type: UpdateType.DELETE, data: UuidString[]];

export async function commitTransaction(
  supabase: SupabaseClient<SupabaseDatabase>,
  transaction: ReadonlyDeep<CrudEntry[]>,
): Promise<PostgrestSingleResponse<undefined>> {
  const res = await supabase.rpc("commit_transaction", {
    transaction_input: transaction,
  });
  return res;
}