> but it would not be okay for so many ORM people to actually have properly bound SQL in-place.

It's okay... sometimes. But, as what the article is about, more often than not you need to start composing that SQL. SQL does not deal well with composability natively. Which means you need to bring your own solution to work around its limitations.

In theory, you could parse the SQL and build up some kind of AST on which composition could be built. Or, you could write a set of functions in the application language which somewhat resemble SQL, as demonstrated in the article, that build the same AST. It turns out the latter is considerably easier to implement.

▲

larodi 7 months ago | parent | next [-]

> SQL does not deal well with composability natively.

not sure what you really mean by this one. I've seen quite complex SQL code, pages long, not PL/SQL , but SQL with intersects, CTEs, recursion and all... and it composes.

it actually composes OK and on a very high level where reading skills similar to those needed to read math crypto-text are needed. is not easy, but the power you get from the declarative writing can never be matched by functional language in terms of readability and concise length. is the same with regexp - once you master it you never go back to writing for-loops slicing commas.

i don't really take this argument that you want a little ORM here and there to save time implementing CRUDs, but then you invest heavily into describing the AST of the SQL by means of .dot.dot.dot OOP composition, which eventually is "translated" (transpiled if u want) into SQL again. So basically you need to understand structure on the ORM API level, but then still understand it all (for debugging) on SQL level. Seems doing the same two times.

Besides not all queries are equally used statistically, and it is apparent you are going to want to optimize the critical stuff, while the trivial stuff - well you can compose CRUDs with simple join+binding and it's the same thing timewise.

	▲	9rx 7 months ago \| parent [-]
		> not sure what you really mean by this one. The same as what everyone has meant, including the article. The naive solution is to simply write out every nearly the same query you need, but that starts to become difficult to maintain once you have more than a small handful of queries. More realistically you are going to want to introduce composition in a practical setting. The SQL solution is views, but they are complicated by being dependent on runtime state. There is a lot of complexity involved in ensuring that everything is in the right state. While that may be a decent tradeoff for some applications, the types of applications that lean on these query builders tend to be of the type where they are trying to prove a business hypothesis and whatever gets there the fastest is the tradeoff that needs to be made. A set of functions in the application language that somewhat resemble SQL, which output a SQL query, are a lot easier to implement. The alternative is to compose queries at "compile time". In the simplest case you might get away with string concatenation, but this too quickly becomes error prone at scale. While many languages of this nature are designed such that all concatenations are valid for this very reason, SQL is not. An innocuous and perfectly valid modification to a query can easily invalidate what was a previously valid concatenation. More realistically you are going to want to build an AST so that properties can be modified without reliance on exacting syntax. You can parse SQL to give you that AST, or you can go back to the aforementioned set of functions to generate the AST. The latter is considerably easier to implement. Each approach has pluses and minus. As with anything, you have to pick your tradeoffs. But it is no surprise that a lot of developers choose the easy tradeoff, especially when a lot of those developers are developing systems where what is easy/fast is the most important tradeoff, not knowing if what they are writing will ever get used. Theoretical engineering perfection is moot if there is no customer to use the product of that engineering.

▲

MathMonkeyMan 7 months ago | parent | prev [-]

> In theory, you could parse the SQL and build up some kind of AST on which compatibility could be built.

This is an interesting problem that I'd like to learn more about. Have you read anything about it?

	▲	ajfriend 7 months ago \| parent [-]
		You can compose SQL with https://ibis-project.org/tutorials/ibis-for-sql-users, which is using https://github.com/tobymao/sqlglot to parse the SQL under the hood. As an alternative to parsing the SQL yourself, DuckDB's https://duckdb.org/docs/api/python/relational_api allows you to compose SQL expressions efficiently and lazily, which I've used when playing around with things like https://gist.github.com/ajfriend/eea0795546c7c44f1c24ab0560a...