Hacker News Reader

Comment by oweiler | Hacker News Reader

> There might be an edge case in which a raw query, tailored to a specific need or a particular use case, might be faster, but generally speaking ORMs will know much much better than the average developer how to optimise queries.

This statement is wrong. ORMs can't use DB specific features, so they have to generate lowest common denominator SQL.

With Postgres e.g. I can insert an entry and return the generated ID in a single query. ORMs can't do that.

I can also upsert entries in a single statement. ORMs can't do that.

View on HN

> ORMs can't use DB specific features

This is technically false. There is no technical reason an ORM couldn't use DB-specific features. It's also practically false. Have a look at https://docs.djangoproject.com/en/4.2/ref/databases/

Django's ORM will do quite clever stuff, specific to your database (eg, if you're using Postgres, it will create additional indexes using appropriate PSQL operator classes).

> I can insert an entry and return the generated ID in a single query. ORMs can't do that

This is also false. Calling .save() on a model will issue an INSERT statement, then the ORM will assign the auto-incremented PK to the "id" property of the model without issuing any further queries. https://docs.djangoproject.com/en/dev/ref/models/instances/?...

> I can also upsert entries in a single statement. ORMs can't do that.

This is also technically and practically false. There is no technical reason an ORM couldn't do an upsert with a single query (granted that the database itself supports it). Practically, while Django's ORM itself can't do this, you could easily use django-postgres-extras (which is just an extension to the ORM's capabilities) to issue an "ON CONFLICT target", which will achieve an upsert with a single query: https://django-postgres-extra.readthedocs.io/en/master/confl...

This is absolutely not the case. In fact I've never used an ORM myself that generates SQL directly – they all generate some kind of abstract model then render that to SQL using a database-specific adapter.

Specifically, Rails' ActiveRecord will transform e.g.

  Customer.create(name: 'test')

into the following when using Postgres

  INSERT INTO "customers" ("name") VALUES ('test') RETURNING "id";

Things like

  Customer.upsert_all([{name: 'test'}])

will also work to perform upserts.

In practice, ORMs are more advanced than you might think, and can be a useful tool in some cases!

Of course they can use DB specific features. I use ecto (elixir) and there you use it with an adapter for the specific db you want. Does is have all the million different specific features? No, but the common ones. Do you need more? Many extensions exist, you can write your own extension of ecto or you could just use "fragment()" and inject raw sql in the middle of a query

Laravel's Eloquent does upserts no problem. Please actually do some research.

Reply on news.ycombinator.com