Forem: Josh Campbell

Unlocking User Data: Building a Secure Supabase Edge Function

Josh Campbell — Tue, 26 Nov 2024 18:15:13 +0000

In this tutorial, we'll delve into the world of Supabase Edge Functions. We'll explore how to set up a local development environment and build a simple function to list all users within your Supabase project. This hands-on guide will empower you to leverage the full potential of Supabase Edge Functions for your next project.

Prerequisites

Assuming you have your local Supabase CLI setup and linked to your remote project.

Make sure Supabase is running with:

supabase start

Creating a New Edge Function

Run this to create a new edge function named 'admin-list-all-users':

supabase functions new admin-list-all-users

This creates a new folder under ./supabase named 'admin-list-all-users' and inside that folder is an index.ts file.

Running Functions Locally

To run functions locally using the CLI, execute this command. Note that this ties up a console window until it is stopped with control + c. While it is running, you can see information about the function calls, and even more if you run it with the --debug flag.

supabase functions serve

Before you edit the function, it would be a good idea to run the demo "hello world" code to make sure the basics are working. This code should be in your newly created function already but here it is just in case.

Demo Code

// Setup type definitions for built-in Supabase Runtime APIs
import 'jsr:@supabase/functions-js/edge-runtime.d.ts';

console.log('Hello from Functions!');

Deno.serve(async (req) => {
  const { name } = await req.json();
  const data = {
    message: `Hello ${name}!`
  };

  return new Response(JSON.stringify(data), { headers: { 'Content-Type': 'application/json' } });
});

Running Your Function Locally

There is also a curl command at the bottom of your index.ts file that looks like this:

curl -i --location --request POST 'http://127.0.0.1:54321/functions/v1/admin-list-all-users' \
    --header 'Authorization: Bearer SUPABASE_ANON_KEY_HERE' \
    --header 'Content-Type: application/json' \
    --data '{"name":"World"}'

Your SUPABASE_ANON_KEY should be automatically populated with the correct key for your local setup. Copy and paste that to your terminal and run it.

You should see a response like this:

{"message":"Hello World!"}%

The Fun Part

Now lets replace the demo code with this:

// Setup type definitions for built-in Supabase Runtime APIs
import 'jsr:@supabase/functions-js/edge-runtime.d.ts';

// Import necessary modules with full URLs
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

// Access environment variables for Supabase URL and Service Role Key
const supabaseUrl = Deno.env.get('SUPABASE_URL')!;
const serviceRoleKey = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!;

// Initialize the Supabase client with the service role key
const supabase = createClient(supabaseUrl, serviceRoleKey);

serve(async (_req) => {
  try {
    // Fetch all users
    const { data, error } = await supabase.auth.admin.listUsers();

    if (error) throw error;

    // Return the list of users as JSON
    return new Response(JSON.stringify(data), {
      headers: { 'Content-Type': 'application/json' },
      status: 200
    });
  } catch (error: any) {
    // Handle errors and return a JSON response
    return new Response(JSON.stringify({ error: error.message }), {
      headers: { 'Content-Type': 'application/json' },
      status: 500
    });
  }
});

This will automatically pull your Supabase URL and service role key then return a JSON object containing all users.

Again, run the function locally like this. Notice that we do not need to pass any data to the function this time.

curl -i --location --request POST 'http://127.0.0.1:54321/functions/v1/admin-list-all-users' \
    --header 'Authorization: Bearer SUPABASE_ANON_KEY_HERE' \
    --header 'Content-Type: application/json'

Conclusion

By following these steps, you've successfully created and tested a Supabase Edge Function locally that can securely access and pass user data to the client. This powerful ability enables you to extend your Supabase application's capabilities without the need for complex server setups.

Take the next steps and deploy your function to run on your linked (live) Supabase project. Explore the vast possibilities of Edge Functions and leverage them to create secure, innovative and efficient applications by reading the Supabase docs.

Supabase Edge Functions DOCS

Watch for part 2 where we lock the function down using Supabase Auth.

Intro to Svelte 5: The Compiler-Free Frontend Framework

Josh Campbell — Mon, 21 Oct 2024 16:45:51 +0000

Svelte has carved a unique niche in the frontend framework landscape. Known for its blazing-fast performance and developer-friendly approach, Svelte recently released its highly anticipated version 5. This update boasts exciting new features and improvements that solidify Svelte's position as a compelling choice for modern web development.

What is Svelte?

Unlike React or Vue, Svelte takes a different approach. It's a component-based framework, but it compiles your code at build time. This means Svelte removes unnecessary parts of your application, resulting in smaller bundle sizes and lightning-fast performance.

What's New in Svelte 5?

Svelte 5 introduces some major improvements, including:

Runes: This new feature simplifies state management and data manipulation within your components. Runes like $derived and $state make reactivity more intuitive and easier to work with.
Snippets: Svelte 5 allows you to define reusable chunks of markup within your components. This promotes code organization and reduces redundancy.
Improved Reactivity: Svelte 5 streamlines reactivity handling, leading to a more robust and reliable experience for developers.
New Built-in Functions: Gain access to functions like mount and unmount for more granular control over your components' lifecycle.
Benefits of Using Svelte 5:

Performance: Enjoy the benefits of smaller bundle sizes and incredibly fast runtime performance.
Developer Experience: Svelte is known for its clean syntax and intuitive approach, making it easy to learn and use.
Component-Based Architecture: Structure your application into reusable components for better maintainability and scalability.
Getting Started with Svelte 5:

Head over to the official Svelte website (svelte.dev) to get started with the framework. You'll find comprehensive documentation, tutorials, and a vibrant community ready to help you on your Svelte journey.

Is Svelte 5 Right for You?

If you're looking for a performant, modern, and developer-friendly framework, Svelte 5 is definitely worth considering. Its unique approach and recent improvements offer a compelling alternative to traditional options.

Dive into Svelte 5 and experience the joy of building lightning-fast web applications!

The docs are currently a work in progress and you can check out the preview here. There are also many great YouTube content creators that can get you started. Here are a few of my personal favorites are.

JoyofCode
Syntax
Huntabyte - He hasn't had any new content on Svelte 5 in a while, but when he does it will be worth the watch.

How VOLATILE, IMMUTABLE, and STABLE Attributes Affect Functions in Supabase

Josh Campbell — Wed, 02 Oct 2024 17:48:19 +0000

In Supabase, which is built on PostgreSQL, when you create functions using PL/pgSQL (Procedural Language/PostgreSQL), you have the option to specify the volatility category of each function: VOLATILE, STABLE, or IMMUTABLE. These categories describe how the function interacts with the database, how predictable its output is, and whether it can have side effects. Understanding these distinctions is crucial for writing efficient, safe, and optimized database functions.

Volatility Categories Explained

1. IMMUTABLE Functions

Definition: Functions that always return the same result for the same input parameters and have no side effects. They do not read or modify the database.
Characteristics:
- Deterministic: Their output depends solely on their input parameters.
- No Side Effects: They cannot access tables, modify database data, or depend on any external state.
Examples:
- Mathematical computations: abs(), sqrt()
- String manipulations: upper(), concat()
Usage Notes:
- Can be used in index expressions and materialized views.
- The query planner can optimize queries by precomputing or caching results.

2. STABLE Functions

Definition: Functions that do not modify the database but can read data. They return consistent results within a single query execution but might return different results in different executions if the underlying data changes.
Characteristics:
- Read-Only Access: They can read tables or views but not modify them.
- Consistent Within a Query: The function returns the same result throughout a single query execution.
Examples:
- Lookup functions: fetching a value from a table based on an input.
- Functions that depend on the current time (CURRENT_TIMESTAMP is actually STABLE).
Usage Notes:
- Suitable for functions that depend on database data that doesn't change during the query.
- Cannot be used in certain index expressions because the output may change between queries.

3. VOLATILE Functions

Definition: Functions that can perform any operations, including modifying the database. The results can vary even within a single query execution.
Characteristics:
- Non-Deterministic: Results may change for the same inputs at any time.
- Possible Side Effects: Can insert, update, delete data, or perform external actions.
Examples:
- Functions that generate random values: random()
- Functions that read system status: currval()
- Functions that modify data: inserting logs, updating records.
Usage Notes:
- Cannot be optimized by the query planner.
- Always executed as they appear in the query.

Why Volatility Matters

The volatility category affects how PostgreSQL (and thus Supabase) handles function execution and query optimization:

Performance Optimization:
- The query planner can optimize queries more aggressively with IMMUTABLE functions, such as by using indexes or caching results.
- STABLE functions have moderate optimization, while VOLATILE functions have minimal optimization.
Correctness and Safety:
- Misclassifying a function can lead to incorrect results or unintended side effects.
- For example, declaring a function as IMMUTABLE when it reads database tables could cause the planner to use stale data.
Security and RLS (Row Level Security):
- Incorrect volatility classification can affect how RLS policies are enforced.
- Functions that access tables with RLS policies need careful consideration.

Applying Volatility Categories in Supabase Functions

When creating functions in Supabase, you choose the appropriate volatility based on what your function does:

When to Use IMMUTABLE

Use Cases:
- Functions that perform calculations or transformations purely based on input parameters.
- Example: A function that converts temperatures from Celsius to Fahrenheit.
Considerations:
- Do not access or modify database tables.
- Ensure that the function's output depends only on its inputs.

When to Use STABLE

Use Cases:
- Functions that read data from the database but do not modify it.
- Example: A function that returns a user's role based on their ID.
Considerations:
- Safe to read from tables, but writes are not allowed.
- The function's result might change between queries if the underlying data changes.

When to Use VOLATILE

Use Cases:
- Functions that modify the database or perform actions that can produce different results even within the same query.
- Example: A function that logs actions to a table or generates random numbers.
Considerations:
- Necessary for functions with side effects.
- Accept that minimal query optimization will occur.

Examples

IMMUTABLE Function Example

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER) RETURNS INTEGER
LANGUAGE SQL IMMUTABLE AS $$
  SELECT a + b;
$$;

Explanation: This function simply adds two numbers. It doesn't access the database, so it's IMMUTABLE.

STABLE Function Example

CREATE FUNCTION get_user_email(user_id UUID) RETURNS TEXT
LANGUAGE SQL STABLE AS $$
  SELECT email FROM users WHERE id = user_id;
$$;

Explanation: The function reads from the users table but doesn't modify any data. It's STABLE because it might return different emails if the users table changes between queries.

VOLATILE Function Example

CREATE FUNCTION record_page_view(page_id UUID, user_id UUID) RETURNS VOID
LANGUAGE plpgsql VOLATILE AS $$
BEGIN
  INSERT INTO page_views (page_id, user_id, viewed_at)
  VALUES (page_id, user_id, NOW());
END;
$$;

Explanation: This function inserts a new record into the page_views table, modifying the database. It's VOLATILE.

Impact on Query Planning and Optimization

IMMUTABLE Functions:
- Can be evaluated at plan time if inputs are constants.
- Can be used in index expressions and indexed searches.
- Example: An index on LOWER(email) allows for case-insensitive searches using an IMMUTABLE function.
STABLE Functions:
- Evaluated once per query execution when used in a query.
- Cannot be used in index expressions.
- The planner assumes they return the same result during a single query.
VOLATILE Functions:
- Evaluated every time they are called in the query.
- No assumptions are made about the result consistency.
- Can't be optimized or pre-evaluated.

Security Considerations in Supabase

Row Level Security (RLS):
- RLS policies can call functions to determine access rights.
- Functions used in RLS should typically be STABLE or IMMUTABLE to ensure consistent behavior during policy enforcement.
Function Privileges:
- Be cautious with SECURITY DEFINER functions, which run with the privileges of the function's owner.
- Ensure that functions don't unintentionally bypass security checks.

Best Practices

Accurately Declare Volatility:
- Always declare the correct volatility to match the function's behavior.
- Resist the temptation to declare a function as less volatile for potential performance gains if it doesn't match its true behavior.
Avoid Side Effects in STABLE and IMMUTABLE Functions:
- Do not perform write operations or rely on mutable external state.
- Ensure that the function's classification aligns with what it actually does.
Use IMMUTABLE Functions When Possible:
- For functions that perform calculations or manipulations without database interaction.
- Allows for better optimization and potential use in indexes.
Consider Function Dependencies:
- If an IMMUTABLE function calls another function that is STABLE or VOLATILE, the volatility must match the most volatile function in the chain.
- PostgreSQL enforces that a function cannot be less volatile than any functions it calls.
Test Function Behavior:
- Verify that functions behave as expected, especially when used in complex queries or with RLS policies.
- Check for consistency and correctness in different scenarios.

Summary

VOLATILE:
- Use for functions that can change data or whose results can vary unpredictably.
- Example: Functions that insert logs, generate random numbers, or read system variables that change rapidly.
STABLE:
- Use for read-only functions that query the database but don't modify it.
- Example: Functions that fetch and return data from tables.
IMMUTABLE:
- Use for functions that always produce the same output from the same input and have no side effects.
- Example: Pure mathematical functions or deterministic conversions.

By correctly categorizing your functions in Supabase, you help the database engine optimize queries effectively and maintain data integrity and security. Accurate volatility declarations ensure that the query planner makes the right assumptions, leading to better performance and reliable application behavior.

Additional Notes

Function Costs and Rows Estimates:
- You can also specify COST and ROWS estimates when creating functions, which can further help the query planner.
Updating Function Definitions:
- If you change a function's behavior, remember to update its volatility classification accordingly.
Documentation and Maintenance:
- Document your functions with comments explaining their purpose and why a particular volatility category was chosen.

Understanding the differences between VOLATILE, STABLE, and IMMUTABLE functions is essential when working with Supabase's PL/pgSQL functions. By carefully considering how your functions interact with the database and their potential side effects, you can write efficient, secure, and reliable database code.

The Great Data Debate: Is It 'Data Is' or 'Data Are'?

Josh Campbell — Wed, 02 Oct 2024 14:55:11 +0000

Hey folks! Ever stumbled over whether to say "data is" or "data are"? You're not alone. This little conundrum has tripped up many, including me! So, let's dig into why both usages exist and how you can confidently choose the right one for your context.

Origin of the Word "Data"

Latin Roots: The word "data" comes from the Latin plural "datum," meaning "something given" or "a piece of information."
Traditional Usage: Because "data" is plural, it traditionally takes a plural verb, leading to phrases like "the data are."

Traditional vs. Modern Usage

Traditional (Plural Noun) Usage

Example Sentences:
- "The data are inconclusive."
- "These data have been analyzed thoroughly."
Contexts: This usage is prevalent in academic, scientific, and statistical writing, where traditional grammar rules are paramount.
Reasoning: Since "data" represents multiple pieces of information, the plural verb form makes logical sense.

Modern (Mass Noun) Usage

Example Sentences:
- "The data is being processed."
- "This data shows an interesting trend."
Contexts: More common in everyday language, journalism, business communication, and fields like IT and data science.
Reasoning: "Data" is often treated as a mass noun (like "information"), representing a collection as a single entity.

Why "Data Is" Sounds More Correct to Many

Language Evolution: Language naturally evolves, and "data" as a singular mass noun is part of this evolution.
Common Usage: Most people encounter "data" collectively, making the singular verb feel more intuitive.
Industry Influence: In technology and business, the singular usage dominates due to the way these sectors view and handle data.

Current Acceptance

Dictionaries and Style Guides: Both usages are recognized in many modern dictionaries and style guides:
- Merriam-Webster Dictionary: Notes that "data" can be singular or plural.
- AP Stylebook: Leans toward the singular for general usage.
- The Chicago Manual of Style: Suggests use based on context.

Considerations for Usage

Audience and Context:
- Scientific/Academic Writing: If writing for a scientific audience, "data are" might be the way to go.
- General/Public Writing: "Data is" is widely accepted and often clearer for general audiences.
Consistency:
- Be consistent throughout your document to avoid confusion.
Clarity:
- Go with what sounds natural and makes your sentence clear.

Examples Illustrating Both Usages

Plural Usage (Formal/Traditional):
- "The experimental data are significant and support the hypothesis."
- "All the data have been collected and analyzed."
Singular Usage (Common/Modern):
- "The customer data is stored securely."
- "This data shows a decline in sales over the last quarter."

Regional Differences

American vs. British English:
- Both forms are used in both dialects, although British English historically favored the plural form more frequently.

Why Some People Prefer "Data Are"

Adherence to Tradition: Some sticklers for tradition prefer "data are," especially in academic and scientific contexts.
Precision: Using "data are" can emphasize multiple discrete pieces of information.

Conclusion

Both Forms Are Correct: Both "data is" and "data are" are grammatically acceptable.
Choose Based on Context: Decide based on your audience, context, and style guide preferences.
Language Evolves: The shift towards "data is" reflects the evolution of language.

Additional Notes

"Datum" Usage: The singular form "datum" is rarely used in everyday language. Most people say "data point" instead.
Other Similar Words: English has several words that transitioned from Latin plurals to mass nouns, such as "agenda" and "media."

Tips for Writers

Check Style Guides: If writing professionally, consult your organization's preferred style guide.
Mind Verb Agreement: Ensure the verb matches your usage of "data."
Avoid Ambiguity: If in doubt, rephrase. Instead of "The data are," say "The data set is" or "The results are."

So, there you have it! Whether you lean towards "data is" or "data are" depends on your context, audience, and personal preference. Remember, both forms are correct—just pick the one that feels right for you. Happy writing! 📖✍️

Improve Your PL/pgSQL Performance: Identifying and Resolving Common Bottlenecks

Josh Campbell — Sun, 29 Sep 2024 19:26:17 +0000

Hey folks! If you’re like me, at some point you've probably experienced the frustration of sluggish database performance with PL/pgSQL functions in PostgreSQL. It's a common struggle, but don't worry. I've been down that road and come out the other side with some valuable insights. Today, I want to share some of the most common bottlenecks I've encountered and how I managed to fix them. We'll look at some very brief code examples, both the pitfalls and the solutions so you can learn from my missteps and successes!

1. Row-by-Row Processing (Slow Loops)

The Issue:

Using loops (FOR, WHILE) to iterate over large datasets is like walking when you could be driving. It processes one row at a time, which is a massive time sink.

-- Inefficient Loop
FOR record IN SELECT * FROM large_table LOOP
  PERFORM some_function(record);
END LOOP;

The Solution:

Enter set-based operations! SQL is designed to handle entire sets of data in one swoop.

-- Efficient Set-Based Operation
INSERT INTO results_table
SELECT some_function(*)
FROM large_table;

2. Unoptimized SQL Queries Within Functions

The Issue:

Ever written a query that just drags its feet? It might be missing indexes, full of unnecessary joins, or pulling more data than it needs.

-- Unoptimized Query
SELECT * 
FROM big_table 
WHERE some_column = 'value';

The Solution:

Always optimize your queries. Index those columns, select only what you need, and keep computations minimal.

-- Optimized Query
CREATE INDEX idx_some_column ON big_table(some_column);

SELECT specific_column 
FROM big_table 
WHERE some_column = 'value';

3. Excessive Context Switching Between PL/pgSQL and SQL

The Issue:

Switching back and forth between procedural code and SQL statements? That overhead piles up faster than you might think.

-- Excessive Context Switching
FOR record IN SELECT * FROM big_table LOOP
  EXECUTE 'INSERT INTO results_table VALUES ($1)' USING record.id;
END LOOP;

The Solution:

Reduce context switches by combining SQL operations and trimming down procedural code.

-- Reduced Context Switching
INSERT INTO results_table
SELECT id 
FROM big_table;

4. Not Utilizing Function Volatility Properly

The Issue:

Function volatility (IMMUTABLE, STABLE, VOLATILE) isn't just a buzzword. Mislabeling it can trip up PostgreSQL's optimization.

-- Incorrect Volatility
CREATE FUNCTION slow_func() RETURNS integer
LANGUAGE plpgsql
IMMUTABLE AS $$
BEGIN
  -- Some complex operation
END;
$$;

The Solution:

Make sure you correctly declare the function's volatility so the query planner can do its thing.

-- Correct Volatility
CREATE FUNCTION optimized_func() RETURNS integer
LANGUAGE plpgsql
STABLE AS $$
BEGIN
  -- Some complex operation
END;
$$;

5. Overuse of Exception Handling

The Issue:

Too many exception blocks? They're dragging you down. They add overhead, especially if you’re using them for things that should rarely happen.

-- Overused Exception Handling
BEGIN
  -- some code
EXCEPTION
  WHEN others THEN
    -- handle exception
END;

The Solution:

Be sparing with exception handling. Keep those blocks as tight as a drum.

-- Optimized Exception Handling
BEGIN
  -- some code that seldom fails
EXCEPTION
  WHEN unique_violation THEN
    -- handle specific exception
END;

6. Inefficient Data Type Casting

The Issue:

Frequent casting—especially within loops or big datasets—can feel like dragging weights around.

-- Inefficient Data Type Casting
FOR record IN SELECT * FROM table LOOP
  PERFORM CAST(record.some_field AS text);
END LOOP;

The Solution:

Keep it consistent to avoid unnecessary conversions.

-- Efficient Data Type Usage
FOR record IN SELECT * FROM table LOOP
  PERFORM record.some_field::text;
END LOOP;

7. Lack of Proper Indexing on Referenced Tables

The Issue:

Your queries are only as fast as your indexes. No index? Get ready for those full table scans to bog you down.

-- No Index
SELECT * 
FROM big_table 
WHERE some_column = 'value';

The Solution:

Create indexes on columns you frequently query.

-- Indexed Query
CREATE INDEX idx_some_column ON big_table(some_column);

SELECT * 
FROM big_table 
WHERE some_column = 'value';

8. Unnecessary Data Retrieval

The Issue:

Are you guilty of selecting more data than you need? Join the club. It's a major performance killer.

-- Unnecessary Data Retrieval
SELECT * 
FROM users;

The Solution:

Only grab what you need to get the job done.

-- Streamlined Data Retrieval
SELECT id, name 
FROM users;

9. Neglecting to Use Prepared Statements for Dynamic SQL

The Issue:

If you're running the same dynamic SQL over and over, that constant parsing and planning hurts performance.

-- Dynamic SQL Without Preparation
EXECUTE 'SELECT * FROM ' || table_name;

The Solution:

Use prepared statements to cut down on the overhead.

-- With Prepared Statement
PREPARE dynamic_stmt AS
SELECT * 
FROM some_table;

EXECUTE dynamic_stmt;

10. Not Cleaning Up Temporary Data

The Issue:

Temporary tables and variables are useful, but if they hang around too long, they start to eat up your resources.

-- Unmanaged Temporary Data
CREATE TEMP TABLE temp_table AS
SELECT * FROM big_table;

The Solution:

Clean up when you’re done.

-- Properly Managed Temporary Data
CREATE TEMP TABLE temp_table AS
SELECT * FROM big_table;

-- Do something with temp_table

DROP TABLE temp_table;

11. Ignoring Transaction Scope

The Issue:

Long-running transactions can hold locks and bloat your transaction logs, especially when things get busy.

-- Long Running Transaction
BEGIN;
-- lot of operations
COMMIT;

The Solution:

Keep your transactions short and sweet.

-- Short Transaction
BEGIN;
-- minimal operations
COMMIT;

12. Failure to Analyze and Vacuum Tables Regularly

The Issue:

Outdated statistics lead to poor query plans, and dead tuples bloat your tables. It's not pretty.

The Solution:

Regularly run ANALYZE and VACUUM to keep things humming along smoothly.

-- Regular Maintenance
ANALYZE;
VACUUM;

13. Insufficient Hardware Resources

The Issue:

Overloaded CPU, memory, or disk I/O can slow things to a crawl, especially under heavy loads.

The Solution:

Monitor your resource usage and scale up your hardware as needed.

By staying mindful of these common performance bottlenecks and applying the fixes, I've significantly boosted the efficiency of my PL/pgSQL functions. I hope this helps you as much as it helped me. Happy coding, and may your queries always be fast! 🚀