Re: Is it better keep most logic in the database or the Node.js application?

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

>
>
> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>
> On Friday, March 19th, 2021 at 14:45, Bzzzz <lazyvirus@gmx.com> wrote:
>
> > 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_YOeyk514H6dYRr3TbYiA8DCNQ%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 ; in either cases, think
> >
> > twice about your relational model to avoid touching it later.
> >
> > Jean-Yves
>
> 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… my own fault (mostly
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 ; 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