Hacker News Reader

Comment by halayli | Hacker News Reader

constructing queries in python is one of the disadvantages of using ORM. It takes away all the sql power that a DB can give you. Not to mention the magic that goes behind constructing the query that could be written in a more optimized way.

.join.filter.like.order_by etc.. is crazy imo. It's often more complicated than that and doing it in a non-sql way is suboptimal/unmaintainable.

View on HN

>"It's often more complicated than that and doing it in a non-sql way is suboptimal/unmaintainable."

It's also more composable, if you ask me. I like to think of it like "building" SQL statements using smaller components. Just like you do with code, you essentially compose a complicated business process using smaller blocks.

It's beautiful in it's simplicity. Even if the syntax requires some extra work/learning.

This is such a huge advantage for the approach. It makes it so much easier to build a query with variable filters than using SQL text.

I've just been using this to my advantage on a system I'm working on.

I've created a bunch of base queries that map from my user account table to each different table that users have access too. I can then use these base queries as a starting point to make sure people don't access records that don't belong to them.

    bq = db.query(Obj).join(ParentA).join(ParentB).join(User).filter(User.id==bindparam('user_id'))

    q = bq.filter(other_rules).params(user_id=1)...

It's a really flexible system.

If you're referring to SQLa Core, it's still SQL. The AST is the same, but by building the SQL AST explicitly you prevent injection and get a degree of composability (with subqueries). IMHO it's the more maintainable way of writing SQL.

You can prevent injection by passing the parameters to cursor.execute(query, (params,)) as a tuple. You don't need to rely on ORM to do that for you.

subqueries are best expressed in sql directly. They can get complicated, especially when using the with clause.

I am not saying that all of what ORM offers is bad. But beside the very basics they should step away. The less they offer the better.

Out of interest, are you talking about SQLAlchemy, or ORMs in general?

    # a subquery
    demo_accounts = db.query(Account.id).join(Client).filter(Client.name=='Demo')
    
    
    # used inside a query
    print(db.query(Account.name).filter(Account.id.in_(demo_accounts)))
    
    SELECT account.name AS account_name
    FROM account
    WHERE account.id IN (SELECT account.id AS account_id
    FROM account JOIN client ON client.id = account.client_id
    WHERE client.name = :name_1)
    
    
    # or as a cte    
    da_as_cte = demo_accounts.cte()
    
    print(db.query(Account.name).join(da_as_cte, da_as_cte.c.id==Account.id))
    
    WITH anon_1 AS
    (SELECT account.id AS id
    FROM account JOIN client ON client.id = account.client_id
    WHERE client.name = :name_1)
     SELECT account.name AS account_name
    FROM account JOIN anon_1 ON anon_1.id = account.id

There are obviously much more complex cases, but SA tends to handle things in a pretty sane way. You can compose query segments (like above) and if you really need to go back to the raw sql, you can and still have the results mapped into your python objects.

I don't agree. For other ORMs maybe, but with SA I can get the queries coming out how I want. Also, there's not really that much magic going on – you've defined the relationships so you know how the joins will be constructed.

Also, you're neglecting all the other benefits you get. Things like the loading strategies and the unit of work tracking are extremely powerful abstractions.

I personally like the approach of JOOQ[1] you are basically writing SQL statements using Java and you still can make complex queries and at the time you get benefits of type checking offered by IDE (for example your IDE will actually catch that you're trying to compare column that is an integer with a string).

[1] http://www.jooq.org/

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.

I have seen both sides of teh argument.

ORM's win hands down over multiple insert and update queries. Complex queries are usually better in SQL.

Reply on news.ycombinator.com