7 ms·

What do you mean? Isn’t your database precisely where you ought to put your business logic?

by tshaddox 5y ago

throw1234651234 5y ago
Is this a joke? SPs and SQL Functions are far less maintainable than .NET/Java/Node/Python.
- ako 5y ago
  Why are they less maintainable? It’s just code you can write in a code editor, version control in a versioning system, have automatic tests for, have automatic deployment scripts for, etc. In the end the database is just an application server executing your code close to the data. Only thing you have to ask yourself is how much your code needs to scale. But it can be much more efficient to run business logic in the database as stored procedures.
  - deleted 5y ago
    [deleted]
  - mdavidn 5y ago
    Using a migration utility like ActiveRecord, Flyway, or go-pg, SQL definitions can also live in version control, have automatic tests, and deploy automatically. Who has a database in production without these things?
    - throw1234651234 5y ago
      Unrelated, but which ones of these do you recommend (also factor in LiquiBase) that's free? As far as automatic tests for dbs - I have no idea what you are talking about unless you mean integration testing through a backend.
      - mdavidn 5y ago
        I’d recommend whichever one is available in a language the project uses already. Automatic tests for a database could simply be writes followed by reads with expectations for the returned rows. This is how Postgres tests itself. My personal go-to right now is ActiveRecord and RSpec. The examples could just as easily run raw SQL directly using the Ruby pg driver.
  - munk-a 5y ago
    I personally have found it's much more difficult to partially mock out concepts when writing logic in the DB layer though my tooling may have been the weakpoint. In a nice application language I can easily isolate specific units of code for direct testing - while every test I write over complex database logic feels like composing a symphony where I need to make sure the horns are coming in on the right pitch and the wind section isn't too early to properly simulate a relatively easy error scenario. Since the application layer is much more naturally aligned with small well isolated chunks of logic it is easier to minimize the volume of code and logic in scope when testing a particular attribute - when that goes into the DB I've always seen things get more complex rapidly.
  - Thaxll 5y ago
    "But it can be much more efficient to run business logic in the database as stored procedures." But this is not code, this SQL or equivalent, if SQL was known to be better than code to do busines logic everyone would do that.
    - ako 5y ago
      How is this not code: https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#insertedID8 https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#in... ? What is your definition of code?
    - mousepilot 5y ago
      There's all sorts of programming styles in use and calling stored procedures from dot net programs for instance is at least a thing. Plus, lots of those ORM things get plenty of hate from DBAs, I mean I like them but then again I'm not a very good programmer.
- mdavidn 5y ago
  That's perhaps true of stored procedures, but SQL views, constraints, and triggers written in a declarative manner are far more maintainable and far less buggy than imperative application code, in my experience.
  - throw1234651234 5y ago
    I have no words for "triggers" and "maintainable" in the same sentence - enjoy your deadlocks and having no clue why a single write says "UPDATED x records" y times without having to dig down through an ancient UI. I am actually "good" at this stuff - writing logic in SQL through triggers, which call functions (at which SQL Server is way better than PG is) and stored procs, etc, and it's just a horrible, horrible practice.
jayd16 5y ago
Like everything, it depends. Some business logic like "this field should never be negative" is probably fine. More complex logic is a lot harder to maintain. You don't want to stress your DB's CPU if you can avoid it. You still need some kind of connection pooler anyway. There's a lot of reasons it doesn't make sense to put all your business logic in the DB. SQL is not the easiest language to maintain (not enough abstraction). IMO the line to draw is just enough to keep data sanity.
- aww_dang 5y ago
  Validation on the client, for user convenience. Validation on the backend, for security. Validation on the DB, for sanity. Error states are a bit funky if you don't validate in the backend logic.
jasonpeacock 5y ago
Business logic goes into the code, data goes into the database. Sometimes business logic is implemented as rules, in which case the rules (configuration) can go into either configuration files or a database. But that doesn't make it data...
- fiddlerwoaroof 5y ago
  Data is just crystallized business logic. Anemic databases aren’t a big deal when the database is just a persistence layer for a single app but, when you’re rewriting the application code or allowing BI queries directly against the database, designing a database as a service whose interface is stored procedures starts making data integrity much easier.
  - peferron 5y ago
    Once you start having multiple data sources, business logic implemented in application code might be easier to reuse than business logic implemented in one of the data sources. For example: start with Postgres, then add ClickHouse to the mix. It might be easier to let the application handle access to both DBs, rather than write RLS policies in Postgres and then try to apply them to ClickHouse.
- ahallock 5y ago
  I think this is slightly reductive. The majority of your business logic, I agree, should reside in application code, but lightweight business logic in the form of database constraints are powerful at disallowing bad data. For example, you may have a rental booking application that shouldn't have overlapping date ranges. The database can give you this guarantee out of the box and will work for ALL clients and applications.
pbreit 5y ago
Wut? You put data in a database. Logic goes in the app.
- tyingq 5y ago
  In some cases, things like constraints and the model itself represent business logic.
  - orange8 5y ago
    I like this distinction from wikipedia [1] "Anything that is a process or procedure is business logic, and anything that is neither a process nor a procedure is a business rule." Meaning constraints and the model represent business rules. 1. https://en.wikipedia.org/wiki/Business_logic https://en.wikipedia.org/wiki/Business_logic

What do you mean? Isn’t your database precisely where you ought to put your business logic?

throw1234651234 5y ago

Is this a joke? SPs and SQL Functions are far less maintainable than .NET/Java/Node/Python.

ako 5y ago
Why are they less maintainable? It’s just code you can write in a code editor, version control in a versioning system, have automatic tests for, have automatic deployment scripts for, etc. In the end the database is just an application server executing your code close to the data. Only thing you have to ask yourself is how much your code needs to scale. But it can be much more efficient to run business logic in the database as stored procedures.
- deleted 5y ago
  [deleted]
- mdavidn 5y ago
  Using a migration utility like ActiveRecord, Flyway, or go-pg, SQL definitions can also live in version control, have automatic tests, and deploy automatically. Who has a database in production without these things?
  - throw1234651234 5y ago
    Unrelated, but which ones of these do you recommend (also factor in LiquiBase) that's free? As far as automatic tests for dbs - I have no idea what you are talking about unless you mean integration testing through a backend.
    - mdavidn 5y ago
      I’d recommend whichever one is available in a language the project uses already. Automatic tests for a database could simply be writes followed by reads with expectations for the returned rows. This is how Postgres tests itself. My personal go-to right now is ActiveRecord and RSpec. The examples could just as easily run raw SQL directly using the Ruby pg driver.
- munk-a 5y ago
  I personally have found it's much more difficult to partially mock out concepts when writing logic in the DB layer though my tooling may have been the weakpoint. In a nice application language I can easily isolate specific units of code for direct testing - while every test I write over complex database logic feels like composing a symphony where I need to make sure the horns are coming in on the right pitch and the wind section isn't too early to properly simulate a relatively easy error scenario. Since the application layer is much more naturally aligned with small well isolated chunks of logic it is easier to minimize the volume of code and logic in scope when testing a particular attribute - when that goes into the DB I've always seen things get more complex rapidly.
- Thaxll 5y ago
  "But it can be much more efficient to run business logic in the database as stored procedures." But this is not code, this SQL or equivalent, if SQL was known to be better than code to do busines logic everyone would do that.
  - ako 5y ago
    How is this not code: https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#insertedID8 https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#in... ? What is your definition of code?
  - mousepilot 5y ago
    There's all sorts of programming styles in use and calling stored procedures from dot net programs for instance is at least a thing. Plus, lots of those ORM things get plenty of hate from DBAs, I mean I like them but then again I'm not a very good programmer.
mdavidn 5y ago
That's perhaps true of stored procedures, but SQL views, constraints, and triggers written in a declarative manner are far more maintainable and far less buggy than imperative application code, in my experience.
- throw1234651234 5y ago
  I have no words for "triggers" and "maintainable" in the same sentence - enjoy your deadlocks and having no clue why a single write says "UPDATED x records" y times without having to dig down through an ancient UI. I am actually "good" at this stuff - writing logic in SQL through triggers, which call functions (at which SQL Server is way better than PG is) and stored procs, etc, and it's just a horrible, horrible practice.

jayd16 5y ago

Like everything, it depends. Some business logic like "this field should never be negative" is probably fine. More complex logic is a lot harder to maintain. You don't want to stress your DB's CPU if you can avoid it. You still need some kind of connection pooler anyway. There's a lot of reasons it doesn't make sense to put all your business logic in the DB. SQL is not the easiest language to maintain (not enough abstraction). IMO the line to draw is just enough to keep data sanity.

aww_dang 5y ago
Validation on the client, for user convenience. Validation on the backend, for security. Validation on the DB, for sanity. Error states are a bit funky if you don't validate in the backend logic.

jasonpeacock 5y ago

Business logic goes into the code, data goes into the database. Sometimes business logic is implemented as rules, in which case the rules (configuration) can go into either configuration files or a database. But that doesn't make it data...

fiddlerwoaroof 5y ago
Data is just crystallized business logic. Anemic databases aren’t a big deal when the database is just a persistence layer for a single app but, when you’re rewriting the application code or allowing BI queries directly against the database, designing a database as a service whose interface is stored procedures starts making data integrity much easier.
- peferron 5y ago
  Once you start having multiple data sources, business logic implemented in application code might be easier to reuse than business logic implemented in one of the data sources. For example: start with Postgres, then add ClickHouse to the mix. It might be easier to let the application handle access to both DBs, rather than write RLS policies in Postgres and then try to apply them to ClickHouse.
ahallock 5y ago
I think this is slightly reductive. The majority of your business logic, I agree, should reside in application code, but lightweight business logic in the form of database constraints are powerful at disallowing bad data. For example, you may have a rental booking application that shouldn't have overlapping date ranges. The database can give you this guarantee out of the box and will work for ALL clients and applications.

pbreit 5y ago

Wut? You put data in a database. Logic goes in the app.

tyingq 5y ago
In some cases, things like constraints and the model itself represent business logic.
- orange8 5y ago
  I like this distinction from wikipedia [1] "Anything that is a process or procedure is business logic, and anything that is neither a process nor a procedure is a business rule." Meaning constraints and the model represent business rules. 1. https://en.wikipedia.org/wiki/Business_logic https://en.wikipedia.org/wiki/Business_logic