A Typesafety Comparison of SQL Access APIs

Posted on by lukaseder
SQL is a very expressive and distinct language. It is one of the few declarative languages which are used by a broad audience in everyday work. As a declarative language, SQL allows to specify what we’re expecting as output, not how this output should be produced. As a side-effect of this, ad-hoc record data types are created by every statement. An example: 

-- A (id: integer, title: varchar) type is created
SELECT id, title
FROM book;
The above statement generates a cursor whose records have a well-defined record type with these properties:
  • The degree of the record is 2
  • The column names are id and title
  • The column types are integer and varchar
  • The column id can be accessed at index 1. The column title can be accessed at index 2
In other words, SQL records combine features from records (access by name) and tuples (access by index). They can be seen like typesafe associative “map-arrays”, where map keys are formally bound to array indexes and their associated key/index type. Another, more complex example shows how these ad-hoc record types can be reused within a SQL statement! 

-- A (c: bigint) type is created
SELECT count(*) c
FROM book

-- A (..: integer, ..: integer) type is created and compared with...
WHERE (author_id, language_id) IN (

  -- ... another, compatible (..: integer, ..: integer) type
  SELECT a.id, a.language_id
  FROM author a
)
This query counts books written by authors in their native language. In the above example, the projected record type is a bit simpler. It contains only one column. The interesting part is the row value expression IN comparison predicate, which compares two compatible (integer, integer) types. In SQL, you can typesafely create ad-hoc record types and immediately compare them with other ad-hoc record types. In these comparisons, column names are not important, but column indexes (and associated column types) are.

Comparing various SQL access APIs

The previous examples show how SQL allows for the formal declaration of record types including record degree, column names, column indexes, column types. While SQL is very expressive in that matter, many client languages accessing SQL are less expressive. When comparing expressiveness and typesafety, two features should be taken into consideration:
  1. Are the records produced into the client language typesafe?
  2. Are the SQL statements produced from the client language typesafe and syntax-safe?
Let’s have a look at various accessing techniques, and how expressive they are in terms of the above typesafety requirements: JDBC: Least typesafety JDBC offers the least expressiveness and typesafety. This isn’t surprising, as JDBC is a very low-level API. It offers:
  1. No typesafety whatsoever when accessing result records.
  2. No typesafety or syntax-safety whatsoever when producing SQL statements.
Here is an example: 

PreparedStatement stmt = null;
ResultSet rs = null;

try {

  // SQL statements are just strings. Constructing them is not
  // typesafe or syntax-safe
  stmt = connection.prepareStatement(
    "SELECT id, title FROM book WHERE id = ?");

  // Bind values are set by index. There is no typesafety or
  // "index safety"
  stmt.setInt(1, 15);

  rs = stmt.executeQuery();
  while (rs.next()) {

    // There is no typesafety or "index safety" when accessing
    // result record values
    System.out.println(
      "ID: " + rs.getInt(1) + ", TITLE: " + rs.getString(2));
  }
}
finally {
  closeSafely(stmt, rs);
}
Now, this wasn’t surprising. JDBC makes up for the lack of typesafety by being absolutely general. It is possible to implement a JDBC driver for any type of relational database, no matter what kinds of SQL and JDBC features they really support. JPA: Some typesafety JPA has implemented quite a bit of typesafety mostly on top of JPQL, but also slightly on top of SQL. With JPA, you can have:
  1. Some typesafety when accessing records.
  2. Some typesafety and syntax-safety when producing JPQL statements through the CriteriaQuery API (not SQL statements).
Record access typesafety can be guaranteed when you project the outcome of your statements onto your JPA-annotated entities. While the mapping itself isn’t really typesafe, the outcome is, as a Java class is the closest match to a SQL record. A Java class, much like a SQL record, has:
  • A degree, expressed in the number of properties
  • Column names, expressed as property names
  • Column types, expressed as property types
  • But: No column indexes. Properties have no explicit order
JPA record mapping has additional features that exceed the expressiveness of SQL, as “flat”, tabular result sets can be mapped onto object hierarchies. In any case, you will have to create one record / entity type per query to profit from this typesafety. If you’re not projecting all columns from every table, but ad-hoc records (including values derived from functions), you will lose this typesafety again. When it comes to statement typesafety, JPA offers the CriteriaQuery API to produce typesafe JPQL statements. The CriteriaQuery API is often criticised for its verboseness and for the fact that resulting client code is hard to read. Here is an example taken from the CriteriaQuery API docs: 

CriteriaQuery<String> q = cb.createQuery(String.class);
Root<Order> order = q.from(Order.class);
q.select(order.get("shippingAddress").<String>get("state"));
 
CriteriaQuery<Product> q2 = cb.createQuery(Product.class);
q2.select(q2.from(Order.class)
            .join("items")
            .<Item,Product>join("product"));
It can be seen that there is only a limited amount of typesafety in the above query construction:
  • Columns are accessed by string literals, such as "shippingAddress".
  • Generic entity types are not really checked. The <Item,Product> generic parameters might as well be wrong.
Of course, there are more typesafe API parts in JPA’s CriteriaQuery API. Using those API parts quickly lead to the aforementioned verbosity, though, as can be seen in this Stack Overflow question, or in the Java EE 6 Tutorials. LINQ: Much typesafety (in .NET) LINQ goes very far in offering typesafety in both dimensions:
  1. Much typesafety when accessing records or tuples.
  2. Much typesafety when producing LINQ-to-SQL statements (not SQL statements).
As LINQ is formally integrated into various .NET languages, it has the advantage of being able to produce formally defined record types, directly into the target language (e.g. C#). Not only can typesafe records be produced, the LINQ-to-SQL statement is formally verified by the compiler as well. An example 

// Typesafe renaming (aliasing with "AS" in SQL)
From p In db.Products
// Typesafe (named!) variable binding
Where p.UnitsInStock <= ReorderLevel AndAlso Not p.Discontinued
// The typesafe projection will produce a Products record
Select p
Another example from Stack Overflow can be seen here: 

// Producing a C# tuple
var r = from u in db.Users
        join s in db.Staffs on u.Id equals s.UserId
        select new Tuple<User, Staff>(u, s);

// Producing an anonymous record type
var r = from u in db.Users
    select new { u.Name, 
                 u.Address,
                 ...,
                 (from s in db.Staffs 
                  select s.Password where u.Id == s.UserId) 
               };
LINQ has many obvious advantages when it comes to typesafety. In the case of LINQ, this comes at the price of losing actual SQL expressivity and syntax, as LINQ-to-SQL is not really SQL (just as JPQL is not really SQL either). The SQL querying API is partially shared with other, heterogeneous querying targets, such as LINQ-to-Entities, LINQ-to-Collections, LINQ-to-XML. This will reduce LINQ’s feature scope (see also a previous blog post, and I will soon blog about this again). But C# offers all typesafety aspects that a SQL record offers as well: degree, column name (anonymous types), column index (tuples), column types (both types and tuples). SLICK: Much typesafety (in Scala) SLICK has been inspired by LINQ, and can thus offer a lot of typesafety as well. It offers:
  1. Much typesafety when accessing tuples (not records).
  2. Much typesafety when producing SLICK statements (not SQL statements).
SLICK takes advantage of Scala’s integrated tuple expressions. This is best shown by example: 

// "for" is the "entry-point" to the DSL
val q = for {

    // FROM clause   WHERE clause
    c <- Coffees     if c.supID === 101

// SELECT clause and projection to a tuple
} yield (c.name, c.price)
The above example shows that the projection onto a (String, Int) tuple is done typesafely by the yield method. At the same time, the whole query expression is formally validated by the compiler, as SLICK makes heavy use of Scala’s language features in order to introduce an internal DSL for querying. Much more than LINQ, SLICK has a unique syntax that doesn’t remind of SQL any more. It is not obvious how subqueries, complex joins, grouping and aggregation can be expressed. jOOQ: Much typesafety jOOQ is mainly inspired by SQL itself and embraces all the features that SQL offers. It has thus:
  1. Much typesafety when accessing records or tuples.
  2. Much typesafety when producing SQL statements.
jOOQ offers similar capabilities as JPA when it comes to mapping SQL result sets onto records, although JPA’s mapping type hierarchies are not supported by jOOQ. But jOOQ also allows for typesafe tuple access, the way SLICK has implemented it. Ad-hoc records produced by arbitrary query projections will maintain their various column types through generic Record1<T1>, Record2<T1, T2>, Record3<T1, T2, T3>, … record types. Unlike in Java, this can be leveraged extensively in Scala, where these typesafe Record[N] types can be used just like Scala’s tuples. On the other hand, just like LINQ-to-SQL, which has formally integrated querying as a first-class citizen into .NET languages, jOOQ allows for heavy type-checking and syntax-checking, when writing SQL statements in Java. In SQL, you can typesafely write things like: 

SELECT * FROM t WHERE (t.a, t.b) = (1, 2)
SELECT * FROM t WHERE (t.a, t.b) OVERLAPS (date1, date2)
SELECT * FROM t WHERE (t.a, t.b) IN (SELECT x, y FROM t2)
UPDATE t SET (a, b) = (SELECT x, y FROM t2 WHERE ...)
INSERT INTO t (a, b) VALUES (1, 2)
In jOOQ 3.0, you can (also typesafely!) write 

select().from(t).where(row(t.a, t.b).eq(1, 2));
// Type-check here: ----------------->  ^^^^
 
select().from(t).where(row(t.a, t.b).overlaps(date1, date2));
// Type-check here: ------------------------> ^^^^^^^^^^^^
 
select().from(t).where(row(t.a, t.b).in(select(t2.x, t2.y).from(t2)));
// Type-check here: -------------------------> ^^^^^^^^^^
 
update(t).set(row(t.a, t.b), select(t2.x, t2.y).where(...));
// Type-check here: --------------> ^^^^^^^^^^

insertInto(t, t.a, t.b).values(1, 2);
// Type-check here: ---------> ^^^^
This also applies for existing API, which doesn’t involve row value expressions: 

select().from(t).where(t.a.eq(select(t2.x).from(t2));
// Type-check here: ---------------> ^^^^
 
select().from(t).where(t.a.eq(any(select(t2.x).from(t2)));
// Type-check here: -------------------> ^^^^
 
select().from(t).where(t.a.in(select(t2.x).from(t2));
// Type-check here: ---------------> ^^^^

select(t1.a, t1.b).from(t1).union(select(t2.a, t2.b).from(t2));
// Type-check here: -------------------> ^^^^^^^^^^
jOOQ is not SQL, but unlike other attempts of introducing SQL as an internal domain-specific language into host languages like Java, Scala, C#, jOOQ looks very much like SQL thanks to its unique fluent API technique, which informally follows an underlying BNF notation. Even if Java offers less expressiveness than other languages like C# or Scala, jOOQ probably comes closest to both result record typesafety and SQL syntax safety in the Java world.

Published by lukaseder

I made jOOQ

10 thoughts on “A Typesafety Comparison of SQL Access APIs”

  1. Hello,

    Nice to have a comparison. However, some examples aren’t right.

    I’ll speak about JPA, which is the technology I know.

    Had you really used the generated metamodel, which is the equivalent of what you did in your JOOQ example, you could have written your example as this:

    // some query
CriteriaQuery q = cb.createQuery(String.class);
Root order = q.from(Order.class);
q.select(order.get(Order_.shippingAddress).get(Address_.state));
  
// second query
CriteriaQuery q2 = cb.createQuery(Product.class);
q2.select(q2.from(Order.class)
            .join(Order_.items)
            .join(Item_.product));

    (I really don’t like the naming of variables, but that’s another story)

    The second query makes no sense, as joining is defined in the model. This has exactly the same effect:

    // second query
CriteriaQuery q2 = cb.createQuery(Product.class);
q2.select(q2.from(Order.class));

    If you want to compare type safety, here is an equivalent example to what you wrote in JOOQ:

    // JOOQ:
select().from(t).where(row(t.a, t.b).eq(1, 2));
// Type-check here: ----------------->  ^^^^
  
// JPA:
query.select(t);
query.where(criteriaBuilder.equal(t.get(T_.a), 1), criteriaBuilder.equal(t.get(T_.a), 2));

    … which isn’t type safe. Wait, why is this? There are some reasons, see comments / answer from TheConstructor of https://stackoverflow.com/questions/12062038/jpa-criteria-api-and-type-safety for some insight. Debatable, I’ll admit.

    Single updates are quite type safe:

    t.setA(1);

    (That’s really all that’s needed for a managed entity with container-provided EntityManager).

    Type safety increases much whith relations, which JOOQ doesn’t handle as such.

    The linked SO example ( https://stackoverflow.com/q/3424696/521799 ) isn’t an honest example. It talks about dynamically adding relations, which is verbose. Relations are normally modelized as such, and querying, navigating etc. doesn’t need all of this. I don’t think it’s relevant for a comparison, as JOOQ doesn’t really handle them.

    Reply

    1. Hi Yannick, thanks for joining!

      Had you really used the generated metamodel, which is the equivalent of what you did in your JOOQ example, you could have written your example as this

      You’re right, that would have been better. On the other hand:

      order.get(Order_.shippingAddress).get(Address_.state)

      This could certainly be improved on an API level, I imagine? Of course, jOOQ also offers dereferncing columns from tables (or attributes from entities if you like) dynamically without a formal compile-time check if the column is really contained in the table. But why would you want to default to that? Why not offer something like:

      order.shippingAddress.state

      … or at least:

      order.shippingAddress().state()

      Thanks for linking to the example from TheConstructor on https://stackoverflow.com/questions/12062038/jpa-criteria-api-and-type-safety. I won’t let that count :) The moment you start using IN instead of EQ just as a workaround because an API is flawed is the moment where you start questioning all of the API. Me, at least. It may still work and get the job done, of course.

      I don’t think it’s relevant for a comparison, as JOOQ doesn’t really handle them.

      I can see your point, but the article really doesn’t aim for comparing the tools per se. Obviously, jOOQ and JPA serve entirely different purposes and one API solves problems that the other doesn’t.

      Nonetheless, JPQL is not THAT different from SQL, syntactically, so the type safe JPQL embedded DSL, which Criteria Query aspires to be, could’ve been designed much much better than it was.

      So, the article compares the quality of type safe, embedded query DSL implementations, and given our experience, I really believe that Criteria Query is a major failure as it could’ve been designed much better.

      Reply

      1. Hi Lukas,

        Why not offer something like:
        order.shippingAddress.state
        … or at least:
        order.shippingAddress().state()

        “order” is a Path (I’d rather call the variable orderRoot, btw). That’s a concrete path in a concrete query, whereas Order_.shippingAddress is an Attribute, which describes some field from Order.

        Should there be a simpler API that allows navigating from a concrete property to another? At first, this looks simpler. Some libraries exist to do things like that, but I know from experience that it will make more complex things more complicated than they are now. Personnally, I don’t mind this “verbosity”.

        If there is a path for improvement, it could be suggested to the JPA expert committee. I know for sure they are listening to this kind of stuff right now.

        Anyway, from a type safety point of view, there is no real difference between JPA and JOOQ on this aspect. I think it would be more correct to state this in your article.

        Thanks for linking to the example from TheConstructor on https://stackoverflow.com/questions/12062038/jpa-criteria-api-and-type-safety. I won’t let that count :) The moment you start using IN instead of EQ just as a workaround because an API is flawed is the moment where you start questioning all of the API.

        He also mentions that you can just write one static method for that. True, that’s ugly and those are workarounds. But the real fact is that “=” isn’t that type safe in SQL. Now, there would sure be room for both a loose and a strict equal in the API. Just count on me to suggest this one as a new JPA feature.

        Really, this is an arguable point which isn’t representative of the whole API. Guess I could find similar arguable points in JOOQ. Both JPA and JOOQ have room for improvement, I guess.

        I can see your point, but the article really doesn’t aim for comparing the tools per se. Obviously, jOOQ and JPA serve entirely different purposes and one API solves problems that the other doesn’t.
        Nonetheless, JPQL is not THAT different from SQL, syntactically, so the type safe JPQL embedded DSL, which Criteria Query aspires to be, could’ve been designed much much better than it was.
        So, the article compares the quality of type safe, embedded query DSL implementations, and given our experience, I really believe that Criteria Query is a major failure as it could’ve been designed much better.

        Criteria API is another API, which doesn’t necessarily translate to JPQL (and doesn’t in EclipseLink, at least). So, it doesn’t “aspire to be a type safe DSL to JPQL”.

        Both before and after reading your article, I’m convinced that JPA and JOOQ have similar type-safety levels. Somehow, this article isn’t quite honest or correct regarding this. Again, there is room for improvement and I guess that’s an opportunity to take ;-)

        Reply

        1. (I hope you don’t mind my editing. Unfortunately, WordPress can’t handle markdown)

          but I know from experience that it will make more complex things more complicated than they are now

          I’m very curious about that experience. Did you write that down in a blog post, somewhere? Would be a very interesting read, e.g. as a response to this post.

          it could be suggested to the JPA expert committee

          It should be, but not from me. I’m way too far away from real-world JPA experience to fully understand all of the existing API – which would be required to make qualified feature requests.

          Anyway, from a type safety point of view, there is no real difference between JPA and JOOQ on this aspect. I think it would be more correct to state this in your article.

          Let’s just agree to disagree :)

          But the real fact is that “=” isn’t that type safe in SQL.

          That’s true, but both JPA and jOOQ may choose to ignore that flaw in SQL and just enforce type safety. I don’t remember the last time that type promotion / coercion on equals operators was lauded as an achievement in any language. Take, for instance, JavaScript…

          But I agree that we’re now discussing details in areas where any API has room for improvement.

          Criteria API is another API, which doesn’t necessarily translate to JPQL (and doesn’t in EclipseLink, at least). So, it doesn’t “aspire to be a type safe DSL to JPQL”.

          That’s interesting, so I based many arguments on a false assumption. What does it aspire to be, then? And why would there exist several competing mini-languages in an API? (Given that the whole bunch of annotations form yet another, semi-independent mini-language)

          Again, there is room for improvement and I guess that’s an opportunity to take

          I will certainly wish you luck with that endeavour! :)

          Reply

          1. (I hope you don’t mind my editing. Unfortunately, WordPress can’t handle markdown)

            No Problem. I’ll try with blockquote, wondering if it works.

            I’m very curious about that experience. Did you write that down in a blog post, somewhere? Would be a very interesting read, e.g. as a response to this post.

            Not yet, but I have an article in preparation where it could fit. I can’t give any example from my head, but I’ll definitely give it another thought. I did a lot of complex, mixed queries where the API definitely made sense and I couldn’t think of a simpler alternative at the time.

            Anyway, I’d investigate a variation of your suggestion:

            (from custom generated classes, whatever the final name will be)

            Order__.root(query).getShippingAddress().getState();

            But checking some real code, the improvement is quite limited: a lot of time, we just get a property (e.g. equal() ) and do something with it rather than getting a sub-sub-property.

            Worth checking anyway.

            Let’s just agree to disagree :)

            Fine with that, but I’m still interested in any argument really comparing JPA and JOOQ which would prove that one of them is more type safe. Didn’t see that yet.

            That’s interesting, so I based many arguments on a false assumption. What does it aspire to be, then? And why would there exist several competing mini-languages in an API? (Given that the whole bunch of annotations form yet another, semi-independent mini-language)

            Quoting http://docs.oracle.com/javaee/6/tutorial/doc/gjitv.html – “The Criteria API is used to define queries for entities and their persistent state by creating query-defining objects.”

            To me, JPQL is history. Some people seem to prefer it. I prefer type safety, and my applications haven’t had any JPQL for years.

            The annotations are complementary, not a language in themselves. They are needed for some things that aren’t defined in queries: primary key, relations, converters, … Some of them are used by JOOQ too, it seems. Other (converters) are defined in xml. Annotations vs xml has been debated enough for years, it’s ;-)

            Reply

Leave a Reply to ymajorosCancel reply