Supascale
Sign Up

Database Functions and RPC for Self-Hosted Supabase: A Complete Guide

Learn how to build efficient database functions and call them via RPC in self-hosted Supabase for faster APIs and cleaner code.

Cover Image for Database Functions and RPC for Self-Hosted Supabase: A Complete Guide

If you're running self-hosted Supabase, you've probably noticed that not every operation fits neatly into a simple SELECT or INSERT. Sometimes you need complex logic: aggregations across tables, conditional updates, transaction guarantees, or business rules that shouldn't live in your application code.

That's where database functions come in. And when you call them through Supabase's API using Remote Procedure Calls (RPC), you get the best of both worlds: the power of PostgreSQL running logic where your data lives, accessible from any client through a clean REST or SDK interface.

This guide covers everything you need to know about building and using database functions in your self-hosted Supabase deployment.

Why Database Functions Matter for Self-Hosted Users

When you're managing your own Supabase infrastructure, understanding database functions isn't optional—it's essential for building performant applications.

The Performance Case

Consider a typical e-commerce dashboard that needs to show:

  • Total orders per customer
  • Revenue by product category
  • Inventory levels with restock alerts

With standard API queries, you might make 5-10 round trips to your database. Each request travels from your app through the API gateway, gets processed by PostgREST, hits PostgreSQL, and returns. On self-hosted infrastructure, these round trips add latency.

With RPC functions, you collapse those into a single call. One developer reported dropping page load times from 3.5 seconds to under 800ms after moving complex logic into database functions.

Why This Matters More for Self-Hosting

On managed Supabase, their infrastructure handles connection pooling, caching, and optimization. On self-hosted, you're responsible for all of that. Database functions help because:

  • Reduced network overhead: Logic runs where data lives
  • Connection efficiency: One function call vs. multiple queries
  • Transaction safety: Complex operations wrapped atomically
  • Less application code: Business logic in SQL, not scattered across services

Creating Your First Database Function

Let's start with a practical example. You have a orders table and need to calculate customer statistics.

Basic Function Structure

CREATE OR REPLACE FUNCTION get_customer_stats(customer_uuid UUID)
RETURNS TABLE (
  total_orders BIGINT,
  total_spent NUMERIC,
  avg_order_value NUMERIC,
  last_order_date TIMESTAMPTZ
)
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT 
    COUNT(*)::BIGINT as total_orders,
    COALESCE(SUM(total), 0) as total_spent,
    COALESCE(AVG(total), 0) as avg_order_value,
    MAX(created_at) as last_order_date
  FROM orders
  WHERE user_id = customer_uuid
    AND status != 'cancelled';
$$;

Breaking this down:

  • CREATE OR REPLACE FUNCTION: Creates or updates the function
  • RETURNS TABLE: Returns multiple columns as rows (can also return single values)
  • LANGUAGE sql: Pure SQL—PostgreSQL can optimize this better than procedural code
  • SECURITY DEFINER: Runs with the function owner's permissions, not the caller's

Calling from Your Application

With the Supabase JavaScript client:

const { data, error } = await supabase
  .rpc('get_customer_stats', { customer_uuid: userId })

That's it. One call, all your stats.

SQL vs. PL/pgSQL: Choosing the Right Language

This decision affects performance more than most developers realize.

Use SQL When Possible

-- SQL function: PostgreSQL can inline and optimize
CREATE FUNCTION get_active_products()
RETURNS SETOF products
LANGUAGE sql
STABLE
AS $$
  SELECT * FROM products WHERE status = 'active';
$$;

PostgreSQL's query planner can see inside SQL functions and optimize them as part of the larger query. This is called inlining. The function essentially disappears, merged into whatever query calls it.

Use PL/pgSQL When You Need Control Flow

-- PL/pgSQL: When you need loops, conditions, or dynamic SQL
CREATE FUNCTION process_refund(order_uuid UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  order_record RECORD;
  refund_amount NUMERIC;
BEGIN
  -- Get order details
  SELECT * INTO order_record 
  FROM orders 
  WHERE id = order_uuid;
  
  IF NOT FOUND THEN
    RETURN FALSE;
  END IF;
  
  -- Check if already refunded
  IF order_record.status = 'refunded' THEN
    RETURN FALSE;
  END IF;
  
  -- Process refund (multiple steps)
  refund_amount := order_record.total;
  
  -- Update order status
  UPDATE orders SET status = 'refunded' WHERE id = order_uuid;
  
  -- Credit customer balance
  UPDATE customer_balances 
  SET balance = balance + refund_amount 
  WHERE user_id = order_record.user_id;
  
  -- Log the refund
  INSERT INTO refund_log (order_id, amount, processed_at)
  VALUES (order_uuid, refund_amount, NOW());
  
  RETURN TRUE;
END;
$$;

The trade-off: PL/pgSQL functions run as a black box. PostgreSQL can't optimize across the function boundary. Use them when you genuinely need procedural logic.

Security Considerations

SECURITY DEFINER vs. SECURITY INVOKER

This choice has major implications for Row Level Security (RLS) in your self-hosted setup.

-- Runs with YOUR permissions (the function owner)
CREATE FUNCTION admin_get_all_users()
RETURNS SETOF auth.users
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT * FROM auth.users;
$$;

-- Runs with the CALLER's permissions (respects RLS)
CREATE FUNCTION get_my_orders()
RETURNS SETOF orders
LANGUAGE sql
SECURITY INVOKER  -- This is the default
AS $$
  SELECT * FROM orders;
$$;

Best practice: Use SECURITY INVOKER (the default) unless you specifically need to bypass RLS. When you do use SECURITY DEFINER, limit what the function can do and validate all inputs.

Exposing Functions to the API

By default, functions in the public schema are exposed through PostgREST. To hide internal functions:

-- Create a private schema for internal functions
CREATE SCHEMA IF NOT EXISTS private;

-- Internal function, not exposed to API
CREATE FUNCTION private.calculate_discount(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
AS $$
  SELECT amount * 0.9;
$$;

-- Public function that uses the private one
CREATE FUNCTION apply_discount(order_id UUID)
RETURNS NUMERIC
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  order_total NUMERIC;
BEGIN
  SELECT total INTO order_total FROM orders WHERE id = order_id;
  RETURN private.calculate_discount(order_total);
END;
$$;

Performance Optimization Patterns

Batch Operations

Instead of calling an RPC for each item, batch them:

CREATE FUNCTION bulk_update_inventory(
  product_updates JSONB
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
  update_count INTEGER := 0;
  item JSONB;
BEGIN
  FOR item IN SELECT * FROM jsonb_array_elements(product_updates)
  LOOP
    UPDATE products 
    SET stock = (item->>'new_stock')::INTEGER
    WHERE id = (item->>'product_id')::UUID;
    
    IF FOUND THEN
      update_count := update_count + 1;
    END IF;
  END LOOP;
  
  RETURN update_count;
END;
$$;

Call it with:

const updates = [
  { product_id: 'uuid-1', new_stock: 50 },
  { product_id: 'uuid-2', new_stock: 30 },
  // ... hundreds more
];

const { data } = await supabase.rpc('bulk_update_inventory', {
  product_updates: updates
});

One API call, one transaction, atomic updates.

Using STABLE and IMMUTABLE Hints

Tell PostgreSQL about your function's behavior for better optimization:

-- STABLE: Returns same result for same inputs within a transaction
-- (can read from database, but doesn't modify it)
CREATE FUNCTION get_current_exchange_rate(currency TEXT)
RETURNS NUMERIC
LANGUAGE sql
STABLE  -- PostgreSQL can cache this within a query
AS $$
  SELECT rate FROM exchange_rates 
  WHERE currency_code = currency 
  AND valid_until > NOW();
$$;

-- IMMUTABLE: Always returns same result for same inputs
-- (can't access database at all)
CREATE FUNCTION calculate_tax(amount NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE  -- PostgreSQL can pre-compute this
AS $$
  SELECT amount * rate;
$$;

Debugging Functions in Self-Hosted Supabase

Using EXPLAIN with RPC

Supabase exposes PostgreSQL's EXPLAIN through the SDK:

const { data, error } = await supabase
  .rpc('get_customer_stats', { customer_uuid: userId })
  .explain({ analyze: true });

This shows you the actual execution plan. Look for:

  • Sequential scans on large tables (need indexes?)
  • High actual time vs. estimated time (statistics stale?)
  • Nested loops with high row counts (need a different approach?)

Logging for Production

Add logging to your functions for troubleshooting:

CREATE FUNCTION process_payment(order_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE LOG 'Processing payment for order: %', order_id;
  
  -- ... your logic ...
  
  RAISE LOG 'Payment processed successfully: %', order_id;
  RETURN TRUE;
  
EXCEPTION WHEN OTHERS THEN
  RAISE WARNING 'Payment failed for %: %', order_id, SQLERRM;
  RETURN FALSE;
END;
$$;

Check logs with docker logs supabase-db on your self-hosted instance.

Real-World Patterns

Leaderboard with Ranking

CREATE FUNCTION get_leaderboard(
  limit_count INTEGER DEFAULT 10,
  offset_count INTEGER DEFAULT 0
)
RETURNS TABLE (
  rank BIGINT,
  user_id UUID,
  username TEXT,
  score BIGINT
)
LANGUAGE sql
STABLE
AS $$
  SELECT 
    ROW_NUMBER() OVER (ORDER BY total_score DESC) as rank,
    u.id as user_id,
    u.username,
    COALESCE(s.total_score, 0) as score
  FROM auth.users u
  LEFT JOIN user_scores s ON u.id = s.user_id
  ORDER BY s.total_score DESC NULLS LAST
  LIMIT limit_count
  OFFSET offset_count;
$$;

Search with Full-Text and Filters

CREATE FUNCTION search_products(
  search_term TEXT DEFAULT NULL,
  category_filter TEXT DEFAULT NULL,
  min_price NUMERIC DEFAULT NULL,
  max_price NUMERIC DEFAULT NULL
)
RETURNS SETOF products
LANGUAGE sql
STABLE
AS $$
  SELECT *
  FROM products
  WHERE 
    (search_term IS NULL OR 
     to_tsvector('english', name || ' ' || COALESCE(description, '')) 
     @@ plainto_tsquery('english', search_term))
    AND (category_filter IS NULL OR category = category_filter)
    AND (min_price IS NULL OR price >= min_price)
    AND (max_price IS NULL OR price <= max_price)
  ORDER BY 
    CASE WHEN search_term IS NOT NULL 
      THEN ts_rank(
        to_tsvector('english', name || ' ' || COALESCE(description, '')),
        plainto_tsquery('english', search_term)
      )
      ELSE 0 
    END DESC,
    created_at DESC;
$$;

Common Pitfalls to Avoid

Don't Put HTTP Calls in Database Functions

Database functions shouldn't call external services. If you need that, use Edge Functions instead:

-- DON'T do this
CREATE FUNCTION send_notification(user_id UUID)
RETURNS VOID AS $$
  -- This won't work and blocks database connections
  PERFORM http_post('https://api.notify.com/send', ...);
$$ LANGUAGE plpgsql;

Instead, use a trigger that inserts into a queue table, and have an Edge Function poll that table.

Watch for N+1 in Loops

-- BAD: N+1 queries inside a loop
CREATE FUNCTION slow_process()
RETURNS VOID AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT * FROM orders
  LOOP
    -- This runs once per order!
    SELECT * FROM customers WHERE id = r.customer_id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- GOOD: Join upfront
CREATE FUNCTION fast_process()
RETURNS VOID AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN 
    SELECT o.*, c.* 
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
  LOOP
    -- Process with all data already joined
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Integrating with Your Self-Hosted Workflow

Version Control for Functions

Store your functions as migration files:

supabase/migrations/
  20260513_create_customer_stats_function.sql
  20260513_create_search_products_function.sql

Apply them during deployment:

supabase db push --db-url $PRODUCTION_DB_URL

Testing Functions

Create test functions that verify behavior:

CREATE FUNCTION test_get_customer_stats()
RETURNS BOOLEAN AS $$
DECLARE
  result RECORD;
BEGIN
  -- Create test data
  INSERT INTO orders (user_id, total, status)
  VALUES ('test-uuid', 100, 'completed');
  
  -- Run function
  SELECT * INTO result FROM get_customer_stats('test-uuid');
  
  -- Assert
  IF result.total_orders != 1 THEN
    RAISE EXCEPTION 'Expected 1 order, got %', result.total_orders;
  END IF;
  
  -- Cleanup
  DELETE FROM orders WHERE user_id = 'test-uuid';
  
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Conclusion

Database functions and RPC are fundamental to building efficient applications on self-hosted Supabase. They let you:

  • Move complex logic to the database layer
  • Reduce API round trips dramatically
  • Maintain transaction guarantees across operations
  • Keep business logic in one place, version-controlled

Start with simple SQL functions for queries, graduate to PL/pgSQL when you need control flow, and always keep security in mind with appropriate SECURITY DEFINER usage.

For self-hosted deployments, these patterns become even more important since you're optimizing for your specific hardware and network topology rather than relying on managed infrastructure.

Further Reading