Skip to content
AnvilBase

RPC & Stored Procedures

Anything you can’t express as a single REST read/write — multi-step logic, aggregations, atomic operations, custom search — belongs in a PostgreSQL function that you call over RPC:

POST /v1/rest/<project_id>/rpc/<function_name>

The function runs in one transaction, inside the same RLS context as any other data-plane request, so it’s both atomic and secure.

Define a function

Author it in the SQL Editor or via exec_sql, and capture it as a migration:

create or replace function increment_views(post_id uuid)
returns integer
language sql
as $$
  update posts set views = views + 1 where id = post_id
  returning views
$$;

Call it

Arguments go in the JSON body, keyed by parameter name:

Terminal window
curl -X POST "http://localhost:39001/v1/rest/<id>/rpc/increment_views" \
  -H "apikey: $ANON_KEY" -H "Content-Type: application/json" \
  -d '{"post_id":"3f2b…"}'
const { data } = await db.rpc('increment_views', { post_id: postId })
db.rpc("increment_views", {"post_id": post_id}).execute()
final views = await db.rpc('increment_views', params: {'post_id': postId});

Returning sets

A function returning setof / table(...) comes back as a JSON array, just like a table read:

create or replace function search_posts(q text, lim int default 10)
returns setof posts
language sql stable
as $$
  select * from posts
  where title ilike '%' || q || '%'
  order by created_at desc
  limit lim
$$;
const { data: results } = await db.rpc('search_posts', { q: 'launch', lim: 5 })

Atomic multi-table operations

The classic reason to use RPC — do several writes atomically:

create or replace function place_order(p_user uuid, p_items jsonb)
returns uuid
language plpgsql
as $$
declare
  v_order uuid;
begin
  insert into orders (user_id) values (p_user) returning id into v_order;
  insert into order_items (order_id, sku, qty)
    select v_order, (i->>'sku'), (i->>'qty')::int
    from jsonb_array_elements(p_items) i;
  return v_order;
end
$$;

If anything in the function fails, the entire operation rolls back — no partial orders.

Security: SECURITY DEFINER vs INVOKER

  • By default a function runs as SECURITY INVOKER — with the caller’s role and RLS context. This is what you usually want: the function respects the user’s permissions.
  • SECURITY DEFINER runs as the function owner and can bypass RLS. Use it deliberately and sparingly, validate every argument, and set a safe search_path (set search_path = public) to avoid hijacking. It’s the tool for controlled privilege escalation (e.g. letting an anon user create a signup record without exposing the table).

The privileged exec_sql RPC

AnvilBase exposes a virtual RPC, rpc/exec_sql, that runs arbitrary SQL. It requires the service_role scope and bypasses RLS — it powers the console SQL editor and admin tooling.

Terminal window
# server-side ONLY
curl -X POST "http://localhost:39001/v1/rest/<id>/rpc/exec_sql" \
  -H "apikey: $SERVICE_KEY" -H "Content-Type: application/json" \
  -d '{"sql":"select count(*) from posts"}'

Never expose exec_sql (or the service_role key) to clients. See SQL Editor and API Keys & Scopes.

Tips

  • Mark read-only functions stable (or immutable) so the planner can optimize and so they’re safe to call with the anon key.
  • Return void for fire-and-forget mutations, or returning the useful value.
  • Functions are part of your schema — put them in migrations so they promote to staging/prod with everything else.

Next: Auth → Overview.