Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q7lCt-0006rN-Ej for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 23:07:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q7lCr-0006Sz-Hq for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 23:07:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q7lCq-0006Sq-Uw for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 23:07:45 +0000 Received: from mail-pj1-x1031.google.com ([2607:f8b0:4864:20::1031]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q7lCl-0019qD-8Q for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 23:07:43 +0000 Received: by mail-pj1-x1031.google.com with SMTP id 98e67ed59e1d1-2563aaceda9so915319a91.1 for ; Fri, 09 Jun 2023 16:07:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686352057; x=1688944057; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :from:to:cc:subject:date:message-id:reply-to; bh=6LWZ6/LYie3LK9F6FyXuaVudjPQt+P3Y4L/0U/mF6JI=; b=qlFMrb7QD2bLxflTrLu0j8gRz/LT8A+OCNkW22uckn0llq7UUJBhQ4MHPwIRssnnkx yDb9H9vpXaQsXzR452IwL+An/TgGKwMIbIxoznjJUIsgb/rr8nPp+4T2StM7LQjISZ0M fzMv0GsrkQabbJO1u2UhGUUmCpB0a/UinPd5R7P0q2E8qL8kwOBhh+K9jFiEKvT8blKU y5smzKP/PQXYwCUlYFzeXnKRIwLaN3tMaCNQ+zrzQfjF1ZzDm1Yx7xbIFkfDhcRjzQBK hGPRIvWYp+HCuPAQVNcCkrgYPNfoX6lov9VavjS0r3qMMhHcfvngEFLpzJ9kisc8SFvf L21w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686352057; x=1688944057; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=6LWZ6/LYie3LK9F6FyXuaVudjPQt+P3Y4L/0U/mF6JI=; b=gxx7yp+gnS2XN8HVsy7HAs394D6JHBGbJQ1guujVQ8QkdpOpUj1SeCgWAF0QWa4nr0 lMt91IpNAuZu1Z8UpuNEK2TQg8jVbROshgqGjY/nhazsEZMK9hwkFF+MOKEclRQNtGSY r3R1DI0zwpxiLeNbEmXAi2NMlg7aqiMDUXUFbmNzK8VNWJHYXEWdjoNDhfKRngWhykb5 Zg9/9p4BDar1QvGMykKgPTfJyuLs725RJgea9PqKamudZIvrf8Ji9mwKpHIS3mvIKzTX cZ5YWmhLUJ5/Vy1Ih9PKNDX6F9jgQHESNaYo5dZL3TDSagC7DUI7Ko/Cb4dKr9/gRiez FxBw== X-Gm-Message-State: AC+VfDxGede7uRwUgnm30klCVbsUAGcTeH22eKwjflZvbt3nsdquuJZQ dU7niDSSOCzr9Y/RAtVCSM18FiJUrYI= X-Google-Smtp-Source: ACHHUZ6yuq+3vBL7i5qKbnqk/hq3za7xlPpyqhtgynM97sdHmr/qATM9e9wIsd16cZh90qKbVYvaLw== X-Received: by 2002:a17:90a:356:b0:256:22e:b93f with SMTP id 22-20020a17090a035600b00256022eb93fmr2011394pjf.18.1686352056740; Fri, 09 Jun 2023 16:07:36 -0700 (PDT) Received: from [2600:8801:8600:635:74:3f0c:100:0] ([2600:8801:8600:635::e]) by smtp.gmail.com with ESMTPSA id p9-20020a17090a74c900b0024e49b53c24sm3349345pjl.10.2023.06.09.16.07.35 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 09 Jun 2023 16:07:36 -0700 (PDT) Date: Fri, 9 Jun 2023 16:07:10 -0700 From: Guyren Howe To: pgsql-general@lists.postgresql.org, Ron Message-ID: In-Reply-To: <1374c239-64ae-8bfa-0ba4-df5bdbe42453@gmail.com> References: <1374c239-64ae-8bfa-0ba4-df5bdbe42453@gmail.com> Subject: Re: Question about where to deploy the business logics for data processing X-Readdle-Message-ID: fe232df6-f489-458b-ab2f-edfd9bd7fef9@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="6483b0b6_27c36576_1f02" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --6483b0b6_27c36576_1f02 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline People change applications and programming languages all the time. But change the database=3F Particularly away from Postgres, which is for = nearly any purpose clearly the best SQL database available=3F You have to pick one. Heck, write your triggers and stored procedures in = Python and you can change to SQL Server, or in Java and you have the opti= on of Oracle. There is never a good reason to use MySQL. :-) Guyren G Howe On Jun 9, 2023 at 13:39 -0700, Ron , wrote: > You can be sure that banks and academic research projects have differen= t needs.=C2=A0 Heck, your University's class scheduling software has diff= erent needs from the research problems that you support. > > The bottom line is that putting all of the =22business=22 logic in Type= ORM locks you into using an ORM, while putting as much =22business=22 log= ic in database as stored procedures, triggers, foreign keys, etc... doesn= 't.=C2=A0 Parts of the application can be in Java, some in JS, C, C++, Ru= st, Perl, even COBOL. > > On the other hand, putting so much logic into the database essentially = locks you into that RDBMS. > > > On 6/9/23 13:36, Nim Li wrote: > > Hello, > > > > Thank you so so much for all the feedback so far.=C2=A0 :D > > > > About this comment: > > > > > =22... an application that requires changing the data model does no= t seem to be well designed...don't allow model change by the business log= ic...=22 > > > > I work in a science research faculity.=C2=A0 When researchers start a= project, they don't necessary get the full picture of what they are hopi= ng to achive (yet they may get some ideas about the starting point that a= llow them to move forward)=C2=A0 By the time they see 40% percent of what= they have done, they may start to have a different thought and move towa= rds a different direction, or in some cases, they may spin it off to some= thing different after a certain period of time=C2=A0 Coming with my Agile= Development mindset in the research area, it is common for me to see use= rs changing their requirement and expectation, with the same buckets for = the data.=C2=A0 Yes, there is quite a lot of work to keep the researchers= happy.=C2=A0 ;-) > > > > I suppose when there is a specific end-goal to achive for a project, = a more specific design can be more feasible based on the goal.=C2=A0 But = when the end-goal is not necessary clear, and/or change-able, I am not ex= actly clear how we may draw a black-and-white line to determine a design = is good or not (.. and for how long...) > > > > I imagine one option may be to put less logics and restrictions on th= e data side, which allows the researchers to have more flexible on their = end.=C2=A0 But this may not be always feasbile due to the specific protoc= ol of the study.=C2=A0 Perhaps there may be some other approaches and/or = principles to deal with situation like mine=3F > > > > My major focus is still on getting more opinions about where to imple= ment the business logics for data processing ...=C2=A0 if you have any th= oughts about the design, I would love to hear your thoughts as well. > > > > Thank you so so much for sharing=21 > > > > On =46ri, Jun 9, 2023 at 12:35=E2=80=AFPM Lorusso Domenico wrote: > > > > Uhm me need to start form 2 concepts: > > > > > > > > 1. competence > > > > 2. Network lag > > > > > > > > Competence: usually programmers aren't skilled enough about the a= rchitectures and the actual needs of each layer. > > > > This is a problem, because=C2=A0often programmers try to do somet= hing with what he already know (e.g. perform join in Java....). > > > > > > > > A correct design requires to identify at least the data logic, th= e process logic, the business logic and the presentation logic. > > > > > > > > One of the most important goals of Data logic is to ensure the co= rrectness=C2=A0of data from many point of view (all is impossible). > > > > > > > > That involve: > > > > > > > > =E2=80=A2 audit information > > > > =E2=80=A2 bitemporal management > > > > =E2=80=A2 strictly definition and verification of data (foreign k= ey, checks, management of compatibility) > > > > =E2=80=A2 replicate consistently data for different usage > > > > =E2=80=A2 isolate access for actual=C2=A0needs > > > > =E2=80=A2 design > > > > > > > > So an application that requires changing the data model does not = seem to be well designed... > > > > > > > > Network lag > > > > The first problem=C2=A0is latency, I must minimize the passage of= data over the network. > > > > This means, for example, creating a service that allows the calle= r 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=C2=A0near to= the frontend or the middle layer. > > > > > > > > Based on these 2 concepts=C2=A0I suggest: > > > > > > > > =E2=80=A2 develop the Data logic near or inside the database; > > > > =E2=80=A2 design powerful and addictive=C2=A0api; > > > > =E2=80=A2 don't allow model change by the business logic > > > > =E2=80=A2 organize/copy data in jsonb=C2=A0with a powerful json s= chema to provide coherence=C2=A0through every layer > > > > =E2=80=A2 ensure a system to grant ACID features=C2=A0to your pro= cess. > > > > > > > > > > > > > > > > > Il giorno ven 9 giu 2023 alle ore 05:22 Nim Li ha scritto: > > > > > > Hello. > > > > > > > > > > > > We have a PostgreSQL database with many tables, as well as fo= reign table, dblink, triggers, functions, indexes, etc, for managing the = business logics of the data within the database.=C2=A0 We also have a cus= tom table for the purpose of tracking the slowly changing dimensions (typ= e 2). > > > > > > > > > > > > Currently we are looking into using TypeORM (from Nest JS fra= mework) to connect to the database for creating a BE that provides web se= rvice.=C2=A0 Some reasons of using TypeORM are that it can update the dat= abase schema without any SQL codes, works very well with Git, etc.=C2=A0 = 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 fin= d out more about this) =C2=A0Yet I do not think the ORM concept deals wit= h database specify functions, such as dblink and/or trigger-function, etc= , which handles the business logics or any ETL automation within the data= base itself (I should read more about this as well.) > > > > > > > > > > > > Anyway, in our team discussion, I was told that in modern pro= gramming concept, the world is moving away from deploying programming log= ics within the database (eg, by using PL/SQL).=C2=A0 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.=C2=A0 So= , all we need in a database should be only the schema (managed by ORM), a= nd we should move all the existing business logics (currently managed by = things like the database triggers, functions, dblink, etc.) to the Typesc= ript codes within the NestJS framework. > > > > > > > > > > > > I wonder if anyone in the community has gone through changes = like this=3F=C2=A0 I mean ... moving the business logics from PL/SQL with= in the database to the codes in NestJS framework, and reply on only the T= ypeORM to manage the update of the database without any SQL codes=3F=C2=A0= Any thoughts about such a change=3F > > > > > > > > > > > > Thank you=21=21 > > > > > > > > > > > > > > > > > > -- > > > > Domenico L. > > > > > > > > per stupire mezz'ora basta un libro di storia, > > > > io cercai di imparare la Treccani a memoria... =5B=46.d.A.=5D > > -- > Born in Arizona, moved to Babylonia. --6483b0b6_27c36576_1f02 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
People change applications and programming language= s all the time.

But change the database=3F Particularly away from Postgres, which is for = nearly any purpose clearly the best SQL database available=3F

You have to pick one. Heck, write your triggers and stored procedures in = Python and you can change to SQL Server, or in Java and you have the opti= on of Oracle.

There is never a good reason to use MySQL. :-)

Guyren G Howe
On Jun 9, 2023 at 13:39 -0700, Ron = <ronljohnsonjr=40gmail.com>, wrote:
You can be sure that banks and academic research projects have = different needs.&=23160; Heck, your University's class scheduling softwar= e has different needs from the research problems that you support.

The bottom line is that putting all of the =22business=22 logic in TypeOR= M locks you into using an ORM, while putting as much =22business=22= logic in database as stored procedures, triggers, foreign keys, etc... d= oesn't.&=23160; Parts of the application can be in Java, some in JS, C, C= ++, Rust, Perl, even COBOL.

On the other hand, putting so much logic into the database essentially locks you into that RDBMS.


On 6/9/23 13:36, Nim Li wrote:
Hello,

Thank you so so much for all the feedback so far.&=23160; :D

About this comment:

> =22... an application that requires changing the data model doe= s not seem to be well designed...don't allow model change by the business= logic...=22

I work in a science research faculity.&=23160; When researchers start a p= roject, they don't necessary get the full picture of what they are hoping= to achive (yet they may get some ideas about the starting point that all= ow them to move forward)&=23160; By the time they see 40% percent of what= they have done, they may start to have a different thought and move towa= rds a different direction, or in some cases, they may spin it off to some= thing different after a certain period of time&=23160; Coming with my Agi= le Development mindset in the research area, it is common for me to see u= sers changing their requirement and expectation, with the same buckets fo= r the data.&=23160; Yes, there is quite a lot of work to keep the researc= hers happy.&=23160; ;-)

I suppose when there is a specific end-goal to achive for a project,= a more specific design can be more feasible based on the goal.&=23160; B= ut when the end-goal is not necessary clear, and/or change-able, I am not= exactly clear how we may draw a black-and-white line to determine a desi= gn is good or not (.. and for how long...)&=23160;

I imagine one option may be to put less logics and restrictions on t= he data side, which allows the researchers to have more flexible on their= end.&=23160; But this may not be always feasbile due to the specific pro= tocol of the study.&=23160; Perhaps there may be some other approaches an= d/or principles to deal with situation like mine=3F

My major focus is still on getting more opinions about where to impl= ement the business logics for data processing ...&=23160; if you have any= thoughts about the design, I would love to hear your thoughts as well.

Thank you so so much for sharing=21

On =46ri, Jun 9, 2023 at 12:35=E2=80=AFPM Lorusso Domenico <domenico.l76=40= gmail.com> wrote:
Uhm me need= to start form 2 concepts:
  1. competence
  2. Network lag
Competence: usually programmers aren't skilled enough about the arch= itectures and the actual needs of each layer.
This is a problem, because&=23160;often programmers try to do someth= ing with what he already know (e.g. perform join in Java....).

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

One of the most important goals of Data logic is to ensure the corre= ctness&=23160;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, ma= nagement of compatibility)
  • replicate consistently data for different usage
  • isolate access for actual&=23160;needs
  • design
So an application that requires changing the data model does not see= m to be well designed...

Network lag
The first problem&=23160;is latency, I must minimize the passage of = data over the network.
This means, for example, creating a service that allows the caller t= o choose only the information it needs.&=23160;
But it also means, to get all the information needed in a single cal= l, design asynchronous service, use cache data physically&=23160;near to = the frontend or the middle layer.

Based on these 2 concepts&=23160;I suggest:
  • develop the Data logic near or inside the database;
  • design powerful and addictive&=23160;api;
  • don't allow model change by the business logic
  • organize/copy data in jsonb&=23160;with a powerful json schema to pro= vide coherence&=23160;through every layer
  • ensure a system to grant ACID features&=23160;to your process.


Il giorno ven 9 giu 2023 = alle ore 05:22 Nim Li <mr.nim.li=40gmail.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 log= ics of the data within the database.&=23160; 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 c= onnect to the database for creating a BE that provides web service.&=2316= 0; Some reasons of using TypeORM are that it can update the database sche= ma without any SQL codes, works very well with Git, etc.&=23160; And from= what I am reading, Git seems to work better with TypeORM, rather than ha= ndling individual batch files with SQL codes (I still need to find out mo= re about this) &=23160;Yet I do not think the ORM concept deals with data= base specify functions, such as dblink and/or trigger-function, etc, whic= h handles the business logics or any ETL automation within the database i= tself (I should read more about this as well.)

Anyway, in our team discussion, I was told that in modern programming con= cept, the world is moving away from deploying programming logics within t= he database (eg, by using PL/SQL).&=23160; Instead, the proper way should= be to deploy all the programming logics to the framework which is used t= o connect to the database, such as NestJS in our case.&=23160; So, all we= need in a database should be only the schema (managed by ORM), and we sh= ould move all the existing business logics (currently managed by things l= ike the database triggers, functions, dblink, etc.) to the Typescript cod= es within the NestJS framework.

I wonder if anyone in the community has gone through changes like this=3F= &=23160; I mean ... moving the business logics from PL/SQL within the dat= abase to the codes in NestJS framework, and reply on only the TypeORM to = manage the update of the database without any SQL codes=3F&=23160; Any th= oughts about such a change=3F &=23160;

Thank you=21=21&=23160;



--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... =5B=46.d.A.=5D

--
Born in Arizona, moved to Babylonia.
--6483b0b6_27c36576_1f02--