Transactions and database functions

Commit several writes together or not at all, lock rows, and call your own SQL functions from your app, with filters on the rows they return.

Two ways to make several changes as one. A transaction sends a list of operations from your app that commit together. A database function keeps the logic in SQL, inside your database, and your app calls it by name. Both run as the signed-in user, under row-level security.

The tables on this page#

sql

create table accounts (
  id bigint generated always as identity primary key,
  owner uuid not null default auth.uid(),
  name text not null,
  balance numeric(12, 2) not null default 0 check (balance >= 0)
);
create table transfers (
  id bigint generated always as identity primary key,
  owner uuid not null default auth.uid(),
  from_id bigint references accounts (id),
  to_id bigint references accounts (id),
  amount numeric(12, 2) not null check (amount > 0)
);
alter table accounts enable row level security;
alter table transfers enable row level security;
create policy own on accounts for all to authenticated using (owner = auth.uid()) with check (owner = auth.uid());
create policy own on transfers for all to authenticated using (owner = auth.uid()) with check (owner = auth.uid());

import { createClient } from '@plexivo/client';

const db = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY);
await db.auth.signUp({ email, password });

dart

import 'package:plexivo_client/plexivo_client.dart';

final db = PlexivoClient(dataUrl, publishableKey);
await db.signUp(email: email, password: password);

Transactions#

Record the operations on tx without awaiting them. One operation can use another’s result with .ref(). They travel as one request and commit together:

const [current, savings] = await db.transaction((tx) => {
  const current = tx.from('accounts').create({ name: 'Current', balance: 1000 });
  const savings = tx.from('accounts').create({ name: 'Savings' });
  tx.from('transfers').create({ from_id: current.ref('id'), to_id: savings.ref('id'), amount: 250 });
  tx.from('accounts').where('name', 'Current').decrement('balance', 250);
  tx.from('accounts').where('name', 'Savings').increment('balance', 250);
});
console.log(current.id, savings.name);

dart

final results = await db.transaction((tx) {
  final current = tx.from('accounts').insert({'name': 'Current', 'balance': 1000});
  final savings = tx.from('accounts').insert({'name': 'Savings'});
  tx.from('transfers').insert({'from_id': current.ref('id'), 'to_id': savings.ref('id'), 'amount': 250});
  tx.from('accounts').where('name', 'Current').decrement('balance', by: 250);
  tx.from('accounts').where('name', 'Savings').increment('balance', by: 250);
});

If any step fails, nothing is saved, and the error says which step it was, counting from 0:

try {
  await db.transaction((tx) => {
    tx.from('accounts').create({ name: 'Holiday', balance: 100 });
    tx.from('accounts').where('name', 'Current').decrement('balance', 1000000);
  });
} catch (error) {
  console.log(error.code, error.operation);
}

A transaction holds up to 50 operations: reads, writes, upserts, counters and function calls (tx.rpc).

Lock rows#

Read a row with lockForUpdate() inside a transaction, and no one else can change it until the transaction commits, as GORM’s clause.Locking:

await db.transaction((tx) => {
  tx.from('accounts').where('name', 'Savings').lockForUpdate().find();
  tx.from('accounts').where('name', 'Savings').increment('balance', 10);
});

Also lockForUpdate({ nowait: true }), lockForUpdate({ skipLocked: true }) for job queues, and forShare().

Database functions#

Write the function in SQL and add @expose to its comment; only then can apps call it. Use security invoker so it runs with the caller’s rights and your policies still apply. Each argument needs a name.

sql

create function account_total() returns numeric
  language sql stable security invoker
  as $$ select coalesce(sum(balance), 0) from accounts $$;
comment on function account_total() is 'My total balance. @expose';

create function accounts_above(minimum numeric) returns setof accounts
  language sql stable security invoker
  as $$ select * from accounts where balance >= minimum $$;
comment on function accounts_above(numeric) is '@expose';

create function move_money(from_account bigint, to_account bigint, amount numeric) returns void
  language plpgsql security invoker
  as $$
  begin
    update accounts set balance = balance - amount where id = from_account;
    update accounts set balance = balance + amount where id = to_account;
    insert into transfers (from_id, to_id, amount) values (from_account, to_account, amount);
  end
  $$;
comment on function move_money(bigint, bigint, numeric) is '@expose';

Call it with named arguments. A function that returns rows takes the same select, filters, order and pages as a table:

const { data: total } = await db.rpc('account_total');

await db.rpc('move_money', { from_account: current.id, to_account: savings.id, amount: 50 });

const { data: rich, count } = await db.rpc('accounts_above', { minimum: 100 }, { count: 'exact' })
  .select('id, name, balance')
  .neq('name', 'Holiday')
  .order('balance', { ascending: false })
  .limit(10);

dart

final total = await db.rpc('account_total');

final rich = await db.rpc('accounts_above', {'minimum': 100})
    .select('id, name, balance')
    .order('balance', 'desc')
    .limit(10);

A function that returns setof one of your exposed tables can also bring in related rows through that table’s foreign keys. Over HTTPS, post the arguments as JSON to /rest/v1/rpc/<name>:

bash

curl -X POST "$PLEXIVO_DATA_URL/rest/v1/rpc/account_total" \
  -H "apikey: $PLEXIVO_PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "content-type: application/json" \
  -d '{}'

Which to use#

You want to	Use
Save a few related rows together from your app	A transaction with `.ref()`
Read, decide and write, with no one changing the row in between	A transaction with `lockForUpdate()`
Keep a rule in one place for every app and client	A database function
Search or report with SQL your app cannot express	A function that returns rows, or a view
Call a payment provider or send an email	Plexivo Functions

Next steps#

Raw SQLSQL and migrations from your server. RealtimePush changes to every client.

Updated 28 October 2026