3 Things you should know BEFORE you start with Supabase

About Supabase

Supabase features

  • Auth
  • Storage
  • Database with real-time subscriptions

Supabase isn’t perfect but it’s powerful

Creating a view for complex queries

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
);

Adding cascading on 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
select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');

Row-level security

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;
can_insert_post(uid())

Summary

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Marcin Skrzyński

Marcin Skrzyński

Building web applications. Creating Bookmarkly — best bookmarks manager 🚀