Generate ID on client before insert?

[tommyc38]

Firebase allows you to get an ID on the client before saving your data. Is there a way to do this with supabase?

Usecase: I have to process images and videos before a facebook like post can be made containing those assets. I would like to associate the post id to each asset's data model as this will be the foreign key in those respective tables (e.g. post-videos, post-images, etc.) I would like to save all data in one transaction as opposed to saving the post, getting the id, and then saving the associated asset data in their respective tables.

Thanks!

[steve-chavez]

Hey @tommyc38,

You'll need a SQL function for this, but the implementation depends on the type of ID you use.

For example if it's an UUID, you could do:

create or replace function gen_id() returns uuid as $$
  select extensions.uuid_generate_v4();
$$ language sql;

Then to get it on the frontend

const { id, error } = await supabase
  .rpc('gen_id')

[tommyc38]

Thank you!

[tommyc38]

@steve-chavez when updating many tables at once, is it best to use a stored procedure so if one insert fails the prior inserts can be rolled back without managing the overhead with multiple requests? This would also remove my need to get ID beforehand. Sorry, I'm new to SQL and don't know what all the best practices are.

[steve-chavez]

is it best to use a stored procedure so if one insert fails the prior inserts can be rolled back

@tommyc38 Correct! That would be much better because you'll get atomicity on a stored procedure.

Sorry, I'm new to SQL and don't know what all the best practices are.

No problem! Feel free to ask me any questions.