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 1q7dhV-0000SZ-Ix for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 15:06:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q7dhT-0000BV-M6 for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 15:06:51 +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 1q7dhT-0000BM-Al for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 15:06:51 +0000 Received: from mail-pf1-x42b.google.com ([2607:f8b0:4864:20::42b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q7dhM-00167K-JH for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 15:06:50 +0000 Received: by mail-pf1-x42b.google.com with SMTP id d2e1a72fcca58-6537d2a8c20so1558258b3a.2 for ; Fri, 09 Jun 2023 08:06:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686323203; x=1688915203; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=CeYKUjWJeN0BZmVDdwNlWIsnoa/IaqU4jNQkbilmgn4=; b=Gh50gdfjWtn+pfrvON2gjumabtmGu1fJPt0Ikm5LWniwxepoK1NdN5n6t/Wxmvcusw dD4EXORucr3XT8dE2lkJegqEBlPtdZ9tjVEQ+QDZhhQEYXwtCK616SMsWaK4Ha5XWidV /R9b4O0akFBZqFlANbwFa9Tgg7ujQQMtD2582DpLP6+6fmKrJ28eQIU3n64z/esatcTk lGYzgHEofCjQYMcXdQ1WtXCPfgwBM3RbbZlZvTj84erRyT9/9DjaYgtmil6CYnkSUsgI H6yYOQ0tL5F6Vu0HkWxtcfncHm4XDojlt3bEH8UJItCSMnhBhcA3WzhH9aSVKTGzDJwp fHJw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686323203; x=1688915203; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=CeYKUjWJeN0BZmVDdwNlWIsnoa/IaqU4jNQkbilmgn4=; b=M1lNBc9fYTxsmvzWaoOkPvLAOxQb4Hjr75apSJYjSv1BcBnjcDO0YnHlg9PLpqG659 L5SGwbfdx9LU489tmANC/jttPZlER8KNBqWRS+nHFXdPmn5hYOquECKwYNO9H2NwJciV zB8f6wwPWYBMxYXX/AAJ6AjEZm3VXhqGauXr3al4Z+vD3XtBtFUFDp7Dzb1pikNA9wle n+8hPlDmleMulEIm8uSu0FSBSVJ5AN9TiZ3ftLTxPUBJW0rspKHla1qVc7rE9yes6Gqk mbHl3sV6ro99s/fVeVu8BeAnVt5fdX9B+uqgy4sdu441GV2hM1v4zMx6uLOMjqO0JOFa mOOw== X-Gm-Message-State: AC+VfDxEY5glH20acQU4KVP0jbue0Kp3CoUM/mNq0JwuZtNwjZXhR42a 1BbyoCBvUM41y6c2zHwwZ7g= X-Google-Smtp-Source: ACHHUZ6EsbYyWekKXGPnP7+36NrzYIBt7OLP3l+Zai/S7ViEk1GLpLXfjJkA3DwkJdshTS1mC1+2Rw== X-Received: by 2002:a05:6a21:170e:b0:10b:fb01:a72a with SMTP id nv14-20020a056a21170e00b0010bfb01a72amr1027814pzb.60.1686323203404; Fri, 09 Jun 2023 08:06:43 -0700 (PDT) Received: from smtpclient.apple ([155.98.131.5]) by smtp.gmail.com with ESMTPSA id y8-20020aa78548000000b0064d31ddb5e4sm2803378pfn.43.2023.06.09.08.06.42 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 09 Jun 2023 08:06:42 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.15\)) Subject: Re: Question about where to deploy the business logics for data processing From: Rob Sargent In-Reply-To: Date: Fri, 9 Jun 2023 08:06:41 -0700 Cc: pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: To: Nim Li X-Mailer: Apple Mail (2.3654.120.0.1.15) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Jun 8, 2023, at 8:21 PM, Nim Li wrote: >=20 > Hello. >=20 > 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).=20 >=20 > 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.) >=20 > 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. >=20 > 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? =20 >=20 > Thank you!!=20 >=20 You=E2=80=99re riding a pendulum which has swung too far. =20 In any organization of even minimal complexity, the physical data model = and the deployable business model are never well aligned: the usages of = the data are in a different dimension than the storage and maintenance = of the data. I=E2=80=99ve not heard of TypeORM but on this list ORMs = are notorious for generating poorly performing queries. The notion that = application programming will replace database triggers is ludicrous.