4 ms·

> 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

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

develatio 3y ago
> 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/ 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/?from=olddocs#auto-incrementing-primary-keys 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/conflict_handling.html https://django-postgres-extra.readthedocs.io/en/master/confl...
matthewmacleod 3y ago
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!
hparadiz 3y ago
Laravel's Eloquent does upserts no problem. Please actually do some research.
victorbjorklund 3y ago
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

develatio 3y ago

> 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/ 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/?from=olddocs#auto-incrementing-primary-keys 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/conflict_handling.html https://django-postgres-extra.readthedocs.io/en/master/confl...

matthewmacleod 3y ago

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!

hparadiz 3y ago

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

victorbjorklund 3y ago

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