5 ms·

jOOQ is a disaster and I would not recommend it to anyone. You write some SQL queries, test them in datagrip or whatnot, then spend the next several hours figu

jOOQ is a disaster and I would not recommend it to anyone. You write some SQL queries, test them in datagrip or whatnot, then spend the next several hours figuring out how to convert them to the DSL. This problem is compounded when you use "exotic" SQL features like json expressions. Debugging is "print the generated sql, copy it into datagrip/whatnot, tune the query, then figure out how to retrofit that back into the DSL". It's a huge waste of time. The primary selling point of jOOQ is "type safe queries". That became irrelevant when IntelliJ started validating SQL in strings in your code against the real data. The workflow of editing SQL and testing it directly against the database is just better. jOOQ reinforces the OP's point about DSLs.

geophile 2y ago
This is a very specific and popular subset of the DSL point: Let's just invent a language L that is better than horrible standard language X but translates to X. Imagine the vast cubicle farms of X programmers who will throw off their chains and flock to our better language L! In many scenarios (including JOOQ and all ORMs), X is SQL. I should know, I spent years working on a Java-based ORM. So believe me when I say: ORMs are terrible. To use SQL effectively, you have to understand how databases work at the physical level -- what's a B-tree lookup, what's a scan, how these combine, etc. etc. You can often rely on the optimizer to do a good job, but must also be able to figure out the physical picture when the optimizer (or DBA) got things wrong. You're using an ORM? To lift a phrase from another don't-do-this context: congratulations, you now have two problems. You now have to get the ORM to generate the SQL to do what really needs to be done. And then there are the generalizations of point made above: There are lots of tools that work with SQL. Lots of programmers who know SQL. Lots of careers that depend on SQL. Nobody gives a shit about your ORM just because it saves you the trouble of the easiest part of the data management problem.
- sroussey 2y ago
  > To use SQL effectively, you have to understand how databases work at the physical level -- what's a B-tree lookup, what's a scan, how these combine, etc. This is a good reason to use an ORM. But also, as a ORM designer, don’t let the ORM be flexible to do any SQL. Only let it do performant data access.
- stickfigure 2y ago
  This is an odd take. Your programming language works with objects, the data is in relational tables, you need software to map the relations to objects. Thus the Object Relational Mapper. There's no reason you can't write SQL and let an ORM handle the result set mapping.
  - sgarland 2y ago
    If that’s all you were doing, then maybe, but it never is. ORMs enable people who have no idea how RDBMS works to use them, which rarely ends well. I’m not suggesting that to use RDBMS you should know how to administrate and tune it (though it helps), but knowing their language, and understanding a single data structure (B+ trees) isn’t too much to ask, I think.
    - gedy 2y ago
      > ORMs enable people who have no idea how RDBMS works to use them, which rarely ends well. In some cases, but the more frequent issue I saw back in the day was the DBA making some really complex schema tuned for what they wanted, then an application trying to use the data in a pretty reasonable OOP manner (1 to many relationships, etc) and the DBA pissed they were using an ORM instead of their perfect SQL queries and procedures.
      - sgarland 2y ago
        > the DBA pissed they were using an ORM instead of their perfect SQL queries and procedures. Tbh, I don't understand why this is seen as a bad thing. Correction: I know why it is (any changes are obviously going to be dramatically slowed down), but in the long run, I don't understand why people are against it. You wanted something done correctly, so you went to the SME for that specific field, and had them do it for you. Then you decided to throw it away?! Why are you bothering to ask them in the first place? > 1 to many relationships, etc I know this was just an example, but 1:M is a perfectly natural part of any RDBMS, and in no way requires an ORM to be done.
        2y ago
- hot_gril 2y ago
  I used MySQL before I understood it at the physical level, and now I'm using some other ones that I don't really understand. A MySQL/Postgres noob can get pretty far just knowing to avoid seq scans. It's not ideal, but it'll work. Understanding schema design is more important. The thing is, ORMs encourage bad schema design and get in the way of the SQL you want. I've seen entire projects ruined this way. I think the only valid reason for an ORM was before RDBMSes had json etc types. Maybe you had a table with very many cols that you just want to get/set, say a "user profile" table. This also contributed to the NoSQL fad. Nowadays you can throw that into one json col.
hot_gril 2y ago
Yep, abstracting away SQL is a common and very costly mistake. The article is about more general system design, otherwise I would have expected to see that in the list.

geophile 2y ago

This is a very specific and popular subset of the DSL point: Let's just invent a language L that is better than horrible standard language X but translates to X. Imagine the vast cubicle farms of X programmers who will throw off their chains and flock to our better language L! In many scenarios (including JOOQ and all ORMs), X is SQL. I should know, I spent years working on a Java-based ORM. So believe me when I say: ORMs are terrible. To use SQL effectively, you have to understand how databases work at the physical level -- what's a B-tree lookup, what's a scan, how these combine, etc. etc. You can often rely on the optimizer to do a good job, but must also be able to figure out the physical picture when the optimizer (or DBA) got things wrong. You're using an ORM? To lift a phrase from another don't-do-this context: congratulations, you now have two problems. You now have to get the ORM to generate the SQL to do what really needs to be done. And then there are the generalizations of point made above: There are lots of tools that work with SQL. Lots of programmers who know SQL. Lots of careers that depend on SQL. Nobody gives a shit about your ORM just because it saves you the trouble of the easiest part of the data management problem.

sroussey 2y ago
> To use SQL effectively, you have to understand how databases work at the physical level -- what's a B-tree lookup, what's a scan, how these combine, etc. This is a good reason to use an ORM. But also, as a ORM designer, don’t let the ORM be flexible to do any SQL. Only let it do performant data access.
stickfigure 2y ago
This is an odd take. Your programming language works with objects, the data is in relational tables, you need software to map the relations to objects. Thus the Object Relational Mapper. There's no reason you can't write SQL and let an ORM handle the result set mapping.
- sgarland 2y ago
  If that’s all you were doing, then maybe, but it never is. ORMs enable people who have no idea how RDBMS works to use them, which rarely ends well. I’m not suggesting that to use RDBMS you should know how to administrate and tune it (though it helps), but knowing their language, and understanding a single data structure (B+ trees) isn’t too much to ask, I think.
  - gedy 2y ago
    > ORMs enable people who have no idea how RDBMS works to use them, which rarely ends well. In some cases, but the more frequent issue I saw back in the day was the DBA making some really complex schema tuned for what they wanted, then an application trying to use the data in a pretty reasonable OOP manner (1 to many relationships, etc) and the DBA pissed they were using an ORM instead of their perfect SQL queries and procedures.
    - sgarland 2y ago
      > the DBA pissed they were using an ORM instead of their perfect SQL queries and procedures. Tbh, I don't understand why this is seen as a bad thing. Correction: I know why it is (any changes are obviously going to be dramatically slowed down), but in the long run, I don't understand why people are against it. You wanted something done correctly, so you went to the SME for that specific field, and had them do it for you. Then you decided to throw it away?! Why are you bothering to ask them in the first place? > 1 to many relationships, etc I know this was just an example, but 1:M is a perfectly natural part of any RDBMS, and in no way requires an ORM to be done.
      - 2y ago
hot_gril 2y ago
I used MySQL before I understood it at the physical level, and now I'm using some other ones that I don't really understand. A MySQL/Postgres noob can get pretty far just knowing to avoid seq scans. It's not ideal, but it'll work. Understanding schema design is more important. The thing is, ORMs encourage bad schema design and get in the way of the SQL you want. I've seen entire projects ruined this way. I think the only valid reason for an ORM was before RDBMSes had json etc types. Maybe you had a table with very many cols that you just want to get/set, say a "user profile" table. This also contributed to the NoSQL fad. Nowadays you can throw that into one json col.

hot_gril 2y ago

Yep, abstracting away SQL is a common and very costly mistake. The article is about more general system design, otherwise I would have expected to see that in the list.