To ORM or Not to ORM

+1 to everything but the last bit. In my experience, the best compromise is something like Hibernate (or some other ORM) for the mapping and the really basic queries (find by ID, find by some random field, etc).

Possibly still hibernate for the really advanced crazy stuff, too, though this is more doubtful. I've had pretty good success in the past with Hibernate Geospatial and full text search, for example.

In between those two extremes, just use SQL. It can still be mapped pretty easily with Hibernate and isn't practically harder than SQL would be without Hibernate.

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.

For me, the best feature of the Django ORM is that it integrates with everything all the way to the frontend forms.

In fact, missing the database integration wouldn't even be that large loss. Sometimes I wonder if it isn't even holding the framework back.

One of the classic bikesheds.

I've wound up writing a low key system in, at the time, Scala, that did something to the effect of:

    class Select {
      fields: Either[List[String], All] 
      table: String
      wheres: List[Clauses]
      orderBy: Option[String]

      def render(): String
    }

The render() method generated SQL; the Select class allowed a _fairly typed_ input; with some evolution, you can get shared queries quite nicely.

This works _reasonably well_. Part of _why_ I went this route is that I tend to use Postgres in a very serious way: it's not just a fancy set of spreadsheets with FK linking: I have indexes, check constraints, enums, pg-specific types, triggers, etc. So being able to directly interact with the database gives you a rich control surface that ORMs tend to exclude you from.

By the way, this was an evolution in concept from a different database oriented system I wrote in Python. Both systems used Postgres rich types, the class/render system was implemented in Scala and was Very Nice.

Of course it does.

Not that a little detail like that would stop someone from complaining about something that they don't put in the effort to learn.