Re: Question about where to deploy the business logics for data processing

Uhm me need to start form 2 concepts:

1. competence
2. Network lag

Competence: usually programmers aren't skilled enough about the architectures and the actual needs of each layer.

This is a problem, because often programmers try to do something with what he already know (e.g. perform join in Java....).

A correct design requires to identify at least the data logic, the process logic, the business logic and the presentation logic.

One of the most important goals of Data logic is to ensure the correctness of data from many point of view (all is impossible).

That involve:

• audit information
• bitemporal management
• strictly definition and verification of data (foreign key, checks, management of compatibility)
• replicate consistently data for different usage
• isolate access for actual needs
• design

So an application that requires changing the data model does not seem to be well designed...

Network lag

The first problem is latency, I must minimize the passage of data over the network.

This means, for example, creating a service that allows the caller to choose only the information it needs. 

But it also means, to get all the information needed in a single call, design asynchronous service, use cache data physically near to the frontend or the middle layer.

Based on these 2 concepts I suggest:

• develop the Data logic near or inside the database;
• design powerful and addictive api;
• don't allow model change by the business logic
• organize/copy data in jsonb with a powerful json schema to provide coherence through every layer
• ensure a system to grant ACID features to your process.

Il giorno ven 9 giu 2023 alle ore 05:22 Nim Li <mr.nim.li@gmail.com> ha scritto:

Hello.

We have a PostgreSQL database with many tables, as well as foreign table, dblink, triggers, functions, indexes, etc, for managing the business logics of the data within the database.  We also have a custom table for the purpose of tracking the slowly changing dimensions (type 2).

Currently we are looking into using TypeORM (from Nest JS framework) to connect to the database for creating a BE that provides web service.  Some reasons of using TypeORM are that it can update the database schema without any SQL codes, works very well with Git, etc.  And from what I am reading, Git seems to work better with TypeORM, rather than handling individual batch files with SQL codes (I still need to find out more about this)  Yet I do not think the ORM concept deals with database specify functions, such as dblink and/or trigger-function, etc, which handles the business logics or any ETL automation within the database itself (I should read more about this as well.)

Anyway, in our team discussion, I was told that in modern programming concept, the world is moving away from deploying programming logics within the database (eg, by using PL/SQL).  Instead, the proper way should be to deploy all the programming logics to the framework which is used to connect to the database, such as NestJS in our case.  So, all we need in a database should be only the schema (managed by ORM), and we should move all the existing business logics (currently managed by things like the database triggers, functions, dblink, etc.) to the Typescript codes within the NestJS framework.

I wonder if anyone in the community has gone through changes like this?  I mean ... moving the business logics from PL/SQL within the database to the codes in NestJS framework, and reply on only the TypeORM to manage the update of the database without any SQL codes?  Any thoughts about such a change?  

Thank you!! 

--

Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]