[HillRat]

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."

[forgetfreeman]

There's value in not having to hunt in several places for business logic, having it all in one language, etc. I was ambivalent on the topic until I encountered an 12 page query that contained a naive implementation of the knapsack problem. As with most things dogma comes with a whole host of issues, but in this case I think it's largely benign and likely did more good than harm.

[yobbo]

> hunt in several places for business logic

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.

[forgetfreeman]

Other than a few ill-advised attempts to implement microservices infrastructure by well-intentioned co-workers I've not encountered situations where multiple applications needed to access a single data store. While I'm sure there are valid use cases there I suspect they're rare and should be treated like the outliers they are.

[simonw]

Did that 12 page query have any automated tests?

[mgkimsal]

I doubt it was even under version control...

[forgetfreeman]

It was absolutely under version control and there was a full test suite. The guy that wrote it is easily in the top 3 smartest human beings I've ever met and an incredibly talented developer. Unfortunately a lot of his stuff required being at the same level on the IQ bell curve, which meant it was functionally unmaintainable by anyone else. If you're familiar with the Story of Mel, it was kinda like that.

[0x457]

A lot of people probably think it's better to keep database "easy to swap". Which is silly, its MUCH easier to change your application layer, than database.

[iamsomewalrus]

genuinely curious, can you steel man stored procedures? views make intuitive sense to me, but stored procedures, much like meta-programming, needs to be sparingly used IMO.

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.

[simonw]

My hunch is that the problems with stored procedures actually come down to version control, change management and automated tests.

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.

[tete]

> My hunch is that the problems with stored procedures actually come down to > version control

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?

[simonw]

That's exactly what I'm saying. If you do those things stored procedures stop sucking.

[ChromaticPanic]

It's also about separately scaling your business logic from the data layer

[saxenaabhi]

You give no reasons why you think it's a sound advice.

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.

[bazoom42]

Stored procedures are great for bulk data processing. SQL natively operates on sets, so pretty silly to pass a dataset over the wire for processing it iteratively in a less efficient language, and then transfer the resultset back to the database.

Like any tool, you just have to understand when to use it and when not to.

[IanCal]

It’s about what you want to tie to which system. Let’s say you keep some data in memory in your backend, would you forbid engineers from putting code there too, and force it a layer out to the front end - or make up a new layer in between the front end and this backend just because some blogs tell you to?

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.