I have to be honest, I find those Julia examples much, much harder to comprehend than a HoneySQL query with explicit aliases used to disambiguate the inner/outer table references...

I also find the idea of ordering of clauses affecting the generated SQL to be just a potential for all sorts of weird bugs. HoneySQL (and most of those other SQL builders) is not order sensitive, which makes programmatic building of queries much easier -- and how could you be order sensitive when the underlying data representation is a hash map?

The quote given turns me off with out knowing anything about funsql. The beauty of honeysql for me is that it has a fairly simple mapping to plain old sql, and I can combine bits of honey with the usual suspects from the core library.

(in fact, that's a specific issue that HoneySQL provides a way to control: join ordering, because that can be sensitive to clause order and a user needs to have control over that)

https://cljdoc.org/d/com.github.seancorfield/honeysql/2.4.972/doc/getting-started/sql-clause-reference#join-by -- for the cases where JOIN order actually matters (and I feel like saying #notalldatabases here since I believe only some databases are sensitive to this issue?).

And, yeah, what @U04V5VAUN said: context-sensitive changes to semantics are nearly always problematic.

But if there are specific things that FunSQL can do that you'd like to see added to HoneySQL @U899JBRPF please feel free to create issues on GH so I can take a look at those use cases! I'm always open to specific ways to improve the library.

(Datalog clauses are order sensitive, right? That's why they use vectors...?)

Thank you for the responses! I will give this some more reflection soon 🙂 > Datalog clauses are order sensitive, right? That's why they use vectors...? The compsci definition of Datalog is fully declarative (first-order logic), but some implementations use the user-provided clause ordering to determine the physical execution plan (rather than having an optimizer doing statistics and join planning).

RE join order: Postgres and MySQL are sensitive about the order of joins. H2 is not. Not sure about anything else. I can write a test and run it against the 14 SQL databases we support in Metabase and get a more detailed answer if it would be useful

I learned recently that SQLite has a CROSS JOIN https://www.sqlite.org/optoverview.html#:~:text=SQLite%20treats%20the%20CROSS%20JOIN%20operator%20specially.%20The%20CROSS%20JOIN%20operator%20is%20commutative%2C%20in%20theory.%20However%2C%20SQLite%20chooses%20to%20never%20reorder%20tables%20in%20a%20CROSS%20JOIN.%20This%20provides%20a%20mechanism%20by%20which%20the%20programmer%20can%20force%20SQLite%20to%20choose%20a%20particular%20loop%20nesting%20order.: > SQLite https://www.sqlite.org/lang_select.html#crossjoin. The CROSS JOIN operator is commutative, in theory. However, SQLite chooses to never reorder tables in a CROSS JOIN. This provides a mechanism by which the programmer can force SQLite to choose a particular loop nesting order.

@U899JBRPF I'd not heard of FunSQL before so I've just read the article you linked to in your first post. My gut reaction to it is that it's an abstraction too far removed from the underlying database to want to use it instead of an AST like HoneySQL. I might however wish to implement something like it on top of HoneySQL. It seems from the examples that you can get much of the same experience by just changing the order in which you build your map. Just write {:from, :select} rather than {:select, :from}. But that's by the by. The article says that "syntax-oriented query builders are harder to use". I guess that depends on what you're trying to use them for. Fundamentally, I'm writing SQL whether I know it or not, and that's what I'm going to be working with if ever I have to debug a broken query, or optimise a slow query, or whatever. I'm going to be reading and comprehending SQL and, once I've worked out how to fix it, I'm then going to be trying to work out how to change my library code to generate that fix. HSQL is essentially a one-to-one mapping. You can intuit just by looking at it what SQL it will produce. That's a big win when fighting with queries. Another thing worth noting about HoneySQL that you should always look for in libraries: it has an escape hatch. If ever the library can't do what you need (or you can't work out what it can do) you can just use :raw to write SQL directly and move on with your life. Frankly, that is worth way more to me than any level of architectural purity. Having said that, I've not looked at this library, so I'm not saying anything about it specifically. These are just the things I would be considering if I were evaluating it for use in one of my projects.

Especially about the :raw

@U899JBRPF This may interest you: https://walkable.gitlab.io/. It's at a similar level of abstraction to FunSQL. Again, I have no practical experience with it