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.