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