3 ms·

In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have

by taffer 5y ago

In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have your stored procedures version controlled, tested, and deployed like any other code.

kbenson 5y ago
The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
- taffer 5y ago
  If your front-end is written in React and your business logic is written in SQL, is it really fair to call what's left in the middle tier a "core app language"? If you're writing a SaaS application today, you're more likely to want to rewrite your Java middle tier in Go than replacing your DBMS.
  - kbenson 5y ago
    Not everyone is making a web app, or even something amenable to using React native, and even if they are, there's no guarantee that their middle tier isn't also in JavaScript. That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have). My actual point is more along the lines of: - Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using. - The tooling for it is likely much worse or possibly non-existent. - You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point. - If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides. - I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
    - taffer 5y ago
      You make some good points. I personally have experience with business web applications, i.e. large complex data models with rather simple updates and report-like queries. These types of queries combined with a well normalized data model map well to set-based SQL. Of course, it's a different story if you're writing technical applications or games that are more about crunching numbers than querying and updating data. To me, the shitty procedural languages you mention are just for gluing queries together. The important stuff happens in SQL and the simplicity of keeping it all in the database is worth it.
- tshaddox 5y ago
  That seems to assume some very specific "scene" of developers, or at least a team environment where it's impossible to select for developers who know SQL. That may very well be the case in your situation, but I see no reason to expect it would be the case in most situations. SQL isn't exactly a new or niche language.
  - kbenson 5y ago
    I'm not talking about plain SQL, I'm talking about the included procedural language that often ships with it[1] as an extension and which is much more DBMS specific. So, the question is, does your team know SQL/PSM, or PL/SQL, or PL/pgSQL, or some other variant, and how well. 1: https://en.wikipedia.org/wiki/SQL#Procedural_extensions https://en.wikipedia.org/wiki/SQL#Procedural_extensions
    - tshaddox 5y ago
      My team would know the variant that we use, yes.

by taffer 5y ago

kbenson 5y ago

The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.

taffer 5y ago
If your front-end is written in React and your business logic is written in SQL, is it really fair to call what's left in the middle tier a "core app language"? If you're writing a SaaS application today, you're more likely to want to rewrite your Java middle tier in Go than replacing your DBMS.
- kbenson 5y ago
  Not everyone is making a web app, or even something amenable to using React native, and even if they are, there's no guarantee that their middle tier isn't also in JavaScript. That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have). My actual point is more along the lines of: - Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using. - The tooling for it is likely much worse or possibly non-existent. - You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point. - If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides. - I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
  - taffer 5y ago
    You make some good points. I personally have experience with business web applications, i.e. large complex data models with rather simple updates and report-like queries. These types of queries combined with a well normalized data model map well to set-based SQL. Of course, it's a different story if you're writing technical applications or games that are more about crunching numbers than querying and updating data. To me, the shitty procedural languages you mention are just for gluing queries together. The important stuff happens in SQL and the simplicity of keeping it all in the database is worth it.
tshaddox 5y ago
That seems to assume some very specific "scene" of developers, or at least a team environment where it's impossible to select for developers who know SQL. That may very well be the case in your situation, but I see no reason to expect it would be the case in most situations. SQL isn't exactly a new or niche language.
- kbenson 5y ago
  I'm not talking about plain SQL, I'm talking about the included procedural language that often ships with it[1] as an extension and which is much more DBMS specific. So, the question is, does your team know SQL/PSM, or PL/SQL, or PL/pgSQL, or some other variant, and how well. 1: https://en.wikipedia.org/wiki/SQL#Procedural_extensions https://en.wikipedia.org/wiki/SQL#Procedural_extensions
  - tshaddox 5y ago
    My team would know the variant that we use, yes.