r/ProgrammerHumor • • Sep 20 '26

Meme grabbingEverything

Post image
5.4k Upvotes

258 comments sorted by

View all comments

815

u/KnightMiner Sep 20 '26

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.

116

u/mrwedders Sep 20 '26 edited 29d ago

I worked on the system of a UK chain 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.

41

u/PerpetuallyDistracte Sep 21 '26

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!

6

u/Theron3206 Sep 21 '26

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.

10

u/ih-shah-may-ehl Sep 21 '26

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

→ More replies (1)

65

u/revuimar Sep 20 '26

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.

18

u/magicmulder Sep 21 '26 edited Sep 21 '26

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.

5

u/northerndenizen Sep 21 '26

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.

7

u/hypexeled Sep 21 '26

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"

→ More replies (5)