So I’ve been using WeWeb for around 6 weeks now and am well into building my first application using Supabase as a backend.
My question is regarding best practices for the connection between a database and the front end.
Is there a right or wrong reason for using a postgres function calls over using the supabase plugin and getting data through collections etc?
For instance, I have a table which has a foreign key or two and some other columns in it. If I’m using the supabase plugin and joining the data in a collection I could probably do that ok, but the data from the entire table is exposed unless a filter is applied to the collection, right?
Alternatively, I could get the same data from a function I’ve created in Supabase and call it directly from WeWeb using the ‘Call PostgreSQL function’ action in the workflows section… I can then store the data etc as necessary in a local variable.
If I use the postgreSQL function method, I can tailor the output to only send the filtered data I need without exposing everything in the table, but if it were more simplistic, it wouldn’t matter if I used a collection or the postgreSQL function.
Am I right?
So my question is, when do you use each method? Is there a reason why I can’t just create functions for my whole app rather than using collections?
Nothing is ‘exposed’ by using the collection. You only get the data you call. If you call with no filters you will get the whole table if you do it in a function or in a collection.
On when to use a function, use them when your call is complex. I use them very rarely most of the time I use collections and sometimes I use a select (the supabase workflow action) @Broberto has a guide on how to use them effectively.
Postgres functions are for very specific use cases when you want to perform some heavy database calculations or any database-heavy task, since Edge Functions have limitations as they don’t live inside your database.
If you are just querying a simple table, you can use select and it will be enough, so in this case Postgres functions are overkill. They are hard to write if you don’t know SQL but allow you to unlock the full capabilities of Supabase, alongside Edge Functions.
For simple queries, use select/collection
For advanced database operations, like getting data from multiple tables and returning them in a specific format with many conditions, use a database function. It’s more efficient.
Have fun with Supabase! I it. (If it’s not obvious I also WeWeb!)
I am always logged into the Supabase Dashboard, but for queries and view/function creation I moved to database tool where it is supa fast.
If you plan to share the database with an iOS or Android app that has a limited set of features, you might want to consider using views and functions in those cases so you don’ t need to rewrite things.
For other technologies that deploy to the app stores, it may be better to push logic to views or functions, as these can be corrected more easily than waiting for a new app deployment.
it. (If it’s not obvious I also