On Wed, Apr 20, 2022 at 12:18:23PM -0700, Guyren Howe <guyren@xxxxxxxxx> wrote: > I’ve really only ever worked in web development. 90+% of web > developers regard doing anything at all clever in the database with > suspicion. > > I’m considering working on a book about implementing business logic in > Postgres, and I’m curious about how common that actually is. I'm used to putting all business logic in the database (after choosing a great FLOSS database that you'll never want to migrate away from - like Postgres). And I've never regretted it (in decades of doing it). One of the main reasons is speed. I once had a job where a program selected data out of a database, dragged it over a network, effectively grouped it into summaries, sent the summaries back over the network, and inserted them back into the database one at a time(!). Replacing it with a stored procedure changed it from taking 2-3 hours to 2 minutes. And that was a place that already made heavy use of stored procedures, so I don't know what went wrong there. The point is that whenever a lot of data activity is needed, it's much faster when it's done where the data lives. The other main reason is security. The database can provide an effective "firewall" between the data and the client. I never liked the idea of trusting arbitrary SQL sent from the client. It means you have to trust every single client application and every single user (even the ones with good intentions that produce bad queries in some reporting software and throwing it at the database and bringing it to its knees) and every single developer (who might not know SQL and relies on ORMs that trick them into thinking they don't need to). But when the clients are only permitted to execute security defining stored procedures that have been loaded by the privileged database owner, you know exactly what code can run inside the database. SQL injections become impossible no matter how many bugs and flaws there are in the client software or its supply chain. Another good but less critical reason is that when you use multiple languages, or you migrate partially or completely from the old cool language to the new cool language, you don't have to replicate the business logic in the new language, and you can eliminate the risk of introducing bugs into mission critical code. The existing business logic and its test suite can stay stable while all the bells and whistles on the outside change however they like. There are other nice benefits but that's enough. I think it's safe to disregard the suspicions of the 90+% of web developers you mentioned. The requirements that they have for a database might be quite undemanding. Most individual actions on a website probably don't result in a lot of data activity (or rather activity that involves a lot of data). The CRUD model is probably all they need. So their views are understandable, but they are based on limited requirements. However, I still use stored procedures for everything on websites for security reasons. Everyone's mileage varies. We're all in different places. cheers, raf
