4 ms·

> Oso’s Django and SQLAlchemy integrations turn partials from Polar into database queries... The SQL they produce relies heavily on nested subqueries Live by O

> Oso’s Django and SQLAlchemy integrations turn partials from Polar into database queries... The SQL they produce relies heavily on nested subqueries Live by ORM, die by ORM. This strikes me as particularly bad because these authorization queries may be running on every request. It's great to see Oso went direct to SQL to address this. And the asides about logical programming were fun as well.

gfody 5y ago
agree this seems like the worst of both worlds between doing things application-side and doing things database-side. imo you're much better off embracing your db's security features
throw_m239339 5y ago
Well (most) ORM often cannot take advantage of CTE, group concat, array types, json types and what not when building queries from code, for obvious reason. So they often can't return multi-dimensional data in a single query. But ORM are still useful to get an application off the ground fast.
- gfody 5y ago
  do ORMs still target lowest common denominator ansi sql? I only know that EF doesn't - it has "providers" for each rdbms it supports and will liberally emit CTEs and db specific language features like "outer apply" or "join lateral" but then stupidly ignore powerful features like "for json" or "json_agg/json_build_object" which could eliminate the outrageously naive way it encodes 1:m and m:m results - it boggles how smart and stupid orms are at the same time.
  - jiggawatts 5y ago
    JSON is too “lossy” to use automatically.
- aidos 5y ago
  To be clear, sqlalchemy does support those features.
aidos 5y ago
But you can build any query with Sqlalchemy - even when using the ORM rather than the Core, you still have that control, right? Don’t get me wrong, I’ve read stuff by these guys and I know they know what they’re talking about. Certainly, if you have to make it work for Django as well you’d be considering going straight to sql. As and aside, I walked through their stuff a little while back and I’m very interested in what they’re building.
airstrike 5y ago
Building bad queries with an ORM isn't necessarily the ORM engine's fault. It's really just a tool and, as such, its value really relies on one's proficiency with it. One simple but illustrative example: https://docs.djangoproject.com/en/3.2/ref/models/querysets/#prefetch-related https://docs.djangoproject.com/en/3.2/ref/models/querysets/#...

gfody 5y ago

agree this seems like the worst of both worlds between doing things application-side and doing things database-side. imo you're much better off embracing your db's security features

throw_m239339 5y ago

Well (most) ORM often cannot take advantage of CTE, group concat, array types, json types and what not when building queries from code, for obvious reason. So they often can't return multi-dimensional data in a single query. But ORM are still useful to get an application off the ground fast.

gfody 5y ago
do ORMs still target lowest common denominator ansi sql? I only know that EF doesn't - it has "providers" for each rdbms it supports and will liberally emit CTEs and db specific language features like "outer apply" or "join lateral" but then stupidly ignore powerful features like "for json" or "json_agg/json_build_object" which could eliminate the outrageously naive way it encodes 1:m and m:m results - it boggles how smart and stupid orms are at the same time.
- jiggawatts 5y ago
  JSON is too “lossy” to use automatically.
aidos 5y ago
To be clear, sqlalchemy does support those features.

aidos 5y ago

But you can build any query with Sqlalchemy - even when using the ORM rather than the Core, you still have that control, right? Don’t get me wrong, I’ve read stuff by these guys and I know they know what they’re talking about. Certainly, if you have to make it work for Django as well you’d be considering going straight to sql. As and aside, I walked through their stuff a little while back and I’m very interested in what they’re building.

airstrike 5y ago

Building bad queries with an ORM isn't necessarily the ORM engine's fault. It's really just a tool and, as such, its value really relies on one's proficiency with it. One simple but illustrative example: https://docs.djangoproject.com/en/3.2/ref/models/querysets/#prefetch-related https://docs.djangoproject.com/en/3.2/ref/models/querysets/#...