Hacker News Reader

Comment by treenyc | Hacker News Reader

This is my approach to ORM.

Use ORM for: 1. INSERT, UPDATE, DETELE get one record

Use SQL/Stored Procedure for: SELECTs that involved more complex joins and queries.

So, if say, you were inserting a recipe into a db where you had 3 tables (IRL there'd be more, surely), recipe, ingredients, directions. Are you saying you'd use the ORM or the SQL?

I ue ORM's similary. I would basiclly go for the easiest to understand / maintain approach. I don't have much expereince with SQLAlchemy, but I have a pretty good idea of where its worth using SQ rather than the Django ORM. (Usually anything that involves complex joins or subqueries, or similar. "Normal" joins are usually better done in the ORM as it makes a lot more of the Django features available).

So, in my example, you might use the ORM, perform three inserts (one for each table) in a transaction. If any fail, rollback.

However, in the select, where'd you'd have to join all three tables, you might use SQL, yes?

I'm just curious how people are using ORMs vs SQL, so sorry for the questions.

Not the parent, but when I'm using SA (sqlalchemy) I tend to just use the ORM for everything. If I needed something to be super performant I might get closer to the raw SQL – though generally I find development so much faster when SA handles everything for me.

If you're using a different ORM then things are probably going to be different. I've worked with a few in various languages and SA is the only one I've found that allows me to express any complex query directly via the ORM. I've heard there are some edge cases where you need to drop down a layer but I haven't run into them yet.

I would use the ORM for basic queries, where the conditions were not too complex - its easier than join syntax. If you need anything like subqueries, or extra conditions on the join caluse it gets tricky (in the django ORM at least), and SQL is easier.

Right now I am working with a suboptimal database design, where I need two nested subqueries to get the most recent event in a related row. I changed them to joins then to (indexed) temporary tables, to try and improve MySQl's performance. It worked to a degree. I doubt that would be possible in an ORM as it is working at the databse level rather than a hgher abstraction.

Reply on news.ycombinator.com