For example: https://stackoverflow.com/questions/65596920/use-django-subq...
Composite types & triggers, for some other examples, are only (at least they are!) available via third-party plugin/'installed app's too. You cannot defer a Django unique 'constraint', because it happens to implement it as a unique index instead.
That's just the few that come to mind.
The useful bit is de/serialisation from 'model' <-> query/record, IMO. Everything else is just limitation.
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.
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.
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.
I just find it pointlessly different to SQL, not really saying it's worse.
The SQL would be `update table set thing = ? where id = ?`.
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.
Like many 9s of development are, it's brownfield anyway, so you're saying the ORM should only be used if one's prepared to make large schema changes?
Of course, as the other person said, you can always always always descend into raw SQL if you must or if it just saves you time.
However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
Sometimes I use sqla just for the connection pool and session handling and dump raw sql in it.
It's not raw sql vs orm, as usual it's use a spectrum with good use cases for each part of it, and you usually benefit from all of it. But orm have pareto value in my xp.
Ruby and Python in particular really missed the boat on this one. I feel like they cargo culted practices from Java rather than embrace the implications of their own langauge's flexibility.
Generally, I think what people call ORMs try to do too much, and there is an impedance mismatch on many levels. Objects and relations don't map; objects are statically-defined at program compilation time, and relations are the result of a dynamic query. I think the reason that ORMs work in dynamic languages is that there isn't a rigid set of objects defined by the program; rather they can come and go during execution. So the problem becomes a problem later -- static analysis, editor tooling, etc.
Lifecycles are also complicated. You can have your ORM return dumb objects that you modify and re-store, or they can be "smart" and updated the database automatically. The "dumb object" approach neglects database transactions (multiple writers could have already modified your cached copy), and the "smart" approach neglects the networked nature of your SQL server (you may need to retry, you may need to time out if the server goes down, etc.) by pretending that some server run by another company is actually your CPU's cache.
Finally, I think it's very optimistic to write an application to support more than one database backend, which seems to be everyone's dream. Many have tried. Few have succeeded. I chalk it up as actually being O(n) work to support n databases. MySQL has some quirk that breaks Postgres's assumptions. Even the humble SQLite is different enough that is hard work to support as a secondary / test-only implementation. (I realized this, I think, when I debugged some code that worked in tests, but not in production. The tests used SQLite and did something like "insert into foo (boolean_value=1); select * from foo where boolean_value='t'". In Postgres, the inserted object is selected. In SQLite, it's not! That, to me, was the end of ever supporting two databases without the explicit requirement to do so. I'd still run my Postgres tests against Postgres and my SQLite tests against SQLite, though.)
All in all, I don't get it. Start a transaction, scan into a struct, mutate the object, write it back to the database. Not much code is required. If your mutation operation is too long to run in a transaction (i.e., it reaches out to the network for something), then you really need a state machine, which you can easily implement in your relational data model.
I'd say the bigger hubris is trying to shove relational data into object graphs and the challenges and pain of ORMs is proof of that.
How so? I've never found this to be the case.