Обсуждение: Is it better keep most logic in the database or the Node.js application?
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 usethat to directly query the database object? I would have thought using database functions would be faster as the databasewill know what the query is ahead of time and can perform some optimisations on it but this is just a guess on mypart. What should I do? Keep everything in the Node.js application or do as much as possible in PostgreSQL? Simon.
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 <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?
Simon.
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
On Fri, Mar 19, 2021 at 08:37:48AM +0000, Simon Connah 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
usethat to directly query the database object? I would have thought using database functions would be faster as the
databasewill know what the query is ahead of time and can perform some optimisations on it but this is just a guess on
mypart.
>
> 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 <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
‐‐‐‐‐‐‐ 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. I'm building a blogging platform so it is not a super complicated applicationalthough there are some features there that could complicate some parts of it. I think I'm going to try and keepas much in the database as possible. It'll make the application code much easier to follow without loads of SQL to figureout and to add.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, March 19th, 2021 at 17:16, Bruce Momjian <bruce@momjian.us> wrote: > On Fri, Mar 19, 2021 at 08:37:48AM +0000, Simon Connah 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 usingfunctions, stored procedures, triggers and everything else or should I keep the SQL in the Node.js web application anduse that to directly query the database object? I would have thought using database functions would be faster as the databasewill know what the query is ahead of time and can perform some optimisations on it but this is just a guess on mypart. > > > > 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 bruce@momjian.us https://momjian.us > > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. Thank you for the link. It was an interesting read.
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