Every so often, I see people lament the SQL syntax’s peculiar disconnect between
Most just lately right here in a Youtube remark reply to a latest jOOQ/kotlin discuss. Let’s take 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 all over the 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 variety of criticism concerning the SQL language is that it doesn’t “compose” (in its native kind).
We are able to argue, that it might be significantly better for a extra composable SQL language to start out 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 may be higher within the sense that it might be extra logical. First, we declare the info supply, predicates, and many others. and solely in the long run would we declare the projection. With the Java Stream
API, we might 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 is able to be:
- No disconnect between syntax and logic
- Therefore: No confusion round syntax, specifially why you’ll be able to’t reference
SELECT
aliases inWHERE
, for instance. - Higher auto-completion (since you don’t write stuff that isn’t declared but, first)
In a means, this ordering could be in keeping with what some RDBMS applied when RETURNING
knowledge from DML statements, reminiscent of:
INSERT INTO e book (id, title)
VALUES (3, 'The E-book')
RETURNING id, created_at
With DML statements, the command (“crucial”) remains to be INSERT
, UPDATE
, DELETE
, i.e. a verb that clearly tells the database what to do with the info. The “projection” is extra of an afterthought. A utility that’s sometimes 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 applied 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 powerful 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 at all times must lookup 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 value mentioning right here is that there exists a contemporary question language on the market that’s sufficiently fashionable to be thought-about 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 toFROM
, however it’s a verb), so it inherits SQL’s “energy” of being intuitive additionally for non-programmers. - Reversed the logical order of operations throughout the studying statements, to be of the shape
MATCH .. RETURN
, makingRETURN
the common type of projecting issues for all operations, not simplySELECT
. - Reused
MATCH
additionally for writing operations, together withDELETE
orSET
(which corresponds to SQL’sUPDATE
)
Whereas working on a special knowledge paradigm (the community mannequin versus the relational mannequin), I’ve at all times discovered the Cypher Question Language to be typically superior to SQL by way of syntax, at the least on a excessive degree. If I needed to truly “repair” SQL by creating SQL 2.0, I’d take inspiration right here.
Fixing this in an API like jOOQ isn’t value it
As mentioned earlier than, SQL has some apparent shortcomings, and there exist higher languages like Cypher fixing the identical sort of downside. However SQL is right here, and it’s 50 years outdated, and it’ll keep. It gained’t be mounted.
That’s one thing that simply must be accepted:
SQL gained’t be mounted
Will probably be amended. It incorporates new concepts, together with:
It at all times does so in an idiomatic, SQL type means. Should you’re studying the SQL customary, or when you’re working with PostgreSQL, which may be very near the usual, you’ll really feel that SQL is kind of constant as a language. Or, it’s constantly bizarre, relying in your tastes.
For jOOQ, one of many fundamental success elements has at all times been to be as shut as attainable to this imaginative and prescient of what SQL actually is by way of syntax. A variety of people are very efficient writing native SQL. Since Java has textual content blocks, it has change 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 file : ctx.fetch(
"""
SELECT id, title
FROM e book
WHERE title LIKE 'A%'
"""
)) {
System.out.println(file);
}
This method is adequate for very easy purposes on the market. In case your “utility” 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 cling of jOOQ, you’ll most likely use jOOQ even for these purposes as properly).
However jOOQ actually shines when your utility has 100s of queries, together with many dynamic ones, and your database has 100s of tables, in case of which the kind security and mannequin security advantages actually assist. Nonetheless, it will probably shine solely when your SQL question interprets 1:1 to the jOOQ API. Randomly fixing SQL to some extent on this most vital assertion (SELECT
) gained’t do the trick.
As a result of: The place will you cease fixing SQL? SQL remains to be bizarre even when you change to FROM .. SELECT
. For instance, the semantics of GROUP BY
remains to be bizarre. Or the connection between DISTINCT
and ORDER BY
. E.g. this is able to seem like significantly better at first (e.g. to separate SELECT
and DISTINCT
, which shouldn’t be situated 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 that you could 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).
Various 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 could be a lot tougher to study that it needs to be. Some competing APIs observe this mannequin, e.g.
Each of those APIs are based mostly on the concept SQL wants “fixing,” and {that a} extra “native,” a extra “idiomatic” really feel of the API could 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 like odd 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 asmap()
orSELECT
, although international to each SQL or kotlin assortment APIsselectAll
, which corresponds to the relational algebra time period “choice”, similar to SQLWHERE
Artificial comfort syntax as a substitute of “fixing” SQL
jOOQ doesn’t observe down this highway and by no means will. SQL is what it’s, and jOOQ gained’t be capable 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 gained’t allow you to down and can can help you write precisely what you take note of by way of SQL function. I imply, wouldn’t it actually make sense to help CONNECT BY
in Slick or Uncovered? Most likely not. They must invent their very own syntax to offer entry to SQL recursion. However will or not it’s full? That’s an issue jOOQ gained’t have.
The one motive why some syntax will not be out there is as a result of it’s not attainable but (and please do ship a function 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 advanced ones. I a lot choose customary SQL/XML and SQL/JSON syntax, (which jOOQ additionally helps). However whereas I don’t very very similar to the syntax, jOOQ gained’t decide. What good would a 3rd syntax, solely invented by jOOQ be for customers? As I mentioned earlier than.
When will the “fixing” cease?
It’ll by no means cease. The options I’ve talked about will run into very tough questions down the road after they begin including extra options, if they begin including extra options. Whereas it’s at all times simple to implement a easy SELECT .. FROM .. WHERE
question builder, and help that performance utilizing arbitrary API, claiming SQL has been “mounted,” it’s a lot tougher to evolve this API, addressing all kinds of superior SQL use-cases. Simply take a look at their subject trackers for function requests like CTEs. The reply is at all times: “Use native SQL.”
Even “easy” SQL options, reminiscent of UNION
change into extra advanced 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” this stuff is rarely so simple as it could take 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 could have, ultimately). Similar to there exist SQL dialects, which “improve” the SQL customary, e.g.
jOOQ may be very conservative about such artificial syntax. There are lots of good concepts, however few are ahead appropriate. Every one among these syntaxes makes different SQL transformation options extra advanced, and every one has flaws that won’t have been addressed but (e.g. as of jOOQ 3.16, implicit joins usually are not attainable in DML statements reminiscent of UPDATE
, DELETE
, even when they make lots of sense there as properly. See subject #7508).
Comfort syntax
One other kind 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 that could be necessary within the underlying SQL dialect, and jOOQ fills the generated SQL with an inexpensive 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 follow SQL syntax on a 1:1 foundation was a bet I took 13 years in the past, after I made jOOQ. I wished to design jOOQ in a means that everybody who already knew SQL would haven’t any issues studying jOOQ, as a result of all the pieces is totally easy. The approach 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 method is the one viable one, as I maintain discovering new, arcane SQL options:
Making a language is extremely tough (let’s take into account an inner DSL API to be a type of language). It’s virtually unimaginable to design correctly, if the purpose is to help just about any underlying SQL function, until, the designer lets go of this dream of “fixing” issues, and begins embracing the “dream” of “supporting” issues. All of the issues.
SQL is what it’s. And which means, the syntax is SELECT .. FROM
, not FROM .. SELECT
.