4 ms·

Just to clarify on why people generally dislike ORMs (I think): at one point you find yourself struggling with the abstraction (the ORM) to get at some features

Just to clarify on why people generally dislike ORMs (I think): at one point you find yourself struggling with the abstraction (the ORM) to get at some features that the underlying database engine might allow, or even worse using the abstraction incorrectly, and creating highly inefficient queries. ORMs are often mixed with for loops to bad effect. They obviously have great value for CRUD applications however, and they prevent you from making mistakes that others have encountered (and fixed). That said, I really want to try out the Postgres + SQLAlchemy + Flask + Flask-Restless (as mentioned by others) combo. I usually end up writing my own queries

rch 11y ago
Concerns about the SQL that SQLAlchemy generates are usually referring to the most basic use of the ORM. The real strength of the framework is that you can build complex queries directly, yielding optimal SQL, without resorting to plain text manipulation. This approach is actually most helpful as applications grow beyond straightforward CRUD operations.
- X-Istence 11y ago
  Not only that, but you can write your optimized queries, and still use the ORM. So you can get the best of both worlds.
  - rch 11y ago
    Absolutely.
plumeria 11y ago
You can use raw queries for that. What kind of features are you referring to?
- hardwaresofton 11y ago
  X feature that exists in the period after dbms release and before SA support -- categorical ORM problem. Also, I'm fully aware of using raw queries in SA/most ORMs -- my point is that if you have to drop down to raw queries, you're paying (mindshare/complexity/concentration/project size/whatever) for an incomplete abstraction you just had to side-step. Just to make sure there's no hostilities -- I'm not saying "don't use ORMs", because that would be dumb, as they offer tremendous value (and no downsides except for maybe complexity, as you can easily write custom queries) -- I just want to point out that the argument against them still stands, so don't drink too much of the koolaid.
  - zzzeek 11y ago
    consider that the "rendering of a SELECT string" is only like 10% of what the ORM is doing for you when you create and run `session.query()`. There is: 1. mapping the columns in the SELECT statement to the object attributes 2. dealing with the execution, result set, unicode, type conversion, etc. etc. idiosyncrasies of the underlying database driver as well as the target database backend, 3. routing various parts of each result row not just to different attributes on the mapped class but also into related attributes and collections of other classes all at the same time (e.g. eager loading) 4. maintaining the unit of work / identity map semantics as these rows are loaded 5. presenting the result set as a hierarchical object structure rather than a straight list of rows.
aidos 11y ago
Regarding the for loops I guess you mean the 1+N problem? The great thing about SA is that you can just change your loading strategy at any point to fix it without having to touch any other code. q = db.query(P) # add this later to fix your issues # q = q.options(subqueryload('children')) p = q.filter(P.id==73) for c in p.children: # oops, loads of db queries here pass

rch 11y ago

Concerns about the SQL that SQLAlchemy generates are usually referring to the most basic use of the ORM. The real strength of the framework is that you can build complex queries directly, yielding optimal SQL, without resorting to plain text manipulation. This approach is actually most helpful as applications grow beyond straightforward CRUD operations.

X-Istence 11y ago
Not only that, but you can write your optimized queries, and still use the ORM. So you can get the best of both worlds.
- rch 11y ago
  Absolutely.

plumeria 11y ago

You can use raw queries for that. What kind of features are you referring to?

hardwaresofton 11y ago
X feature that exists in the period after dbms release and before SA support -- categorical ORM problem. Also, I'm fully aware of using raw queries in SA/most ORMs -- my point is that if you have to drop down to raw queries, you're paying (mindshare/complexity/concentration/project size/whatever) for an incomplete abstraction you just had to side-step. Just to make sure there's no hostilities -- I'm not saying "don't use ORMs", because that would be dumb, as they offer tremendous value (and no downsides except for maybe complexity, as you can easily write custom queries) -- I just want to point out that the argument against them still stands, so don't drink too much of the koolaid.
- zzzeek 11y ago
  consider that the "rendering of a SELECT string" is only like 10% of what the ORM is doing for you when you create and run `session.query()`. There is: 1. mapping the columns in the SELECT statement to the object attributes 2. dealing with the execution, result set, unicode, type conversion, etc. etc. idiosyncrasies of the underlying database driver as well as the target database backend, 3. routing various parts of each result row not just to different attributes on the mapped class but also into related attributes and collections of other classes all at the same time (e.g. eager loading) 4. maintaining the unit of work / identity map semantics as these rows are loaded 5. presenting the result set as a hierarchical object structure rather than a straight list of rows.

aidos 11y ago

Regarding the for loops I guess you mean the 1+N problem? The great thing about SA is that you can just change your loading strategy at any point to fix it without having to touch any other code. q = db.query(P) # add this later to fix your issues # q = q.options(subqueryload('children')) p = q.filter(P.id==73) for c in p.children: # oops, loads of db queries here pass