I think this is a very valuable and important point and wanted to highlight it.

>> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns.

There's no absolute law that states that SP using environments will always end up as a mess, but there should be such a law, because they do!

I've worked across dozens to hundreds of enterprise environments, and the pattern is clear.

I suppose it comes from personality and culture with the people working on db coding more often in the 'get it done' end of the spectrum vs programmers where you find a significant number interested in 'get it right'.

An even stronger reason is probably that db coders are more likely to be autonomous instead of in large centrally driven teams. That central hierarchical organisation is a necessary but not sufficient condition for working to tame the entropy of large complex systems, without which they evolve into "a maze of complexity and footguns".

But if I look at a lot of the SP anti-patterns I've seen over the years, some common themes start to arise. I'd say the biggest issue is that it is more complex to maintain interfaces between two discrete systems than it is to write monolithic code paths. Seeing SP_ProcedureName accompanied by SP_ProcedureName_New is very common. It's easy to get into that situation, and tedious to get out of it.

Storing business logic in the DB also presents a lot of very tempting, but ultimately costly, shortcuts to developers. Using a non-temporary table when you should have used a temporary table, because you wanted debugging to be easier, but now you've got a concurrency issue, but it'll probably be OK, so you leave it there for 15 years. I have also never seen what I would consider to be a sensible use of a trigger. You also end up with SPs invoking SPs invoking SPs... and figuring out the side effects of calling something becomes a lot more complicated than it would be in a monolith.

I don't think they're always bad, but I do think that reasonable use cases for them are rather uncommon. When you had a lot of services directly consuming one database, SP interfaces used to make a lot more sense. But that's not a very common architecture is not very common any more. Even in large enterprises, you'll typically see new projects being developed as web applications, even if they're only served over the corporate network.

I think this is a problem because the database code is treated differently from the application code, because so few coders understand SQL well enough to be comfortable in it.

So, as you say, the DBA's tend to be autonomous, and held to different standards. And with less ability to say "we have tech debt, we need to refactor our sprocs" because of the knock-on effect on the application code.

It's a management problem rather than a technical certainty, I think.

Pointing out that it's probably caused by management is not really very useful since there are so few means available for line workers to fix their managers.

After all, if (for instance) a new IDE plugin appeared that managed sprocs consistently with code and solved this problem, then we'd still face "sprocs are bad" criticism because the problem was mis-attributed.

You already did, you just chose to ignore that the DB is actually a separate service. Ignoring this has meant that I've spent a not-insignificant amount of time in my last few jobs cleaning up bad ORM code because the queries performed terribly. I've cleaned such messes as pulling in all rows and then looping/filtering on them client-side, just generally querying for too many things/too many columns, querying for data and then looping over it to do more queries (instead of figuring out how to do it in a join), etc. When people treat the database as if its part of your monolith, because all the query logic is locally part of your codebase in your ORM, there's a temptation to be lazy and treat it that way. And it works fine in development because the database is small with only test data, but can be painfully slow in production.

I'm sure plenty of people do it properly, even with an ORM, but I've had to clean up the mess too many times now, across multiple companies and dev teams.

I'm not advocating for putting all of this logic into stored procedures, I think that's going too far in the other direction, only that you already have a distributes system, if you have a database, where the boundaries do matter.

I'd also point out that ORM is not the only alternative to stored procedures. In addition to that, there's nothing unique about ORMs that make them susceptible to bad code, or poor SQL understanding. Somebody who doesn't understand data structures and how the CBO works is going to write equally poor stored procedures. Removing the ORM is not the solution to your engineer not know how RDBMSes work. ORMs also don't suit every use case, but again, having a bias against ORMs is not the solution to failing to understand how your use case fits in with your RDBMS.

Absolutely. I personally use HugSQL[1], so no ORM, but I also don't use stored procedures. I guess my main point is that the boundary between application and database matters and not putting logic in the database doesn't make that boundary go away (putting logic in the database also doesn't make it go away). But I take your point, its not to the same degree.

> there's nothing unique about ORMs that make them susceptible to bad code, or poor SQL understanding.

I think that there is: it makes database code look like client-side application code, so there's some temptation to just blur the boundaries and mix them together. This is how you end up with code that queries for data, then does looping and filtering outside of the query, then queries some more. Sure, you can write equally bad code in stored procedures, but the boundaries are more obvious. At the very least, its likely a different programming language from the rest of your application.

> Removing the ORM is not the solution to your engineer not know how RDBMSes work.

Sure, however, in practice, at least in my experience across multiple teams and companies, education hasn't been a reliable solution. People still abuse the ORM and treat it as if the database is magic. Removing the ORM doesn't automatically fix it, of course, but it does force people to consider the database as a separate service whose access crosses a boundary. In an ideal world, everyone already keeps this in mind and ORMs can be used as they were intended, but, at least in my experience, this just hasn't been reality. Have I just been unlucky?

If different people and teams keep making the same mistakes with the tools, maybe its time to re-evaluate the tools and what they encourage people to do. Its not that the tools are bad in and of themselves, but that they encourage bad usage. (EDIT: I just saw another article on the front page and the title seems apt: "Discipline Doesn’t Scale")

[1] https://www.hugsql.org/

Just call your procedures "micro services" and you are in the clear ;)