Hacker News Reader

Comment by jbm | Hacker News Reader

One of the design decisions I made for a project I'm working on is to skip the ORM layer and make direct SQL instead. This pairs with the "SQL (almost never) changes" aspect; the same SQL I learned in college 15-20 years ago is still completely usable for the majority of the queries I use.

Moreover, I can actually implement requests for complex logic fairly easily. Instead of trying to understand how the ORM works (which is a problem in of itself with a lot of ORM documentation, due to the sheer number of corner cases), I can either implement or google an example.

One of the nicer parts to working w/ SQL is that even different dialects of SQL offer helpful ideas on solving a problem. Yes, MySQL might not have materialized views or what have you, but seeing a Postgres solution will at least point you in the right direction. (Using ORMs just adds a level of indirection that you don't need IMO)

View on HN

String appending parameterized SQL statements is all sorts of awful. I for one have accepted my EF/LINQ overlords for the general sanity it provides to most queries.

However, I don’t touch any of the “ORM” features other than certain conveniences like .Include() for trivially handling trivial joins, and most of the code ends up with an almost 1:1 translation to the actual SQL statement.

Same with SQLAlchemy Core in python, though the expression API is distinctly less pleasant than LINQ.

Anything that doesn’t fit cleanly in LINQ usually ends up as a View or procedure in the DB anyways (except for DELETE statements, which EF is uniquely awful with; requiring the rows to be fetched first before turning around and actually deleting them)

Pretty much no one is doing string appending to build SQL statements for a long time now. You use positional parameters [0] which also addresses problem of SQL injection attacks.

[0] https://www.postgresql.org/docs/current/sql-expressions.html...

That’s what I meant by parameterized queries.

But composing strings together to forge a SQL query (outside the inputs) is generally a terrible, no-good, awful job. E.G. optionally adding filters to a query.

Largely because the SQL language itself does not compose well, due to its general inconsistency in just about every expression, and it’s awkward/unnecessary strictness in things as simple as SELECT/FROM/WHERE order and evaluation order

LINQ is an interesting case as I can legitimately imagine only using LINQ for 5-10 years as Microsoft is dedicated to it, and I understand that all .NET languages use it.

However, outside of the .NET world, there is no common ORM that I would use between languages and between projects. Learning an ORM repeatedly is much, much worse than learning SQL once, especially considering the low quality of some of them (I'm thinking Sequelize here, but I'm sure there are others).

For 99% of cases, I don't do string appending; I create different SQL files for different situations and select the file at runtime depending on the situation. 10 SQL files? Sure, I don't care. It's better than the hours of going through JavaScript code at 3AM and trying to understand how to do some random feature that may or may not be implemented in the ORM layer.

Incidentally, I am curious, how do you keep stuff like Views and Stored Procedures version controlled?

Reply on news.ycombinator.com