Comment by sanderjd | Hacker News Reader

Comment by sanderjd | Hacker News Reader

Parent Full thread

I'm always very skeptical of this sort of claim. How are you constructing queries? Bespoke string concatenation? That's a recipe for disaster. How do you compose queries? That is, given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? Is that a completely separate question? If so, that quickly has unmanageable multiplicative effects. Do you write a layer that knows how to take the two original queries in some abstract form and build the correct WHERE ... AND ... clause for the composed query? Well, now you have a bespoke and immature version of an ORM.

I can understand peoples' disdain for ORMs (which tend to be large dependencies that are more frameworks than libraries), but I don't understand not having a query construction and composition layer, and usually the popular ORMs for a language have the most mature implementations of that. In Ruby, there is actually both ActiveRecord (ORM) and Arel (query composition). Nobody really uses Arel directly, though, but I think that's a shame.

  How are you constructing queries?

Entirely with bind variables.

  given the questions "give me all posts owned by user
  X" and "give me all posts newer than timestamp Y",
  how do you ask for all posts owned by user X that are
  newer than timestamp Y?

The API our service presents isn't an advanced search API, so it doesn't need to support searching on arbitrary combinations of database columns.

Obviously, if your software needs to provide an arbitrary search capability, your needs will be different to mine and an ORM might make sense :)

What are "bind variables"?

I'm not talking about a search API, I'm talking about code units that ask myriad questions of the database in order to implement myriad business logic. If you don't have the problem of asking myriad questions of the database, fair enough, but that is a simpler database-connected-software use case than any I've come across (including the ones I thought were pretty simple). I'm very curious now what your service does :)

Bind variables are the ? in "SELECT fname, lname, pcode FROM cust WHERE id = ?" and they're a better choice than concatenating the ID you want into the SQL string because (a) it prevents SQL injection and (b) it lets the query optimiser cache plans.

We have plenty of complicated queries - when we've got complex SQL queries, we find it valuable to have pure SQL as you can paste it into a tool that lets you execute it on the database and view the results, the explain plans, and suchlike.

Another bonus - depending on your DB/driver, it may set this up as a prepared statement, so executions after the first one happen much quicker.

What are "bind variables"?

I’m sorry, but bind variables are Database 101. I have a hard time believing your use cases are particularly “myriad” given lack of the very basics.

No. "bind variables" are Oracle 101. Everyone else calls them "query parameters".

What is wrong with writing a raw SQL for such a simple thing? I never want to fight with a tool for achieving such a simple query. Tools are supposed to help and when queries tend to get complicated, I think that's when ORM starts to fail with performance. So, I don't find a room for it. I'd rather learn how SQL works instead of how to get random ORM of the time to work like it should.

> What is wrong with writing a raw SQL for such a simple thing?

Mostly query composition. I run in to this a lot. Say I need to write a less-than-simple query and reuse it with several different WHERE clauses.

Sometimes I think I just want migrations, not so much an ORM. I want to define views and stored procs in my repo and have them move to prod automatically, but I still want to write in straight SQL.

This resonates with me.

But the other super critical thing (which was the first part of my original comment, and which nobody has really highlighted here) is a mature query construction library that knows how to properly protect against injection.

In all fairness a properly used sql connector library will hopefully do this too.

Although the majority of what I do is in Python, I've taken to using Flyway for DB migrations.

A lot of what I do is using some of the more esoteric features of PG (think domains, custom aggregate functions, etc), so the ability to run migrations as direct SQL is great.

If you're not doing anything to manage and autogen your queries, does that imply that any change to the model's table entails manually updating all N different queries that touch the table in your application?

I'd be really concerned about the fragility of having that many disparate sources of truth about the structure of your data. Particularly when errors are only going to be discovered when the query actually runs.

You don't need to have an ORM, but at the same time, having no abstraction above "hardcode every query" is its own enormous set of problems.

Don't get me wrong: everyone should learn SQL and learn it well. But a query construction and composition abstraction is also an invaluable tool. Fortunately, it is quite achievable to learn both things well.

Reply on news.ycombinator.com