This somehow reminds me of Ecto in Elixir, which also counts composability as one of its main advantages. In my experience, this type of library suffers from a translation problem: you have to learn something that isn’t quite SQL, and you end up first coming up with SQL solutions that you then translate into this dialect, which is quite a bit of an overhead. On the other hand, the benefits of composability are relatively small and don’t balance the annoyance of having to do SQL translation, at least in the kinds of applications I’ve worked on.
So I remain in the camp of “write plain SQL with a few affordances” as the most practical approach (which was actually the original premise of Massive.js, if I recall).
That is still the premise with monstrous! To be specific about the affordances, though, when it comes to these libraries we’re talking about dynamic SQL generation. Case in point is the where clause, which is something you can’t reasonably hand-write if you want to do something as simple as test a variable number of predicates. Massive did that very well, but painted itself into a number of other corners I discuss in the post. monstrous’ query-builder approach seeks to make that JS-inflected dynamicism easier to implement in other areas of the query (joins, projections) and expand the field of what you can do dynamically before you have to break out hand-written SQL (grouping, subqueries, values lists – which would have been painful at best with Massive’s “do-it-all” single method approach, similar to the nightmare of aggregation in MongoDB).
Yes it’s a DSL that “compiles” down to SQL, but SQL isn’t necessarily the most intuitive way to think about transforming data – it’s the one we’re used to. My main point of dissatisfaction with query-building, either through a library or by plain string concatenation, is that it tends to be too close to SQL. This in turn means the procedural control flow of statement construction in the host language, conditionals and iteration, inevitably breaks up that construction in jarring and increasingly difficult to test and cross-validate (every conditional doubles the number of different possible output queries) ways. The only escape from if (x) sql += 'on y = z' else.... requires getting enough distance from SQL to reframe query composition as a set of discrete transformations.
yes, i completely agree. Every time i try to use a ORM (prisma, ecto, activerecord), unless I am doing simple CRUDs for an object, or gets, it always seems like i have to write the SQL and then translate which does not seem worth it. I have really started to like the philosophy of mojo.js which takes a old school approach and the database adapter only allows raw sql, but I see the wisdom in it.
This somehow reminds me of Ecto in Elixir, which also counts composability as one of its main advantages. In my experience, this type of library suffers from a translation problem: you have to learn something that isn’t quite SQL, and you end up first coming up with SQL solutions that you then translate into this dialect, which is quite a bit of an overhead. On the other hand, the benefits of composability are relatively small and don’t balance the annoyance of having to do SQL translation, at least in the kinds of applications I’ve worked on.
So I remain in the camp of “write plain SQL with a few affordances” as the most practical approach (which was actually the original premise of Massive.js, if I recall).
That is still the premise with monstrous! To be specific about the affordances, though, when it comes to these libraries we’re talking about dynamic SQL generation. Case in point is the
whereclause, which is something you can’t reasonably hand-write if you want to do something as simple as test a variable number of predicates. Massive did that very well, but painted itself into a number of other corners I discuss in the post. monstrous’ query-builder approach seeks to make that JS-inflected dynamicism easier to implement in other areas of the query (joins, projections) and expand the field of what you can do dynamically before you have to break out hand-written SQL (grouping, subqueries, values lists – which would have been painful at best with Massive’s “do-it-all” single method approach, similar to the nightmare of aggregation in MongoDB).Yes it’s a DSL that “compiles” down to SQL, but SQL isn’t necessarily the most intuitive way to think about transforming data – it’s the one we’re used to. My main point of dissatisfaction with query-building, either through a library or by plain string concatenation, is that it tends to be too close to SQL. This in turn means the procedural control flow of statement construction in the host language, conditionals and iteration, inevitably breaks up that construction in jarring and increasingly difficult to test and cross-validate (every conditional doubles the number of different possible output queries) ways. The only escape from
if (x) sql += 'on y = z' else....requires getting enough distance from SQL to reframe query composition as a set of discrete transformations.yes, i completely agree. Every time i try to use a ORM (prisma, ecto, activerecord), unless I am doing simple CRUDs for an object, or gets, it always seems like i have to write the SQL and then translate which does not seem worth it. I have really started to like the philosophy of mojo.js which takes a old school approach and the database adapter only allows raw sql, but I see the wisdom in it.