https://supabase.com/ logo
#off-topic
Title
# off-topic
b

btelles

10/26/2021, 4:04 PM
'mornin'! It seems there's a need for tooling that bridges the gap between client side business logic and applying that logic on the database side via triggers, functions, etc (think "write once, use in client and server type tooling). Before I spin up a new project to start working on said tooling, are there existing solutions I should be aware of? (I'm thinking specifically about migration tooling that copies JS functions over to PostgreSQL PLV8, but am open to other approaches).
c

chipilov

10/26/2021, 4:36 PM
@User I agree with you, I've thought a bit about this and have come up with the following rough direction that I plan to follow in my current project: - as suggested by the PostgREST project itself, I plan to keep all my data in a private schema and put in the public schema only views and functions (i.e. data will be fetched via views and functions, not directly throught the tables) - any relatively simple logic will be done in views and standard SQL-based functions/triggers - any complex logic will be implemented in TypeScript, compiled to JavaScript and then executed via PLV8
this last bullet raises some interesting questions about how to re-use code between functions, what will be the API endpoints, etc. My current plan is bundle ALL the code in a single PostgreSQL PLV8 function where the top level will be a dispatched (so the rpc() call you make will include the method to invoke and the params)
On the one hand, if most of the methods are simple enough, the dispatcher should also be simple enough
On the other hand, if the methods or the dispatcher become complex, this would be a sign that I need to bite the bullet and deploy a full-blown API server...OR wait for Supabase Serverless functions 🙂
I'd be curious to hear your thoughts about this general direction and after than we can dive into more details
b

btelles

10/26/2021, 5:01 PM
Interesting Chipilov. I haven't looked into PostgREST much, so I'm curious how it interacts with updatable views. Assuming that's as easy as using regular tables, I'll move on to the next topic of the general approach. Having a separate schema certainly sounds like a great start...will look into that a bit more. WRT the single function, I'm not familiar with the performance implications of PLV8. For example, how does loading one large function compare to loading smaller purpose-built functions? I imagine the IMMUTABLE, VOLATILE options for functions end up going out the window, leaving some performance on the table. I think having a single function may also limit use of built-in functionality such as triggers to handle some business logic. Without understanding more about how you'd address those, I'd lean toward using a larger number of purpose-built functions, and a migration script that converts between TS/JS to PLV8. The bridge between the two (i.e. how do functions map to views, and such) would be an interesting project. 🙂
c

chipilov

10/26/2021, 5:03 PM
I don't have a lot of datapoints yet to discuss performance - I did a prototype with 2/3 JS endpoints inside a webpack-generated JS file of about 200 lines and there was no meaningful degradation compared to a native SQL function. I know 200 lines is NOT much, but then again - if I need more than 1000 lines of such code, it seems to me that I just need to move to a dedicated app server or serverless functions
(the prototype was done WITHOUT IMMUTABLE as you mention)
The reason why I currently lean towards a single function instead of multiple ones (i.e. separate PostgreSQL function wrapper for each JS function) is simplicity - this makes both sharing of code and deployment of changes easier
the way I think about it is like this - if I need more than 500/1000 lines of JS code, then I probably need a dedicated app server and stuffing the logic inside PLV8 is probably the wrong approach. And if the logic is less than 1000 lines, then perf will most likely NOT suffer and will make things much easier
with regards to triggers - I have NOT tested it yet, I hope that triggers allow passing custom parameters to the trigger method
btw, one interesting thing about PLV8 - as far as I understand, PLV8 is inited per session, so if you can use connection pooling per session (rather than per request), then the cost loading of the code is paid only once when setting up the session. This, of course, would only be possible if you don't have a lot of concurrent users OR if user session are short-lived
b

btelles

10/26/2021, 6:00 PM
Great insights there. Thanks for the detailed notes. Will try to respond later today when I have more time.
Yeah, the idea of a dedicated app server seems reasonable after a certain point. That kind of goes against the whole serverless architecture that Supabase is going for though. There's got to be a way to organize serverless functions, database configurations/migrations in a more seamless manner. Hmm...If people currently employ conventions to manage their serverless functions, then applying those principles to PostgreSQL might also work. I'll look into that next. The issue with that though is that you end up with spaghetti functions, and managing interactions across multiple functions becomes difficult, so at the very least you need guidance on keeping function interactions to a minimum. When that's not possible...well, I'll need to think about that. I recently came across Event-Driven Microservices which presents the idea of an orchestrator that manages operations. That sounds promising, and the question then becomes "where does the orchestrator live? in the PostgreSQL DB, or a separate server. Perhaps when it becomes big enough, you deploy it by itself, but you start out with a PLV8 function. As for PLV8 performance, I see that it starts a new global js runtime context for every session, but I need to understand whether that's the same as the global execution context (which would be nice) or if they're talking about starting up an entirely new process for every session (which seems expensive)...will come back in a bit.
c

chipilov

10/27/2021, 9:33 PM
- "That kind of goes against the whole serverless architecture that Supabase is going for though." - I agree to a large extent but with a few caveats. For example, even if you setup an app server, Supabase still gives you things out of the box like a managed DB, auth and storage . So it's not like having an app server completely defeats the purpose of using Supabase - sure, it diminishes its value, but you still get something non-trivial out of it.
- ".If people currently employ conventions to manage their serverless functions" - I think that's a great question and since I am new to the serverless world, it definitely struck me as something of a blind spot - there are very few resources about how to structure such functions and keep them both maintainable and performant. I've noticed that different providers have different approaches (for example Firebase serverless functions are very different from CloudFlares's workers). I think that might also be the reason why there are so many examples with Supabase and Next.JS, i.e. because Next.JS actually gives you a serverless app server (pun intended) behind Next.JS's /api routes (but then you also need to live with the complexities of SSR/hydration/etc)
- "I recently came across Event-Driven Microservices which presents the idea of an orchestrator that manages operations. " - to me personally, this is actually what goes somewhat against Supabase/Firebase's promise of simplicity. Because the world of microservices is actually quite complex. To be crude, I am a nobody so I am not going to give you my personal convictions about microservices but everyone knows Martin Fowler and he's the one that says "MonolithFirst" (https://martinfowler.com/bliki/MonolithFirst.html) - and I thought that was the whole point of serverless, that you don't need to "orchestrate" and keep things simple. And it seems like you can do that with serverless AS LONG AS you actually have a relatively simple app OR you are in the early stages of your app when velocity is more important than stability. Beyond that I don't think serverless can help you, at least NOT with its current capabilities
Where exactly you draw the line - it's a good question, but I don't think there is a universal answer, it really depends on experience probably
Having said that I think what makes Supabase stick above the other serverless platforms like Firebase and AWS Amplify is that if it came to it, it would be much easier to move to a more standard serverull solution because you are already using similar pieces (i.e. SQL db), you just repackage them a little differently
...but I digress, I would love to hear more about (1) how you think you can manage/orchestrate serverless functions and (2) what you find out about how PLV8 manages the JS environment
b

btelles

10/27/2021, 10:03 PM
Any chance you're available for a quick video conference? would love to chat more about approaches so I can take a quick stab at something simple.
c

chipilov

10/27/2021, 10:06 PM
(moving conf arrangements to DM, feel free to continue the actual discussion here)