Re: Are stored procedures/triggers common in your industry

You mentioned testing, and reminds me of another benefit. Way faster, more
reliable, cheaper to test on the DB side. Testing logic in SPs or SQL is
much easier, especially when testing requires a sequence of calls for a use
case. It is easier because of the DBs support for transactions. With
transactions and state management built into the DB, a testing process can
always revert to a reliable starting point and end point, thereby
facilitating more dependable, automated test harnesses. The alternative
done mostly now is testing via UIs or APIs where there is no inherent
transaction management, so a lot of work goes into preparing the test bed
to be a known state and introspecting the results to verify. This is
usually done with some mix of manual and automated processes.

On Thu, Apr 21, 2022 at 12:31 AM raf <raf(at)raf(dot)org> wrote:

> On Wed, Apr 20, 2022 at 12:18:23PM -0700, Guyren Howe <guyren(at)gmail(dot)com>
> 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
>
>
>
>