Hacker News Reader

Comment by lcnPylGDnU4H9OF | Hacker News Reader

Comments here make me wonder if I've just been spoiled by ActiveRecord. Not that I use it for all queries but 1) it's rare that I have to resort to raw SQL and 2) it kindly gets out of the way when I do.

View on HN

> resort to raw SQL

I'm the opposite, I would rather write SQL than "resorting to" ORM queries, which is why my favourite libraries are aiosql[1] in Python, Hugsql[2] in Clojure and similar: write the queries as SQL in .sql files, which then get exposed as functions to your code.

[1] https://nackjicholson.github.io/aiosql/

[2] https://www.hugsql.org/

Those tend to be great until two words come into play:

Dynamic SQL.

What’s your issue with dynamic SQL in this case?

I’ve written aiosql functions that use dynamic SQL (via plpgsql) just fine.

I’ve been using aiosql and Hugsql for years and have never had any problems. In fact, Hugsql has a “snippets” feature that allows you to compose bits of SQL dynamically.

https://www.hugsql.org/using-hugsql/composability/snippets

But the vast majority of SQL I’ve worked with simply doesn’t need it. But even when you do, the situation has never been any worse than it is with ORM’s for me.

I guess I failed to set the context correctly given that you presented solutions for Clojure and Python, where it isn't as much of a problem since from the start the language fails to provide compiler guarantees you usually come to expect out of a SQL driver wrapper in typed languages (even though Clojure macros are probably powerful enough to allow this).

As a comparison, DX-wise this is no safer and is indeed very similar to the usual idiom in Go for example, where you just concatenate (pre-interpolated) SQL strings. But when you actually want the compiler to prove the correctness of your queries even in a rudimentary way, these .sql file solutions usually (if not, everytime) fail to provide the necessary external checker that processes templates and uses an accurate model of your database and SQL to verify that all used combinations make sense.

The closest thing to a proper take on this I've seen is https://github.com/andywer/squid with https://github.com/andywer/postguard which, although the SQL is inlined in the code, it uses the right approach for verifying correctness as far as I could tell in the little time I experimented with it.

ActiveRecord seems great until it isn't--though I still think in SQL first and foremost. Common problems are (1) instantiation of many preloaded associated AR models that lead to memory pressure and GC time, (2) N+1 queries--yes you can preload, but there aren't easy ways of avoiding them on mutating a batch of AR models (validations and other callbacks on them don't batch).

Activerecord uses an enormous amount of resources that the databasr is way better equipped to handle, unfortunately.

There is also a mindset problem that's pretty serious in the ruby world, developers would use activerecord and load the universe, rather than running a select query carefully narrowed to the specific needs. on top of this, often there are entities that are the result of joining tables, but these are never surfaced with ORMs (activerecord specifically), since the concept of an entity from a query joined with multiple tables doesn't exist except in the limited fashion of a view.

Reply on news.ycombinator.com