Stored Procedures Debate

It's almost like stored procedures were a good idea.

We use stored procedures. We actually use them for a different reason: we treat the database server as an application server that happens to use an proprietary binary protocol instead of HTTP. Its API is exposed using stored procedures. This allows us to maintain and update the database data model independently of the "traditional" web tier. DB experts can work on the most effective data model without hunting through client source code.It also means that we can use access controls o

What’s wrong with stored procedures?

Ive worked on several apps that tried this approach and each of them was a mess. I’ve seen two fundamental problems with building your app on top of stored procs:* Tooling around sql is generally inferior to what’s available for . I’ve yet to see a company with effective automated tests around their database... it’s far more common to have _no_ tests around the database. Even if you’re the unicorn that does have all of that figured out, it still tends to b

Just put it all in stored procedures. What could go wrong?

Please don't write a lot of stored procedures. Changing them in large or sharded DBs is not trivial and tooling to support them is minimal.

Your colleague is dumb. Stored procedures have advantages and disadvantages. For certain data processing operations it is much better to perform everything inside the database than transfer large amount of data back and forth to some external application.But I have also seen organizations with the policy that any operation touching base tables should be encapsulated in a stored procedure. This makes development extremely cumbersome, especially if some DBA is gatekeeper for the stored procedur

Probably because your devs didn't write them in stored procs like they should.

Logic in the Database has the following issues:1. it doesn't work well with source control2. deployments, rollback, replication, synchornization - they don't work very well with db procedures3. unless you connect directly to the db, then you must have some logic on the serverside, usually you end up replicating logic from the db to the serverside4. Databases languages (even advanced ones like PL/SQL) are not expressive enough5.It's much easier to scale out the

I'm not a big fan of PL/SQL. Almost nobody is, so your approach is standard.It's ok if all accesses to the database go through the primary application. Triggers and stored procedures make more sense when a database serves multiple applications possibly in multiple languages. Think of the db as the microservice in front of the data store. Simple stored procedures are no that bad.