When you build mobile apps with client-side code, standard CRUD operations hit a wall the moment your business logic requires multi-table transactions, heavy data aggregation, or secure server-side execution. Relying purely on client widgets to chain multiple inserts and updates leaves your app vulnerable to partial failures and network interruptions. That is where database functions come into play. By packaging procedural code directly inside your PostgreSQL database, you can execute complex operations atomically and cleanly.
Supabase bridges your database functions to your mobile frontend using the supabase_flutter client library via remote procedure calls. Instead of writing brittle client-side transaction chains, you can invoke a single database function that handles validation, row manipulation, and relational joins in milliseconds. This approach reduces network round trips, keeps business logic centralized where it belongs, and secures sensitive routines behind database permissions.
Setting Up PostgreSQL Functions for Supabase
Before you write any Dart code, you need a function living inside your Supabase schema. PostgreSQL functions can be written in PL/pgSQL, SQL, or even external languages, but PL/pgSQL is the standard choice for handling procedural logic, variables, and exception blocks.
Consider a scenario where an application needs to process an order, deduct inventory, and record a transaction ledger entry simultaneously. Doing this from Flutter via separate sequential requests risks leaving inventory deducted without an order if the app loses connection halfway through.
You create a database function in your Supabase SQL editor:
create or replace function create_order(
p_user_id uuid,
p_items jsonb,
p_total numeric
)
returns bigint
language plpgsql
security definer
as $$
declare
v_order_id bigint;
begin
-- Insert into orders table
insert into orders (user_id, total_amount, status)
values (p_user_id, p_total, 'pending')
returning id into v_order_id;
-- Insert order items (assuming p_items is a jsonb array of objects)
insert into order_items (order_id, product_id, quantity, price)
select
v_order_id,
(item->>'product_id')::uuid,
(item->>'quantity')::int,
(item->>'price')::numeric
from jsonb_array_elements(p_items) as item;
return v_order_id;
end;
$$;
This function wraps multiple writes into a single atomic block. If any insert statement fails, PostgreSQL rolls back the entire transaction automatically. This guarantees database integrity without writing complex compensation logic on your mobile client.
Invoking Basic RPCs from Flutter
Once your function exists in the database, calling it from your Flutter codebase requires the rpc method provided by the supabase_flutter package. You do not need custom API endpoints or intermediate server routes for standard database-adjacent logic.
Here is how you call a function that requires no parameters:
Future<void> fetchSystemStatus() async {
try {
final response = await Supabase.instance.client.rpc('check_system_status');
print('System status: $response');
} catch (error) {
print('Failed to fetch status: $error');
}
}
When your function expects arguments, you pass them inside a params map. The keys in your Dart map must match the parameter names defined in your PostgreSQL function signature.
Future<void> submitNewOrder(String userId, List> items, double total) async {
try {
final response = await Supabase.instance.client.rpc(
'create_order',
params: {
'p_user_id': userId,
'p_items': items,
'p_total': total,
},
);
final orderId = response as int;
print('Order created successfully with ID: $orderId');
} catch (error) {
print('Transaction failed: $error');
}
}
Supabase handles serialization behind the scenes, mapping JSON responses and scalar return values directly into native Dart types.
Advanced Parameter Handling and Complex Types
Real-world mobile apps often pass complex payloads, nested lists, or custom composite types to backend services. PostgreSQL handles JSON and JSONB types natively, making jsonb the ideal format for passing structured maps and arrays from Dart.
When passing arrays or nested maps, ensure your Dart structures serialize cleanly into JSON equivalents. For instance, bulk processing arrays can be executed with a database function designed to loop through elements efficiently:
create or replace function add_one_each(arr int[])
returns setof int
language sql
as $$
select unnest(arr) + 1;
$$;
In your Flutter project, you call this array-processing function like this:
Future<void> processBulkData() async {
final response = await Supabase.instance.client.rpc(
'add_one_each',
params: {'arr': [1, 2, 3]},
);
// Returns [2, 3, 4]
List<dynamic> results = response;
print(results);
}
You can also chain query modifiers directly onto your RPC response if your function returns a table or a set of rows. For example, if a function returns multiple rows, you can filter, sort, or isolate a single object using postgrest query builders:
final singleResult = await Supabase.instance.client
.rpc('list_stored_countries')
.eq('id', 1)
.single();
Error Handling and Security Best Practices
Because database functions execute with elevated privileges when marked as security definer, proper exception handling and schema scoping are mandatory to secure your application.
Catching PostgREST Exceptions
Network timeouts, constraint violations, and database syntax errors throw a PostgrestException in your Dart code. Always catch this specific exception type to handle user-facing errors cleanly:
import 'package:supabase_flutter/supabase_flutter.dart';
Future<void> executeSecureAction() async {
try {
await Supabase.instance.client.rpc('restricted_operation');
} on PostgrestException catch (error) {
// Handle database-specific errors (e.g., unique violation code '23505')
print('Database error code: ${error.code}, message: ${error.message}');
} catch (error) {
// Handle network or unexpected client errors
print('Unexpected error: $error');
}
}
Managing Function Privileges
By default, new PostgreSQL functions in the public schema might be executable by anonymous users depending on your database configuration. Always explicitly revoke execution rights from public roles and grant them only to authenticated users when necessary:
revoke execute on function create_order(uuid, jsonb, numeric) from public;
grant execute on function create_order(uuid, jsonb, numeric) to authenticated;
For more details on setting up client libraries and security rules, check the official Supabase Flutter Documentation and review PostgreSQL Official Documentation for advanced procedural programming concepts.
Frequently Asked Questions
Can I return custom Dart models directly from an RPC call?
Yes. Supabase returns JSON or scalar data. You can map the resulting Map<String, dynamic> or List<dynamic> directly into your application models using standard factory constructors like MyModel.fromJson(response).
When should I choose an RPC call over a standard Supabase table insert?
Use standard table queries for simple, single-table CRUD operations protected by Row Level Security. Use an RPC call when your operation requires multi-table transactions, conditional logic, complex data aggregation, or bypassing client-side round trips.
How do I handle optional parameters in PostgreSQL functions called from Flutter?
You can define default values for parameters in your PostgreSQL function signature (e.g., p_limit int default 10). If you omit those keys from your Dart params map, PostgreSQL automatically uses the default values.
Are RPC calls secure against SQL injection?
When you use the params map in the Supabase Dart client, arguments are passed safely as parameterized queries rather than concatenated strings, protecting your application against SQL injection vulnerabilities.
