Clojurians Log v2
honeysql
On #sql we had a little discussion about vendor specific adapters for honeysql. It boiled down to the question, how much vendor-awareness is really desirable for honeysql. I think an example explains it best. Currently the translation of DSL to SQL in honeysql is quite literal, e.g. :limit 10 translates to "LIMIT 10", which is straightforward, but meaningless for most dialects (e.g. Oracle, DB2, MS SQL, ...), i.e. even syntactically wrong. Should honeysql focus on semantic meaning and translate to vendor-specific dialects or should it process syntax regardless of the DB vendor used? What is the design intention?
:limit is a bad example -- and was added before I took over the project -- since it is DB-specific.
The guiding principle for HoneySQL is that it should implement SQL-92 only and all DB-specific extensions should be in other libraries.
Well, actually my scenario can be well covered with the status quo right now. Now that I know how the design philosophy is, it is easy to accept and provide local helpers for special constructs. However, my original solution idea would require something like that, yes. It would require some dynamic translation layer from Pure Clojure Query Representations to dialect-specific SQL queries. The problem is: What would the pure representation look like? That endeavour is the same as creating a new, feature-enhanced SQL standard. I mean a pragmatic starting point would be to just elevate PostgreSQL to the de-facto-standard and translate from Postgres to other dialects.
@johannes.f.knauf Unfortunately PostgreSQL has a lot of features that other databases don't have so there would be a lot of stuff that couldn't be translated to other database dialects.
(and such a library wouldn't come from me since I don't use PostgreSQL)
That is all true. The question is: Do we have a better candidate for some meta-SQL covering a good amount of the common cross-provider query features?
That principle hasn't always been followed -- hence :limit
There are some complex issues tho'. update/`set` seems to have different precedence in PostgreSQL vs MySQL for example, so HoneySQL has to support :set0 and :set1 for the two different precedences.
Is https://github.com/nilenso/honeysql-postgres a good example for that practice?
Yes.
If I understand that right, this implies: these libraries should just add syntax and map 1:1 between DSL and SQL statements. It's the user's responsibility to use the proper vendor-specific terms. (As you already mentioned in #sql, porting between databases is a rare occasion and hence considered a non-issue.) Correct?
No, what I said was that you -- or any library maintainers -- could add a common DSL with a different implementation in each library.
So far no one has wanted to do that.
Or at least, no one has been sufficiently motivated to contribute it.
Right now, that PostgreSQL-specific library is the only DB-specific extension library that I know of.
Specifically, no one has been motivated to provide a way to have format render different SQL based on some configuration -- I think it would have to be dynamic, so you could switch dialects on a per-call basis. Which is what I think you would need for the scenario you are suggesting @johannes.f.knauf?