Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Don't use your ORM entities for everything – embrace the SQL

blackparrotlabs.io

Post reply on HN
Print view View original

41–48 of 48 posts

Re: Don't use your ORM entities for everything – embrace the SQL

#41

Post by zo1 »

jillesvangurp wrote: post #38
mixmastamyk wrote: post #21

Earlier quoted context omitted.

Maybe, but not sure you’re getting much from that trade. If you’re not querying often, then performance shouldn’t be an issue. Modern hardware/dbms can definitely handle a couple of joins without blinking. So you’ve mostly lost flexibility. Also having to redo later will wipe out the time savings of many, many simplifications. Not to mention doc and teaching reqs you’ve added to new devs.

I've fixed a few systems by simplifying their schemas. Over-engineering at the database level can lead to a lot of issues beyond just performance. The extra complexity causes a lot of overhead with technical debt, lots of ceremony around changes, etc. Performance issues are usually a good indicator of a team that is a bit out of their depth. Usually goes hand in hand with over engineered data models being mapped 1 to…

And I've fixed a few systems by properly engineering their schema on the database level.

Every single system I've encountered that had issues with scaling, performance, tech debt, etc have all been due to badly denormalized (and broken) database schemas, with no one understanding who did what why or what piece of code owns what part of the schema. You start getting arcane knowledge and little fiefdom silos and grumpy grey-beards with inflated savior complexes that are the "goto person" despite the whole thing being ridiculously simple.

Re: Don't use your ORM entities for everything – embrace the SQL

#42

Post by cqqxo4zV46cp »

gigatexal wrote: post #8
wuliwong wrote: post #4

I think the correct approach is to understand the SQL that your ORM is using. If you can't have it create the correct query than roll your own. I think most of the trouble people get in with ORMs is not taking the time to understand the SQL that it creates and using it incorrectly as a result.

Imo software engineers look to the ORM to abstract the declarative set-theory, blah blah of SQL back into the object model they’re familiar with. IMO that’s a dereliction of duty as a developer but it happens and ORMs have gotten pretty good of late. In the Java space the JOOQ team is doing really good work. In my space of Python SQLalchemy has been the dominate ORM of choice and I hated it but without putting out a…

Django’s ORM is also widely popular in the Python world. It’s just not standalone.

Re: Don't use your ORM entities for everything – embrace the SQL

#43

Post by Fire-Dragon-DoL »

lcnPylGDnU4H9OF wrote: post #16

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.

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.

Re: Don't use your ORM entities for everything – embrace the SQL

#44

Post by Wulfheart »

Spivak wrote: post #13

> Will you actually need to change between fundamentally different database technologies? Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing. I'm of the opinion that SQLite is the musl of the SQL world. By deciding th…

But then your tests and prod setup would differ.

Re: Don't use your ORM entities for everything – embrace the SQL

#45

Post by Spivak »

Wulfheart wrote: post #44
Spivak wrote: post #13

> Will you actually need to change between fundamentally different database technologies? Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing. I'm of the opinion that SQLite is the musl of the SQL world. By deciding th…

But then your tests and prod setup would differ.

Yes and no, we still run the tests against PG but it's done in CI instead of locally.

Re: Don't use your ORM entities for everything – embrace the SQL

#46

Post by mixmastamyk »

zo1 wrote: post #41
jillesvangurp wrote: post #38

Earlier quoted context omitted.

I've fixed a few systems by simplifying their schemas. Over-engineering at the database level can lead to a lot of issues beyond just performance. The extra complexity causes a lot of overhead with technical debt, lots of ceremony around changes, etc. Performance issues are usually a good indicator of a team that is a bit out of their depth. Usually goes hand in hand with over engineered data models being mapped 1 to…

And I've fixed a few systems by properly engineering their schema on the database level. Every single system I've encountered that had issues with scaling, performance, tech debt, etc have all been due to badly denormalized (and broken) database schemas, with no one understanding who did what why or what piece of code owns what part of the schema. You start getting arcane knowledge and little fiefdom silos and grumpy…

Indeed—never heard proper normalization described as "over engineering" before.

Re: Don't use your ORM entities for everything – embrace the SQL

#47

Post by dkersten »

mixedCase wrote: post #30
dkersten wrote: post #18

Earlier quoted context omitted.

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

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.

Re: Don't use your ORM entities for everything – embrace the SQL

#48

Post by mixedCase »

dkersten wrote: post #47
mixedCase wrote: post #30

Earlier quoted context omitted.

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

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.

Post reply on HN