SyncedSupabase option to specify schema of synced collection

[warrenbhw]

some of my tables live in schemas other than public. It doesn't seem like legend-state recognizes the table names outside of the public schema. Is there a missing option here?

[warrenbhw]

I'm guessing that the default to public schema comes from supabase's createClient, see: initializing with custom schema here:
https://supabase.com/docs/reference/javascript/initializing

Let me test and see whether a single supabase client can find tables in multiple schemas. If yes, then this should be all good. If no, then I do think this makes sense to add as an option, as doing a query that joins across tables in two different schemas is a valid case

[warrenbhw]

The other feature I'd love to see in the supabase plugin is support for supabase-functions (i.e. those invoked via supabase.rpc().

I imagine that I could do this by building my own fetch or crud plugin, but it would be nice to benefit from the supabase plugin's db type inference.

Ofc functions don't necessarily map to clear create-read-update-delete behaviors, but it could perhaps be achieved by defining an interface to which these synced-store supabase functions must conform

[jmeistrich]

Thanks for the suggestions! I'm not very experienced with Supabase so this is great stuff to make it more flexible for real-world Supabase usage!

Schemas

I think I had misunderstood how schemas work. I looked at these docs (https://supabase.com/docs/guides/api/using-custom-schemas#exposing-custom-schemas) and thought the typical usage would be to create a client for each schema. But it looks like we should also have a schema parameter which would basically do supabase.schema('schemaParam'), right?

Would you like to PR that into the supabase plugin? I think it would just need to update the three places that call client.from(collection) to use a schema if provided, and probably pull that out to a helper function so there's no redundancy. The hard part would be getting the types to work with the schema. No worries if not - I can try to update it in a few days after I finish some projects.

RPC

Yes, supporting rpc functions would be great! Those functions could be used for all crud functions right? So you might use them for get/list as well as create/update/delete? Would you ever mix them? Like use an rpc to list rows but then just rely on upsert to create/update? Or the flipside to list with a select and an rpc to create/update?

[warrenbhw]

I actually don't know whether the typical usage is to create a client for each schema.

To give some context, I have most of my tables in the public schema, but my workspace/roles/billing-related tables are in a basejump schema (so-named because I used this starter kit).

Currently, all off my access to data in tables under the basejump schema is mediated by database functions, which I call via supabase.rpc().

I wanted to test whether I could just list my workspaces a-la select * from basejump.workspaces using syncedSupabase - but I realize that I haven't ever done that via the standard supabase client. So I need to check whether I can do that with a single client, and whether that's the standard usage.

I'm experimenting with a couple of other legend-state features before I get around to that, but will update this thread as I figure out which patterns make sense.

Thanks!

[warrenbhw]

Yes, supporting rpc functions would be great! Those functions could be used for all crud functions right? So you might use them for get/list as well as create/update/delete? Would you ever mix them? Like use an rpc to list rows but then just rely on upsert to create/update? Or the flipside to list with a select and an rpc to create/update?

Yep, these could be used for all CRUD functions, and there are cases where it would make sense to list with a select, but use an rpc to create/update.

[warrenbhw]

OK - it looks like you can specify only a single default schema when constructing the client, but supabase does support changing the schema per-query. It looks like that was something supabase added around this time last year.

looks like we should also have a schema parameter which would basically do supabase.schema('schemaParam'), right?

Yep, I think that we should expose this option.

I'm going to start by just writing a syncedCrud for my case, but once I get a feel for how this should look, I may take a stab at adding the custom schema and rpc support.

[jmeistrich]

Just a little status, I think I have this working. The code is easy to just add supabase.schema('...') but the types are harder. I'll just test it some more before releasing an update.

[jmeistrich]

I added a schema parameter in alpha.27. Let me know how that works for you?

And I'd love to look at adding rpc support at some point - if you want to add more ideas or draft a PR can you open an issue specific to that? 😀

[warrenbhw]

Oh epic. Thanks very much @jmeistrich - excited to check this out!

[warrenbhw]

@jmeistrich btw, apologies I haven't followed up on some of these feature requests. I ended up pivoting what I was working on and switching over to solidJS for the new app, so I'm not actively using legend-state any more. I still think this is a fantastic library, and will be using it again if I build a react app :)