5 ms·

The problem with having business logic in the DB is not a performance problem, it's a maintainability, testing, and documentation problem.

dkjaudyeqooe 3y ago
You've demonstrated the real issue here, but not the one you claim, it's that programmers have no idea how to use (SQL) databases or how they work. The idea that software has those negative characteristics merely because it resides within a database, is laughable.
- setr 3y ago
  All of the PLSQL dialects are pretty much universally mediocre at best. They don’t (de-)compose well, they don’t version-control well, they don’t document well, they don’t debug well — they pretty much lack any of the learnings and tooling that occurred in application languages over the last 20 years. You’re basically dealing with all of the problems of a poorly designed DSL made by your favorite coworker. You could do something more sane like running lang-extensions on the db so you have access to a “real” language to specify your business logic in, but then you haven’t really changed anything except to run your app logic on the DB server and doesn’t really have anything to do with the RDBMS at that point. Moving business logic into the schema/queries can be fine, but you still have the issues that you can’t document it particularly well, can’t version control it well, can’t test it well, can’t trust your performance tuning well, etc. Which all derives from DBMS expecting to be treated as “living systems”, and has nothing to do with knowing/not knowing SQL. And the problem with modifying living systems is that you really don’t want to be fucking around with it because anything could potentially send it reeling (see: surgeons). You really want it to be stable… so moving your unstable business logic out is almost a necessity
  - nicoburns 3y ago
    A lot of the stuff I see people writing application code for doesn't require any more complex SQL features than joins, aggregations and filters. i.e the stuff plain old SQL is really good at.
    - setr 3y ago
      That’s just born of either psychosis or naivety; but even when one does know SQL, there’s more to consider. RDBMS’s are awkward systems to work with, and SQL knowledge isn’t the issue moving business logic into schema/queries is usually fine, (and perhaps the only sane place for the DB to directly interact with business logic) though schema changes has all the issues described. Queries get to live with your application code, and so can be treated sanely, but you only really get to a good setup with a decent query builder library (ORM-haters smashing strings together like an ape is naturally full of issues, and ORM-lovers get all the fun of table/object model-mismatch and poor query generation). But then you run into the issue that every database library tries to be database-agnostic, and you lose access to all the fancy features your database provides
      - nicoburns 3y ago
        > but you only really get to a good setup with a decent query builder library (ORM-haters smashing strings together like an ape is naturally full of issues, and ORM-lovers get all the fun of table/object model-mismatch and poor query generation). But then you run into the issue that every database library tries to be database-agnostic, and you lose access to all the fancy features your database provides I've found that smashing strings together works excellently so long as: - You use something like JavaScript's template strings so that interpolated fragments are inline - You have named placeholder support (so that you're not trying to count parameter placements which gets hugely complex for optional parameters). - You mainly use it for SELECTS and have minimal query builder for INSERTs/UPDATEs/UPSERTs And you get full access to all of the database's features.
        setr 3y ago
        If you’re maintaining a layer between construction and finalization (the query builder), then you’re not really in the string smashing group I was describing. The main thing I was trying to get at there is no one should make the mistake of “ORMs are shit, I can just write SQL strings directly” because that road is plagued with problems (because SQL is not a language that composes well..). And it’s not just named parameters; with a sane query builder you don’t need to totally rewrite things when dealing with subqueries vs top-level, aggregations, etc. As long as you don’t go so far as to introduce the horror of an ORM
  - dkjaudyeqooe 3y ago
    You're making my point here. RDBMSes are not any more difficult or mysterious than any other software except for the fact that you're more familiar with that software and language, thus your 'doesn't X well'. What you will find though is any sufficiently complicated business application software implementing an ad hoc informally-specified bug-ridden slow implementation of half of what the RDBMS is giving you for free.
    - setr 3y ago
      > except for the fact that you're more familiar with that software and language, thus your 'doesn't X well'. That’s exactly not my point. My point is that, knowing SQL, knowing what RDBMS’s have and what they are, RDBMS’s are a bad place to stuff your business logic. You have a variety of options that don’t work well. Schemas/queries are the only relevant place where RDBMS’s excel, and because it’s a living system, you don’t really want something so unstable as business logic to permeate through your schema significantly. It’s not a matter of familiarity. It’s the wrong tool for the job being discussed.
      - dkjaudyeqooe 3y ago
        What's not a "living system"? You can update RDBMSes much more cleanly and reliably than almost all other software. Stored procedures (in a variety of languages) and the like do not necessitate changes to your data schema and the data that ends up in your schema belongs there, regardless of how its produced. That is a data design issue. Being able to roll forward and back with code and data (including an updated schema) that is synchronised and works is a huge win. In most places where I've worked with serious RDBMS systems developers have no access whatsoever to production. The fact that this isn't the case universally illustrates the strength of these RDBMS approaches.
  - JCharante 3y ago
    why aren't stored procedures on a DB not able to be version controlled well? People already version control database schemas and migrations, why would stored procedures not also be able to be version controlled just as well? You can test it by creating shadow databases (what Prisma does) to make sure all migrations can be done on an empty database just fine, add test data, call the procedure, and check the output. Genuinely please do enlighten me as to why this isn't possible or doable, I would love to learn more about this part of the field.

dkjaudyeqooe 3y ago

You've demonstrated the real issue here, but not the one you claim, it's that programmers have no idea how to use (SQL) databases or how they work. The idea that software has those negative characteristics merely because it resides within a database, is laughable.

setr 3y ago
All of the PLSQL dialects are pretty much universally mediocre at best. They don’t (de-)compose well, they don’t version-control well, they don’t document well, they don’t debug well — they pretty much lack any of the learnings and tooling that occurred in application languages over the last 20 years. You’re basically dealing with all of the problems of a poorly designed DSL made by your favorite coworker. You could do something more sane like running lang-extensions on the db so you have access to a “real” language to specify your business logic in, but then you haven’t really changed anything except to run your app logic on the DB server and doesn’t really have anything to do with the RDBMS at that point. Moving business logic into the schema/queries can be fine, but you still have the issues that you can’t document it particularly well, can’t version control it well, can’t test it well, can’t trust your performance tuning well, etc. Which all derives from DBMS expecting to be treated as “living systems”, and has nothing to do with knowing/not knowing SQL. And the problem with modifying living systems is that you really don’t want to be fucking around with it because anything could potentially send it reeling (see: surgeons). You really want it to be stable… so moving your unstable business logic out is almost a necessity
- nicoburns 3y ago
  A lot of the stuff I see people writing application code for doesn't require any more complex SQL features than joins, aggregations and filters. i.e the stuff plain old SQL is really good at.
  - setr 3y ago
    That’s just born of either psychosis or naivety; but even when one does know SQL, there’s more to consider. RDBMS’s are awkward systems to work with, and SQL knowledge isn’t the issue moving business logic into schema/queries is usually fine, (and perhaps the only sane place for the DB to directly interact with business logic) though schema changes has all the issues described. Queries get to live with your application code, and so can be treated sanely, but you only really get to a good setup with a decent query builder library (ORM-haters smashing strings together like an ape is naturally full of issues, and ORM-lovers get all the fun of table/object model-mismatch and poor query generation). But then you run into the issue that every database library tries to be database-agnostic, and you lose access to all the fancy features your database provides
    - nicoburns 3y ago
      > but you only really get to a good setup with a decent query builder library (ORM-haters smashing strings together like an ape is naturally full of issues, and ORM-lovers get all the fun of table/object model-mismatch and poor query generation). But then you run into the issue that every database library tries to be database-agnostic, and you lose access to all the fancy features your database provides I've found that smashing strings together works excellently so long as: - You use something like JavaScript's template strings so that interpolated fragments are inline - You have named placeholder support (so that you're not trying to count parameter placements which gets hugely complex for optional parameters). - You mainly use it for SELECTS and have minimal query builder for INSERTs/UPDATEs/UPSERTs And you get full access to all of the database's features.
      - setr 3y ago
        If you’re maintaining a layer between construction and finalization (the query builder), then you’re not really in the string smashing group I was describing. The main thing I was trying to get at there is no one should make the mistake of “ORMs are shit, I can just write SQL strings directly” because that road is plagued with problems (because SQL is not a language that composes well..). And it’s not just named parameters; with a sane query builder you don’t need to totally rewrite things when dealing with subqueries vs top-level, aggregations, etc. As long as you don’t go so far as to introduce the horror of an ORM
- dkjaudyeqooe 3y ago
  You're making my point here. RDBMSes are not any more difficult or mysterious than any other software except for the fact that you're more familiar with that software and language, thus your 'doesn't X well'. What you will find though is any sufficiently complicated business application software implementing an ad hoc informally-specified bug-ridden slow implementation of half of what the RDBMS is giving you for free.
  - setr 3y ago
    > except for the fact that you're more familiar with that software and language, thus your 'doesn't X well'. That’s exactly not my point. My point is that, knowing SQL, knowing what RDBMS’s have and what they are, RDBMS’s are a bad place to stuff your business logic. You have a variety of options that don’t work well. Schemas/queries are the only relevant place where RDBMS’s excel, and because it’s a living system, you don’t really want something so unstable as business logic to permeate through your schema significantly. It’s not a matter of familiarity. It’s the wrong tool for the job being discussed.
    - dkjaudyeqooe 3y ago
      What's not a "living system"? You can update RDBMSes much more cleanly and reliably than almost all other software. Stored procedures (in a variety of languages) and the like do not necessitate changes to your data schema and the data that ends up in your schema belongs there, regardless of how its produced. That is a data design issue. Being able to roll forward and back with code and data (including an updated schema) that is synchronised and works is a huge win. In most places where I've worked with serious RDBMS systems developers have no access whatsoever to production. The fact that this isn't the case universally illustrates the strength of these RDBMS approaches.
- JCharante 3y ago
  why aren't stored procedures on a DB not able to be version controlled well? People already version control database schemas and migrations, why would stored procedures not also be able to be version controlled just as well? You can test it by creating shadow databases (what Prisma does) to make sure all migrations can be done on an empty database just fine, add test data, call the procedure, and check the output. Genuinely please do enlighten me as to why this isn't possible or doable, I would love to learn more about this part of the field.