JOOQ: an alternative approach to traditional ORMs | Hacker News Reader

JOOQ: an alternative approach to traditional ORMs

jooq.org

73 pointsalanfranz61 comments

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.

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

I mean, the JOOQ guys all seem to hate the hell out of LINQ for abstracting the query language across data stores.

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

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

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

Agreed.

I don't use PostgreSQL and haven't got my head around it fully, but have you seen http://www.pomm-project.org/ ?

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.

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.

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.

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

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.

If we had selected from or joined on BOOK, it would be a Boolean expression. But in this case, BOOK is undefined, so BOOK.LANGUAGE.eq("DE") shouldn't type check at all.

The corresponding SQL is nonsensical:

    select * from AUTHOR where BOOK.LANGUAGE = 'DE'

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

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.

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 :) )

That was a great steal. Thanks again for the idea, Adam! :)

Torque does code-gen as well. This was before any fancy tools so we basically just had hand generated DDL.

https://db.apache.org/torque/torque-4.0/documentation/orm-re...

Regen, then fix compile issues. Thats how I used it a long long (10 years?) time ago.

jOOQ DSL is nice, this is just as readable(ABC is a generated class)

  Criteria crit = new Criteria()
    .where(ABC.A, 1, Criteria.LESS_THAN)
    .and(ABC.B, 2, Criteria.GREATER_THAN)
    .or(ABC.A, 5, Criteria.GREATER_THAN);

Working with XML, not the nicest thing, i do agree, but also not the worst thing.

Not a flag-bearer in any way, but I just wanted to point out that if code-gen db access is your thing, there are also other tools to consider. It most certainly had its warts. Like issues handling complicated joins. Or more seriously, whos maintaining it. Its been a long time since i've look at it with any seriousness, not sure if they have fixed them or not.

edit: formatting

Here's a jOOQ example from the "PostgresDatabase" class, which is used to reverse-engineer the meta data. How would you write it in Torque?

    .select()
    .from(
         select(
            TABLES.TABLE_SCHEMA,
            TABLES.TABLE_NAME,
            TABLES.TABLE_NAME.as("specific_name"),
            inline(false).as("table_valued_function"),
            inline(false).as("materialized_view"),
            PG_DESCRIPTION.DESCRIPTION)
        .from(TABLES)
        .join(PG_NAMESPACE)
            .on(TABLES.TABLE_SCHEMA.eq(PG_NAMESPACE.NSPNAME))
        .join(PG_CLASS)
            .on(PG_CLASS.RELNAME.eq(TABLES.TABLE_NAME))
            .and(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE)))
        .leftOuterJoin(PG_DESCRIPTION)
            .on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS)))
            .and(PG_DESCRIPTION.OBJSUBID.eq(0))
        .where(TABLES.TABLE_SCHEMA.in(getInputSchemata()))
    
        // To stay on the safe side, if the INFORMATION_SCHEMA ever
        // includs materialised views, let's exclude them from here
        .and(row(TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME).notIn(
            select(
                PG_NAMESPACE.NSPNAME,
                PG_CLASS.RELNAME)
            .from(PG_CLASS)
            .join(PG_NAMESPACE)
                .on(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE)))
            .where(PG_CLASS.RELKIND.eq(inline("m")))
        ))
    
    // [#3254] Materialised views are reported only in PG_CLASS, not
    //         in INFORMATION_SCHEMA.TABLES
    .unionAll(
        select(
            PG_NAMESPACE.NSPNAME,
            PG_CLASS.RELNAME,
            PG_CLASS.RELNAME,
            inline(false).as("table_valued_function"),
            inline(true).as("materialized_view"),
            PG_DESCRIPTION.DESCRIPTION)
        .from(PG_CLASS)
        .join(PG_NAMESPACE)
            .on(PG_CLASS.RELNAMESPACE.eq(oid(PG_NAMESPACE)))
        .leftOuterJoin(PG_DESCRIPTION)
            .on(PG_DESCRIPTION.OBJOID.eq(oid(PG_CLASS)))
            .and(PG_DESCRIPTION.OBJSUBID.eq(0))
        .where(PG_NAMESPACE.NSPNAME.in(getInputSchemata()))
        .and(PG_CLASS.RELKIND.eq(inline("m"))))
    
    // [#3375] [#3376] Include table-valued functions in the set of tables
    .unionAll(
        tableValuedFunctions()
    
        ?   select(
                ROUTINES.ROUTINE_SCHEMA,
                ROUTINES.ROUTINE_NAME,
                ROUTINES.SPECIFIC_NAME,
                inline(true).as("table_valued_function"),
                inline(false).as("materialized_view"),
                inline(""))
            .from(ROUTINES)
            .join(PG_NAMESPACE).on(ROUTINES.SPECIFIC_SCHEMA.eq(PG_NAMESPACE.NSPNAME))
            .join(PG_PROC).on(PG_PROC.PRONAMESPACE.eq(oid(PG_NAMESPACE)))
                          .and(PG_PROC.PRONAME.concat("_").concat(oid(PG_PROC)).eq(ROUTINES.SPECIFIC_NAME))
            .where(ROUTINES.ROUTINE_SCHEMA.in(getInputSchemata()))
            .and(PG_PROC.PRORETSET)
    
        :   empty)
    .asTable("tables"))
    .orderBy(1, 2)
    .fetch()) {

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.

That's not quite correct.

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

And if you sold your soul (I mean infrastructure) to oracle already, jOOQ is the least of your worries!

Also supporting Oracle is a huge PITA. So charging for that is completely understandable :)

Exactly, if you use an Open Source database (e.g. MariaDB or PostgreSQL) you'll be OK with the Open Source version of jOOQ.

It is "free" if you squint, but they kind of screw with you when you don't pay them, to the point where I ditched jOOQ entirely because I didn't feel like I could trust them to deal straight with me. They spam your application output like nagware unless you dig into the code for an undocumented flag (which has a really godawful guilt-trip comment next to it begging you to allow them to continue littering your logs with unparseable garbage that can't even be bothered to go through logback to be piped correctly) and they hide their unit tests (and are real jerks about them, calling them "an enterprise feature", when in reality they're both documentation and verification that the stuff they say works actually does work). It's their prerogative to do that, of course, but it's not behavior I'd support. Team's kind of shitty on Twitter, too, trolling around keyword searches and looking for a fight.

I don't recommend its use, both from a "can I trust this" perspective as well as a "do I want to support these guys" one. If you can use Slick, I recommend it, as its developers have in my experience been uniformly solid people; I haven't needed either recently, as I've switched stacks for the project I was going to use one or the other for, but Slick's developers don't make me feel icky to support.

How do you explain this to management?

If you're already paying for a database (MS, Oracle, etc) then you're paying for another tool to work with that database. If you're using an open source database, you're using an open source tool.

Not sure there's all that much to explain…?

Or you could use Slick[0] for free.

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

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

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.

You don't use Slick's code generator? How big is your schema, then?

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

This looks suspiciously similar to SQLAlchemy[1].

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

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

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

It's just my first impression. The experience when using it may feel very different.

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.

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

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

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

Massive and continual support? That has not been my experience. Even after leaving TS, it looks like Lynch (who took over internal support: https://github.com/salynch/DKO) has make only a dozen or so commits. SQL is not a fast-changing target.

This is because you consider DKO to be "equivalent" :)

Fair enough. I don't know enough about jOOq to claim equivalency to your baby. I just wrote DKO to scratch an itch. (That itch being Hibernate!) Fully supportive of the general pattern, and glad you're free now for open source databases. In hindsight, perhaps I should have sought out VC funding. :)

VC don't care that much about middleware anymore...

Well with out extensively using your library I can only guess a couples of things must be true for not needing maintenance:

* Your library has a very limited set of capabilities

* Your library is not extensively tested on all supported databases all the time and thus bugs are not being found.

Also I find the claim of streaming support sort of disingenuous or at least the word streaming misleading. Real streaming that is required for reactive like programming (ie reactive-streams) is not supported by the JDBC drivers because operations are bound to thread/connection. If you are just talking about Iterator like streaming than you do realize you are at the mercy of the database driver itself. For example Postgres and many other databases will almost always preload a certain amount of the ResultSet regardless (and in my experience a rather large ammount).

Correct, iterator-based-streaming, as java6 was the norm when I wrote this. And yes, you're obviously at the mercy of your JDBC driver. Though I'd argue if your results are fitting into PostgreSQL's default preload size (whatever it may be) you don't really need to worry about it. But I promise you it does stream. (TS is not known for it's small datasets...)

I think you understand this but its more than just handling large datasets memory friendly. True streaming would allow you to keep your connection pool happy. If you stream a large dataset you have to keep the connection open (lets say for a web request which is the common case) and you can't reuse that connection till you retrieve the entire dataset (or I guess read a subset and stop). For batch processing this ok but for web requests this is generally not ok. Asynchronous drivers are push based and are analogous to NIO HTTP like Netty (some non-JDBC async drivers I think even use Netty). But I'm going to gather you understand that and/or either using a very sophisticated pooling technique/drivers.

So if I block too long while reading an iterator like object because the client is taking to long to read... I think you can imagine what happens. This is why so many of the JDBC wrappers (such as Spring JDBC and JDBI ) do not return iterators or at least do not advertise it as an awesome feature.

Our pooling was pretty shitty actually, but it didn't need to be fancy as 95% of our code was some sort of batch processing (as you guessed), after which the JVM terminated. But yes, highly tweaked JDBC drivers over all.

Hibernate has iterator methods, but I recall (in 2010) it still loaded the entire result set into memory, with a //TODO comment. I remember thinking "W...T...F..." I can't tell you how many -Xmx16G (or 32/64) flags I deleted...

> Its an extremely well maintained and documented library.

I find its documentation coverage to be good but its quality to be lacking, being mostly written by non-native English speakers. Which is fine, if there are good examples and tests to use--but jOOQ actually deleted unit tests from the open-source release of the library (declaring them "an enterprise feature") to remove the easiest and best way to investigate the library, as well as the only unambiguous documentation the project has. ("Ask a question on our Google Group" was suggested with a straight face. Because in 2015, it's better to wait on an email than look at code, I guess.)

that is shady. bit it looks like they're still there in the git history: https://github.com/jOOQ/jOOQ/tree/fc7afb40760246153da4a2334c...

even the stuff that has been xxxed out: https://github.com/jOOQ/jOOQ/blob/63717b517fccf475089648ca3c... and it's still under the APL.

Yup. I ended up using the last version of them I could when I had to use jOOQ, but I didn't feel at all comfortable endorsing the behavior going forward.

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.

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

No, it's essentially a way to automatically map between manually written SQL and custom object types; you can use MyBatis with annotations only; no XML required.

The advantage is that, as you're writing and embedding actual SQL, you can take advantage of any database-vendor-specific extensions to SQL, plus you can easily run, debug and test such SQL from another tool. Having full control over the actual SQL also means you don't risk potentially horribly inefficiently generated SQL, or risk running into unexpected N+1 scenarios. At the same time, you don't have to deal with JDBC and instead get a DAO layer that uses strongly typed objects.

Of course the disadvantage is that you lose portability; moving for instance a MyBatis/SqlServer app to Oracle will mean verifying, testing and potentially rewriting every single SQL statement in your app. If you're willing to accept that risk though, MyBatis is a great lightweight library to manage your database access layer.

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

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

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

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

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

This cannot be repeated enough. It is an argument I have over and over with people who want to treat the database as a dumb store usually because they do not want to understand databases. Any successful software that stores or generates data will see that data live and used way beyond the original program. The database used will absolutely be the foundation multiple different pieces of software are built on.

10x this! I always get a shiver when I hear people saying "... and it just generates the DB for you...".

DB is what I always start with and it shall stay so.

BTW, is there some jOOQ equivalent for .NET? EntityFramework 7 is all about code-first, which I really don't like.

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.

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

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

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

Reply on news.ycombinator.com