Is there a way enter multiple id’s to delete multiple rows using the Supabase Delete function?
Only with the SDK I think.
// Define supabase from the WeWeb's exposed instance
let supabase = wwLib.wwPlugins.supabaseAuth.publicInstance;
let table = "table_name" // e.g. users
let column = "column_name" // e.g. id
let data = [1,2,39,13] // This is an array of the ID's
const { data, error } = await supabase
.from(table) // this becomes .from("table_name")
.delete()
.in(column, items) // this becomes .in("id", [1,2,39,13]) ..
Do you think I would be able to do it within weweb by running a loop and deleting each item individually?
This would be veeery slow. Basically you would wait for each one (around 100-200ms, depending on various factors) to complete before doing the other one. So if you have 10 IDs, that’s 500ms which is like half second - second give or take.
Ok thanks.
Do you think weweb will ever be updated to include bulk delete as standard? Built in to the delete function?
It’s coming soon, probably by the end of december, for sure in the Q1 2024, I would say more of the start of it. @Alexis is including the bulk into it as one of the features.
1 Like
Awesome. I can wait until then…
Would that also include bulk insert?
^^^
Correct!
1 Like
Hi @benji241 the ideal way to do a bulk job is to do it on the backend, NOT the front end. And in this case this bulk job you’re trying to do is a very common database job. Doing it one-by-one from the front end would not be the fastest.
I agree with @Broberto about using the SDK, but even without sdk it can still be done, another way to do it is via RPCs (remote procedure calls) since you’re using Supabase I will guide you to do it.
Just follow along. If you got questions, let me know afterwards. So let’s go:
- Go to your Supabase account and go to the SQL editor. In the SQL editor copy and paste this code and press ctrl + enter (or cmd + enter on mac) you can read my explanation of what each line is doing:
CREATE OR REPLACE FUNCTION bulk_delete_data(column_name text, table_name text, _ids uuid[])
RETURNS json AS $$
DECLARE
query text;
deleted_count int;
BEGIN
-- This constructs the dynamic SQL query
query := format('DELETE FROM %I WHERE %I = ANY($1) RETURNING *', table_name, column_name);
-- This line executes the query and gets the number of deleted rows
EXECUTE query USING _ids;
GET DIAGNOSTICS deleted_count = ROW_COUNT;
-- Here we return the result as a JSON object
RETURN json_build_object('completed', true, 'items_deleted', deleted_count);
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;
-
Next go to your weweb action editor and make a simple REST API call.
The api url of the call is {your supabase_url}+“/rest/v1/rpc/bulk_delete_data” -
The headers are
‘apikey’ : {your supabase public anon key}
‘Authorization’: ‘Bearer + space + {the current logged in user’s access token}’
explanation: the user access token is easily gotten in the weweb editor since you’re using supabase auth, just click on the auth variables under current user open the object open it to the bottom you will see ‘_session’ as a field open it, and you will see access token -
The body of the call is:
column_name : the name of the id column
table_name : the name of the table you wanna delete from
_ids : the array of ids you’re deleting -
Call the action and voila! your data will be bulk deleted!
the call will return a response like this
{
'completed': true,
'items_deleted': 260
}
Which you can use to display information to your front end user.
Notes: This is the ideal way to bulk delete from the front end (by making the backend do the delete operation and return a response). Your RLS security rules will apply, so if the user calling the function does not have permission, the process will fail
Best of luck!
Has this been added to WeWeb yet? Also will this include bulk update also?
Not yet, and yes it will include bulk update too.
@Alexis Was this feature added by now? I can’t find it. Thanks!
Yes, use the delete action and use “by custom filters” instead of “by primary key”
1 Like