I wrote an opinion on ORMs here in 2017: https://news.ycombinator.com/item?id=14661391

Most of the stuff I said still sticks for me (after reading article, and after using ORM this time).

I haven't programmed much golang. But with Python + Django I can use `./manage.py shell_plus` and can drop immediately into a shell with history, readline support, syntax highlighting, and tab completion, with all model objects in local scope.

I can then do lookups and annotations (Yes, they're expensive, not for production, but save lots of time during the day in a pinch):

Lookup:

    Library.objects.filter(books__author__name='Book author')

Annotation:

    Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5)

When the data model of a project gets bigger, writing queries by hand gets harder to think about. Perhaps its due to my own overreliance on ORM's: but I can't fathom how I'd manage without them. Because in practice the data models are far more complex than above, and it'd take a lot of time (not to mention mental energy that could be put to use elsewhere)

Also, there are ways around performance problems: For one, it may entail rejecting features offered by the ORM. Such as django's content types, multi-table inheritance to instead use abstract inheritance (basically just reusing common fields) and even plain one-to-one relations. They're just too complicated and slow at scale.

Same goes for extending the ORM with stuff like django-polymorphic (and django-model-utils). I've gotten nice performance out of django-polymorphic (and cleaner code), but it's hiding a ton of metaprogramming, there's still a penalty "upcasting" naive models, and they're very burdensome to maintain if APIs fall out of date.

Next is trimming down queries with prefetching, only(), and doing direct ID lookups. This prevents multiple queries from piling up by doing the join ahead of time, only getting fields asked for, and doing the fastest lookup for objects after a more expensive "filtering" query has ran.

Here's an example of the query earlier, with .only():

    Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).only('id')

And to print the SQL query out:

    print(Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).only('id').query)

That would then be used in something like:

    BookSet.objects.filter(book__in=Author.objects.annotate(total_books=Count('books')).filter(total_books__gt=5).values_list('id', flat=True))

And finally, for debugging certain types of query performance, django-debug-toolbar is nice. If its API calls django-silk can "look back" on background requests.

The article was fair IMO, but I'd wager ORM's payoff depends on the ecosystem. Software being about tradeoffs: the convenience outweighs the downsides everytime for me. Maybe I'm to be humbled and find I'm not getting the big picture, and it could be just plain learnt-dependence, but it'd be a step back in productivity for me not to have an ORM.