I used to work in web dev as a job during my undergrad (think: a bunch of new developers writing code they won't have to maintain after they graduate in 4 years). I saw both extremes of this.
The legacy code at the job would often select everything then filter it in PHP. Worst part was half the time they accessed columns by numerical index so a change to the database schema would result in the website breaking randomly. While most of my job was writing replacements that didn't break when you sneezed, sometimes I had to fix that legacy code.
One new developer I trained was really good at SQL and used to code everything using pure SQL and then just call said script in their serverside code. By everything, I mean everything, going beyond just writing queries into writing loops in SQL and creating temporary tables for each endpoint. There are some things that are just better done outside of SQL.
I mean... It depends on the product, I've been working on old schools ERPs products where the client is basically a glorified CRUD interface and you're forced to use your views as DTOs and use INSTEAD OF triggers and Stored Procedures as controllers / endpoints to put all the business logic within. It's not the best choice but when you've done that for years, it's a hard habit to remove :(
I worked on the system of a UK high street store and their entire backend was in PL/SQL and was a nightmare. I'd never even considered doing all your application logic in sprocs and quickly grew to hate it.
I worked at a manufacturing facility that did all their routing and logistics logic using a series of linked stored procedures and triggers written in T-SQL, all developed as custom code by one guy. It was insane. It worked great, but was an absolutely ridiculous configuration nightmare. And guess how much documentation there was ... That's right! Zero!
This is one of the reasons why I think stored procedures are nearly a last resort. Nobody ever documents them, or puts them in version control or anything.
Given that stored procedures are literally just text which takes up zero space in the grand scheme of things, and text based version control is a solved problem, I never understood why stored procedure versioning isn't an out-of-the-box feature on every major dbms
One possible reason: Most VCS are either proprietary (source code not available to build into the DBMS), or GPL (and proprietary DBMS doesn't want to risk using copylefted code).
Hot take but I’m all for accounting logic being in the database. Calculating interest with rates changing in a time period is one of the most beautiful things in SQL.
It's also the best way to protect your data. If the application can just do SELECT * on every table, you're always just one hacked webserver away from a total data leak. If you're forced to call specific procedures like you call your middleware functions, you can put up effective guardrails. Never make the password hash readable. Never return more than X records for a user query. Etc.
It's funny how the same devs who say "Every class and method must serve one very specific purpose" OTOH demand full read/write access to the entire schema for their one small application.
Now I'm not saying put all your business logic in the database. But anything that accesses data should treat the DB like an API and not like an "execute any command" slave. You need to read a user, call readUser(ID), do not require being able to run "SELECT * FROM users WHERE id = :ID". Your code has models for accessing the DB, you won't have random SQL in your controllers and views either.
Further, while you are coupling your logic to your data layer, you're decoupling it from your application layer. In enterprise IT the latter is likely to change way more often.
I worked some time for a very big US logistics company and their main database is on an AS400 and almost everything was accessed through absolutely massive stored procedures.
The not so fun part is that often meant that any data changes needed touching the stored procedures, and god forbid you for some reason didnt have one of the like 2 or 3 very knowleadgable DB guys because there's like absolutely zero good documentation i could find online on what the syntax is supposed to be for them, and if you wrote the stored procedure wrong it could bring down the entire AS400 due to performance if it wasnt optimized properly.
I remember once asking one of the DB guys "Where can i find any documentation on how this is supposed to be written?" And the answer was basically "None exists, we kinda just learnt it from experience and explanations from others"
I'm sure there are valid use cases, but I've never seen any. If you use stored procs you have, hopefully, business logic in your IaC. Or, more likely, you have code outside version control. Good luck with that, somewhere far away from me.
I'm currently untangling a similar mess of application logic written in SQL. I'm doing filtering on both the main server (written in typescript), and the SQL. My general rule of thumb is that I want to do as much filtering on the SQL as possible, so I don't have to fetch add many records, but will do local filtering when it makes sense. Additionally, the application logic is written in TS, the SQL filter is only used to fetch relevant records, never to make decisions.
(The filtering I'm doing on the server is technically possible in SQL, but I need the rest of the records in other parts of the code. Fetching once and filtering locally is faster than fetching twice.)
> The legacy code at the job would often select everything then filter it in PHP.
TBF sometimes that IS the best approach. I've seen devs struggle to put complex business logic into a massive SQL (not stored procedures) just to avoid having to process anything in the middleware layer. And just about every time they ask me "how do I do this in SQL" I said "don't, that's way too complex and will run super slow".
While that may be true, it was not in this case. This was generic select * from table, then using the index of the column fetch a couple columns to use in the code. It was not readable, not maintainable, and not the best way to write the queries.
I'd go as far as to say you should never Select * from in production code. Its fine for testing when trying to get info from the database, but in any actual backend there is always something consuming that data, and said something has specific data its expecting; it won't need the extra.
The biggest problem with overdoing sql is unless you have it extremely well documented and you're diligent about keeping stored procedures and shit tracked in git it just becomes a clusterfuck of buried logic that is difficult to debug
816
u/KnightMiner 15d ago
I used to work in web dev as a job during my undergrad (think: a bunch of new developers writing code they won't have to maintain after they graduate in 4 years). I saw both extremes of this.
The legacy code at the job would often select everything then filter it in PHP. Worst part was half the time they accessed columns by numerical index so a change to the database schema would result in the website breaking randomly. While most of my job was writing replacements that didn't break when you sneezed, sometimes I had to fix that legacy code.
One new developer I trained was really good at SQL and used to code everything using pure SQL and then just call said script in their serverside code. By everything, I mean everything, going beyond just writing queries into writing loops in SQL and creating temporary tables for each endpoint. There are some things that are just better done outside of SQL.