Thread: Are stored procedures/triggers common in your industry

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.

> On Apr 20, 2022, at 3:18 PM, Guyren Howe <guyren@gmail.com> wrote:
>
> I’ve really only ever worked in web development. 90+% of web developers regard doing anything at all clever in the
databasewith suspicion. 
>
> I’m considering working on a book about implementing business logic in Postgres, and I’m curious about how common
thatactually is. 
>


We have some business logic in Postgres functions, particularly triggers. Our apps are written in Python, and we use
pytestto exercise our SQL functions to ensure they're doing what we think they’re doing. It works well for us. 

FWIW, we’re not a Web dev shop.

Cheers
Philip

On 4/20/22 12:18, Guyren Howe 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.

For my purposes keeping this logic in the database makes changing or 
running multiple front ends easier. There is one place to change the 
logic vs keeping the same logic in different front ends in potentially 
different languages in sync. So for me it is common.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 20/04/2022 20:26, Philip Semanchuk wrote:
> We have some business logic in Postgres functions, particularly triggers. Our apps are written in Python, and we use
pytestto exercise our SQL functions to ensure they're doing what we think they’re doing. It works well for us.
 
>
> FWIW, we’re not a Web dev shop.
>
> Cheers
> Philip


We have a a great amount of our business logic in triggers; makes for
light, multiple and consistent front-ends. It's worked very well for
many years and continues to grow.

Tim Clarke



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852
58031687 | Toronto: +1 647 503 2848
 
Web: https://www.manifest.co.uk/

Watch our latest Minerva Briefings on
BrightTALK<https://www.brighttalk.com/channel/18792/?utm_source=brighttalk-sharing&utm_medium=web&utm_campaign=linkshare>



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee
youmust not use or disclose such information, instead please report it to
admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The
ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here
https://www.manifest.co.uk/legal/for further information.
 

>I've really only ever worked in web development. 90+% of web
>developers regard doing anything at all clever in the database with suspicion.

One common argument they use is that if you write your business logic in stored procedure, you are locked to that database since stored procedure languages are pretty much vendor locked.

TBH when one sees tens of thousands of Oracle PL/SQL code, there is some truth in this.

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.

Well, there are 2 schools of thought:

1. Put the business logic into the application
2. Put the business logic into the database

Putting the business logic into the application can give you more flexibility around enforcing them. On the other hand, you also increase chances of inconsistency. There will likely be more than one application using reference tables like ADDRESS, ZIP_CODE, STATE, COUNTRY, QUARTER, ACCOUNT, CUSTOMER and similar. If there is a rule that a country must exist before you add an address in that country into the table, that can be enforced by a foreign key. Enforcing it within the application does 2 things:

1. Move the rule code to the application server which is traditionally weaker than a database server. In other words, you are more likely to run out of CPU juice and memory on an application server than you are likely to run out of resources on the DB server.
2. There is a possibility for inconsistency. Different applications can use different business rules for the same set of tables. That means that data entered by one application may make the table internally inconsistent for another application.

I am a big proponent of using foreign keys, check constraints and triggers to enforce business rules. I am also a big proponent of avoiding NULL values wherever possible. Database design is an art. CAD software used to be popular once upon a time, in a galaxy far, far away.  Properly enforcing the business rules in the database itself makes the application more clear and easier to write.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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

On Thu, Apr 21, 2022 at 08:42:10AM -0500, Alex Aquino <alex@efficiencygeek.com> wrote:

> 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.

Actually, my full work tests take ages to run (~40m).
I know that mocking the db to make unit tests fast is popular,
but that's not helpful when the most important code being tested
is in the database. :-) It's more important to me that the tests
actually test everything than that they be fast.

But yes, being able to do complex system testing with transaction
rollback is great.

cheers,
raf