The cargo-cult shibboleth of "never put business logic in your database" certainly didn't help, since a lot of developers just turned that into "never use stored procedures or views, your database is a dumb store with indexes."
But that is the result of having multiple applications needing to enforce valid states in the database.
"Business logic" is a loose term. The database is the effective store for state so it must enforce states, eg by views, triggers, and procedures.
Other "business logic" can happen outside of the db in different languages. When individual apps need to enforce valid states, then complexity, code, etc grows exponentially.
At my new company, the use of stored procedures unchecked has really hurt part of the companies ability to build new features so I'm surprised to see what seems like sound advice, "don't use stored procedures", called out as a cargo cult.
If not, why would you then avoid putting code alongside your data at the database layer?
There are definitely valid reasons to not do it for some cases, but as a blanket statement it feels odd.
Stored procedures can do things like smooth over transitions by having a query not actually know or care about an underlying structure. They can cut down on duplication or round trips to the database. They can also be a nightmare like most cases where logic lives in the wrong place.
If you don't have a good way to keep stored procedures in version control, test them and have them applied consistently across different environments (dev, staging, production) you quickly find yourself in a situation where only the high priests of the database know how anything works, and making changes is painful.
Once you have that stuff in git, with the ability to run automated tests and robust scripting to apply changes to all of your environments (I still think Django's migration system is the gold standard for this, though I've not seen that specifically used with stored procedures myself) their drawbacks are a lot less notable.
Git? (and migrations)
> change management
Again. Just like any other code.
> and automated tests.
Just write an automated test like you write any other kind of test?
My experience is following
1) Tx are faster when they are executed a sql function since you cut down on network roundtrip between statements. Also prevents users from doing fancy shenanigans with network after calling startTransaction.
2) It keeps your business logic separated from your other code that does caching/authorization/etc.
3) Some people say it's hard to test sql functions, but since pglite it's a non issue IMO.
4) Logging is a little worse, but `raise notice` is your friend.
> At my new company, the use of stored procedures unchecked has really hurt part of the companies ability to build new features
Isn't it just because most engineers aren't as well versed in SQL as they are in other programming languages.
Like any tool, you just have to understand when to use it and when not to.