6 ms·

> There is always an alternative to writing SQL queries by hand, and it's usually a better one IME. I spent years writing code using Spring/Hibernate, and I ca

> There is always an alternative to writing SQL queries by hand, and it's usually a better one IME. I spent years writing code using Spring/Hibernate, and I can state with certainty that both of those statements are demonstrably false. Every application starts with good intentions, a simple CRUD webapp, and an ORM, then at some point the business requirements yield an N+1 problem in ORMs or several non-trivial left joins into records that don't map the shape of the entities. At that point it's far easier to write the query in straight SQL and produce a straightforward mapping into the record structure, which doesn't play well with the ORM because that bypasses its entity cache, which causes another huge set of problems on its own. So now not only is there ORM maintenance and SQL maintenance, there is now a problem with the conjunction of the two technologies.

caladin 4y ago
I agree but I think Hibernate is an extreme. There is a middleground, query builders, which enable dynamic query construction (e.g. dynamically appending filter predicates) with reduced cognitive load of something like Hibernate. I personally was heavily in the camp of "write raw queries ideally with code generation for statically typed/generated code" (as exist in Rust, Go, TypeScript, etc.), but I have since tempered my position since it does become a bit brittle and repetitive. Lately I've been playing with Jooq and it seems great. There are tradeoffs everywhere though, so with Jooq you still aren't 1-to-1 with raw SQL, there is a bit to learn, but I consider it a worthwhile investment (and a minor one relative to an actual ORM).
- int_19h 4y ago
  A pure query builder is just writing SQL on syntax tree level, more or less. This makes sense for the same reason why you want your macros to operate on ASTs and not raw text. But I would argue that it's still much closer to plain text SQL in the code than to any ORM.
  - rahimnathwani 4y ago
    Right, but it lives with your application code and has the same syntax as the application code. That's probably preferable to SQL stored procedures (which often live outside source control).
    - nicoburns 4y ago
      I’ve found that all this does is make the query less readable. SQL is purpose made for writing queries, and avoids unnecessary syntax noise you get when trying to fit the query into a host language based dsl.
      - int_19h 4y ago
        That really depends on the language - specifically, on whether it already has constructs that can map nicely (e.g. LINQ in C#), or macros to define them, or syntax that is generally amenable to DSLs even without macros in the picture (e.g. Lisps). SQL itself is also not a particularly well-designed query language. E.g. the order of the query doesn't reflect the natural data flow (SELECT .. FROM .. is reversed - compare to XQuery's FLWOR, for example), there are warts like WHERE vs HAVING etc. A good DSL can do much better.
        lukaseder 4y ago
        SQL is powerful. A DSL that "fixes" things in this area getting all the other language feature interactions right isn't trivial, all the while users have to learn yet another language. Take PRQL for example: https://prql-lang.org https://prql-lang.org. It looks nice, but the examples are very basic. What about window functions, grouping sets, lateral, DML, recursive SQL, pattern matching, pivot/unpivot etc. Might be doable, but perhaps, they've already made a decision that won't enable one of those features without adding new kludges. Besides, every single "fix" will be a proprietary solution, while SQL is an ISO/IEC standard that's here to stay and universally adopted. > A good DSL can do much better. Stonebraker's QUEL was "better", before SQL, and yet, where is QUEL today?
- lukaseder 4y ago
  > There are tradeoffs everywhere though, so with Jooq you still aren't 1-to-1 with raw SQL You're probably hinting at writing derived tables / CTEs? jOOQ will never keep you from writing views and table valued functions, though. It encourages you do so! Those objects play very well with code generation, and you can keep jOOQ for the dynamic parts, views/functions for the static parts.
lmm 4y ago
> I spent years writing code using Spring/Hibernate, and I can state with certainty that both of those statements are demonstrably false. > Every application starts with good intentions, a simple CRUD webapp, and an ORM, then at some point the business requirements yield an N+1 problem in ORMs or several non-trivial left joins into records that don't map the shape of the entities. At that point it's far easier to write the query in straight SQL and produce a straightforward mapping into the record structure, which doesn't play well with the ORM because that bypasses its entity cache, which causes another huge set of problems on its own. So now not only is there ORM maintenance and SQL maintenance, there is now a problem with the conjunction of the two technologies. I agree that that's often the end result, but in my experience 100% of cases are due to SQL fanboys who are unwilling to spend 5 minutes actually reading the ORM documentation and finding out how to do their N+1 query or complex join properly, which is actually easier than doing it in SQL if you try. And don't get me started on "Hibernate is slow. The entity cache? Oh, our unnecessary custom SQL query made that inconsistent so we've disabled all caching".
JohnBooty 4y ago
I agree with you, however, some ORMs handle this fairly elegantly. At that point it's far easier to write the query in straight SQL and produce a straightforward mapping into the record structure, which doesn't play well with the ORM because that bypasses its entity cache, which causes another huge set of problems on its own Rails' ActiveRecord ORM offers at least two ways to handle this. 1. ActiveRecord plays really nicely with views (including materialized views) in my experience. It treats them just like tables, basically, except you can't write to them. (note: there may actually be some cases where you can write to them; not sure) 2. You can supply your own handrolled SQL to ActiveRecord, e.g. `User.find_by_sql("select a,b,c from blahblahblah")` YMMV obviously but I've been working with Rails since 2014 but this has covered all of my performance needs. Plain old ActiveRecord default query generation is fine 99% of the time, and it's rather elegant/easy to sidestep it when I wish.

caladin 4y ago

I agree but I think Hibernate is an extreme. There is a middleground, query builders, which enable dynamic query construction (e.g. dynamically appending filter predicates) with reduced cognitive load of something like Hibernate. I personally was heavily in the camp of "write raw queries ideally with code generation for statically typed/generated code" (as exist in Rust, Go, TypeScript, etc.), but I have since tempered my position since it does become a bit brittle and repetitive. Lately I've been playing with Jooq and it seems great. There are tradeoffs everywhere though, so with Jooq you still aren't 1-to-1 with raw SQL, there is a bit to learn, but I consider it a worthwhile investment (and a minor one relative to an actual ORM).

int_19h 4y ago
A pure query builder is just writing SQL on syntax tree level, more or less. This makes sense for the same reason why you want your macros to operate on ASTs and not raw text. But I would argue that it's still much closer to plain text SQL in the code than to any ORM.
- rahimnathwani 4y ago
  Right, but it lives with your application code and has the same syntax as the application code. That's probably preferable to SQL stored procedures (which often live outside source control).
  - nicoburns 4y ago
    I’ve found that all this does is make the query less readable. SQL is purpose made for writing queries, and avoids unnecessary syntax noise you get when trying to fit the query into a host language based dsl.
    - int_19h 4y ago
      That really depends on the language - specifically, on whether it already has constructs that can map nicely (e.g. LINQ in C#), or macros to define them, or syntax that is generally amenable to DSLs even without macros in the picture (e.g. Lisps). SQL itself is also not a particularly well-designed query language. E.g. the order of the query doesn't reflect the natural data flow (SELECT .. FROM .. is reversed - compare to XQuery's FLWOR, for example), there are warts like WHERE vs HAVING etc. A good DSL can do much better.
      - lukaseder 4y ago
        SQL is powerful. A DSL that "fixes" things in this area getting all the other language feature interactions right isn't trivial, all the while users have to learn yet another language. Take PRQL for example: https://prql-lang.org https://prql-lang.org. It looks nice, but the examples are very basic. What about window functions, grouping sets, lateral, DML, recursive SQL, pattern matching, pivot/unpivot etc. Might be doable, but perhaps, they've already made a decision that won't enable one of those features without adding new kludges. Besides, every single "fix" will be a proprietary solution, while SQL is an ISO/IEC standard that's here to stay and universally adopted. > A good DSL can do much better. Stonebraker's QUEL was "better", before SQL, and yet, where is QUEL today?
lukaseder 4y ago
> There are tradeoffs everywhere though, so with Jooq you still aren't 1-to-1 with raw SQL You're probably hinting at writing derived tables / CTEs? jOOQ will never keep you from writing views and table valued functions, though. It encourages you do so! Those objects play very well with code generation, and you can keep jOOQ for the dynamic parts, views/functions for the static parts.

lmm 4y ago

> I spent years writing code using Spring/Hibernate, and I can state with certainty that both of those statements are demonstrably false. > Every application starts with good intentions, a simple CRUD webapp, and an ORM, then at some point the business requirements yield an N+1 problem in ORMs or several non-trivial left joins into records that don't map the shape of the entities. At that point it's far easier to write the query in straight SQL and produce a straightforward mapping into the record structure, which doesn't play well with the ORM because that bypasses its entity cache, which causes another huge set of problems on its own. So now not only is there ORM maintenance and SQL maintenance, there is now a problem with the conjunction of the two technologies. I agree that that's often the end result, but in my experience 100% of cases are due to SQL fanboys who are unwilling to spend 5 minutes actually reading the ORM documentation and finding out how to do their N+1 query or complex join properly, which is actually easier than doing it in SQL if you try. And don't get me started on "Hibernate is slow. The entity cache? Oh, our unnecessary custom SQL query made that inconsistent so we've disabled all caching".

JohnBooty 4y ago

I agree with you, however, some ORMs handle this fairly elegantly. At that point it's far easier to write the query in straight SQL and produce a straightforward mapping into the record structure, which doesn't play well with the ORM because that bypasses its entity cache, which causes another huge set of problems on its own Rails' ActiveRecord ORM offers at least two ways to handle this. 1. ActiveRecord plays really nicely with views (including materialized views) in my experience. It treats them just like tables, basically, except you can't write to them. (note: there may actually be some cases where you can write to them; not sure) 2. You can supply your own handrolled SQL to ActiveRecord, e.g. `User.find_by_sql("select a,b,c from blahblahblah")` YMMV obviously but I've been working with Rails since 2014 but this has covered all of my performance needs. Plain old ActiveRecord default query generation is fine 99% of the time, and it's rather elegant/easy to sidestep it when I wish.