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

Does OLAP Need an ORM

clickhouse.com

Post reply on HN
Print view View original

41–50 of 52 posts

Re: Does OLAP Need an ORM

#41

Post by halfcat »

ram_rar wrote: post #24

Unpopular opinion: in 2025, nobody should be reaching for an ORM first. They’re an anti-pattern at this point. The “abstraction” it promises rarely delivers—what you actually get is leaky, slow, and a nightmare to operate at scale. The sane middle ground is libraries that give you nicer ergonomics around SQL without hiding it (like Golangs sqlx https://github.com/jmoiron/sqlx ). Engineers should be writing SQL, perio…

> Engineers should be writing SQL, period.

As another commenter wrote:

”If you're doing OLAP…SQL isn't wholly adequate for this, it's hard work to get the SQL right and if there's joins involved it's not hard to accidentally fan out and start double counting.”

I feel this in my bones. Anytime someone in the business changes something in a source system, the joins create extra rows. Trying to address this with SQL is like plowing a field with a spoon.

And I don’t think ORMs are the answer. Just imperative code. The ability to throw in a few lines of sanity checking outside of SQL is such a massive boost to reliability and data quality, when the source systems are moving underneath your feet.

Re: Does OLAP Need an ORM

#42

Post by halfcat »

primering wrote: post #34
sgarland wrote: post #31

Earlier quoted context omitted.

Mapping a DB response to a Pydantic model is hardly an ORM.

No, that is exactly what an ORM is, plus mapping it back. Anything around that is additional toolings that no ORM needs to be ORM, but is nonetheless usefull.

Pydantic isn’t an ORM, any more than JSON.stringify() and JSON.parse() are an ORM.

Pydantic knows nothing of your database. It’s schema-on-read (a great pattern that pydantic is well suited for), or serialization, or validation, but not an ORM.

Re: Does OLAP Need an ORM

#43

Post by tucnak »

cyberax wrote: post #36
ram_rar wrote: post #24

Unpopular opinion: in 2025, nobody should be reaching for an ORM first. They’re an anti-pattern at this point. The “abstraction” it promises rarely delivers—what you actually get is leaky, slow, and a nightmare to operate at scale. The sane middle ground is libraries that give you nicer ergonomics around SQL without hiding it (like Golangs sqlx https://github.com/jmoiron/sqlx ). Engineers should be writing SQL, perio…

> Engineers should be writing SQL, period. Is it a variation of: "I suffered when I was young, so everyone must suffer as I did?" SQL is terrible, however you slice it. It's a verbose bass-ackwards language. ORMs that remove the need to deal with SQL for 99% of trivial cases are great. The rest 1% can remain painful.

Please stop spewing nonsense.

SQL remains the only way to efficiently perform MANY computations in the database precisely because it's lingua franca for the database planner. If you're not writing SQL, it doesn't mean that you're unable to cover 1% of the queries, it only means that you're leaving 99% of performance on the table. You can tell a bad programmer by their refusing to use views and materialized views. Not to mention normalisation! I'm yet to see a coder using ORM produce a sane schema. And don't get me started on aggregates. Relational databases represent relations, not objects, period.

Re: Does OLAP Need an ORM

#44

Post by odie5533 »

halfcat wrote: post #42
primering wrote: post #34

Earlier quoted context omitted.

No, that is exactly what an ORM is, plus mapping it back. Anything around that is additional toolings that no ORM needs to be ORM, but is nonetheless usefull.

Pydantic isn’t an ORM, any more than JSON.stringify() and JSON.parse() are an ORM. Pydantic knows nothing of your database. It’s schema-on-read (a great pattern that pydantic is well suited for), or serialization, or validation, but not an ORM.

Do you advise then use Pydantic for data mapping to/from raw SQL, to avoid using a full ORM? My thinking is you're almost at an ORM with that method with tools like SQLModel that I'm unsure what the benefit is to the plain Pydantic method.

Re: Does OLAP Need an ORM

#45

Post by cies »

lucisferre wrote: post #12
cies wrote: post #6

My take is... No one needs an ORM: https://dev.to/cies/the-case-against-orms-5bh4 The article opens with "ORMs have proven to be useful for many developers" -- I believe the opposite is true.

I agree with this as well. I started my career at the height of ORMs. Most software developers were only learning the ORM APIs (which of course all differed significantly) and very few were learning SQL outside of the bare basics. ORMs, like all abstractions, are a leaky abstraction. But I would argue because of the ubiquity and utility of SQL itself they are a very leaky one where eventually you are going to need to…

High five!

Re: Does OLAP Need an ORM

#46

Post by tucnak »

halfcat wrote: post #41
ram_rar wrote: post #24

Unpopular opinion: in 2025, nobody should be reaching for an ORM first. They’re an anti-pattern at this point. The “abstraction” it promises rarely delivers—what you actually get is leaky, slow, and a nightmare to operate at scale. The sane middle ground is libraries that give you nicer ergonomics around SQL without hiding it (like Golangs sqlx https://github.com/jmoiron/sqlx ). Engineers should be writing SQL, perio…

> Engineers should be writing SQL, period. As another commenter wrote: ”If you're doing OLAP…SQL isn't wholly adequate for this, it's hard work to get the SQL right and if there's joins involved it's not hard to accidentally fan out and start double counting.” I feel this in my bones. Anytime someone in the business changes something in a source system, the joins create extra rows. Trying to address this with SQL is…

In my experience, LATERAL is a massive quality-of-life improvement when it comes to double-counting specifically; where a normal subquery is not possible, a correlated subquery in FROM is much nicer than a CTE+JOIN.

Doubleagree on sanity checks.

Re: Does OLAP Need an ORM

#47

Post by halfcat »

odie5533 wrote: post #44
halfcat wrote: post #42

Earlier quoted context omitted.

Pydantic isn’t an ORM, any more than JSON.stringify() and JSON.parse() are an ORM. Pydantic knows nothing of your database. It’s schema-on-read (a great pattern that pydantic is well suited for), or serialization, or validation, but not an ORM.

Do you advise then use Pydantic for data mapping to/from raw SQL, to avoid using a full ORM? My thinking is you're almost at an ORM with that method with tools like SQLModel that I'm unsure what the benefit is to the plain Pydantic method.

In one sense you’re right, but (at least in data projects) the goal is a bit different. We’re often reading not only from SQL databases, but also parquet files, CSV, JSON, APIs, piped input from another process’s STDOUT, and so on.

Basically we don’t always know what the future unknown data source we may be reading from, and also the schema of the source might change, but we can define what we expect on the receiving end (in pydantic), and have it fail loudly when our assumptions change.

Re: Does OLAP Need an ORM

#48

Post by cyberax »

tucnak wrote: post #43
cyberax wrote: post #36

Earlier quoted context omitted.

> Engineers should be writing SQL, period. Is it a variation of: "I suffered when I was young, so everyone must suffer as I did?" SQL is terrible, however you slice it. It's a verbose bass-ackwards language. ORMs that remove the need to deal with SQL for 99% of trivial cases are great. The rest 1% can remain painful.

Please stop spewing nonsense. SQL remains the only way to efficiently perform MANY computations in the database precisely because it's lingua franca for the database planner. If you're not writing SQL, it doesn't mean that you're unable to cover 1% of the queries, it only means that you're leaving 99% of performance on the table. You can tell a bad programmer by their refusing to use views and materialized views. Not…

Please stop spewing nonsense.

> If you're not writing SQL, it doesn't mean that you're unable to cover 1% of the queries, it only means that you're leaving 99% of performance on the table.

Honestly? You're spewing bullshit. In most apps most of SQL is trivial. It's typically limited to simple joins (with indexes) with simple filters. Anything more complicated, and it's usually not suitable for OLTP. Heck, all our SQL is linted to not have full-table scans.

This kind of SQL is perfectly auto-generated by ORMs.

Those multi-page queries that required mystic DB knowledge for placing hints, burning incense, and paying $1000 per hour to Oracle consultants? They're entirely useless in modern software stacks. Because you can either keep the entire working set in RAM so these queries can be trivially rewritten, or you just won't use regular SQL for it.

> You can tell a bad programmer by their refusing to use views and materialized views.

You can tell a bad programmer by them using DB in a way that requires materialized views. It typically ends with moving app logic into SQL, and may even lead to "SELECT '' + row.cust_name + ''".

Re: Does OLAP Need an ORM

#49

Post by odie5533 »

halfcat wrote: post #47
odie5533 wrote: post #44

Earlier quoted context omitted.

Do you advise then use Pydantic for data mapping to/from raw SQL, to avoid using a full ORM? My thinking is you're almost at an ORM with that method with tools like SQLModel that I'm unsure what the benefit is to the plain Pydantic method.

In one sense you’re right, but (at least in data projects) the goal is a bit different. We’re often reading not only from SQL databases, but also parquet files, CSV, JSON, APIs, piped input from another process’s STDOUT, and so on. Basically we don’t always know what the future unknown data source we may be reading from, and also the schema of the source might change, but we can define what we expect on the receiving…

The nice thing about SQLModel is they're still Pydantic models so you can use them with custom data mappers like parquet, csv, json, etc. I think you make a good point about keeping the data model pure so you're not dependent on a data source. But I think SQLModel largely accomplishes that, and so does SQLAlchemy's declarative dataclass mapping (though I've not used the latter).

Re: Does OLAP Need an ORM

#50

Post by wredcoll »

caspper69 wrote: post #17
wredcoll wrote: post #8

Earlier quoted context omitted.

I think what I just really want is a language that treats sql as a "first class" component in the same way perl treats regexes. The devil is of course in the details, but it's a nice dream.

LINQ? Just throwing it out there; obviously not everybody can or wants to run a C#/.NET stack, but entity framework (core) is about as close as you can get to the perl and regex integration. I think Ruby on Rails gets there too, but I'm not a RoR guy, so I can't comment.

I think linq is the closest anyone has come so far, although it is a dsl with slightly different semantics as I understand.

ActiveRecord, RoR's ORM is the complete opposite.

Post reply on HN