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

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