I’ve written much of the back end logic in Postgres stored functions and it feels awesome. Instead of writing database logic in typescript I just do a very simple sql query to call the stored procedure……. it’s much easier.
And ChatGPT seems extremely good at writing stored functions, so much of the development is conversing with AI to add features to the stored functions.
So this has felt awesome and I’ve been more productive than ever before. There’s almost no logic in the web application, just very simple queries that receive JSON from Postgres so I don’t even have to do any faffing around to convert rows into arrays/objects.
And it the same time I can almost hear all the industry experts yelling at me about what a bad idea it is to put your business logic in stored functions.
But why? What’s the big problem? I don’t get it. I feel like I’ve discovered some secret superpower that everyone says is kryptonite.
The stored functions are version controlled by my jetbrains IDE, so it can’t be that. So what is it that’s so bad about stored functions?
I think a few things came together to make programmers crap on stored procedures and putting business logic in the database:
- OOP, which typically treats relational databases as dumb backing store for objects.
- No enterprise experience, and weak RDBMS/SQL skills.
- Popular open source relational databases with weak and clunky languages and tools, such as MySQL. Compared to Oracle and SQL Server the low-end RDBMSs most programmers learn look hobbled.
- Unless you use Oracle or SQL Server or DB/2 you may have trouble debugging procedural code running in the database.
If this style works for you don’t worry what other programmers say or what you read online. No matter how you choose to build your application you will get people telling you that you made bad choices.
The hard thing is how to horizontally scale.
And I swear there was a time when every web developer knew that if you wanted a consistent, reliable layout for a home page, you sliced the design up into a table, and stayed away from that new CSS stuff because it was buggy and incompatible with different browsers.
The reality is that there are several very different general approaches that can work for any particular information system. Some of them will be more compatible with each other or with certain tools.
But I think that for the vast majority of applications, this comes down to trends more than any particular technical issue.
I mean, technically speaking there are an effectively infinite number of ways to organize and distribute the code and data that will given the same outputs from given inputs.
People use "best practices" as a shortcut to make it easier to judge things or decide how to approach a problem. I think one mark of a good engineer is their ability to adapt to different approaches. This actually requires more intelligence or at least energy because it means they might need to actually read code to decide if the approach makes sense.
But it will save people quite a lot of energy to simply say "we [always/never] use stored procedures here!" And also even for brilliant engineers, constantly trying to wrap your head around completely different approaches can be impractical.
So programming communities adopt their own worldviews. For the most part, it just means that you spend slightly more time on certain types of problems than the other group. But that group may not realize they are spending time on another thing. So there are trade-offs with every approach.
Which is not to say that some frameworks can't save you more time than others. One aspect of this is compatibility. The whole approach may get thrown off if you use stored procedures versus not, depending on the rest of the framework.
But I would suggest that there is usually just as much cultural preference as actual technical merit to these debates when you dig deep into the details.