>But the problem wasn't that it couldn't be done with the ORM, but that the ORM code quickly became unreadable.

This is the problem with not using an ORM. If you cut it out and move everything to parameterized SQL queries the SLOC explodes which massively inhibits readability as well as introducing bugs.

If your issue with ORMs is just that you're familiar with SQL and you don't like how ORMs look then I think the issue is just about becoming more familiar with a decent ORM.

> If you cut it out and move everything to parameterized SQL queries the SLOC explodes

My experience has been the opposite: that raw SQL queries end up much shorter (and consequently more readable) than the equivalent ORM code. The exception to that is INSERT/UPDATE queries, where I do tend to use some kind of ORM/query builder. I have used both, and I prefer raw SQL for anything beyond very simple queries.

I have never seen an ORM in my life that didn't reduce the total amount of code written. Not even the Java monstrosities increased the SLOC.

Perhaps it depends what you're doing?

IMO:

    .where('column_a', '=', 'value1')
    .and(q => q.isNull('column_b').orWhere('column_b', '=', 'value2')))

is a lot less readable than:

    WHERE
       column_a = 'value1'
       AND (column_b IS NULL OR column_b = 'value2')

In Django that would be .filter(column_a='value1', Q(column_b__isnull=True)|Q(column_b='value2'))

And obviously you can use whatever indentations you like.

Ah, that's quite a bit nicer. You can't do that in JavaScript on two counts:

- No keyword arguments

- No operator overloading (so you can't override | to get the nice "or" syntax)

I actually messed it up a little because I'm not sure you can mix positional and kwargs in filter, and you definitely can't use kwargs first. Still, the idea is there.

In JS, theoretically you could design an API like

.where({column_a: 1}, Q(column_b__isnull=True).or({column_b: 2}))

Which really isn't bad IMO

It's been a bit since I've used Django, but I believe you can just swap the order so the kwarg comes last.

    filter(     Q(column_b__isnull=True)|Q(column_b='value2'),
      column_a='value1',
    )

Or just turn it into another Q

    .filter(Q(column_a='value1'), Q(column_b__isnull=True)|Q(column_b='value2'))

The docs show both of those as working examples, but Q supports using & for "and", and can be combined in any way - you could also do this:

  .filter(Q(column_a='value1') & (Q(column_b__isnull=True) | Q(column_b='value2')))

One of these my IDE can typecheck and apply code hightlighting, the other is just a blob of text.

On my case that would be SQL, as I use nice SQL aware IDEs for Oracle and SQL Server.

Separate chain for a different Django example, it only natively supports joins on explicit foreign keys, but because it has that extra information in the model the syntax for using it is extremely compressed. Let's say you have a "Dog" table with a foreign key to "Owner", and "Owner" has a foreign key to "City". Getting all the dogs that live in New York would be:

   Dog.objects.filter(owner__city__name = 'New York')

The double-underscore follows the foreign keys until the last one, which is a field on the last model.