Hacker News Reader

Comment by karmakaze | Hacker News Reader

> ORMs solve a real problem: SQL isn't composable. That's it.

How so? I've always found the opposite. I can build up a compilex query referencing many tables in SQL without impediment but am unable to do the same with an ORM.

View on HN

OP means combining two different queries or clauses into a bigger query/clause

You cannot easily "modify" a sql statement.

There are type-safe DSLs in certain languages (if they have an expressive enough type system) that let you do this, but they are uncommon

They're not that uncommon. You can do this with SQLAlchemy in Python or jOOQ in Java.

You could use views. Although personally I dislike adding this layer of obfuscation. Generally devs are very underskilled in SQL (relative to it's importance) and ORMs hiding the SQL IMO is one of the reasons why.

There is great value in having the entire SQL easily visible in your source code. If there is a performance issue with a specific query my app is running, I can very quickly pull it out and into a database IDE and see what's going on using the more detailed tools available (REPL, execution plan/statistics usage analysis etc).

If the query is composed from a bunch of ORM function calls, I have to step through the program to first generate it.

Reducing friction around debugging allows your devs to become more capable in fixing things or making them work better, and SQL is no exception to that.

Views are not a layer of obfuscation. They are a layer of abstraction...

Thanks for the explanation. I've never found adding to an SQL query to be any effort. The only place I'd put any value into a DSL is that it doesn't miss including all the conditions for a join.

Nested queries? Joins? Stored procedures? Views? This is literally everything SQL was made for.

Modify a query? Literally just code.

Like what do you people think SQL is? Select * from table?

We are talking about programmatically constructing a query at runtime by stictching together bits of sql, in a way that which bits of sql you use are actually dependent on the runtime state, and in a way that is type-safe.

You could do something similar with either dynamic queries in a stored procedure, but without a DSL those dynamic queries are brittle, and the runtime composition using them is hard and error prone.

Right, how often do you actually need to do this?

In my experience query building to this degree is usually a sign of over optimising for code reuse where it isn't really needed.

The times it does make sense, it's usually a search problem that is better solved by something like elastic search.

That isn't always feasible so what you end up with is a naive approach and just build the damn string. Or if it's needed you decompose it into the parts that are needed elsewhere table_headers() is usually all you need to stay DRY.

It's not as pretty as an ORM, it shouldn't be used for every little query "because you might need it in the future". But the cases it is needed are rare, so does it really matter?

Like you've got to adjust your frame of reference. Your database schema is static, it's not going to completely change out from under you at runtime, it's just a non problem.

>Right, how often do you actually need to do this? In my experience query building to this degree is usually a sign of over optimising for code reuse where it isn't really needed.

I need to very often and it's rarely about code reuse. You requirements like "on the client list screen we want to be able to search by client id or client name and we want to be able to sort by last login time or name"? What about writing an API, that's 90% handling things like this.

And then there's the other cases ORM's solve, like representing data as something more than a 2D table.

That problem is a query building problem you probably want to look into graphql.

ORMs sometimes come with query builders that help things but in my experience it's not worth having to clusterbomb my codebase for it.

Implementing graphql, which you can only apply in a limited number of client-server scenarios, to solve a relatively trivial problem, sounds like the clusterbomb or a good 2,000 lb mk-84 general purpose bomb to me e.g. graphql is not a panacea

The metaprogrammability using an ORM has never been wroth the loss of expressiveness in my experience. If I needed it, I'd probably not be using the procedural language the the ORM uses.

Reply on news.ycombinator.com