This thread is littered with people singing praise of Django's ORM. He is not the only one. I'm a huge proponent of it as well.

> Could you share some resources of using it properly for advanced use cases? Good cookbooks etc.

The official documentation covers advanced use cases. Stackoverflow will help you shape up more complicated queries.

> Mistype a property on a model instance - no problem, no error, it will just not get saved to the db and data will be lost.

That's a drawback of most dynamic languages, not a flaw of Django's ORM.

> Need to write complex hand-coded-sql queries that return complicated data, but you's still want to be able to serialize them into a graph of objects that can then be modified by regular ORM code - good luck with that, nobody cared about this scenario.

Maybe that's the threshold Django ORM devs thought sensible to stop supporting? At some point, mixing hand-coded-SQL and ORM code introduce a whole set of hard questions and decisions. Very few people expect ORM code to properly play with hand-coded-SQL. If you need to switch back to SQL, you are generally on your own.

> They've somehow managed to make it too simple and too complex at the same time! Oh, and don't expect to just click a few jump to definitions an make sense of the ORM's inner code easily, god forbid :|...

It's simple to use, yet have complex internals. That's about what I would expect from a tool abstracting something complicated.

>> Mistype a property on a model instance - no problem, no error, it will just not get saved to the db and data will be lost.

> That's a drawback of most dynamic languages, not a flaw of Django's ORM.

In Python, mistyping a name usually results in an exception at runtime. If Django doesn't check models for correctness, it can't be explained by being written in a dynamic language.

In Python, mistyping a property of an object doesn't result in an exception. JS have the same behavior.

If you mistype a property on a nullabe or already filled property of a model instance, Django will happily save the model with the mistyped property (which will be ignored because it's not part of the model) without actually modifying anything in database.

Calling that behavior a flaw of Django's ORM is disingenuous.

> In Python, mistyping a property of an object doesn't result in an exception.

Sure it does. It doesn't on assignment, as you can modify the object however you like, but it does have AttributeError for accessing a property that doesn't exist.

Actually it does on asignments to `__slots__`-based classes.

Which you can only use compile time - not dynamically. That said I recall the sqlalchemy orm reject fields that it did not recognize. A decent IDE (pycharm) will hilight these for you on the Django orm. Having now used both the Django orm and SQLAlchemy extensively I prefer SQLAlchemy a lot. The Django ORM is usable, but many of its behaviours are odd and I agree that writing more complex queries gets ugly fast, unlike with SQLA where you can write some really hairy sql in a composable manner that makes it just so much more legible.

There is no such distinction between compile time and run time in Python; everything can be done dynamically. Want to use a class with slots computed at runtime? No problem:

  >>> def with_slots(**kwargs):
  ...   class SlottedClass(object):
  ...     __slots__ = kwargs.keys()
  ...     def __init__(self, **kwargs):
  ...       for k, v in kwargs.items():
  ...         setattr(self, k, v)
  ...   return SlottedClass(**kwargs)
  ... 
  >>> with_slots(foo=1, bar='qux').zork = 4
  Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
  AttributeError: 'SlottedClass' object has no attribute 'zork'

Of course there are many improvements that could be made to this approach (e.g. not creating a new class for each instance, or setting the __name__ to something meaningful) but I hope it shows that dynamic typing and robust error checking aren't contradictory.

They were talking about assignment.

If you misspell the property when setting the value, there is no error.

Again, this can be caught by eye, by testing and probably soon by static type checkers.

i mean if you try to access undefined attribute in model it will throw an exception

> ...you're the ONLY person I saw with a good opinion of Django's ORM. I'm willing to accept you could be right in preferring it,

No, he's not. Almost the entire Django community has a good opinion of it.

The main criticisms I see of Django's ORM seems to be from people that prefer SQLAlchemy. They have different design philosophys and the distinction seems largely a matter of taste and differing use-cases.

> main criticisms I see of Django's ORM seems to be from people that prefer SQLAlchemy

...then I'm in the minority of hating them both, and in the process of looking for an alternative since I'd be using a lot of python too for the foreseable future :|

I liked web.py's database abstraction layer a long time ago - know of anything similar in philosophy but more powerful?

Massimo still maintans it at https://github.com/web2py/pydal and he's working on py4web at https://github.com/web2py/py4web.

DAL was the first Python db abstraction I used, and I liked it well enough, but I've since used both Django and SQLAlchemy, and they both seem less opaque to me, maybe because not obscured by some of the weird things about web2py itself.

I love the auto-generated backwards references you get in Django and SA when you use foreign key and many to many fields. They make life so much easier. I don't recall how that was achieved in DAL, I just remember it being... complicated.

Years ago I had the same discussion [0] on Medium and here is my take on that: You will end up reinventing "an ORM" because your SQL won't handle all the edges cases. At least with eg. Django ORM we know it helps avoid some security flaws.

[0]: https://medium.com/@iMitwe/been-working-with-django-for-over...

thanks for the reference, especially to the Doordash's original article "Tips for Building High-Quality Django Apps at Scale" that was related to... I really like their thinking and think they are solving for the same problems, really like their idea of abstracting/wrapping over the ORM instead of using signals, and I'm looking for a similar architecture for a "default" Django boilerplate, featuring:

- data models in separate files one for each model, under a data_models/

- business logic models that may or may not map one to one with data models under a models/ that would content roughly framework-independent code

- encourage "fat apps"

- figure out some way to let the db be the source of truth and allow "multiple services, one DB" scenarios - maybe having "core models" for which all db migrations are excluded, table names fixe, and possibly extra columns that the django app could ignore but other services could use, and "app/project models" for which regular django migrations would run

I might write it up after I get the boilerplate app, and I'm really interested in hearing opinions for people with opposing viewpoints especially!

Migrations and integration databases don't mix. One works in a code-first paradigm, the other is database-first.

...of course they don't: I was referring to having a sane standardized way of having each app work with two dbs, one that it owns and includes some models of no interest to other apps, a different one that it shares with other apps and is used by other models (and has migrations turned off for these models, ofc).

The main point is that it can result way less code, think of all the APIs between microservices that would never need to be developed or documented or maintained because they'll never exist, yet the problem they would've solved will be addressed :)

Doesn't work all the time, but if most of what you need is data-sharing, and there's a clear model of "this service produces this data, which has a clear documented fixed format, all others just consume it", you'll prevent thousands of LOCs from being written. And I do see my mission more and more as preventing (useless) code from being written nowadays...

In Django we can have multiple databases using multiple backends and either the same, different, or overlapping ORM models.

Indeed. I recently worked on a legacy project where I don't have the luxury to modify the old database. I ended up using Peewee [0] to access the old database, in conjunction with the normal Django based database and even wrote a simple model that use Peewee [1]. Everything works, even Django Rest Framework associated with the model.

[0]: http://docs.peewee-orm.com/en/latest/ [1]: https://stackoverflow.com/questions/57602206/mysql-gone-away...

Django's ORM is fine. I would say that it's good but not particularly powerful.

Its easy to use and makes inserting / updating data fast and easy. It's simple and fast for simple queries. It starts getting a bit crap when you need complex queries. Annotations / GROUP BY stuff isn't intuitive.

It does make it easy for inexperienced people to generate huge numbers of queries by looping through querysets and getting field from another table.

It lets you run raw SQL through it and prevents SQL injection, so its really a case of knowing when to switch to that.

Where did I say I was concatenating data? You might want to take a look at Django's ORM before making comments like that, it handles parametrised queries fine.

The way I see it, an ORM is just a way of formalising the false assumption that a database is stupid and useless.

I've taken a look at a couple of Django's most popular ORM options and I suppose if I was a mid-level corporate cog writing CRUD interfaces for Stack Overflow tutorial-grade operations like customer-sale-item-product relationships it would probably work fine. I've only had passing exposure to that kind of soul-crushing drudge work.

For the way I work an ORM is baby stuff, and I'm not even doing anything particularly advanced or sophisticated. I maintain (among other things) a relatively unremarkable bespoke online discussion forum. It comprises hundreds of queries, few of which could be composed correctly by an ORM, let alone composed and run performantly. The median complexity query in my code base probably has two or three joins and two or three subqueries—at least one of which has some kind of tangential aggregation or window function.

> For the way I work an ORM is baby stuff

https://www.reddit.com/r/iamverysmart/

> a relatively unremarkable bespoke online discussion forum.

>It comprises has hundreds of queries

Sounds like you have a design issue. Making the unremarkable complex.

Thanks for the well deserved lesson in internet arguments. You win, sir.

(For the record, complexity and remarkability aren't strong correlates. And it's not particularly complex; probably 90% of the app's queries are written for indirect tasks like moderation tools, automations, statistical reports and various stratum of anti-abuse mechanisms. Most common pages only have one security-related query and one content-related query.)

> progres native json and array fields - good luck running your tests on sqlite

you're not testing what you actually run in production, then what are you testing? If you just need to test in-mem behaviour, mock the DB completely; instead of expecting compatibility for vendor specific features across vendors.

For database testing, I often use a "ramdisk", or in-memory filesystem: sudo mount -t ramfs -o size=512m ramfs /path/to/mount/point, initdb, pg_ctl start. (There's a bit more bookkeeping to generate passwords, store them to /path/to/mount/point/.pgpass, etc.) You can tear it down afterwards with pg_ctl stop, sudo umount /path/to/mount/point. I'll put the necessary commands in start / stop scripts, then call those from within the beforeAll / afterAll hooks of whatever testing framework I'm using.

It's definitely slower than mocking, and therefore not really suitable for unit tests - but it's great for repeatable, isolated integration tests (continuous or otherwise).

Sounds like a job for docker or CI systems.

Hmm... can one test use in-mem db mocking with django.contrib.postgres fields?

I'll definitely look into this, not a solution for existing projects bc of legacy baggage, but probably the right way to do it. Problem in practice is that you yes what you really want is to mock the db completely, but practically speaking existing code is to entangled with the db adapter specifics, so you end up with running with a "fake real db" :|

Can you write the tests so that you create object instances and not save them to a database at all? You don't need to test the ORM and database, after all, just your code.

The closer you keep things to production, the more likely you are to catch errors that will occur on production.

unit tests, yeah, but if you try to get "the most bang for the buck" in like "test a lot with zero/little effort" you'll end up with a layer of "integration tests": that (1) set stuff in db, (2) do stuff, (3) read stuff from db and assert its correctness... A sane ORM would not actively hinder this scenario even cross-dbs or with memory-mocked-dbs, but Django's does!

I don't see the issue - you're asking it to support a feature of one database on another, something it never claims to do. You need to use the same database for testing that you use in production.

I'd say the sqlite backend and using it in testing is mostly useful for reusable django apps that are supposed to be used in a wide variety of projects.

Most of the code I write in Django would never need to be SQLite-compatible, even if a lot of it would be. The SQLite compatibility is almost like a relict from a time when setting up RDBMSs was difficult and slow.

> Most of the code I write in Django would never need to be SQLite-compatible,

Then don't run your unit tests against SQLite, run them against Postgres.

If so, don't use things specifically in the Postgres contrib package of Django? The package is built that way for a reason

I don't know if there is already some plugin to make mypy aware of Django models, but that should be possible and would avoid bugs where you misspelled a property and it slipped through your quality control and unit-test.

I suspect "raw sql" to be a bigger headache and source of bugs in any moderately complex project.

Most of the use-cases of queries are actually very simple, and there the ORM allows you to simplify the code a lot.

My hints how to enjoy the ORM more: Write custom managers and QuerySets (for example for commonly used filters), write generic views that take QuerySets, take advantage of Models and QuerySet in Form Validation and REST APIs.

If you use the Django ORM like you would concatenate raw SQL, then it ain't gonna be pretty...

> Most of the use-cases of queries are actually very simple

I write web applications / data tools with raw SQL and I find that only about a quarter of the queries are simple. Many have subtle specificity to the joins, subqueries and window functions that return for me almost exactly what I want to output.

The problem I see in most people's ORM-powered code is that they lean so heavily on application code to get all the data assembled in the right place and in the right order, resulting in scaling problems

> concatenate raw SQL

Often dangerous and almost never needed. Parameterised SQL is the right approach.

> If you use the Django ORM like you would concatenate raw SQL, then it ain't gonna be pretty...

Depends on what exactly you mean here. The Django ORM fully supports some things you wouldn't initially expect if you hadn't seen it before, like passing one queryset into the filter args of another. It'll actually compose the queries and run it once on the database, instead of running the first and passing the result into the second.

> Mistype a property on a model instance - no problem, no error, it will just not get saved to the db and data will be lost.

Test your code. Use an IDE. PyCharm highlights these errors immediately.

> Use progres native json and array fields - good luck running your tests on sqlite,

You're using stuff from the Postgres contrib package on a different db. It's pretty clear in the docs about that

> Need to write complex hand-coded-sql queries that return complicated data, but you's still want to be able to serialize them into a graph of objects that can then be modified by regular ORM code

Make a view and manage it through the ORM? If you're updating data from this, then what you're doing seems really strange.

I'll leave the other parts since sibling comments responded to those, but:

> Need to write complex hand-coded-sql queries that return complicated data, but you's still want to be able to serialize them into a graph of objects that can then be modified by regular ORM code - good luck with that, nobody cared about this scenario.

It's been built-in since at least 1.7, and I think I remember it since 1.4: https://docs.djangoproject.com/en/2.2/topics/db/sql/

I am the other person with an excellent opinion of Django's ORM. I vastly prefer it to writing SQL queries manually, and the only thing I miss is making it ever more functional-like.

I'll chime in as someone who has used Django and its ORM for about 5 years and I have to say it's a pleasure to use.