Comment by ken | Hacker News Reader

Comment by ken | Hacker News Reader

Parent Full thread

> the problem they are actually employeed to "solve": developers afraid of SQL. Whether they can't write it, can't learn it, or just straight hate the string representation in their code, it's the main reason people sign on for ORM.

This sounds awfully like a straw man. I've worked on several projects that used an ORM, and it was never because developers couldn't write SQL.

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

Sooner or later, every non-trivial program needs to compose arbitrary pieces of one query with arbitrary pieces of another, so you'll either use an ORM (i.e., native data structures instead of raw strings), or essentially write your own ad-hoc one.

It has nothing to do with lack of ability. The staunchest ORM advocate I've worked with was also the first to pull out the debugger and single-step the MySQL driver to track down a bug (that one was nasty -- it was indeed in the driver).

I use an HTML generator, and a compiler, too, and it's not because I'm "afraid" of assembly language or that I "can't learn" HTML. These abstractions have real value.

That's not a straw man and I specifically said ORMs had their design reasons? My complaint is that they got popularised because of their perceived ease of use because of a perceived difficulty with SQL.

Anyway..

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

It's a DSL. It's literally the most composeble thing you could build as a solution. Hell it's so composable people decided they could build ORMs on top of it.

SQL was created to solve the problem of interfacing, composing and reasoning about tabular data.

ORMs were built because?

I feel like your thought that SQL isn't composable is due to a reluctance to create a function that executes a new query?

Or are you really looking for a way to compose queries over objects? Because SQL has that too..

> 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.

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.

>it was never because developers couldn't write SQL.

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

But really though, these are at odds. The question is never "how can we compose this SQL." ORMs are always about writing less SQL.

ORMs are about abstractions. We use django, and django gives us great abstractions so we can create more value customers rather than do sql kungfu.

When we find something can be better expressed as SQL because of a complicated join from many tables, we use SQL.

ORM is just another tool in your toolbox. They are not mutually exclusive.

Mixing paradigms can often cause issues with ORM caching and transacting so in a sense they are often exclusive, although you can usually just throw more code around to make it work.

Reply on news.ycombinator.com