Comment by develatio | Hacker News Reader

Comment by develatio | Hacker News Reader

> Performance: the generated SQL is often badly optimized.

I can't but disagree completely with this statement. 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.

They will also know exactly what DB features to use to leverage extra features (for example, Django's F() functions).

Edit: I'm totally amused that the author didn't even mention the fact that ORMs will go ways to prevent you from shooting yourself in the foot (from a security stand point). ORMs have made SQLi so much less common than it used to be. Of course, there still might be a bug here and there which will lead to an SQLi, but it's like day and night compared to how it used to be before, when average Joe would just slap a bunch of queries in a "queries.php" and call it a day.

Edit 2:

> I think migrations should come first. You should write migrations as separate files

Why? Why would you want any of that? The entire point of having the ORM do its thing is so that you (the developer) can focus on the code itself (the models, in this case). Modifying the models to adapt them to the requested changes is something that you'll do anyways, as that's your job. Then the ORM generates the migrations between each modification you did in the code, which leads you to not having to think or even care about how to apply these changes to the database. Why would you want it any different? We've literally reached the point where the code itself can represent "what the database should look like", and the changes to that code represent (via migrations generated by the ORM) "how the database should be changed in order to adapt to the new code".

I literally never saw an ORM output anything approaching optimized.

It’s mostly.. adequate, which is fine. I guess. It depends.

Also have a bit of a history with these “magic migrations”. It doesn’t come close to “we have reached the point”, it’s more like “in some circumstances and under extremely strict conditions these features may provide some benefit and will keep working over the years”.

> 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.

> 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!

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

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

I think making schema changes via migrations would be explicit and should lead to it being predictable. Maybe the best of both worlds is the ORM spitting out what it thinks the migration file should be based on the database schema and the new model changes?

The migration process then runs the actions in the migration file. If it fails in new environments this is telling that something is different in that environment and needs to be investigated.

This is how it works. ORMs generate migration files automatically.

Reply on news.ycombinator.com