Hacker News Reader

Comment by lmm | Hacker News Reader

If you half-ass using an ORM then you get the worst of both worlds. To get value out of it you have to embrace it completely: write all the queries in the ORM (so that the ORM's caching functionality etc. can actually work for you), define the schema in the ORM and generate the tables and migrations from that. Working with an ORM and avoiding SQL is not just doable, it's easy, but you have to actually try.

View on HN

How do you deal with complex reporting queries when embracing ORM completely?

I think the most important part is to create your database schema through your ORM. I used to do that ages ago with Ruby on Rails, and that worked very well. We still had our complex reporting queries in SQL, and that was no problem. The point is that if you use an ORM, your database needs to be optimised for that ORM. Bolting an ORM onto an existing database is a recipe for disaster.

I learned to use the Django ORM in depth, but there were some things it couldn't do at the time (it may have improved). Extra conditions on joins. Subqueries, conditional aggregates (it does those now). Once you get to a certain level of complexity you can shoehorn the query into the ORM,but I am not sure it makes sense. The next developer is more likely to know SQL well than the Djnago ORM.

Yeah, all of that stuff is much easier in Django now. I find myself dipping into raw SQL or stored procs far less often.

Either figure out how to express the queries you need within your ORM, or create a place where you can run ad-hoc SQL reporting queries that's completely segregated from your live data - e.g. a read-only replica, or regular database dumps.

It depends. ORMs are for dealing with the objects and schema, not with aggregate reports. I still use raw SQL for that. But more often you can just project your data into some BI system and let the users click together their reports themselves.

Reply on news.ycombinator.com