NEnerdponx·Jan 25, 2024
I really liked this response and I agree with all of it, but I especially agree with this statement:
> I think the fundamental issue here for DSLs is that at the end of the day, they're all compile-to-SQL languages, because the database does not offer any other API than SQL. And the general confusion is that SQL pretends to be standardized, so DSLs try to support all the databases, and end up implementing only some minimal shared subset... no matter how nice the DSL might be, you end up having to leave it anyways.
Until databases offer a better interface for actually interacting with the database, we are stuck with SQL, and as long as we are stuck with SQL, X-that-compiles-to-SQL will always be challenging to design and use successfully. I know libpq/Postgres has a binary wire protocol, but I'm not sure what it consists of, or to what ext
Read the rest
ent it improves on the current model of sending a blob of SQL to be parsed the server.
My bias against query builders specifically I think comes from spending too long with Python, where SQLAlchemy Core is the only standalone query builder in town, and it comes with a lot of ORM-derived baggage and a relatively high level of abstraction (not to mention complexity). This IMO makes it not much better than string templating because it's a relatively large amount of fuss to get it working, unless you also want to take advantage of other features like uniform logging, client-side connection pooling, client-side parameter binding of arbitrary Python objects, supporting multiple database backends in an application like Airflow, etc.
Whereas in Common Lisp I very much enjoy S-expression-based DSLs like SxQL (https://github.com/fukamachi/sxql), and I agree that they're much better than constructing SQL out of raw strings.
I'm not aware of a Scheme equivalent, although it looks like Gauche is planning to add one in the future (https://practical-scheme.net/gauche/man/gauche-refe/SQL-pars...): "The plan is to define S-expression syntax of SQL and provides a routine to translate one form to the other."