3 Things you should know BEFORE you start with Supabase

Supabase is powerful and gaining much popularity. It’s a free and simple great alternative for Firebase. Even if you are not familiar with Firebase. Supabase has everything that you need. A database with real-time subscriptions, Authentication, and Storage.

Hi, I created Happy React. Service for adding reactions widgets on your website for free. I took care of performance and ease of using it. You can add reactions to your blog or documentation pages! It’s still in early access, let me know what you think about it and join the waiting list. Learn more on https://happyreact.com/

About Supabase

Supabase is built on top of open-source technologies. PostgreSQL, PostgREST, gotrue, and many many others. It’s a great idea. There are a lot of great libraries and technologies out there. Smart using established tools can be better than spending time reinventing the wheel.

The free plan has all functionality you need to build an application. The best part of that is you are not locked in. You can dump your database data and move it to another provider or custom server. There is no lock-in!

On top of all these features, there is a dashboard that lets you manage your project.

Supabase features

• Auth
• Storage
• Database with real-time subscriptions

Supabase isn’t perfect but it’s powerful

What is the greatest strength is the biggest downfall. I repeat myself but Supabase is built on top of PostgreSQL. You need to know SQL, how to create queries and what PostgreSQL features have. Below are common gotchas about supabase you can have after starting.

Creating a view for complex queries

Supabase query builder is flexible. It can make basic selects, count rows, or join queries but you sometimes need more advanced usage of SQL. Postgres views can help you with that.

drop view if exists sold_products;
create
or replace VIEW public.sold_products AS (
  select
    DISTINCT ON (orders.product_id) product_id,
    products.name as name
  from
    orders
    inner join products on orders.product_id = products.id
);

This little snippet gets all orders and excludes repeated products so we get a list of all sold products.

⚠️ Note that view inherits permissions of creator. When you are creating it as admin it will have same permissions (bypass RLS) as admin. Create views with caution.

Adding cascading on delete

When you delete one record from a table and has a foreign key column on another table, you get an error. To delete a record and other records with foreign keys you need to add cascading delete.

CREATE OR REPLACE FUNCTION 
    replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR) 
RETURNS VARCHAR
AS $$
DECLARE constraint_name varchar;
DECLARE reftable varchar;
DECLARE refcolumn varchar;
BEGIN
SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' 
   AND tc.table_name= f_table AND kcu.column_name= f_column
INTO constraint_name, reftable, refcolumn;
EXECUTE 'alter table ' || f_table || ' drop constraint ' || constraint_name || 
', ADD CONSTRAINT ' || constraint_name || ' FOREIGN KEY (' || f_column || ') ' ||
' REFERENCES ' || reftable || '(' || refcolumn || ') ' || new_options || ';';
RETURN 'Constraint replaced: ' || constraint_name || ' (' || f_table || '.' || f_column ||
 ' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options;
END;
$$ LANGUAGE plpgsql

Then you need to invoke a function like this:

select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');

Row-level security

Protecting your database from unauthorized access is by using RLS. Those special “checks” are run before the query to make sure the person who is running it has the rights to do so.

You can reference uid() which will be replaced with a user id that is currently authorized. You can compare if the record that is currently processed belongs to the authorized user.

All values that you are sending using Supbase SDK will be replaced inside the RLS policy.

Next, you need to add RLS. A good idea is to keep it clean and create a Postgres function where you can pass all variables and make the query:

CREATE
OR REPLACE FUNCTION can_insert_post(_user_id uuid) RETURNS bool AS $$
SELECT EXISTS (
  SELECT
      1
    FROM
      posts
      INNER JOIN users ON users.id = posts.user_id
    GROUP BY
      users.id,
      posts.user_id
    HAVING
      COUNT(posts.id) < users.post_limit
      AND _user_id = posts.user_id
);
$$ LANGUAGE sql SECURITY DEFINER;

This function will check if the user doesn’t exceed the posts limit. Next, you need to reference it in RLS insert policy.

can_insert_post(uid())

Summary

Supabase is a great tool but it’s requiring some Postgres knowledge. Keep in mind that it is only the tip of the iceberg. The more you dive into Supabase you will need more Postgres knowledge.