
JOOQ: an alternative approach to traditional ORMs - alanfranz
http://www.jooq.org/
======
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](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](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.

~~~
pbowyer
> 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/](http://www.pomm-project.org/) ?

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

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

~~~
continuational
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'

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

~~~
critium
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...](https://db.apache.org/torque/torque-4.0/documentation/orm-
reference/running-the-generator.html)

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

~~~
lukaseder
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()) {

------
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/](http://www.jooq.org/download/)

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

------
ehartsuyker
Or you could use Slick[0] for free.

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

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

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

------
DasIch
This looks suspiciously similar to SQLAlchemy[1].

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

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

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

------
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](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](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](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...](https://github.com/agentgt/jirm/blob/master/jirm-
core/README.md) . 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).

~~~
vtman2002
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](https://github.com/salynch/DKO)) has make
only a dozen or so commits. SQL is not a fast-changing target.

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

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

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

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

------
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](http://blog.mybatis.org/?m=1)) and it
worked great.

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

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

------
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...](https://docs.spring.io/spring-
boot/docs/current/reference/html/boot-features-jooq.html)

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

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

------
guiye
This is another option: [http://ebean-orm.github.io/](http://ebean-
orm.github.io/) sound very nice and complete

