Comment by randomdata | Hacker News Reader

Comment by randomdata | Hacker News Reader

Parent Full thread

> 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