4 ms·

In many contexts the trade offs make using these features painful/dangerous/not worth it. It’s code that’s much harder to get visibility into, harder to track

In many contexts the trade offs make using these features painful/dangerous/not worth it. It’s code that’s much harder to get visibility into, harder to track over time, harder to reason about in the context of a larger codebase, and harder to measure and understand the performance implications of. And for most cases, all this downside with little/no upside. Sometimes you run into a situation where they’re clearly the best choice, but I would think long and hard about alternatives before embracing them (again, for most projects, not all).

convolvatron 5y ago
I certainly agree that its generally an awful idea in practice...would you agree that its potentially a really good idea in theory?
- sparker72678 5y ago
  If there’s anything I can agree on it’s that most things in software development are potentially really good ideas in theory and awful in practice.
KronisLV 5y ago
And yet, i once talked with a developer (who primarily worked just in procedural SQL), who claimed that having a separate back end oftentimes makes no sense whatsoever: the endless shuffling around of data, transforming it in the back end, especially for something like reports. While there is some merit to either end of the spectrum (e.g. not thinking about the schema or SQL much on the one end, letting the ORM do the heavy lifting, vs doing a large amount of in database processing), i've found that preferences in that regard largely depend on what any particular person is familiar with. Personally, i think that processing data within the database makes sense for large bulk operations, however otherwise databases are largely unsuitable for anything apart from being the data layer for CRUD, or analytic queries - whenever you try to create any sorts of complex algorithms in SQL, without fail you'll end up with code that isn't readable, isn't extensible and doesn't compose as well as even Java libraries would. Databases will also lose out on logging, debugging (e.g. breakpoints) and oftentimes scalability - it's easier to scale instances of a REST API horizontally than it is to scale a database engine, given that most of them scale vertically. Of course, Yugabyte or TiDB might try to be exceptions in regards to my last point, but the latter doesn't support stored procedures and triggers altogether, so scaling can have serious limitations: https://docs.pingcap.com/tidb/stable/mysql-compatibility https://docs.pingcap.com/tidb/stable/mysql-compatibility On the other hand, trying to do everything in the app is just asking for horrible schemas that have been automatically generated and aren't as well normalized as they should be, awkward framework specific ORM functionality and pitfalls (Hibernate lazy/eager init in particular), as well as having an insane amount of opportunities to run into the N+1 problem or issues with how linked data is represented and persisted. In summary, there are pitfalls with either approach and i'd just suggest that any team consider what they know how to use better and what better fits their needs. So far, in my experience, it's mostly doing apps within the application, not shying away from SQL when necessary, but also considering the very few use cases when using stored procedures or triggers indeed makes sense, and then looking for ways to comment them as much as possible. Testing them, however, is another story, which should reflect upon certain shortcomings: https://www.jetbrains.com/lp/devecosystem-2021/databases/#Databases_do-you-have-tests-in-your-database https://www.jetbrains.com/lp/devecosystem-2021/databases/#Da...
pjmlp 5y ago
There are source control tools for Oracle and SQL Server, debugging tools, code profilers, and no need to waste bandwidth with data. I would rather reach for them than the currently trendy ORM framework.
mamcx 5y ago
"Many" are not that much many. Refuse to use the full capabilities of a RDBMs is like refuse to use the full capabilities of a NoSQL. In fact, the ideal is to code all in a rdbms-first language. All this complexity is because codebases are split in multiple mismatched paradigms, langs and stacks.
throwaway81523 5y ago
I had an application where being able to write a customized aggregation function (like SUM, MAX, etc. but your own function) would have been a huge help, but unfortunately the database was an RDS MySQL instance where that is impossible. MySQL requires those functions to be written in C, which RDS doesn't allow (you can do it with self-hosted MySQL of course). There was once a version of MariaDB that allowed writing those functions in Javascript, which would have been fine, but I don't think it was ever released. Being able to write ordinary mathematical functions (UDF's, user defined functions) can also be a big help. I remember doing some completely crazy stuff to do those entirely with MySQL builtins, because again, loading C functions wasn't permitted in RDS.
deleted 5y ago
[deleted]
rvdginste 5y ago
> It’s code that’s much harder to get visibility into, harder to track over time, harder to reason about in the context of a larger codebase, and harder to measure and understand the performance implications of. I think this really depends on the developers and their tools. I've seen at least one system that is built on the database using hundreds of stored procedures and triggers, and that was still maintainable. The reason that it was maintainable was that there was a code and naming style that was used consistently throughout the project, that each trigger and stored procedure was documented, and that the developers had a good tool to maintain everything. Maybe in practice it's the exception, but good coding style can help just as it would help with code in a service. > And for most cases, all this downside with little/no upside. Well... obviously there is a very important upside and that is performance: you are executing code very close to the data and this can give you a big performance gain. If you execute business code on an application server, you have to first fetch the data from the database, then manipulate the data and then send the data back so that the database can store it. If you execute business code inside the database server, you don't need the round-trip (which goes typically over a network connection) and this can result in much better performance. Personally I never built a system that relies on triggers and stored procedures. I'm used to working with an ORM and implementing business logic in the service. I do rely on database features (different types of constraints) to protect data consistency as much as possible. Still, at some point I would love to build a system that has its business logic in the database just to get the experience and to really know how that works. I also wonder how performant the language integrations (which might be more expressive than the default) are that database systems sometimes offer.

convolvatron 5y ago

I certainly agree that its generally an awful idea in practice...would you agree that its potentially a really good idea in theory?

sparker72678 5y ago
If there’s anything I can agree on it’s that most things in software development are potentially really good ideas in theory and awful in practice.

KronisLV 5y ago

And yet, i once talked with a developer (who primarily worked just in procedural SQL), who claimed that having a separate back end oftentimes makes no sense whatsoever: the endless shuffling around of data, transforming it in the back end, especially for something like reports. While there is some merit to either end of the spectrum (e.g. not thinking about the schema or SQL much on the one end, letting the ORM do the heavy lifting, vs doing a large amount of in database processing), i've found that preferences in that regard largely depend on what any particular person is familiar with. Personally, i think that processing data within the database makes sense for large bulk operations, however otherwise databases are largely unsuitable for anything apart from being the data layer for CRUD, or analytic queries - whenever you try to create any sorts of complex algorithms in SQL, without fail you'll end up with code that isn't readable, isn't extensible and doesn't compose as well as even Java libraries would. Databases will also lose out on logging, debugging (e.g. breakpoints) and oftentimes scalability - it's easier to scale instances of a REST API horizontally than it is to scale a database engine, given that most of them scale vertically. Of course, Yugabyte or TiDB might try to be exceptions in regards to my last point, but the latter doesn't support stored procedures and triggers altogether, so scaling can have serious limitations: https://docs.pingcap.com/tidb/stable/mysql-compatibility https://docs.pingcap.com/tidb/stable/mysql-compatibility On the other hand, trying to do everything in the app is just asking for horrible schemas that have been automatically generated and aren't as well normalized as they should be, awkward framework specific ORM functionality and pitfalls (Hibernate lazy/eager init in particular), as well as having an insane amount of opportunities to run into the N+1 problem or issues with how linked data is represented and persisted. In summary, there are pitfalls with either approach and i'd just suggest that any team consider what they know how to use better and what better fits their needs. So far, in my experience, it's mostly doing apps within the application, not shying away from SQL when necessary, but also considering the very few use cases when using stored procedures or triggers indeed makes sense, and then looking for ways to comment them as much as possible. Testing them, however, is another story, which should reflect upon certain shortcomings: https://www.jetbrains.com/lp/devecosystem-2021/databases/#Databases_do-you-have-tests-in-your-database https://www.jetbrains.com/lp/devecosystem-2021/databases/#Da...

pjmlp 5y ago

There are source control tools for Oracle and SQL Server, debugging tools, code profilers, and no need to waste bandwidth with data. I would rather reach for them than the currently trendy ORM framework.

mamcx 5y ago

"Many" are not that much many. Refuse to use the full capabilities of a RDBMs is like refuse to use the full capabilities of a NoSQL. In fact, the ideal is to code all in a rdbms-first language. All this complexity is because codebases are split in multiple mismatched paradigms, langs and stacks.

throwaway81523 5y ago

I had an application where being able to write a customized aggregation function (like SUM, MAX, etc. but your own function) would have been a huge help, but unfortunately the database was an RDS MySQL instance where that is impossible. MySQL requires those functions to be written in C, which RDS doesn't allow (you can do it with self-hosted MySQL of course). There was once a version of MariaDB that allowed writing those functions in Javascript, which would have been fine, but I don't think it was ever released. Being able to write ordinary mathematical functions (UDF's, user defined functions) can also be a big help. I remember doing some completely crazy stuff to do those entirely with MySQL builtins, because again, loading C functions wasn't permitted in RDS.

deleted 5y ago

[deleted]

rvdginste 5y ago

> It’s code that’s much harder to get visibility into, harder to track over time, harder to reason about in the context of a larger codebase, and harder to measure and understand the performance implications of. I think this really depends on the developers and their tools. I've seen at least one system that is built on the database using hundreds of stored procedures and triggers, and that was still maintainable. The reason that it was maintainable was that there was a code and naming style that was used consistently throughout the project, that each trigger and stored procedure was documented, and that the developers had a good tool to maintain everything. Maybe in practice it's the exception, but good coding style can help just as it would help with code in a service. > And for most cases, all this downside with little/no upside. Well... obviously there is a very important upside and that is performance: you are executing code very close to the data and this can give you a big performance gain. If you execute business code on an application server, you have to first fetch the data from the database, then manipulate the data and then send the data back so that the database can store it. If you execute business code inside the database server, you don't need the round-trip (which goes typically over a network connection) and this can result in much better performance. Personally I never built a system that relies on triggers and stored procedures. I'm used to working with an ORM and implementing business logic in the service. I do rely on database features (different types of constraints) to protect data consistency as much as possible. Still, at some point I would love to build a system that has its business logic in the database just to get the experience and to really know how that works. I also wonder how performant the language integrations (which might be more expressive than the default) are that database systems sometimes offer.