Comment by ndriscoll | Hacker News Reader

Comment by ndriscoll | Hacker News Reader

Parent Full thread

I'm not sure what distinction you're making besides allowing duplicate rows (which don't affect composition and you can remove with `distinct `).

I'm also not sure how to answer your question. Obviously a query builder is just spitting out queries that you can just write yourself. In the best case, they're a thin wrapper to give different names to SQL (e.g. where vs filter, select vs map, join vs flatMap). Perhaps an example would be how frequently, ORMs encourage you to do a load-modify-save pattern that turns into multiple statements to execute. This is usually more code, more error-prone, and worse performing than just doing an UPDATE WHERE. If you need complex reporting with a dozen joins and several window functions, you'll also see how difficult it is to write similar code in an application.

I'm not sure what you mean with composition of the language. The language consists of operators which you can chain together, and you can substitute expressions into each other to form larger expressions. E.g. you can join to a (select from where group by) expression in place of a table, and you can factor such things out into CTEs. What's not composable?

> I'm not sure what distinction you're making besides allowing duplicate rows

Duplicate rows, NULLs, ordering, etc. But there is no distinction to be made here, just calling attention to your grievous error so you don't make it again. We don't want to look like a fool again next time. Viva la education!

> I'm also not sure how to answer your question.

You put a lot of effort into a rather detailed response not once, but twice. You obviously know how to answer the question at a technical level, but perhaps you don't understand the nuance of my question? What I mean is: Show us some example code that demonstrates query builder composition and the pure SQL that you would use to replace it.

So to be clear, my choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables", but you can of course use these things in SQL expressions. So tables are relations (not in the mathematical sense, but in the sense that e.g. postgresql documentation uses), but not all relations are tables. In that sense, the things that compose are relations and their operations.

I'm not sure what you have in mind either for query builders or their composition. Like I said, some builders are really just wrappers to rename SQL operations and have a method chaining syntax. Those are always going to compile to obvious, analogous sql (e.g. `Users.filter(_.id==id).map(_.name)` will compile to `select name from users where id=?`. For the most part I think these are fine but maybe redundant. Then there are ORMs that do a bunch of state tracking and might produce multiple statements from one expression. These are usually what people get opinionated about. What's an example of query builder composition that you think can't be written in SQL?

> choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables"

The T in CTE literally stands for table. Even if you're right about the others, how could someone not think of that one as being a table? Regardless, now they can! Isn't education a wonderful thing?

> postgresql documentation uses

In fairness, Postgres originally implemented QUEL, which is relational. It didn't move to SQL until quite late in its life. It often takes longer to update documentation than to write code, especially in open source projects where the contributors tend to much prefer writing code over updating documentation.

> and have a method chaining syntax.

And this is often how composition is implemented. Not strictly so, but a common way to do it. Consider:

    users = select("*").from("users")
    admin_users = users.where("role = 'admin'")
    recent_admin_users = admin_users.where("created > LAST_WEEK()")

And now imagine having tens, maybe even hundreds, of slight variations on the same query in the same vein. Each used in different parts of the application, which is quite common in any kind of line of business application. I'll save your bandwidth and not spell them all out as this is just a contrived example anyway, and I'm sure your imagination can fill in the blanks.

Of course, you could do the obvious and write out 100 completely separate almost identical SQL queries, but that's not exactly maintainable and it's not the 1940s anymore. You are going to quickly hate everything about your existence as soon as those queries need to change. This is the reason people turn to query builders. If you only had to ever write one or two queries there'd be no point, but that never happens within the domain where these tools are used.

But perhaps there is a better way. This is where you would give us example code to show how you would replace that query builder code with a pure SQL solution.

If I'm understanding you correctly, then you can do something like

    create view admin_users as select * from users where role='admin';
    create view recent_admin_users as select * from admin_users where created > LAST_WEEK();

etc. You can also give different roles different permissions to access views without access to the underlying tables as a way to define a stable, high performance API, for example.

I wouldn't use views for something so small, but I probably wouldn't use a query builder either. If you want a stable API, make a view to indirect access to the table(s). Don't break your view API. If you change the underlying table, update the view to keep it as a stable interface.

Query builders can be nice for generic code. E.g. you have a variable length list of predicates (e.g. from http query strings) and want to do `predicates.fold(_ => true)(_ and _)`. In that case you're basically using it as a macro system, which works because sql fragments compose. In fact IMO the most pleasant way to use a query builder is usually exactly as string interpolation macros.

ORMs, the original topic at hand, are an entirely different beast. I think generally people who bash ORMs don't have much issue with query builders.

> If I'm understanding you correctly, then you can do something like

That gets you halfway there, perhaps, but I'm not sure it explains how you would use it in an actual application. Are you back to writing hundreds of "SELECT * FROM recent_admin_users"-type queries in the application, once again exploding the development effort and maintenance surface – exactly what you're trying to avoid with these types of tools?

> ORMs, the original topic at hand, are an entirely different beast.

The original topic at hand is active record, not ORM. ORM is, as the name literally tells, about mapping between relations (or, in practice, tables) and objects. No sane person is going to bash ORM. They may dislike a particular toolkit that tries to help with ORM, but at very least they are going to do ORM by hand.

But that's not what we're talking about. The original comment that set the context for this particular discussion is about query building. It literally proposed using an LLM to generate queries instead. You can query build in the active record style: e.g. `User::all().admins().latest()`, but that's still query building, exactly like the earlier example except with a different API.

Reply on news.ycombinator.com