Leveraging Supabase Database Functions

Leveraging Supabase Database Functions

Prathamesh's photo
·

6 min read

In this post, we'll dive deep into the architecture and implementation of a serverless calculator application. We'll explore how to leverage Next.js for the frontend, Supabase for the database, and Supabase database functions for performing calculations. This project demonstrates the power of modern web technologies and serverless architectures.

Project Overview

Our serverless calculator is a web application that performs basic arithmetic operations (addition, subtraction, multiplication, and division) on numbers stored in a database. The frontend is built with Next.js, providing a responsive and interactive user interface. The backend logic is implemented using Supabase database functions, which perform calculations directly on the data stored in the database.

Architecture

Our application follows a serverless architecture:

  1. The Next.js frontend is hosted on Netlify, providing a scalable and efficient static site hosting solution.

  2. Supabase serves as our backend, offering both database storage and serverless function execution.

  3. Database functions in Supabase handle the calculation logic, operating directly on the stored data.

  4. A database trigger automates the calculation process whenever new data is inserted or updated.

This architecture minimizes latency by performing calculations close to the data and reduces the need for data transfer between different services.

Database Schema Design

Let's examine the database schema for our calculator application:

CREATE TABLE revenue_sources (
  id SERIAL PRIMARY KEY,
  revenue1 NUMERIC,
  revenue2 NUMERIC,
  revenue3 NUMERIC,
  total_revenue NUMERIC
);

This schema allows us to store calculation requests and their results. The result column is initially NULL and will be populated by our database function.

Pro Tip: Using NUMERIC instead of FLOAT or DOUBLE PRECISION ensures precise decimal arithmetic, which is crucial for financial calculations or any scenario where floating-point imprecision is unacceptable.

Supabase Database Function

Now, let's implement the calculation logic as a Supabase database function:

CREATE FUNCTION calculate_total_revenue() RETURNS TRIGGER AS $$
BEGIN
  NEW.total_revenue := NEW.revenue1 + NEW.revenue2 + NEW.revenue3;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Pro Tip: By using a database function and trigger, we offload the calculation logic to the database layer. This approach can significantly improve performance for large-scale applications by reducing data transfer and leveraging the database's optimized execution engine.

In addition to the calculation trigger, we've implemented another trigger to automatically update total revenue when new revenue sources are added:

CREATE TRIGGER before_insert_revenue_sources
BEFORE INSERT ON revenue_sources
FOR EACH ROW
EXECUTE PROCEDURE calculate_total_revenue();

This trigger fires before each INSERT operation on the revenue_sources table, executing the calculate_total_revenue() function. This approach ensures that our total revenue is always up-to-date, maintaining data consistency and reducing the need for manual calculations or separate update operations. It's a great example of leveraging database triggers to automate business logic and maintain data integrity.

Pro Tip: When designing triggers, always consider their performance impact, especially for tables with high insert rates. In some cases, you might want to use an AFTER trigger or batch updates for better performance.

Next.js Frontend Implementation

Let's look at a simplified version of our Next.js component that interacts with Supabase:

'use client';

import { useState } from 'react';
import { Input } from '@/components/ui/input';
import { Button } from '@/components/ui/button';
import { supabase } from '@/lib/supabaseClient';

const RevenueForm = () => {
  const [revenue1, setRevenue1] = useState(0);
  const [revenue2, setRevenue2] = useState(0);
  const [revenue3, setRevenue3] = useState(0);
  const [totalRevenue, setTotalRevenue] = useState(null);

  const handleSubmit = async () => {
    try {
      const { data, error } = await supabase
        .from('revenue_sources')
        .insert([{ revenue1, revenue2, revenue3 }])
        .select('total_revenue')
        .single();

      if (error) {
        throw error;
      }

      setTotalRevenue(data.total_revenue);
    } catch (error) {
      console.error('Error inserting data:', error);
    }
  };

  const handleInputChange = (setter) => (event) => {
    const value = parseFloat(event.target.value) || 0;
    setter(value);
  };

  return (
    <div className="flex flex-col items-center gap-4">
      <Input
        type="number"
        placeholder="Revenue Source 1"
        value={revenue1}
        onChange={handleInputChange(setRevenue1)}
      />
      <Input
        type="number"
        placeholder="Revenue Source 2"
        value={revenue2}
        onChange={handleInputChange(setRevenue2)}
      />
      <Input
        type="number"
        placeholder="Revenue Source 3"
        value={revenue3}
        onChange={handleInputChange(setRevenue3)}
      />
      <Button onClick={handleSubmit}>Submit</Button>
      {totalRevenue !== null && (
        <div className="mt-4 text-lg">
          Total Revenue: ${totalRevenue}
        </div>
      )}
    </div>
  );
};

export default RevenueForm;

This component manages the state of our calculator inputs and interacts with Supabase to perform calculations. When the user clicks "Calculate", it inserts a new record into the calculations table and retrieves the calculated result.

Frontend UI for our Simple Calculator

Pro Tip: By leveraging Supabase's real-time capabilities, you could subscribe to changes in the calculations table, allowing for real-time updates in a collaborative environment.

Deployment and Hosting

Deploying our application involves two main steps:

  1. Deploy the Supabase project, including the database schema, functions, and triggers.

  2. Deploy the Next.js frontend to Netlify.

For Netlify deployment, ensure your package.json includes the following scripts:

{
  "scripts": {
    "dev": "next dev",
    "build": "next build",
    "start": "next start"
  }
}

Configure Netlify to use the build command for production builds.

Pro Tip: Use Netlify's environment variable management to securely store your Supabase URL and anon key. This keeps your sensitive information out of your codebase.

Supabase Database Testing

Testing our calculator by adding data. Notice that RLS is disabled in the screenshot below. More on this later.

Pro Tips and Best Practices

  1. Error Handling: Implement robust error handling in both your database functions and frontend code. Consider edge cases like division by zero or invalid operations.

  2. Performance Optimization: For complex calculations, consider using PostgreSQL's built-in mathematical functions or writing custom C functions for maximum performance.

  3. Security: Implement Row Level Security (RLS) in Supabase to ensure users can only access their own calculation data.

  4. Scalability: Monitor your database performance and consider implementing caching strategies for frequently performed calculations.

  5. Testing: Write comprehensive unit tests for your database functions and integration tests for your Next.js components.

  6. Observability: Implement logging and monitoring to track usage patterns and identify potential issues early.

  7. Row Level Security (RLS) in Supabase: Leverage Supabase's Row Level Security for fine-grained access control. Here's a pro tip for implementing RLS effectively:

     sqlCopy-- Enable RLS on the calculations table
     ALTER TABLE calculations ENABLE ROW LEVEL SECURITY;
    
     -- Create a policy that allows users to see only their own calculations
     CREATE POLICY "Users can only access their own calculations" ON calculations
     FOR ALL USING (auth.uid() = user_id);
    

    This policy ensures that users can only access rows in the calculations table where the user_id matches their authenticated user ID. Implement similar policies for INSERT, UPDATE, and DELETE operations to create a robust security model. Remember to add a user_id column to your table and populate it with auth.uid() in your application logic or through a trigger. Pro Tip: Use Supabase's auth.uid() function in your RLS policies to automatically tie data to authenticated users. This approach simplifies user-specific data access without requiring additional application logic.

Conclusion

This project demonstrates the power of combining Next.js, Supabase, and database functions to create a serverless calculator application. By leveraging database functions, we've moved our business logic closer to our data, potentially improving performance and reducing complexity in our application layer.

The serverless architecture we've implemented offers excellent scalability and cost-efficiency, making it suitable for applications of various sizes. As you build upon this foundation, consider exploring more advanced features like real-time collaboration, more complex mathematical operations, or integration with other APIs for extended functionality.