Date: Fri, 19 Mar 2021 20:38:01 +0100
From: Bzzzz <lazyvirus@gmx.com>
To: pgsql-novice@lists.postgresql.org
Subject: Re: Is it better keep most logic in the database or the Node.js
 application?
Message-ID: <20210319203801.788e224f@msi.defcon1.lan>
In-Reply-To: 
 <_mfPnMS1u4MuZo4RbRpPJcRPW2eSbqUyYqZvJZQ-GNzxbus55FF-CHIwd7SY-gNTbY4LRjieTCGKMauoCgafMzdFJjHU0JDctjta_AkeXCU=@protonmail.com>
References: 
 <iP1Q7A5jXefT-1pnTm_xe11oZwWMxeZydnC6UfZAOu4BesbseT2xtKgwpR_3FORbOpg44gUNHiH1N_9FRzCDyz98hXlIubXTRuTKX1TNFVA=@protonmail.com>
	<20210319154549.173d0923@msi.defcon1.lan>
	<_mfPnMS1u4MuZo4RbRpPJcRPW2eSbqUyYqZvJZQ-GNzxbus55FF-CHIwd7SY-gNTbY4LRjieTCGKMauoCgafMzdFJjHU0JDctjta_AkeXCU=@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/20210319203801.788e224f%40msi.defcon1.lan>
Precedence: bulk

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

>=20
>=20
> =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90 Original =
Message =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90
>=20
> On Friday, March 19th, 2021 at 14:45, Bzzzz <lazyvirus@gmx.com> wrote:
>=20
> > 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.
> > >
> > > 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_YOeyk51=
4H6dYRr3TbYiA8DCNQ%40mail.gmail.com
> >
> > https://vertabelo.com/blog/business-logic-in-the-database-yes-or-no-it-=
depends/
> >
> > (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
>=20
> Thank you for the links they were really useful.

You're welcome!

> I'm building a
> blogging platform so it is not a super complicated application
> although there are some features there that could complicate some
> parts of it. I think I'm going to try and keep as much in the database
> as possible. It'll make the application code much easier to follow
> without loads of SQL to figure out and to add.

Try to avoid composite programming at all cost : 1 year later, you won't
remember where this or that is - into the code or into the DB.

I've tested all business logic into the DB with an app that makes
reservations ; at first, I was ranting because of=E2=80=A6 my own fault (mo=
stly
by adding features during coding) but at last it was very comfortable :
the DB's doing what it is good at (procedures, Fn, ruling user's
permissions, etc) and code only interrogates it through the API,
avoiding completely middleware/backend - moreover, there is no
possibility for clients to directly reach tables/views=C2=A0; at the
beginning, it was possible but I rapidly saw it was lame for the above
reason and the Fn/procs overhead is not that large that it is blocking
(especially after the 1st call).

Ah, I found the bookmark I was looking after at first, also read this :
https://gnuhost.medium.com/stored-procedures-as-a-backend-c5d2db452fc2

Jean-Yves