[tasuki]

I used to deal with stored procedures in legacy systems and honestly it was not bad.

The first concern was getting the stored procedures into version control and creating a mechanism to update the systems based on the things in version control upon deployment. After that it was smooth sailing.

Why are stored procedures to be avoided?

[saltcured]

I think most people are reacting to the impedance mismatch of normal software lifecycles versus sticky databases. Naive or legacy users and documentation use the database as the system of record for the custom code, rather than as an execution environment provisioned from a real system of record like a source control system.

Where it gets even more tricky is not just stored procedures but application-specific functions embedded in views, or triggers running custom functions. It's no longer just a library of functions you can choose to call (or not) durng a query, but code that runs on its own based on clients queries that never directly mention the functions.

The same goes for schema management, and I think that is a big reason why so many developers fixate on "schemaless" approaches. They want to pretend that the database exists in a static way outside the software lifecycle, just like they ignore the filesystem and operating system and treat it as an unchanging abstraction.