7 ms·

For the SO question, why do you need to represent that in the ORM? It seems like you've got the SQL, and most ORMs have an escape hatch to just run SQL. Does Dj

by notJim 5y ago

For the SO question, why do you need to represent that in the ORM? It seems like you've got the SQL, and most ORMs have an escape hatch to just run SQL. Does Django not?

gabereiser 5y ago
Bingo. This. If you’ve broken your relational model then go ahead and use raw sql with your orm. They all support a way of executing raw sql against your data. This is the exception, not the norm.
- OJFord 5y ago
  I just don't see the value in using one for the trivial stuff (select from join where); the intermediate stuff is longer, more verbose, more DSLy (functions, aggregates, groups); and anything rarer/more 'advanced' is just missing. The useful bit is de/serialisation from 'model' <-> query/record, IMO. Everything else is just limitation.
  - gabereiser 5y ago
    If you are putting business logic in your database, you are doing it wrong. I get your point. Which is why I said it’s sometimes ok to run with raw sql with your orm. The problem is your database is really great at storing and retrieving data. Keep it doing what it’s good at and do your aggregate work on a separate machine where you have cpu cycles to spare. This is the point. I’ve told people before about hibernate and it’s foreign key constraints on models - “sometimes is best to not define the relationship at the model and just make another trip to the database to load”. Heavily utilized databases are a bottleneck to your systems, stop making them do the work. Do it on the edge.
  - notJim 5y ago
    I think I feel the opposite to you. I want the ORM to handle the trivial stuff like SELECT * FROM table WHERE id=? and UPDATE table SET thing=? WHERE id=?. That's the lion's share of what I'm doing in an app, and it's pointless to type it out with slight variations repeatedly. On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with.
    - OJFord 5y ago
      > I want the ORM to handle the trivial stuff like SELECT * FROM table WHERE id=? and UPDATE table SET thing=? WHERE id=?. Why are list(Table.objects.filter(id=?)) and Table.objects.filter(id=?).update(thing=?) better? That's what I mean by the trivial, it's 'handled', sure, with some pointless (IMO) thin wrapper that's Django-specific to learn. (Sure it's not loads to learn, but it has its quirks. I always have to check the meaning of the 'defaults' kwarg to update it create for example.) > On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with. Yes, I agree! I just think the trivial wrapper stuff isn't valuable, and especially if I'm using raw SQL for less trivial stuff, I may as well always do so. I think my comment above might've sounded ambiguously like maybe I wanted DSL for more complex stuff. That's not what I meant - I meant that it tends to be more complex in the DSL than in SQL, up to the point where it's just impossible in the DSL, and you either use SQL or don't do it.
      - notJim 5y ago
        > Why are list(Table.objects.filter(id=?)) and Table.objects.filter(id=?).update(thing=?) better? I'm not familiar with SQLAlchemy, but with other ORMs, this isn't how you'd write this. It would be more like Widgets.findOne(id) and then widget.property = bla; widget.save(); For me this is much more natural than writing SQL, especially for the update query. There are usually also query helpers for simple queries by fields, like Table.findWhere({property: 'value', property2: 'value2'}). To me this syntax is much less awkward than writing SQL in strings, and it saves you from repeatedly writing out the SELECT statements and where clauses.
        OJFord 5y ago
        That was Django, not SQLAlchemy. You can also do it more as you describe, `get(pk=id)` instead of `findOne`. I just find it pointlessly different to SQL, not really saying it's worse. The SQL would be `update table set thing = ? where id = ?`.
OJFord 5y ago
I responded to the same point in a comment there at the time iirc. No reason other than to be friendly/idiomatic to a project and colleagues that's largely Django. We've used more and more SQL though, for things Django doesn't support, such as (materialised) views. (You can use models with managed=False, but not have it manage their DDL & schema migrations.) I still try to use Django where possible and especially if it's auxiliary to a Django-managed table though.

by notJim 5y ago

For the SO question, why do you need to represent that in the ORM? It seems like you've got the SQL, and most ORMs have an escape hatch to just run SQL. Does Django not?

gabereiser 5y ago

Bingo. This. If you’ve broken your relational model then go ahead and use raw sql with your orm. They all support a way of executing raw sql against your data. This is the exception, not the norm.

OJFord 5y ago
I just don't see the value in using one for the trivial stuff (select from join where); the intermediate stuff is longer, more verbose, more DSLy (functions, aggregates, groups); and anything rarer/more 'advanced' is just missing. The useful bit is de/serialisation from 'model' <-> query/record, IMO. Everything else is just limitation.
- gabereiser 5y ago
  If you are putting business logic in your database, you are doing it wrong. I get your point. Which is why I said it’s sometimes ok to run with raw sql with your orm. The problem is your database is really great at storing and retrieving data. Keep it doing what it’s good at and do your aggregate work on a separate machine where you have cpu cycles to spare. This is the point. I’ve told people before about hibernate and it’s foreign key constraints on models - “sometimes is best to not define the relationship at the model and just make another trip to the database to load”. Heavily utilized databases are a bottleneck to your systems, stop making them do the work. Do it on the edge.
- notJim 5y ago
  I think I feel the opposite to you. I want the ORM to handle the trivial stuff like SELECT * FROM table WHERE id=? and UPDATE table SET thing=? WHERE id=?. That's the lion's share of what I'm doing in an app, and it's pointless to type it out with slight variations repeatedly. On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with.
  - OJFord 5y ago
    > I want the ORM to handle the trivial stuff like SELECT * FROM table WHERE id=? and UPDATE table SET thing=? WHERE id=?. Why are list(Table.objects.filter(id=?)) and Table.objects.filter(id=?).update(thing=?) better? That's what I mean by the trivial, it's 'handled', sure, with some pointless (IMO) thin wrapper that's Django-specific to learn. (Sure it's not loads to learn, but it has its quirks. I always have to check the meaning of the 'defaults' kwarg to update it create for example.) > On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with. Yes, I agree! I just think the trivial wrapper stuff isn't valuable, and especially if I'm using raw SQL for less trivial stuff, I may as well always do so. I think my comment above might've sounded ambiguously like maybe I wanted DSL for more complex stuff. That's not what I meant - I meant that it tends to be more complex in the DSL than in SQL, up to the point where it's just impossible in the DSL, and you either use SQL or don't do it.
    - notJim 5y ago
      > Why are list(Table.objects.filter(id=?)) and Table.objects.filter(id=?).update(thing=?) better? I'm not familiar with SQLAlchemy, but with other ORMs, this isn't how you'd write this. It would be more like Widgets.findOne(id) and then widget.property = bla; widget.save(); For me this is much more natural than writing SQL, especially for the update query. There are usually also query helpers for simple queries by fields, like Table.findWhere({property: 'value', property2: 'value2'}). To me this syntax is much less awkward than writing SQL in strings, and it saves you from repeatedly writing out the SELECT statements and where clauses.
      - OJFord 5y ago
        That was Django, not SQLAlchemy. You can also do it more as you describe, `get(pk=id)` instead of `findOne`. I just find it pointlessly different to SQL, not really saying it's worse. The SQL would be `update table set thing = ? where id = ?`.

OJFord 5y ago

I responded to the same point in a comment there at the time iirc. No reason other than to be friendly/idiomatic to a project and colleagues that's largely Django. We've used more and more SQL though, for things Django doesn't support, such as (materialised) views. (You can use models with managed=False, but not have it manage their DDL & schema migrations.) I still try to use Django where possible and especially if it's auxiliary to a Django-managed table though.