Hacker News Reader

Comment by cageface | Hacker News Reader

Building SQL queries by gluing strings together is tedious and error prone. So you write a lot of helper functions to build queries for you and pretty soon you've invented your own crappy ORM.

Why not save a lot of time and bugs and use a battle tested ORM and drill down to SQL for the queries that really matter instead?

View on HN

Or you rewrite you queries so that you can use prepared statements. You’re right that we should avoid gluing strings together, but in most cases we can use prepared statements.

There’s also a middleground where developers learn to use the ORM better. I’ve seem people get terrible performance using the Django ORM, but after a rewrite, redesigning the queries and using the more advanced features performance would improve massively. The problem is that need to be able to write the SQL and then mentally backport to the ORMs syntax.

We’ve had customers complain about poor database performance. When we find the horrible queries generated by their ORM it’s frequently easier for the developer to just request more hardware or ask if we can: “performance tune” the database.

Prepared statements are a good tool to have in your arsenal but for the majority of the queries you need an ORM will be fine. It doesn't absolve you of the need to understand SQL but it does save a huge amount of time and generally results in easier to follow code.

We’ve had the opposite experience. We had a mix of irk code and raw SQL, and we’ve found that the SQL is much more readable. New code is all being written in SQL. Exception is insert and upsert queries which only ever write to one table and involve a lot of boilerplate.

There's a difference between an ORM and a query builder. Full-blown ORMs often allow you to pretend that all you have are "regular" objects that just happen to do database queries when you access them; and that's the kind of ORM that often leads to inefficient database access and fragile code when the program throws an exception when you just wanted to iterate over a list because that iteration actually caused a fetch from a database that happened to have a failover event.

With a query builder, you can still use the database in a type-safe way and without gluing strings together, but accessing the database is also more explicit, so mistakes are harder to make.

It's fine to represent the result of a database query as a sequence, but its type and interface should be different from that of eg. a list datastructure, because it can fail. similarly, if you have "User" objects representing users, it needs to be explicit which methods fetch data from the database and which use only in-memory properties that code may freely access. Preferably, have different types for "UserInDB" and "User" altogether, with some glue to go between them.

In Java, you don't build SQL queries by gluing strings together. You use https://www.jooq.org, of course.

Reply on news.ycombinator.com