4 ms·

On the other hand, there are good reasons to put logic in the database, chiefly: - Performance - Consistency - Security Basically, you want to construct a p

On the other hand, there are good reasons to put logic in the database, chiefly: - Performance - Consistency - Security Basically, you want to construct a public API in the form of stored procedures, functions and views, and then funnel all clients through that API, by granting execute/select permissions only on the artifacts which are part of the public API. That can be good for performance not just because computation is close to data so you don’t pay the communication cost, but also because it opens optimization opportunities that might be impractical on the client, e.g. by returning several interdependent resultsets at once, or by cleverly using temporary tables or cursors. It also ensures that a rogue client can’t circumvent the consistency rules implemented by your API. If you care about your data, this is a big one! And finally, the API may be a natural place for performing authorization in case your security needs cannot be readily met by the database’s built-in security. — Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s. For the most part, they’re just pain to work with. But enduring that pain might very well be worth the end result.

jerf 3y ago
"Performance" Performance is my primary reason for using stored procedures. The thing that almost every high-performance app eventually runs up against is sheer query quantity. No matter how good you are with doing set operations rather than operating on one value at a time, the time inevitably comes when you need to do a query from your app, do something to the result, and generate the next query, and repeat this for an arbitrary number of times. If you can move that process to the database, you can get performance wins so massive your peers won't believe such wins were even possible. "Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s." Postgres is chipping away at this at least. It's still a mess, but every time I leave for a couple of years and come back, the game has been upped. I don't know exactly how long it's been in there, but as a for-instance, Postgres stored procedures have print debugging now, which I described in a Go context here but should apply to any other language that has a good Postgres-specific driver: https://www.reddit.com/r/golang/comments/12k6c6m/persistent_data_with_golang/jg3tf0c/?context=3 https://www.reddit.com/r/golang/comments/12k6c6m/persistent_... That's just a night & day change. I can live with print-debugging even if I don't get a true debugger, but working without even print debugging is a little too 1960s for my tastes. There's also been a lot of improvement in being able to intermix arrays, tables, and other such things with each other, which used to be a major hassle, though it's still pretty chaotic in terms of naming schemes and such.
- Capricorn2481 3y ago
  Can you test any of this logic?
  - jerf 3y ago
    Testing code is exactly where I'm doing the print debugging from. I'm sure not doing it on production unless I have no choice! I don't take a dogmatic view of testing. There is no requirement that test code in X must be run in a test harness in X. There is no problem testing my database procedures with code being driven from my primary app language. What matters is that cases are covered, and that the desired outcomes are verified. My database code is perfectly well covered with testing. If yours isn't, that's a process problem on your end and is your problem, not mine.
    - Capricorn2481 3y ago
      I wasn't accusing you of anything, I was genuinely asking. Do you mean that your testing logic is done in the code? I don't really know what that has to do with print debugging. Are you reading output from the database and running assertions on that?
  - branko_d 3y ago
    For what it’s worth, we implement tests in the client language (C# in our case) and just call the stored procedures/views (in SQL Server) as any client would. Most tests are written so that they rollback after execution, and to tolerate any residual data that might have been left after the tests that must commit. A very small number of tests require a clean database, which we accomplish by traversing INFORMATION_SCHEMA and just emptying all the tables (we also temporarily disable constraints and triggers). These tests are just normal “unit” tests from the perspective of Visual Studio, which brings one enormous benefit: you can put a breakpoint in T-SQL code and just right-click on the test and choose Debug, and the breakpoint will be hit! That’s possible because Visual Studio debugger can switch from C# to T-SQL seamlessly. This is a fantastic way to debug in exactly the right test case which exposes the bug or behavior that you want to test.
yrashk 3y ago
I agree that SQL and flavors of PL/SQL have a 1980s vibe attached to them (well, they were born in those times!), but even small adjustments can make them feel better: for example, dropping the TYPING IN CAPS tradition. Queries like `select this from that`, in my opinion, feel different than `SELECT this FROM that`, and we can often use color to distinguish keywords from names. --- More importantly, performance seems much better if you don't have to have a middleman between your data and the user. I wrote a bit about this earlier this year https://yrashk.com/blog/2023/02/16/what-happens-if-you-put-http-server-inside-postgres/ https://yrashk.com/blog/2023/02/16/what-happens-if-you-put-h... I am also very excited about what you can do with row and column-level security in databases like Postgres.

jerf 3y ago

"Performance" Performance is my primary reason for using stored procedures. The thing that almost every high-performance app eventually runs up against is sheer query quantity. No matter how good you are with doing set operations rather than operating on one value at a time, the time inevitably comes when you need to do a query from your app, do something to the result, and generate the next query, and repeat this for an arbitrary number of times. If you can move that process to the database, you can get performance wins so massive your peers won't believe such wins were even possible. "Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s." Postgres is chipping away at this at least. It's still a mess, but every time I leave for a couple of years and come back, the game has been upped. I don't know exactly how long it's been in there, but as a for-instance, Postgres stored procedures have print debugging now, which I described in a Go context here but should apply to any other language that has a good Postgres-specific driver: https://www.reddit.com/r/golang/comments/12k6c6m/persistent_data_with_golang/jg3tf0c/?context=3 https://www.reddit.com/r/golang/comments/12k6c6m/persistent_... That's just a night & day change. I can live with print-debugging even if I don't get a true debugger, but working without even print debugging is a little too 1960s for my tastes. There's also been a lot of improvement in being able to intermix arrays, tables, and other such things with each other, which used to be a major hassle, though it's still pretty chaotic in terms of naming schemes and such.

Capricorn2481 3y ago
Can you test any of this logic?
- jerf 3y ago
  Testing code is exactly where I'm doing the print debugging from. I'm sure not doing it on production unless I have no choice! I don't take a dogmatic view of testing. There is no requirement that test code in X must be run in a test harness in X. There is no problem testing my database procedures with code being driven from my primary app language. What matters is that cases are covered, and that the desired outcomes are verified. My database code is perfectly well covered with testing. If yours isn't, that's a process problem on your end and is your problem, not mine.
  - Capricorn2481 3y ago
    I wasn't accusing you of anything, I was genuinely asking. Do you mean that your testing logic is done in the code? I don't really know what that has to do with print debugging. Are you reading output from the database and running assertions on that?
- branko_d 3y ago
  For what it’s worth, we implement tests in the client language (C# in our case) and just call the stored procedures/views (in SQL Server) as any client would. Most tests are written so that they rollback after execution, and to tolerate any residual data that might have been left after the tests that must commit. A very small number of tests require a clean database, which we accomplish by traversing INFORMATION_SCHEMA and just emptying all the tables (we also temporarily disable constraints and triggers). These tests are just normal “unit” tests from the perspective of Visual Studio, which brings one enormous benefit: you can put a breakpoint in T-SQL code and just right-click on the test and choose Debug, and the breakpoint will be hit! That’s possible because Visual Studio debugger can switch from C# to T-SQL seamlessly. This is a fantastic way to debug in exactly the right test case which exposes the bug or behavior that you want to test.

yrashk 3y ago

I agree that SQL and flavors of PL/SQL have a 1980s vibe attached to them (well, they were born in those times!), but even small adjustments can make them feel better: for example, dropping the TYPING IN CAPS tradition. Queries like `select this from that`, in my opinion, feel different than `SELECT this FROM that`, and we can often use color to distinguish keywords from names. --- More importantly, performance seems much better if you don't have to have a middleman between your data and the user. I wrote a bit about this earlier this year https://yrashk.com/blog/2023/02/16/what-happens-if-you-put-http-server-inside-postgres/ https://yrashk.com/blog/2023/02/16/what-happens-if-you-put-h... I am also very excited about what you can do with row and column-level security in databases like Postgres.