4 ms·

I dont really know why im arguing this, there are 100s of blog posts out there for decades that explain why sprocs are considered bad. The marketplace of ideas

I dont really know why im arguing this, there are 100s of blog posts out there for decades that explain why sprocs are considered bad. The marketplace of ideas has spoken, and we've moved on. I'll never likely never convince you, but here goes my take expanded some more. You can source control them, but then you have a deployment coordination problem. Often changes to business logic will be half in application code and half in the sproc, and you have to coordinate the deployment of that change. You have to somehow reason about what version of the sprocs are on your server. Add on that usually in enviroments that use sprocs, changes to them are gatekept by DBAs. Just write your business logic in application code. Transpile? why on earth would anyone bother with that, it just makes it even more complicated and impossible to debug. without that, TSQL/PSQL/PGSQL are objectively very awkward languages for imperative business logic. People are not familiar with them, it makes it hard to jump from regular code to *SQL code to read the logic flow, nevermind debugging them with breakpoints and suchlike. Splitting your buis logic between database and app makes it much more awkward to test too. Just write your business logic in application code. Scale - making the hardest to scale piece of your architecture , the traditional rdmbs, an execution environment for arbitrarily complex business logic is going to add a lot of extra load. Meaning you're going to have scaling problems sooner than you otherwise would. Just write your business logic in application code, you can scale those pods/servers horizontally easy peasy, they're cheap. Look you can make any technology work if you try hard enough, but sprocs are almost all downsides these days. The one minor upside is some atomicness, but you can get that with a client side transaction and a few more round trips. There's just IMO no reason to pick them anymore and pay these costs, given what we've learned. BTW I could have made this clearer in the original comment but i love RDBMSs - i love schemas, indexes, complex querying, and transactions. It is not weird to keep picking these databases to get those things. I just will never again use them as a programming environment.

Arch-TK 2y ago
>[appeal to popularity] I think you put too much weight on what people currently do. Every year it seems the previous years trendy ideas are "terrible" and something new is "the way it should have always been done from the start". >[stored procedures, business logic splits, and DBA gatekeeping] You probably shouldn't be using stored procedures to be implementing more than you need to keep things internally consistent (i.e., almost never). I actually don't understand why you're so hung up on stored procedures. I've seen people try to implement whole applications on top of stored procedures, certainly not anything I'm recommending. Yes if you split your business logic like that then you're going to have trouble, so don't. If instead you treat your database as an isolated unit and keep it internally consistent, then it's not really that difficult to keep things always working even when migrations happen. As for the DBAs, nobody says you have to have DBAs, but if you're going to use a technology, it's worth having someone who understands it. (or, you know, just pick a different database). >[transpile?] I mean, lots of people use query generators which let you write e.g. native python and generate equivalent SQL (SQLAlchemy Core). Anyway, your complaint seems to be that people don't understand SQL databases well enough to use them properly. Sure, but this is like complaining that git is bad because nobody knows how to use it. People who don't know or want to learn git, SQL, or a tool they're using, should pick a different tool. >[it scales worse if you try to implement an application in it] well yes >[more complaining about abuse of stored procedures] sure ... I think at some point you read something I wrote as: "And you should attempt to find out a way to shoehorn your entire application into the RDBMS such that your application is just a frontend which calls stored procedures on the RDBMS" but that's certainly not anything close to what I said. Designing your database such that it stays internally consistent when you perform operations on it is a good goal, and doesn't require filling it with stored procedures and making it your backend.

Arch-TK 2y ago

>[appeal to popularity] I think you put too much weight on what people currently do. Every year it seems the previous years trendy ideas are "terrible" and something new is "the way it should have always been done from the start". >[stored procedures, business logic splits, and DBA gatekeeping] You probably shouldn't be using stored procedures to be implementing more than you need to keep things internally consistent (i.e., almost never). I actually don't understand why you're so hung up on stored procedures. I've seen people try to implement whole applications on top of stored procedures, certainly not anything I'm recommending. Yes if you split your business logic like that then you're going to have trouble, so don't. If instead you treat your database as an isolated unit and keep it internally consistent, then it's not really that difficult to keep things always working even when migrations happen. As for the DBAs, nobody says you have to have DBAs, but if you're going to use a technology, it's worth having someone who understands it. (or, you know, just pick a different database). >[transpile?] I mean, lots of people use query generators which let you write e.g. native python and generate equivalent SQL (SQLAlchemy Core). Anyway, your complaint seems to be that people don't understand SQL databases well enough to use them properly. Sure, but this is like complaining that git is bad because nobody knows how to use it. People who don't know or want to learn git, SQL, or a tool they're using, should pick a different tool. >[it scales worse if you try to implement an application in it] well yes >[more complaining about abuse of stored procedures] sure ... I think at some point you read something I wrote as: "And you should attempt to find out a way to shoehorn your entire application into the RDBMS such that your application is just a frontend which calls stored procedures on the RDBMS" but that's certainly not anything close to what I said. Designing your database such that it stays internally consistent when you perform operations on it is a good goal, and doesn't require filling it with stored procedures and making it your backend.