[SOLAR_FIELDS]

Do you manage your functions and triggers through source code? What framework do you use to do that? I like Supabase but it’s desire to default to native pg stuff for a lot of that has kind of steered me away from using it for more complex projects where you need to use sprocs to retrieve data and pgtap to test them, because hiding away business logic in the db like that is viewed as an anti pattern in a lot of organizations. I love it for simple CRUD apps though, the kind where the default postgrest functionality is mostly enough and having to drop into a sproc or build a view is rarely necessary.

I think if there was a tightly integrated framework for managing the state of all of these various triggers, views, functions and sproc through source and integrating them into the normal SDLC it would be a more appealing sell for complex projects

[inian]

The Supabase CLI [1] provides a way for you to manage functions, triggers and anything else in your Postgres database as a migration. These migrations would be checked into your source control.

You can then take it a step further but opting-in to use Branching [2] to better manage environments. We just opened up the Branching feature to everyone [3].

[1]: https://supabase.com/docs/guides/cli/local-development#datab... [2]: https://supabase.com/docs/guides/platform/branching [3]: https://supabase.com/blog/branching-publicly-available

[SOLAR_FIELDS]

Thanks for the response. I don’t think I was super clear about what I meant - I’m more talking about the following scenario:

Let’s say that we are using the Typescript sdk to make our app and need to do some fancy aggregation on a table that isn’t supported by Postgrest natively (specifically we can’t retrieve the data with Postgrest out of the box with its typical setup). Postgrest tells us that in this case we can do two things: create a view or make a Postgres function. Each has their pros and cons, but with either choice now we have this problem: some of our business logic is in sproc/function/view and some of it is in Typescript. In a typical setup using an ORM it would all be in Typescript.

The conventional wisdom is that db’s are dumb state holders and all of the business logic goes in the app - Supabase attempts to turn this on its head and say no actually it’s ok to store business logic in the db. But now if we do that we have a new problem: we don’t have a blessed path forward for where the line is on what goes where anymore. We don’t have good patterns for storing and managing this so other developers understand where to put things and how to work with our app anymore, because it no longer holds the principle of least astonishment. That’s what I mean by framework in this context.

Maybe all that is necessary here is a battle tested example project that demonstrates the “correct” way to make this demarcation. But as-is it steers me away from using Supabase for more complex projects if I even think they will need something that Postgrest won’t support without making a view or sproc/function