Hacker News Reader

Comment by stickfigure | Hacker News Reader

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.

View on HN

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.

> 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.

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.

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.

> 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.

> 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.

> Then you decided to throw it away?! Why are you bothering to ask them in the first place?

Usually this was a mismatch of mgmt or expectations. Hiring old school DBAs and letting them think they "own the data", while plopping them into a huge dev team changing the big SaaS features daily is a recipe for trouble.

I don't fault DBAs per se, though I did work with some who wouldn't look outside their blinders at all.

Of course. And after you understand SQL and databases, ORMs can save you a lot of typing. I've never understood the either/or attitude.

Sometimes it can. Other times, you have the SQL already written in your head, but then you have to figure out how to coerce the ORM to doing what you want.

Even Hibernate has `em.createNativeQuery("type your sql here", SomeResult.class)`. I've never seen an ORM (for an RDBMS) that didn't make it easy to run SQL.

Then what's the point of using Hibernate? Just use the ODBM driver... why are you dragging the gorilla and all of the jungle with you if all you wanted was a banana?

Since we're talking about Hibernate, I assume you mean the JDBC driver? Because the API is tedious and unpleasant.

The mapping of database results to java objects with Hibernate is convenient. The basic "load entity, change a couple fields, let Hibernate persist it" flow is convenient. In a limited set of cases, basic entity graph navigation is convenient.

As I said, if you're working in an object-based language, by definition you need something that maps relations to objects. Hibernate is a competent choice. There are other competent choices, but JDBC is not one of them unless your app is trivial.

Yeah, I confused multiple acronyms here :)

Anyways. Hibernate works on top of JDBC, so, if you like its interface, then it means you could make your own, but skipping >99% of the rest of Hibernate code that has nothing to do with wrapping the driver.

Or, imagine there was a library Hibernate', that threw away all the ORM stuff, and only offered mapping of SQL results to Java objects and sending queries to the database. Then, why not use Hibernate' instead of Hibernate?

NB. About triviality. From experience: trivial apps tend to work OK with ORM. Non-trivial will usually ditch the ORM because of performance, missing functionality and general difficulty with servicing it. So, it's the other way around: if you are shooting for the stars, you are probably not going to use Hibernate, Hibernate is one of the variety of tools that helps losers loose less, it's not a tool for the winners.

What you've said makes no sense. The "ORM stuff" is what I want, the Object Relational Mapping. Taking relational data and converting it back and forth to objects. And Hibernate is actually pretty good at this.

I think you've built up a strawman in you mind of what you think "ORM" is. Yes Hibernate is huge and has a lot of features that people shouldn't use. But you can say the same about Microsoft Word, the problem is that everyone uses a different 5% of the huge feature set.

People who work with these technologies on a daily basis don't screw up the core acronyms. I suggest softening your opinion and dropping the platitudes.

It's clear that you want ORM. But you didn't explain why you want it. For all I know, you like to suffer, and that's why you want it, but you've made no compelling argument for people who don't like to suffer to use ORM.

BTW. I'm absolutely on-board with you: nobody should use Microsoft Word. There's absolutely no reason to do that. It's a marketing ploy with a lot of grease money paid to people in charge of procurement in various places. It's absolutely not about 5% of features. It's just downright worst kind of text editor that's in popular use today. Ask me how I know this? I worked in a newspaper! Somehow, Microsoft never ventured into this field, and didn't sell their garbage there. And nobody uses Microsoft in book publishing or any other sort of publishing. Not for any % of its features. So much so that if you bring a manuscript (as an outside author) to publish a book or an article in a newspaper / magazine, and it will be in MS Word format, you'll be most likely asked to convert it to another format. And we are talking about people who need a lot of different features of text editing!

And, I really don't care about what you have to suggest. You aren't in a position to make suggestions really ;)

Heh. Nice. I like your zinger.

My go to metaphor has been "XYZ is an angry 800lb gorilla sitting between you and your work."

Nah. That's an inconsequential part of the interaction between the database and the application. The reality is that your code has both, the database and the application. And if you want to write good software, you need to know how both work and be an expert at that.

It's infinitely easier and less error-prone to keep the interface between the database and the application to the minimum (just convert the final results of a query to the application objects and embed complete queries in the application code) than to try and create complex query builders behind the scenes of object-to-object interaction.

If you want to make a good product, you may start with ORM, as it may, for a time, delay the need of understanding the relationship between the application and the database, and allow you to experiment faster at the expense of lost performance. Once you know what you need to do, ORM just no longer works: you will have to break it at least in order to deal with performance issues, but often you will also find yourself dealing with the fact that a lot of what you want to express in your queries is either too difficult or even impossible to express in a particular ORM.

> you need software to map the relations to objects

If you start with a network data model perspective and build that into your system, then it follows that you'll want a network data model to SQL mapper. That's what ORMs are, and the need for them comes from your approach, not from the tools you use.

There's a different approach - use OOP to build computational abstractions rather than model data. Use it to decompose the solution rather than model the problem. Have objects that talk to the database, exchange sets of facts between it and themselves, and process sets of facts. In the process, you can also start viewing data relationally - as n-ary relations over sets of values - as opposed to binary relationships between tables of records.

Information systems are not domain simulations, simulations compute the future state of the domain whereas information systems derive facts from known facts at the present time.

For a visual metaphor, car engineers don't use roadmaps as design diagrams and they don't model the problem domain in the systems they build. A car isn't built from streets, turns, road signs, traffic lights, etc. And despite that, cars function perfectly well in the problem domain. A car generally doesn't need to be refactored and reassembled when roads or rules change.

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.

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.

Reply on news.ycombinator.com