Database transactions in Supabase

Database

What Are Database Transactions?

Before diving into Supabase implementation, let's understand what database transactions are and why they're crucial for data integrity.

The Money Transfer Problem

Imagine you're transferring money to your friend. This seemingly simple operation actually involves two critical steps:

  1. Debit the amount from your account
  2. Credit the amount to your friend's account

What happens if there's a system failure between these steps? You could end up with money debited from your account but never credited to your friend's account. This is exactly the kind of data inconsistency that can destroy user trust and create serious business problems.

Understanding Database Transactions

A database transaction is a sequence of database operations that are treated as a single unit of work. The key principle is simple: all operations succeed together, or all operations fail together.

This follows the ACID properties:

  • Atomicity: All operations complete or none do
  • Consistency: Data remains in a valid state
  • Isolation: Concurrent transactions don't interfere
  • Durability: Committed changes persist

Data inconsistency and corruption can have catastrophic consequences for any application. Database transactions are your safety net.

Implementing Transactions in Supabase

Here's the important thing to know: Supabase doesn't support native database transactions from its client libraries. However, since Supabase is built on PostgreSQL, we can leverage PostgreSQL's powerful stored procedures (Through functions) to implement transactions.

The solution is to create a PostgreSQL function that encapsulates our transaction logic, then call it using Supabase's rpc method.

Step 1: Create the Database Function

Here's a PostgreSQL function that demonstrates how to implement atomic money transfers using database transactions. This function ensures that both the debit and credit operations succeed together or fail together, maintaining data consistency.

Note: This is a simplified example focused on demonstrating transaction concepts. In a production environment, you would add additional validations such as sufficient balance checks, account existence verification, and business rule enforcement.

create or replace function transfer(
  amount bigint, 
  source bigint, 
  destination bigint
) 
returns boolean as $$
declare
  affected_rows bigint;
begin
  -- Debit from source account
  update account set balance = balance - amount where account_id = source;
  get diagnostics affected_rows = row_count;
  
  if affected_rows = 0 then
    raise exception 'Failed to debit from source account';
  end if;
  
  -- Credit to destination account
  update account set balance = balance + amount where account_id = destination;
  get diagnostics affected_rows = row_count;
  
  if affected_rows = 0 then
    raise exception 'Failed to credit to destination account';
  end if;
  
  -- Return true on successful completion
  return true;
  
exception
  when others then
    -- Return false on any error
    return false;
end
$$ language plpgsql;

Step 2: Call the Function from JavaScript

Here's how you initiate the transfer from your JavaScript code:

const { data, error } = await supabase.rpc('transfer', {
  amount: 10,
  source: 127890
  destination: 123456
})

Why This Approach Works

Since the transfer function runs entirely within PostgreSQL, both operations are automatically wrapped in a transaction. This means either both statements are executed or none of them are executed.

The Problem with Client-Side Operations

If you attempted the same logic from JavaScript using separate Supabase calls, there's a possibility of only one statement being executed. The amount may be deducted from the source account, but may not be credited to the destination account due to network failures or application crashes.

This is why using PostgreSQL functions for transactional operations is crucial for data consistency and reliability.

Here's what the problematic client-side approach would look like:

// ❌ PROBLEMATIC: Non-atomic operations
async function transfer(amount, sourceId, destinationId) {
  // First, get current balances
  const { data: sourceAccount } = await supabase
    .from('account')
    .select('balance')
    .eq('account_id', sourceId)
    .single()
    
  const { data: destAccount } = await supabase
    .from('account')
    .select('balance')
    .eq('account_id', destinationId)
    .single()
  
  // Step 1: Debit from source account
  const { error: debitError } = await supabase
    .from('account')
    .update({ balance: sourceAccount.balance - amount })
    .eq('account_id', sourceId)
  
  if (debitError) {
    throw new Error('Failed to debit from source account')
  }
  
  // Step 2: Credit to destination account
  // ⚠️ DANGER: If this fails, money is lost!
  const { error: creditError } = await supabase
    .from('account')
    .update({ balance: destAccount.balance + amount })
    .eq('account_id', destinationId)
    
  if (creditError) {
    // Money already debited but credit failed!
    throw new Error('Failed to credit to destination account')
  }
}

The problem: if the network fails or the application crashes between the two operations, you could end up with money debited but never credited.

Could you implement client-side transaction logic to handle these failures? Technically yes, but it would require building complex retry mechanisms, rollback procedures, and failure recovery systems that databases already provide out of the box. You'd need to handle network timeouts, partial failures, idempotency, and state reconciliation essentially reimplementing what PostgreSQL already does reliably at the database level.

When to Use Database Transactions

Use database transactions when your operation meets any of these criteria:

1. Multiple Related Operations

If you need to perform multiple database operations that must all succeed or all fail together.

2. Data Consistency Requirements

When partial completion would leave your data in an invalid or inconsistent state.

3. Business Logic Atomicity

If the business operation only makes sense when all database changes are completed as a unit.

4. Rollback Necessity

When you need the ability to automatically undo all changes if any part of the operation fails.

When NOT to Use Transactions

Avoid transactions when:

  • Performing a single, independent database operation
  • The operations are unrelated and can succeed or fail independently
  • You're only reading data (no modifications)
  • The operation involves long-running processes or user interactions

Simple rule: If answering "yes" to "Would partial completion create problems?" then use a transaction.

Conclusion

Database transactions are a critical tool for maintaining data integrity in any application. While Supabase doesn't provide direct transaction support through its client libraries, PostgreSQL functions offer a powerful and reliable solution.

By encapsulating your multi-step operations in PostgreSQL functions and calling them via rpc, you ensure that your critical business operations remain atomic and consistent. Remember that data corruption and inconsistency can have serious consequences for your application and user trust.

7cd8770b-ee9c-4267-9ecb-28cdcd8eb7f0