Date: Fri, 19 Mar 2021 15:45:49 +0100
From: Bzzzz <lazyvirus@gmx.com>
To: pgsql-novice@lists.postgresql.org
Cc: Simon Connah <simon.n.connah@protonmail.com>
Subject: Re: Is it better keep most logic in the database or the Node.js
 application?
Message-ID: <20210319154549.173d0923@msi.defcon1.lan>
In-Reply-To: 
 <iP1Q7A5jXefT-1pnTm_xe11oZwWMxeZydnC6UfZAOu4BesbseT2xtKgwpR_3FORbOpg44gUNHiH1N_9FRzCDyz98hXlIubXTRuTKX1TNFVA=@protonmail.com>
References: 
 <iP1Q7A5jXefT-1pnTm_xe11oZwWMxeZydnC6UfZAOu4BesbseT2xtKgwpR_3FORbOpg44gUNHiH1N_9FRzCDyz98hXlIubXTRuTKX1TNFVA=@protonmail.com>
Organization: Anyone, anywhere BUT in banana demokratik republik of france
 and UERSS.
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/20210319154549.173d0923%40msi.defcon1.lan>
Precedence: bulk

On Fri, 19 Mar 2021 08:37:48 +0000
Simon Connah <simon.n.connah@protonmail.com> wrote:

Hi,

> I've been wondering this for some time but is it better to keep the
> vast majority of the logic in the database using functions, stored
> procedures, triggers and everything else or should I keep the SQL in
> the Node.js web application and use that to directly query the
> database object? I would have thought using database functions would
> be faster as the database will know what the query is ahead of time
> and can perform some optimisations on it but this is just a guess on
> my part.
> =20
> What should I do? Keep everything in the Node.js application or do as
> much as possible in PostgreSQL?

TL;DR : it depends on the kind of your app.

Read these links:
https://www.postgresql.org/message-id/CAHyXU0z4BKZm324JGEj50LL6_YOeyk514H6d=
YRr3TbYiA8DCNQ%40mail.gmail.com

https://vertabelo.com/blog/business-logic-in-the-database-yes-or-no-it-depe=
nds/
(IMHO, one of the best article there is on this subject)

Rule of thumb: if it is a very simple app, use the code, otherwise, use
the <del>force, Luke</del> DB own language=C2=A0; in either cases, think
twice about your relational model to avoid touching it later.

Jean-Yves