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

From: Simon Connah <[email-masked]>
Date: Fri, 19 Mar 2021 08:37:48 +0000

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?

Simon.

---

From: Laszlo Forro <[email-masked]>
Date: Fri, 19 Mar 2021 14:41:48 +0000

My experience is that
- design your entities and represent them via a view. That means: you may
normalize an entity - say, customer entity across tables like 'address' :
'personal data ' etc. various ways. But the view you offer to the client
code will be consistent like an API. If your entity representation turns
out to be suboptimal, you still can change the table structure and adjust
your view but nothing to do in the client code.
- use deterministic data transformations/pure functions in the DB - that is
faster, business logic independent, easy to test and you save client code.
Such as aggregates, combinations of columns - concatenations, math
operators, rolling averages, etc.
The rest I would put into the client code - more business logic related
data transformations.
This architecture keeps your DB as an encapsulated domain but also makes it
possible to switch the client code.
I hope this helped.
Laszlo

On Fri, Mar 19, 2021 at 8:38 AM Simon Connah <[email-masked]>
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.
>

---

From: Bzzzz <[email-masked]>
Date: Fri, 19 Mar 2021 15:45:49 +0100

On Fri, 19 Mar 2021 08:37:48 +0000
Simon Connah <[email-masked]> 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.
>  

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

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 19 Mar 2021 13:16:04 -0400

> What should I do? Keep everything in the Node.js application or do as much as possible in PostgreSQL?

I have a blog entry about it:

	https://momjian.us/main/blogs/pgblog/2017.html#June_14_2017

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

---

From: Simon Connah <[email-masked]>
Date: Fri, 19 Mar 2021 19:08:58 +0000

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Friday, March 19th, 2021 at 14:45, Bzzzz <[email-masked]> wrote:

> Simon Connah [email-masked] wrote:
>
> Hi,
>
> > What should I do? Keep everything in the Node.js application or do as
> >
> > much as possible in PostgreSQL?
>

Thank you for the links they were really useful. 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.

---

From: Simon Connah <[email-masked]>
Date: Fri, 19 Mar 2021 19:09:38 +0000

On Friday, March 19th, 2021 at 17:16, Bruce Momjian <[email-masked]> wrote:

> ---------------------------------------------------------------------------------------------
>
> Bruce Momjian [email-masked] https://momjian.us
>

Thank you for the link. It was an interesting read.

---

From: Bzzzz <[email-masked]>
Date: Fri, 19 Mar 2021 20:38:01 +0100

On Fri, 19 Mar 2021 19:08:58 +0000
Simon Connah <[email-masked]> wrote:

> 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