Altering SELECT .. FROM Into FROM .. SELECT Does Not “Repair” SQL – Java, SQL and jOOQ.

From time to time, I see of us lament the SQL syntax’s peculiar disconnect between

Most lately right here in a Youtube remark reply to a latest jOOQ/kotlin speak. Let’s have a look at why jOOQ didn’t fall into this entice of attempting to “repair” this, and why that is even a entice.

The English Language

SQL has a easy syntax mannequin. All instructions begin with a verb in crucial kind, as we “command” the database to execute a press release. Frequent instructions embody:

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• TRUNCATE
• CREATE
• ALTER
• DROP

All of those are verbs in crucial kind. Take into consideration including an exclamation mark in every single place, e.g. INSERT [this record]!

The Order of Operations

We are able to argue that pure languages are very poor inspiration for laptop programming languages, which are typically extra mathematical (some greater than others). A number of criticism in regards to the SQL language is that it doesn’t “compose” (in its native kind).

We are able to argue, that it might be a lot better for a extra composable SQL language to begin with FROM, which is the primary operation in SELECT based on the logical order of operations. E.g.

FROM e-book
WHERE e-book.title LIKE 'A%'
SELECT e-book.id, e-book.title

Sure, that might be higher within the sense that it might be extra logical. First, we declare the information supply, predicates, and many others. and solely in the long run would we declare the projection. With the Java Stream API, we’d write:

books.stream()
     .filter(e-book -> e-book.title.startsWith("A"))
     .map(e-book -> new B(e-book.id, e-book.title))

The advantages of this might be:

• No disconnect between syntax and logic
• Therefore: No confusion round syntax, specifially why you may’t reference SELECT aliases in WHERE, for instance.
• Higher auto-completion (since you don’t write stuff that isn’t declared but, first)

In a method, this ordering can be in keeping with what some RDBMS carried out when RETURNING knowledge from DML statements, akin to:

INSERT INTO e-book (id, title)
VALUES (3, 'The E-book')
RETURNING id, created_at

With DML statements, the command (“crucial”) continues to be INSERT, UPDATE, DELETE, i.e. a verb that clearly tells the database what to do with the information. The “projection” is extra of an afterthought. A utility that’s often helpful, therefore RETURNING will be positioned on the finish.

RETURNING looks like a practical selection of syntax, and isn’t even a part of the usual. The usual defines the <knowledge change delta desk>, as carried out by Db2 and H2, whose syntax is:

SELECT id, created_at
FROM FINAL TABLE (
  INSERT INTO e-book (id, title)
  VALUES (3, 'The E-book')
) AS e-book

I imply, why not. I don’t have a robust choice for one or the opposite syntax (jOOQ helps each and emulates them into each other). SQL Server invented a 3rd variant, whose syntax might be the least intuitive (I all the time should search for the precise location of the OUTPUT clause):

INSERT INTO e-book (id, title)
OUTPUT id, created_at
VALUES (3, 'The E-book')

Cypher question language

Most likely price mentioning right here is that there exists a contemporary question language on the market that’s sufficiently in style to be thought of for such discussions: The Cypher Question Language from neo4j. With a easy “trick”, it each:

• Maintained the language mannequin the place a verb in crucial kind begins a press release (the verb is MATCH, which is analogous to FROM, however it’s a verb), so it inherits SQL’s “energy” of being intuitive additionally for non-programmers.
• Reversed the logical order of operations inside the studying statements, to be of the shape MATCH .. RETURN, making RETURN the common type of projecting issues for all operations, not simply SELECT.
• Reused MATCH additionally for writing operations, together with DELETE or SET (which corresponds to SQL’s UPDATE)

Whereas working on a unique knowledge paradigm (the community mannequin versus the relational mannequin), I’ve all the time discovered the Cypher Question Language to be typically superior to SQL by way of syntax, a minimum of on a excessive degree. If I needed to really “repair” SQL by creating SQL 2.0, I’d take inspiration right here.

Fixing this in an API like jOOQ isn’t price it

As mentioned earlier than, SQL has some apparent shortcomings, and there exist higher languages like Cypher fixing the identical form of downside. However SQL is right here, and it’s 50 years outdated, and it’ll keep. It received’t be mounted.

That’s one thing that simply needs to be accepted:

SQL received’t be mounted

Will probably be amended. It incorporates new concepts, together with:

It all the time does so in an idiomatic, SQL fashion method. When you’re studying the SQL commonplace, or in case you’re working with PostgreSQL, which may be very near the usual, you’ll really feel that SQL is sort of constant as a language. Or, it’s persistently bizarre, relying in your tastes.

For jOOQ, one of many foremost success elements has all the time been to be as shut as doable to this imaginative and prescient of what SQL actually is by way of syntax. A number of of us are very efficient writing native SQL. Since Java has textual content blocks, it has develop into much more bearable to simply copy paste a static SQL question out of your SQL editor into your Java program, and e.g. execute it with JDBC or with jOOQ’s plain SQL templating API:

for (Document document : ctx.fetch(
    """
    SELECT id, title
    FROM e-book
    WHERE title LIKE 'A%'
    """
)) {
    System.out.println(document);
}

This strategy is enough for very easy functions on the market. In case your “software” runs a complete of 5 distinct SQL queries, you are able to do it with JDBC alone (though, when you’ve began to get a dangle of jOOQ, you’ll in all probability use jOOQ even for these functions as nicely).

However jOOQ actually shines when your software has 100s of queries, together with many dynamic ones, and your database has 100s of tables, in case of which the sort security and mannequin security advantages actually assist. Nonetheless, it may possibly shine solely when your SQL question interprets 1:1 to the jOOQ API. Randomly fixing SQL to some extent on this most essential assertion (SELECT) received’t do the trick.

As a result of: The place will you cease fixing SQL? SQL continues to be bizarre even in case you swap to FROM .. SELECT. For instance, the semantics of GROUP BY continues to be bizarre. Or the connection between DISTINCT and ORDER BY. E.g. this might look like a lot better at first (e.g. to separate SELECT and DISTINCT, which shouldn’t be positioned so intently collectively):

FROM e-book
WHERE e-book.title LIKE 'A%'
SELECT e-book.title
DISTINCT
ORDER BY e-book.title

However the bizarre caveats would nonetheless not disappear, specifically you can ORDER BY expressions that aren’t listed in SELECT within the absence of DISTINCT, however not within the presence of DISTINCT (see our earlier article about that).

Different syntaxes in different DSL APIs

So, the place does the “fixing” of SQL cease? When will SQL be “mounted?” It’ll by no means be mounted, and as such, an API like jOOQ can be a lot more durable to be taught that it must be. Some competing APIs comply with this mannequin, e.g.

Each of those APIs are primarily based on the concept that SQL wants “fixing,” and {that a} extra “native,” a extra “idiomatic” really feel of the API can be considerably higher. Some examples:

Slick:

Right here’s an instance from the getting began information:

This corresponds to the next SQL:

SELECT max(value)
FROM coffees

It’s arguably a bit extra idiomatic. It appears to be like like peculiar Scala assortment API utilization, eradicating the SQL really feel from the equation. In any case, the standard map(x => y) assortment strategies actually correspond to a SQL SELECT clause (a “projection”).

Uncovered:

Right here’s an instance from Baeldung:

StarWarsFilms
  .slice(StarWarsFilms.sequelId.rely(), StarWarsFilms.director)
  .selectAll()
  .groupBy(StarWarsFilms.director)

The API introduces new phrases, e.g.

• slice which suggests the identical factor as map() or SELECT, although overseas to each SQL or kotlin assortment APIs
• selectAll, which corresponds to the relational algebra time period “choice”, akin to SQL WHERE

Artificial comfort syntax as a substitute of “fixing” SQL

jOOQ doesn’t comply with down this street and by no means will. SQL is what it’s, and jOOQ received’t have the ability to “repair” that. The 1:1 mapping between SQL syntax and jOOQ API implies that even if you wish to use one thing subtle, like:

Even then, jOOQ received’t allow you to down and can help you write precisely what you take note of by way of SQL characteristic. I imply, wouldn’t it actually make sense to assist CONNECT BY in Slick or Uncovered? Most likely not. They must invent their very own syntax to provide entry to SQL recursion. However will or not it’s full? That’s an issue jOOQ received’t have.

The one motive why some syntax isn’t accessible is as a result of it’s not doable but (and please do ship a characteristic request). The instance of FOR XML is a wonderful one. SQL Server invented this FOR clause, and whereas it’s handy for easy circumstances, it’s not very highly effective for complicated ones. I a lot desire commonplace SQL/XML and SQL/JSON syntax, (which jOOQ additionally helps). However whereas I don’t very very similar to the syntax, jOOQ received’t choose. What good would a 3rd syntax, solely invented by jOOQ be for customers? As I stated earlier than.

When will the “fixing” cease?

It’ll by no means cease. The alternate options I’ve talked about will run into very tough questions down the road once they begin including extra options, if they begin including extra options. Whereas it’s all the time straightforward to implement a easy SELECT .. FROM .. WHERE question builder, and assist that performance utilizing arbitrary API, claiming SQL has been “mounted,” it’s a lot more durable to evolve this API, addressing all types of superior SQL use-cases. Simply have a look at their concern trackers for characteristic requests like CTEs. The reply is all the time: “Use native SQL.”

Even “easy” SQL options, akin to UNION develop into extra complicated as soon as primary SQL syntax is modified. The semantics is already tough sufficient in SQL (and it’s solely SQL’s fault, certain), however “fixing” these items is rarely so simple as it might have a look at first.

Now, there are 2 exceptions to this rule:

Artificial syntax

One exception is: “Artificial syntax.” Probably the most highly effective artificial syntax in jOOQ are implicit joins. Implicit joins aren’t “fixing” SQL, they’re “enhancing” SQL with a syntax that SQL itself might need (hopefully may have, finally). Identical to there exist SQL dialects, which “improve” the SQL commonplace, e.g.

jOOQ may be very conservative about such artificial syntax. There are a number of good concepts, however few are ahead appropriate. Every certainly one of these syntaxes makes different SQL transformation options extra complicated, and each has flaws that will not have been addressed but (e.g. as of jOOQ 3.16, implicit joins are usually not doable in DML statements akin to UPDATE, DELETE, even when they make a number of sense there as nicely. See concern #7508).

Comfort syntax

One other sort of enchancment is what I name “comfort syntax.” For instance, no matter the underlying RDBMS, jOOQ lets you write:

choose(someFunction()); // No FROM clause
selectFrom(someTable);  // No express SELECT checklist

In each circumstances, customers can omit clauses which may be obligatory within the underlying SQL dialect, and jOOQ fills the generated SQL with an affordable default:

• A FROM DUAL desk declaration, or one thing related
• A SELECT * projection declaration, or one thing related

Conclusion

The concept that jOOQ ought to persist with SQL syntax on a 1:1 foundation was a chance I took 13 years in the past, once I made jOOQ. I needed to design jOOQ in a method that everybody who already knew SQL would don’t have any issues studying jOOQ, as a result of every part is completely easy. The method behind this API design is described right here.

Others have tried to “repair” SQL by both making their API very idiomatic contemplating the goal language, or by inventing a brand new language.

13 years later, I’ve discovered that the 1:1 mimicking strategy is the one viable one, as I hold discovering new, arcane SQL options: