[ErwinSmout]

That "the API becomes unwieldy" is in fact inevitable. In order to support .where(<boolean expression>) as well as .select(<any expression>, ascolname) you need a way to pass a parameter of type something-like-expression to the .where() and .select() methods, and lambdas or at least something like them appear to be the right way to do that (e.g. .where((foothing) -> (foothing<0)), but languages offer no means to "introspect into the lambda" which is what is needed to translate it into SQL.

Plus (assume my .where() example was Java), the host language compiler is never going to find a way to cope with the declaration of the 'input' of the 'lambda', *precisely* because [the definitions of] those things are outside the scope of what the host language compiler knows about. So you need a way to do something like "import my-db-definition;" And then you need to put machinery in place to verify at runtime that the definitions as they are for the db, are still the same (or compatible) with the definitions that the program was compiled with. Etc. etc. etc.

[cryptonector]

> lambdas

Lambdas in the host language won't play well with remote RDBMSes -- you'd have to be able to serialize the lambda and make the serialized form reasonably efficient. I'm skeptical of lambdas for expressions in DB query APIs.

An expression like `foo + bar` has to become `.expr(plus("foo", "bar"))`, except, if you take this to its limit you'll want to use non-string objects to identify column names and other such things, and, again, it quickly becomes unwieldy.

You cover some of this in your comment, so I think we're in agreement:

> That "the API becomes unwieldy" is in fact inevitable.

And yet an API is kinda desirable.

Ultimately I think a query language with no constant literals, only query parameters, compiling to a standard AST that can also be constructed by APIs, may be the best way forward. One could then write in a QL to start with, compile to an AST, serialize into a host language if desired, modify and use that, or always use the QL, or even always use the API, unwieldy though it would be. Then lambdas could actually be in the QL and compiled on the fly as needed:

  Query q = db.query();
  
  q = q.from(...).join(...).using(...)
       .select(...);
  q = q.where(q.expr("foo + bar < baz"));

Ok, that snippet has a problem if we want `q` to be a unique pointer. Let's fix it up a bit:

  QueryScope qs;
  Query q = db.query();
  
  q = q.from(...).join(...).using(...)
       .select(...);
  
  /* Get a context of in-scope identifiers, types, ... */
  qs = q.getWhereScope();
  
  /* Now we can compile an expression string */
  q = q.where(qc.expr("foo + bar < baz"));

And a hybrid QL + API might look like:

  QueryScope qs;
  Query q = db.parse("SELECT ... FROM ... JOIN ... USING (...)");
  
  /* Get a context of in-scope identifiers, types, ... */
  qs = q.getWhereScope();
  
  /* Now we can compile an expression string */
  q = q.where(qc.expr("foo + bar < baz"));

[ErwinSmout]

"And yet an API is kinda desirable."

Yeah. however I may not live to see the day when that desire, which I agree is felt by 99.99% of the developer community (hell, even by 99.99% of the end user community because don't come and tell me that what that community is feeling isn't some sort of sense that "the developers just can't offer us any answers"), actually gets to be fulfilled.

"compiling to a standard AST"

I think that what you might be failing to appreciate is that achieving that requires a "standard algebra", and that no such thing exists at this present day.

[cryptonector]

> I think that what you might be failing to appreciate is that achieving that requires a "standard algebra", and that no such thing exists at this present day.

Oh I appreciate that. What I've in mind is something like the SQL standard (which isn't exactly universally adhered to) of QL ASTs.