public inbox for [email protected]help / color / mirror / Atom feed
Is it better keep most logic in the database or the Node.js application? 7+ messages / 4 participants [nested] [flat]
* Is it better keep most logic in the database or the Node.js application? @ 2021-03-19 08:37 Simon Connah <[email protected]> 2021-03-19 14:41 ` Re: Is it better keep most logic in the database or the Node.js application? Laszlo Forro <[email protected]> 2021-03-19 14:45 ` Re: Is it better keep most logic in the database or the Node.js application? Bzzzz <[email protected]> 2021-03-19 17:16 ` Re: Is it better keep most logic in the database or the Node.js application? Bruce Momjian <[email protected]> 0 siblings, 3 replies; 7+ messages in thread From: Simon Connah @ 2021-03-19 08:37 UTC (permalink / raw) To: [email protected] <[email protected]> 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. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it better keep most logic in the database or the Node.js application? 2021-03-19 08:37 Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> @ 2021-03-19 14:41 ` Laszlo Forro <[email protected]> 2 siblings, 0 replies; 7+ messages in thread From: Laszlo Forro @ 2021-03-19 14:41 UTC (permalink / raw) To: Simon Connah <[email protected]>; +Cc: [email protected] <[email protected]> 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 protected]> 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. > > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it better keep most logic in the database or the Node.js application? 2021-03-19 08:37 Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> @ 2021-03-19 14:45 ` Bzzzz <[email protected]> 2021-03-19 19:08 ` Re: Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> 2 siblings, 1 reply; 7+ messages in thread From: Bzzzz @ 2021-03-19 14:45 UTC (permalink / raw) To: [email protected]; +Cc: Simon Connah <[email protected]> On Fri, 19 Mar 2021 08:37:48 +0000 Simon Connah <[email protected]> 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.gma... 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it better keep most logic in the database or the Node.js application? 2021-03-19 08:37 Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> 2021-03-19 14:45 ` Re: Is it better keep most logic in the database or the Node.js application? Bzzzz <[email protected]> @ 2021-03-19 19:08 ` Simon Connah <[email protected]> 2021-03-19 19:38 ` Re: Is it better keep most logic in the database or the Node.js application? Bzzzz <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Simon Connah @ 2021-03-19 19:08 UTC (permalink / raw) To: Bzzzz <[email protected]>; +Cc: [email protected] ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, March 19th, 2021 at 14:45, Bzzzz <[email protected]> wrote: > On Fri, 19 Mar 2021 08:37:48 +0000 > > Simon Connah [email protected] 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.gma... > > 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 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. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it better keep most logic in the database or the Node.js application? 2021-03-19 08:37 Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> 2021-03-19 14:45 ` Re: Is it better keep most logic in the database or the Node.js application? Bzzzz <[email protected]> 2021-03-19 19:08 ` Re: Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> @ 2021-03-19 19:38 ` Bzzzz <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bzzzz @ 2021-03-19 19:38 UTC (permalink / raw) To: [email protected] On Fri, 19 Mar 2021 19:08:58 +0000 Simon Connah <[email protected]> wrote: > > > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > > On Friday, March 19th, 2021 at 14:45, Bzzzz <[email protected]> wrote: > > > On Fri, 19 Mar 2021 08:37:48 +0000 > > > > Simon Connah [email protected] 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.gma... > > > > 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it better keep most logic in the database or the Node.js application? 2021-03-19 08:37 Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> @ 2021-03-19 17:16 ` Bruce Momjian <[email protected]> 2021-03-19 19:09 ` Re: Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> 2 siblings, 1 reply; 7+ messages in thread From: Bruce Momjian @ 2021-03-19 17:16 UTC (permalink / raw) To: Simon Connah <[email protected]>; +Cc: [email protected] <[email protected]> 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 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? I have a blog entry about it: https://momjian.us/main/blogs/pgblog/2017.html#June_14_2017 -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it better keep most logic in the database or the Node.js application? 2021-03-19 08:37 Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> 2021-03-19 17:16 ` Re: Is it better keep most logic in the database or the Node.js application? Bruce Momjian <[email protected]> @ 2021-03-19 19:09 ` Simon Connah <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Simon Connah @ 2021-03-19 19:09 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: [email protected] <[email protected]> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, March 19th, 2021 at 17:16, Bruce Momjian <[email protected]> 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 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? > > I have a blog entry about it: > > https://momjian.us/main/blogs/pgblog/2017.html#June_14_2017 > > --------------------------------------------------------------------------------------------- > > Bruce Momjian [email protected] 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. ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-03-19 19:38 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-03-19 08:37 Is it better keep most logic in the database or the Node.js application? Simon Connah <[email protected]> 2021-03-19 14:41 ` Laszlo Forro <[email protected]> 2021-03-19 14:45 ` Bzzzz <[email protected]> 2021-03-19 19:08 ` Simon Connah <[email protected]> 2021-03-19 19:38 ` Bzzzz <[email protected]> 2021-03-19 17:16 ` Bruce Momjian <[email protected]> 2021-03-19 19:09 ` Simon Connah <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox