Quick Tour of Supabase Database Functions

Supabase Database Functions

Supabase is an open-source platform that enables developers to easily construct and administer apps. Supabase's Functions capability is a major feature that offers a strong tool for automating business logic and procedures inside your application.

Functions are serverless computing units that enable bespoke logic to be performed on the Supabase backend. Data validation, sophisticated computations, and linkages with third-party systems are examples of such jobs. You can automate these activities and remove the need for human intervention using Functions, minimizing mistakes and enhancing productivity.

Getting started

Supabase offers a number of choices for developing database functionality. You may use the Dashboard to generate them or write them directly in SQL. We provide a SQL editor on the Dashboard, but you can also connect to your database and perform the SQL queries manually.

1. Go to the "SQL editor" section.
2. Click "New Query".
3. Enter the SQL to create or replace your Database function.
4. Click "Run" or cmd+enter (ctrl+enter).

Simple Functions

Let's create a basic Database Function that returns the string "good day".

create or replace function good_day() -- 1
returns text -- 2
language sql -- 3
as $$  -- 4
  select 'good day';  -- 5
$$; --6

‍

After the Function is created, we have several ways of "executing" the function - either directly inside the database using SQL, or with one of the client libraries.

SQL
select good_day();

‍

Returning Data sets

Database Functions can also return data sets from Tables or Views.

For example, if we had a database with some NFL data inside:

Data

Teams

SQL

create table teams (
  id serial primary key,
  name text
);

insert into teams
  (id, name)
values
  (1, 'Packers'),
  (2, 'Bears'),
  (3, 'Chiefs');

create table people (
  id serial primary key,
  name text,
  team_id bigint references planets
);

insert into people
  (id, name, team_id)
values
  (1, 'Aaron Rodgers', 1),
  (2, 'Christian Watson', 1),
  (3, 'Justin Fields', 2),
  (4, 'Patrick Mahomes', 3);

Players

We could create a function that returns all the teams:

create or replace function get_teams()
returns setof teams
language sql
as $$
  select * from teams;
$$;

‍

Because this function returns a table set, we can also apply filters and selectors. For example, if we only wanted the first team:

select *
from get_teams()
where id = 1;

‍

Passing Parameters

Let's create a Function to insert a new planet into the teams table and return the new ID. Note that this time we're using the plpgsql language.

create or replace function add_planet(name text)
returns bigint
language plpgsql
as $$
declare
  new_row bigint;
begin
  insert into planets(name)
  values (add_planet.name)
  returning id into new_row;

  return new_row;
end;
$$;

‍

Once again, you can execute this function either inside your database using a select query or with the client libraries:

SQL

select * from add_team('Raiders');

Database Functions vs Edge Functions

For data-intensive operations, use Database Functions, which are executed within your database and can be called remotely using the REST and GraphQL API.

For use cases that require low latency use low latency, which is globally distributed and can be written in Typescript.

Security Definer vs Invoker

Postgres allows you to specify whether you want the function to be executed as the user calling the function (invoker), or as the creator of the function (definer). For example:

create function good_day()
returns text
language plpgsql
security definer set search_path = public
as $$
begin
  select 'good day';
end;
$$;

It is best practice to use a security invoker (which is also the default). If you ever use a security definer, you must set the search_path. This limits the potential damage if you allow access to schemas that the user executing the function should not have.

Function Privileges

By default, database functions can be executed by any role. You can restrict this by altering the default privileges and then choosing which roles can execute functions.

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- Choose which roles can execute functions
GRANT EXECUTE ON FUNCTION good_day TO authenticated;
GRANT EXECUTE ON FUNCTION good_day TO service_role;

Advantages of Using Functions in Supabase

Scalability

Functions are extremely scalable, enabling you to handle massive volumes of traffic without worrying about infrastructure administration. This scalability is possible because of the cloud-based infrastructure that functions are built on. This infrastructure is designed to automatically scale up or down in response to changes in demand, so you don't have to worry about manually adjusting your resources.

Cost Effective

Since functions are priced on a usage basis, you only pay for what you use. As a result, they are a cost-effective alternative for enterprises of all sizes. This is especially true for small businesses that may not have the resources to invest in large-scale infrastructure. With a pay-as-you-go model, businesses can scale their services up or down depending on their needs, without having to worry about the long-term commitments and financial risks associated with traditional subscription models. This model allows businesses to pay for the exact resources they need when they need them, without having to pay for additional services they may not use. This flexibility is especially beneficial for businesses that are just starting out and need to keep costs low. It allows them to access the services they need without having to invest in a more expensive package that includes services they may not need. This flexibility also helps businesses that are growing and expanding.

Simplified Integration

Functions may simply interact with other Supabase services, such as Auth, Database, and Storage, to help you speed up your development process. Supabase Auth provides a secure authentication layer for your applications, allowing you to easily manage user accounts and access control. The Database service provides a powerful, real-time database that can be used to store and query data quickly and easily.

Simple Deployment

Functions may be deployed with a single click, making it simple to update your app and add new features.

To construct a Function in Supabase, you must first write the function's code. This may be done in the language of your choice, such as JavaScript, Python, or Go. After your function has been developed, you may deploy it to the Supabase backend through the Supabase CLI or the web interface.

HTTP requests, database events, or scheduled intervals may all be used to activate Supabase Functions. This enables you to automate tasks such as emailing, processing payments, and updating database entries.

Assume you have an e-commerce application that sells things online. You may program a Function to handle orders and update your inventory. When a consumer puts in an order, the Function is called, and the bought products are removed from your inventory database. This guarantees that you never oversell anything and that your inventory is always up to date in real-time.

A Function that sends a confirmation email to a user after they sign up for your application is another example. After a user completes the registration form, the Function is activated, and it sends a tailored email to the user's email address. This reduces the need for human intervention and creates a more seamless user experience.

Conclusion

Supabase Functions, in conclusion, are a great tool for automating business logic and operations inside your application. They are scalable, cost-effective, and simple to use, making them an excellent choice for businesses of all sizes. You may use Functions to improve your development process, eliminate mistakes, and boost efficiency. Functions are a great way to streamline your workflow and increase productivity. By automating mundane tasks, you can free up time for more important tasks and focus on innovation. Functions can also help you avoid costly mistakes and reduce the risk of errors.