JOOQ: an alternative approach to traditional ORMs

[solutionyogi]

Never heard of this before as I mainly use MS stack.

However, my first impression is very positive.

1. Database First

Great way to distinguish your product from traditional ORMs. I am personally not a fan of code first approach especially as a LOB applications developer. I have been writing code for 15 years and the database/schema has outlived each and every application that I wrote. (I am in full agreement with lukaseder's comment: https://news.ycombinator.com/item?id=10880942) As an experienced developer, they immediately made their value proposition clear to me and I wanted to learn more.

2. Examples

Side by side example comparing jOOQ to SQL. A great way for me to quickly see if I like their DSL design.

3. Convince your manager page.

I absolutely loved this: http://www.jooq.org/why-jOOQ.pdf

We all have worked with 'a technical manager' who isn't really technical. When you need to purchase a tool, you need to convince your manager and this page is as good as I have seen. All commercial software development tool product websites should feature a 'Convince your manager' page.

[lawpoop]

> Side by side example comparing jOOQ to SQL. A great way for me to quickly see if I like their DSL design.

I'm always astounded how many times this is neglected. It seems pandemic.

I live in the PHP world, and it seems that every hot new framework wants to do its own SQL re-hash. They always seem created by programmers who either hate SQL and want to avoid it as much as possible in favor of OO paradigms, or have never used it beyond its basic features.

Sooner or later, I'll find myself wanting to do something moderately complex, like delete through a join, and all of a sudden the interface breaks down. I'll scour the internet or ask a question, and it either can't be done, or relies on some arcane, poorly documented part of the API, or uses weird, unintuitive syntax that makes you wonder if the API is any improvement over plain SQL at all.

[habitue]

I came across JooQ when looking for LINQ in Java. I believe that's the original inspiration

[moron4hire]

On your point #1, I personally believe that the correct solution is to treat application code as yet another target for your schema state, and all issues of deployment are just synchronization between two different states. So it's "database first" if you make the database the master state in the sync, or "code first" if you make the C# or Java or whatever code the master state in a particular sync. It doesn't ever have to be set in stone (like EF does). Indeed, there is a certain rapidity of iteration early in the project if you can go back and forth, using different tools at different stages that might edit code, might edit schema, and just be able to sync the two when you're done.

[hiram112]

>We all have worked with 'a technical manager' who isn't really technical. When you need to purchase a tool, you need to convince your manager and this page is as good as I have seen. All commercial software development tool product websites should feature a 'Convince your manager' page.

Agreed. The electrical metaphors and accompanying images are marketing gold!

[ahachete]

I have been using jOOQ for quite some time. Indeed, it's the only mechanism (definitely not an ORM) to access the database that we use in my company.

From my own experience, and this is general consensus, it's a big win to use it. Its SQL inspired fluent syntax brings you back the power of SQL, including really advanced stuff. But at the same time, in a Java native manner, type safe, by the way.

If you haven't tried it yet, you definitely should.

[matwood]

Same. jOOQ is great and makes dealing with databases from Java land simple.

After dealing with some many hibernate WTF issues jOOQ was a breath of fresh air. I could finally write queries again using a simple DSL and get what I was expecting to happen.

[continuational]

> type safe SQL queries

Really? So the compiler will catch this error?

    create.select().from(AUTHOR).where(BOOK.LANGUAGE.eq("DE"))

No it won't, because `where` has this type:

    SelectConditionStep<R> where(Field<Boolean> field)

If it was type safe, `Field<Boolean>` would have to mention the type of the records that can be queried.

Type safety is what is being sold on the front page as the main value proposition.

Needless to say, I'm not sold on it.

[lukaseder]

Your criticism is certainly valid. The SQL language can be type checked using Java types only to a certain degree. There is no way to type check SQL thoroughly without an actual parser, though. For example, your query would be fine again (although a bit pointless) as a subquery:

    create.select()
          .from(BOOK)
          .where(exists(
              create.select().from(AUTHOR).where(BOOK.LANGUAGE.eq("DE"))
          ))

Other frameworks may have jumped to the conclusion that predicates should be strictly tied to whatever is placed in the FROM clause. This is possible only if you completely limit the scope of what your framework can do (namely CRUD).

[HCIdivision17]

I'm not sure I understand; doesn't

  BOOK.LANGUAGE.eq("DE")

mean something like

  Book.Language = 'DE'

in SQL? That's certainly a boolean expression. I've only started reading the manual, though, so it's possible I got the semantics wrong, but I've had SSMS point out to me this sort of thing before.

[critium]

Sorry if I did too much TL;DR but this reads very much like Apache Torque, which has been around for a very loooooooong time.

https://db.apache.org/torque/torque-4.0/index.html

Apache Torque is an object-relational mapper for java. In other words, Torque lets you access and manipulate data in a relational database using java objects. Unlike most other object-relational mappers, Torque does not use reflection to access user-provided classes, but it generates the necessary classes (including the Data Objects) from an XML schema describing the database layout. The XML file can either be written by hand or a starting point can be generated from an existing database. The XML schema can also be used to generate and execute a SQL script which creates all the tables in the database.

As Torque hides database-specific implementation details, Torque makes an application independent of a specific database if no exotic features of the database are used.

Usage of code generation eases the customization of the database layer, as you can override the generated methods and thus easily change their behavior. A modularized template structure allows inclusion of your own code generation templates during the code generation process.

[agentgt]

They are not the same and probably many modern ORM's share more in common with jOOQ than Torgue has in common with jOOQ. An example would be schema generation. jOOQ is reverse schema based. You make your schema with whatever schema evolution tools you like (Flyway is the most common) and then run the code generator. Torque is the opposite (notice they say starting point not final) not to mention its also XML based.

Oh and jOOQ has an extremely powerful DSL that is close to 1-1 with SQL and whole bunch of reflection based conversion abilities (including dotted path data binding which I am happy to say I inspired the author of jOOQ to add :) )

[levosmetalo]

Looked interesting until the moment I saw it's not free. Sorry, but in this day and year I'm not gonna consider using a piece of basic infrastructure that is not free and open source, at least not for something as fundamental as ORM layer.

[morgo]

That's not quite correct.

It is free if your database is free (using Apache 2.0): http://www.jooq.org/download/

[lukaseder]

How do you explain this to management?

[ehartsuyker]

Or you could use Slick[0] for free.

[0]: http://slick.typesafe.com/

[tokenizerrr]

JOOQ is completely free to use for Open Source databases. Here is a list: http://www.jooq.org/legal/licensing#databases

[Ciantic]

I think this is not fully comparable, since it's Scala library.

I tried to use JOOQ with Scala in past, it does not feel good solution for it, all that generated code seemed really difficult to keep in order.

JOOQ is clearly Java tool first and foremost.

[cwyers]

I am not sure that a Scala FRM is a one-for-one replacement for a Java-based ORM.

[DasIch]

This looks suspiciously similar to SQLAlchemy[1].

[1]: http://www.sqlalchemy.org/

[ZitchDog]

That is a gigantic compliment, given the restrictions of Java.

[devy]

Syntactically similar but in fact SQLAlchemy is the Hibernate for Python (because it adopts the data mapper pattern.)

[kimi]

A simple library that is similar (but not multi-database; this said, at least you don't have to learn another syntax for SQL) https://github.com/l3nz/ObjectiveSync

- Minimal wrapper over JDBC.

- Querying done in SQL. You should not be afraid of SQL. If you are, you should not be doing anything above the trivial CRUD.

- Centralizing object marshaling and unmarshaling - each object should know how to sync itself and its descendents

- Single syntax for inserting and updating

- Ruby-like objectivized JDBC fetching with exception handling

- User-definable deep fetching and updating (almost Hibernate-like).

- Batch API to avoid round-trips when submitting multiple queries.

- Stats collection and similar stuff.

[vtman2002]

A fully open source equivalent: https://github.com/keredson/DKO

I wrote this for Two Sigma's internal use back in 2010.

[agentgt]

I too wrote my own crappy (anti)ORM-like library a few years back as well https://github.com/agentgt/jirm . The most notably difference from other libraries was focusing on immutable objects and embracing real SQL (not a DSL like jOOQ) using a better SQL template language (instead of the JDBC '?') https://github.com/agentgt/jirm/blob/master/jirm-core/README... . I guess the closest library would be MyBatis.

The problem with writing your own library is the massive and continuous support that is required. This is where I eventually gave in and used jOOQ. Its an extremely well maintained and documented library. People complain about it not being completely opensource but neither is intellij so I don't have a problem (as well I also only use opensource databases).

[matdrewin]

Not too sure what's special about this thing. I also wonder why people keep making DSLs to replace SQL. I was using iBatis back in 2005 (now called MyBatis - http://blog.mybatis.org/?m=1) and it worked great.

[lukaseder]

... which is, essentially, an XML-based external DSL

[ssijak]

Does it "feel" natural to use it with spring boot instead of jpa and hibernate?

[lukaseder]

It depends on the nature of your project. Using JPA as a default is probably a bit of a historic issue. For a lot of time (2005-2010), Hibernate has been the de-facto standard way to access RDBMS from Java, unrightfully so in many cases that would have really fared much better with a SQL-based solution, not an ORM-based one. I'm talking about reporting, analytics, complex queries (outside of reporting), data-centric middle ware, batch processing, i.e. everything that isn't plain old CRUD.

While Spring also often defaults to JPA, it isn't required at all to use JPA with Spring. Spring has always included JdbcTemplate, a JDBC extension for convenience when working with plain SQL.

Apart from that, jOOQ is part of the Spring Boot manual. It cannot be feel that unnatural :)

https://docs.spring.io/spring-boot/docs/current/reference/ht...

[mediumdave]

> jOOQ is SQL-centric. Your database comes "first".

This approach has always seemed completely backwards to me. Isn't the database simply a mechanism for persisting records/objects whose structure is determined by domain modeling?

To me, it would make about as much to say of a GUI framework "foobarWidgets is GUI-centric. Your UI comes 'first'.", as though the application itself was just an afterthought.

Don't get me wrong - I like SQL, and I happily use relational databases to store objects. I just see the database as a means, not an end.

[lukaseder]

Your data will live for the next 30 years. Your UIs are replaced with every new fad. Do you think it is more important to thoroughly design your database or your client domain model?

Of course, projects are different, and some projects are more user-centric, others are more data-centric, but chances are that you're successful and then you'll regret working with a horrible database schema that you didn't properly design 5 years ago, cause all you cared for were your fancy foobarWidgets that you implemented in a tech that no longer exists...

[Pamar]

"Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious." -- Fred Brooks

At least in the realm of business applications (i.e. not scientific apps, or games, etc.) the most common approach is to keep data (including data dictionaries and db structures) at the center.

[HelloNurse]

In many enterprise contexts, shared databases of very important data are more valuable, more long-lived and more managed than easily replaced applications; applications, often strange and ad hoc ones, are the means applied to the end of keeping databases current and useful for business. Altering old tables in backwards-incompatible ways isn't normally an option.

If you treat a database as "a mechanism for persisting records/objects whose structure is determined by domain modeling" your database will be messy and redundant.

[vtman2002]

> Isn't the database simply a mechanism for persisting records/objects whose structure is determined by domain modeling?

not at all. you're thinking of a file system.

[guiye]

This is another option: http://ebean-orm.github.io/ sound very nice and complete