4 ms·

At my last position we simply did sprocs and dapper. When you take as true that the database is the source of truth, it only makes sense to put the queries in

At my last position we simply did sprocs and dapper. When you take as true that the database is the source of truth, it only makes sense to put the queries in the database. The only exception is dynamic queries or modifications.

UK-Al05 7y ago
It's great when you have good database ci systems and have your database schema versioned + sprocs in source control. But I've worked places where dba refused to use version control.
- rjbwork 7y ago
  With the MS stack it's really easy. You can design the schema and procs, views, etc. in Visual Studio, then have it compare against each environment to create the deployment script.
johnnyb9 7y ago
How do you avoid leaking business logic into database stored procedures? Sounds like you would now have two sources of truth, application logic, and database stored procedures.
- jessaustin 7y ago
  I've seen "business logic" refer to both A) how a discount promotion works and B) how to arrange images of the product for a particular set of customers. Obviously it's fine to have A in the database...
  - mgkimsal 7y ago
    thanks for bringing that up. everyone on a team needs to have the same working definitions, and yeah... "business logic" is a term that's used a lot, but I very often see people not really understanding that it's usually meant in opposition to "display logic", not just as an interchangeable term for "any code".
- paragraft 7y ago
  I'd like to see someone have a decent go at reexamining the idea that business logic (BL) doesn't belong in the DB and tease apart exactly what that should mean, if it's actually still true, or even if it's ever actually been true. Maybe something like that's been posted here before, but I haven't seen it... I bought into the idea myself for a while, but when I interrogate my belief there it was just something I picked up at uni as part of the general 3-tier approach, and I'm not sure how much of it is really practically grounded. I see the same ideas held dogmatically by newer employees, and when I ask them about it in detail, I see the same fuzziness. After all, if you want to really be pedantic, then you could claim anything beyond having a single table with two columns, "key" and "value" is pushing BL into the DB. I.e, what practically does the separation gain you? You may want to swap out the DB in future? Almost never happens, not without some significant other refactoring going on (e.g breaking out into separate DBs because the product's grown to the point of needing distributing across services, etc). If you want to really design with that in mind, you're almost certainly giving up a lot of functionality that the particular DB is going to give you. It's on the level of holding onto the possibility that you'll want to change the language you're writing in at some stage (and that event would favour pushing more into the DB anyway). In reality, what I've found is when there's a reliable, transactional, relational datastore at the bottom (i.e we're not talking about a larger distributed system), then for your own sanity you want to be pushing as much as possible down the stack as possible, to reduce the surface area that someone could reach through to change data in the wrong way. Data consistency issues are some of the worst when it comes to eroding customer trust, and if your data access/mutation paths are many and varied then you can do your head in or burn through a lot of dev morale trying to diagnose them. The strongest advocates otherwise I've found are those in office cultures where there's little trust between devs and DBAs and getting things through DB review are a rigamorale that end up driving devs towards doing as much in code as possible. I've always suspected that beyond Google envy, these sort of dynamics are what drove a lot of the initial NoSQL movement...
  - taffer 7y ago
    Some interesting posts/articles on the topic: https://www.vertabelo.com/blog/business-logic-in-the-database-yes-or-no-it-depends/ https://www.vertabelo.com/blog/business-logic-in-the-databas... https://sivers.org/pg https://sivers.org/pg https://www.martinfowler.com/articles/dblogic.html https://www.martinfowler.com/articles/dblogic.html
- Rapzid 7y ago
  Have done stuff like this where the queries were quite complex and essentially BI style.. Wrote a system to store and manage the procs in VCS and manage them through the ORM migration system(ORMs are quite a la cart). Your either generating and submitting the queries through prepared statements or just storing them in the database. It's not "leaking" IMHO to do the later and in fact can come with benefits; you can call the procs while connected to the database through various other clients be it command line or a BI system. So in effect you are encapsulating the logic in the place with the most potential for re-use.
- ben509 7y ago
  Business logic being in stored procedures is the ideal place for it. The problem with it is technical: the tooling for migrating and managing schemas and stored procedures is hot garbage, and there aren't good ways to enforce consistency between the invariants of the applications and the database. Really, it should be possible, on both application deploy and when stored procedures are updated, to import library functions that the stored procedures use from the database and run unit tests against them to ensure that all clients are compatible.

UK-Al05 7y ago

It's great when you have good database ci systems and have your database schema versioned + sprocs in source control. But I've worked places where dba refused to use version control.

rjbwork 7y ago
With the MS stack it's really easy. You can design the schema and procs, views, etc. in Visual Studio, then have it compare against each environment to create the deployment script.

johnnyb9 7y ago

How do you avoid leaking business logic into database stored procedures? Sounds like you would now have two sources of truth, application logic, and database stored procedures.

jessaustin 7y ago
I've seen "business logic" refer to both A) how a discount promotion works and B) how to arrange images of the product for a particular set of customers. Obviously it's fine to have A in the database...
- mgkimsal 7y ago
  thanks for bringing that up. everyone on a team needs to have the same working definitions, and yeah... "business logic" is a term that's used a lot, but I very often see people not really understanding that it's usually meant in opposition to "display logic", not just as an interchangeable term for "any code".
paragraft 7y ago
I'd like to see someone have a decent go at reexamining the idea that business logic (BL) doesn't belong in the DB and tease apart exactly what that should mean, if it's actually still true, or even if it's ever actually been true. Maybe something like that's been posted here before, but I haven't seen it... I bought into the idea myself for a while, but when I interrogate my belief there it was just something I picked up at uni as part of the general 3-tier approach, and I'm not sure how much of it is really practically grounded. I see the same ideas held dogmatically by newer employees, and when I ask them about it in detail, I see the same fuzziness. After all, if you want to really be pedantic, then you could claim anything beyond having a single table with two columns, "key" and "value" is pushing BL into the DB. I.e, what practically does the separation gain you? You may want to swap out the DB in future? Almost never happens, not without some significant other refactoring going on (e.g breaking out into separate DBs because the product's grown to the point of needing distributing across services, etc). If you want to really design with that in mind, you're almost certainly giving up a lot of functionality that the particular DB is going to give you. It's on the level of holding onto the possibility that you'll want to change the language you're writing in at some stage (and that event would favour pushing more into the DB anyway). In reality, what I've found is when there's a reliable, transactional, relational datastore at the bottom (i.e we're not talking about a larger distributed system), then for your own sanity you want to be pushing as much as possible down the stack as possible, to reduce the surface area that someone could reach through to change data in the wrong way. Data consistency issues are some of the worst when it comes to eroding customer trust, and if your data access/mutation paths are many and varied then you can do your head in or burn through a lot of dev morale trying to diagnose them. The strongest advocates otherwise I've found are those in office cultures where there's little trust between devs and DBAs and getting things through DB review are a rigamorale that end up driving devs towards doing as much in code as possible. I've always suspected that beyond Google envy, these sort of dynamics are what drove a lot of the initial NoSQL movement...
- taffer 7y ago
  Some interesting posts/articles on the topic: https://www.vertabelo.com/blog/business-logic-in-the-database-yes-or-no-it-depends/ https://www.vertabelo.com/blog/business-logic-in-the-databas... https://sivers.org/pg https://sivers.org/pg https://www.martinfowler.com/articles/dblogic.html https://www.martinfowler.com/articles/dblogic.html
Rapzid 7y ago
Have done stuff like this where the queries were quite complex and essentially BI style.. Wrote a system to store and manage the procs in VCS and manage them through the ORM migration system(ORMs are quite a la cart). Your either generating and submitting the queries through prepared statements or just storing them in the database. It's not "leaking" IMHO to do the later and in fact can come with benefits; you can call the procs while connected to the database through various other clients be it command line or a BI system. So in effect you are encapsulating the logic in the place with the most potential for re-use.
ben509 7y ago
Business logic being in stored procedures is the ideal place for it. The problem with it is technical: the tooling for migrating and managing schemas and stored procedures is hot garbage, and there aren't good ways to enforce consistency between the invariants of the applications and the database. Really, it should be possible, on both application deploy and when stored procedures are updated, to import library functions that the stored procedures use from the database and run unit tests against them to ensure that all clients are compatible.